next »

[Gary C.]

Hi

I have been trying to get to grips with pivot tables and have been using both the static and dynamic samples as a reference.

For this exercise I have been looking at a report for use by both the finance and warehouse team - the finance team are interested in stock quantity and value by product whereas the warehouse team are more interested in stock quantity by warehouse location.

However, for some reason I seem to be unable to get any aggregate totals displayed at the relevant groupings. Instead I am getting a row in the report for each row of data sent, even though I have selected a subset of the available dimensions.

I have attached a screen shot of the report structure where you can see the dimensions and measures defined.

In addition, I have attached two spreadsheets:

pivot01 - this shows the results I get when I select the warehouse, area, location and product to group by with the stock on hand being totaled. You can see that there are duplicate entries and no aggregate totals.

pivot02 - this shows the results of using Excel to create a pivot table from the results and is more as I would expect. Here I have created the "data" worksheet via the 4gl and then manually added a pivot table in the second sheet and one can see the aggregate totals.

I am hoping that I am missing something obvious but as I have tried every option I can see I thought I would ask you chaps.

Many thanks

Gary

[Alex G.]

Hi Gary,
Did you try setting the “Display Fact Rows” attribute (attribute of the FACT element) to false?
If that doesn’t do the trick I suggest to send a GREDATAFILE and the .4rp to support and we have a look at it.
Regards,
Alex

[Gary C.]

Hi Alex

Yes, I have tried that option. If that option is set to false, then I get no rows at all, if set to true I get the fact rows. In both cases though, there are no sub totals.

I will send the data and 4rp to support who I am sure will immediately see what I am doing wrong.
Regards
Gary

[Gary C.]

Hi

So, it transpires that this is expected behaviour, i.e. when sending the output to excel, one is to use its pivot table capabilities so the fact rows are sent without any aggregation. When sending to other formats, the aggregation functions as expected.

That's fine and I can explain this to the user base. However, I have just one other issue I could do with some help on. When sending the output to Excel I am forcing the page size to be A3 landscape to ensure there is plenty of space for the columns. If there are many rows, I am getting the pivot table title and column headings repeated on the theoretical page break. I can suppress the title using RTL but cannot seem to see how to address the repeating titles.

One option would be I guess to set the page size to a large height value but I read on here I believe that this is not a recommended solution.

Thanks
Gary

[Alex G.]

Hi Gary,
I have entered an issue (GRE-601 "Suppress repetition of Pivot-Table table titles in Excel output when page merging is selected") and fixed the issue.
For the time being I see no other workaround than to do what you have already suggested, namely to increase the page length. And yes, that is indeed problematic because we free memory on a page basis so that a longer, single paged report could exhaust memory.
Kind regards,
Alex