Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone tell me how to achieve this?
I have a straight chart like this right now. 'Week' and 'Product' are dimensions while 'City' 'Type' and 'Orders' are expressions using SUM(field).
Week | Product | State | City | Type | Orders | |
---|---|---|---|---|---|---|
Week 1 | 1A | California | Fremont | Plastic | 40 | |
Week 1 | 1A | California | Fremont | Plastic | 50 | |
Week 2 | 1A | California | Fremont | Paper | 50 | |
Week 2 | 2B | Arizona | Phoenix | Paper | 100 | |
Week 2 | 2B | Arizona | Phoenix | Paper | 60 | |
Total | 300 |
Is there any way to show the the following without all the other information? Or would I need to create some other 'Totals' Table?
Week 1 Total for all plastic orders
Week | Product | State | City | Type | Orders |
---|---|---|---|---|---|
Week 1 | Plastic | 90 |
or
Week 1 from Fremont
Week | Product | State | City | Type | Orders |
---|---|---|---|---|---|
Week 1 | Fremont | 140 |
in your expected output as you show in your post, do you need to show the columns even though they have no info?
why not use list boxes for the columns that you don't need to show in your table and use the dimensions you need to show? so in your example, why not make have City and Type (and possibly Product and State as well) as list boxes and remove them from your straight table? if you want to then make it a pivot table, all you have to do is go to properties, go to the general tab and click on pivot table in the chart type and click ok.
ps: in your example, week 1 from Fremont table should be 90 and not 140.
You could try a pivot table chart with Week as the 1st dimension. For the 2nd dimension i suggest you create a new group by hitting the 'groups' button on the dimension tab. Add Type and City to the group and ensure its a cycle group. Add the new group as a 2nd dimension .
On the presentation tab, enable 'partial sums' on the group dimension.
it will give you subtotals and allow users to dynamically switch which field you are subtotalling.