Query on Pivot Format

[#1]

Hi Everyone,

I need to built a report like the image below:When I select 2016-2017, data for selected year and previous year should be populated.

The problem I am facing is showing both of the totals.(the Horizontal and Vertical in the same shot)

If I calculate Nos and % in Expression, I get the Vertical Total showing Nos and %. But I don't get Total for years separately.

If I use ValueList function for Nos and % and add 2016-17 and 2015-16 as expression, I get the Horizontal Total for Years but don't get Totals based on Nos and %.

Is there a way to get both the totals?

Thanks in Advance,

Jemimah

[swuehl · Accepted solution]

I would suggest that your are using a link table for your ZONEs in your chart:

DIM_ZONE_TOTAL

LOAD ZONE, ZONE as DIM_ZONE

RESIDENT YourTableWithZoneDimension;

CONCATENATE

LOAD ZONE, 'TOTAL' as DIM_ZONE

RESIDENT YourTableWithZoneDimension;

Then use DIM_ZONE as dimension in your chart instead of ZONE field (and enable partial sums for bucket dimension).

Hope this helps,

Stefan

[#3]

Thanks a lot.