Summary Tab from a list page

[cbyrne]

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

[Lynn]

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:

Code Select Expand

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.

Code Select Expand


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 ;)

[cbyrne]

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

[Lynn]

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:

Code Select Expand


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.

Code Select Expand


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!!

[cbyrne]

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