Different totals in the bottomw of crossTab

[sanchoniathon]

Different totals in the crossTab footer ?

Hi to all,

1- We have this crossTab in Report Studio 10.1.1

2- It is composed of "months" as lines and we have nested the "PRODUCT LINE" on top of "PRODUCTS" as columns.

3- The metrics beeing nested in this crossTab is [Sales Month to Date] and [Sales Year to Date]

4- They display what is needed

QUESTION:
We now need to have a crossTab footer (not sure if possible) or a way to display in the bottom of the [Sales Month to Date] column the SUM of it.
AND BUT
then we want to display the MAXIMUM (in fact i'm not sure if this is the right agrgreate function to use) but we need to display the last value of the [Sales Year to Date] column on the
bottom of that column. Is any of this possible and how ?

Thanks in advance for any valuable feed-back or at least the big picture of it !

[blom0344]

Fairly easy to achieve by using a union set. This allows you to build the crosstab based on a query that itself is the projection of a bunch of union queries.

You can even work in the standard total as part of the set like (example with SQL):

Code Select Expand


SELECT '1.Details',[dim1],[dim2],[measure1] from ..............
union all
SELECT '2.Summary','Total',[dim2],sum([measure1]) from ..............
union all
SELECT '3.Last_value',[last_value],[dim2],[measure1] from ..............


You will need an after auto-aggregation filter for the 3rd part to compute the last value from a range, something like :

[date] = maximum([date] for [somedimension])

Order the data in the crosstab using the  1. / 2. /3. constant values in order to get details first, then summary, then special row.

Check for posts on how to use after autoaggregation