Hello can anyone suggest the best method for calculating a summary tab from a list report tab? I created one query and then copied this into another page. I need to summarise this but was wondering do i need to show the list/columns if i have totals. It doesn't seem to show unless i have the full list and then totals at the bottom but what i want is for the totals to show in a table.
Many Thanks
Chris
Not sure I follow the question. Maybe you could sketch out the scenario a little?
If, for example, you have a query that contains product, year, and amount, you might have a list container that presents results like this:
Product Year Amount
------- ---- ------
A 2009 500
A 2010 600
A 2011 700
------
1800
======
B 2010 100
B 2011 200
------
300
You can use that same query to feed a different layout container and the results would just aggregate around whatever you have selected (if auto group and summarize is Yes). So a summary page with another list container but only referencing product and amount (without year) would give you the product totals.
Product Amount
------- ------
A 1800
B 300
This sort of sounds like what you described, but I'm not entirely sure -- sorry if I'm way off the mark here. I much prefer to be on the mark, but it doesn't always work out that way ;)
Thanks Lynn, for explaining this. Yes i think this is what i mean, it might be easier if i could send you the report. I basically want the summary as an extra tab in excel with a table which summarises the quantities.
Query1/List view report
Sales No Unit Type Manufacturer Make Screen Size CC Customer Code
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 MON FUJITSU-SIEMENS LCD MONITOR 17 DWPREMP03
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
4326044 BU HEWLETT PACKARD DC7900 DWPREMP07
Query2/Summary
Summary - Redeploy Order - 4326044
CC Customer Code Order Number Unit Type Make Model Screen Size Quantity
DWPREMP07 4326044 Base Unit Hewlett Packard DC7900 360
DWPREMP04 4326044 Laptop Hewlett Packard 6930p 20
DWPREMP24 4326044 Laptop Hewlett Packard 8440p 22
DWPREMP19 4326044 Laptop Hewlett Packard 8730w 6
DWPREMP03 4326044 TFT Fujitsu L7ZA/A17 17 84
DWPREMP06 4326044 TFT Fujitsu P20-2 20 12
DWPREMP21 4326044 TFT Fujitsu P20W-3/5 20 (WIDE) 6
DWPREMP08 4326044 TFT Hewlett Packard L1710 17 40
DWPREMP20 4326044 TFT Hewlett Packard LE1711 17 2
DWPREMP09 4326044 TFT Hewlett Packard LP2065 20 7
DWPREMP13 4326044 TFT Hewlett Packard L2245W 22 1
DWPREMP23 4326044 TFT Hewlett Packard L2205WG 22 2
DWPREMP12 4326044 Printer Xerox 4500 8
DWPRF0013 4326044 Server Dell 2900 4
Total 574
Total Number of Pallets 25
Thanks Lynn
Chris
I don't see any figure in Query1 that would be summed up, so I guess you're doing a count?
What are you trying to count? If you don't have a figure in the database to sum up and are just doing a count within the report than that could be your problem if the scope of what you are counting isn't correct.
For example, if you have something like below where the "1" comes from data item in the report that is just the number 1 as an expression, you'd get correct totals when viewing it at the detail level including both item and customer:
ProdA Customer1 1
ProdA Customer2 1
ProdA Customer3 1
-----------------------
3
=======================
ProdB Customer1 1
ProdB Customer2 1
ProdB Customer3 1
ProdB Customer4 1
ProdB Customer5 1
-----------------------
5
The SQL for the above might be something like
select ProductName, CustomerName, 1 from blah_blah
But if you then try to use the same data with only Item (not customer), then the SQL will render a count of 1 for each product because customer is not in scope.
ProdA 1
ProdB 1
The SQL for this second version might be
select ProductName, 1 from blah_blah
You can make a data item using the count function and explicitly define the scope to get around this problem:
count(1 for [ProdName], [CustomerName])
Or if you have a data item in your model that is a unique key for each thing-y to be counted then you can just use that as the expression and set the aggregate function to "Count".
Good luck!!
Hi Lynn, Thanks for your time on this. I managed to do this in the end. I had a list report in query1 with totals and wanted them in a summary tab so i created the below data items and pointed them towards query1.
Query1 - Data Item
CASE [Tabular SQL1_tabularSQL].[OBJ_ASMLEVEL] WHEN 'DC7900' THEN 1 ELSE 0 END
Query1 - Data Item
total([Case When DC7900] for report)
Summary Page Tab - Created a table with a singleton object which had defined properties and selected the objects from query1, i then put this expression into the singleton "total([Case When DC7900] for report)", this worked.
Thanks Again
Chris