Skip to main content

developerWorks >  Information Management  >  Forums  >  Cognos 8  >  developerWorks

Need to display totals for selected as well as All values in CrossTab.    Point your RSS reader here for a feed of the latest messages in this thread


     
 
 
My developerWorks
 Welcome, Guest
Sign in or register
This question is not answered.

Reply to this Thread Reply to this Thread Post New Thread Post New Thread Watch list Watch list Forum settings Forum settings Help Help
Permlink Replies: 3 - Pages: 1 - Last Post: Nov 9, 2009 5:23 PM Last Post By: mgibson Threads: [ Previous | Next ]
Alfred,T

Posts: 14
Registered: Mar 25, 2009 08:54:53 PM
Need to display totals for selected as well as All values in CrossTab.
Posted: Nov 04, 2009 05:47:44 PM 		 
Click to report abuse...   Click to reply to this thread Reply
Attachment Report 3 Prompts&Totals.png (49.6 KB)
Hi All,

I have a specific requirement where the user wants to see the totals for the values that were selected on the prompt page for a dimension (which is straight forward) and totals for all values in that dimension. Please refer to the attached file to get a clear picture.

There are 7 Races and 50 States.

In essence the "Total for All Races" column should be the total for all the 7 Races.
and the "Total for All States" column should be the total for all the 50 States.

The data is sourced from a Cube (Dimensional Model).

Please let me know how we can achieve this. Is there something that can be done in Transformer that can help achieve this while building the cube or some calculation in the report query that will help achieve this.

Thanks for your responses in advance.
Alfred.
mgibson

Posts: 225
Registered: Feb 02, 2009 06:25:16 PM
Re: Need to display totals for selected as well as All values in CrossTab.
Posted: Nov 08, 2009 10:45:38 PM   in response to: Alfred,T in response to: Alfred,T's post 		 
Click to report abuse...   Click to reply to this thread Reply
Hi Alfred,

You may need to remove any filters for the whole crosstab - as these will be applied to the entire crosstab by default. You can then add columns, or measures that each perform the filtering (i.e. using the filter or tuple function).

Then you can add the botton summary line using the 'Tuple' function as well - it's very effective for doing these sorts of things.

Regards
Mick
Alfred,T

Posts: 14
Registered: Mar 25, 2009 08:54:53 PM
Re: Need to display totals for selected as well as All values in CrossTab.
Posted: Nov 09, 2009 02:21:46 PM   in response to: mgibson in response to: mgibson's post 		 
Click to report abuse...   Click to reply to this thread Reply
Hi Mick,

Thanks for your response. Since there are prompts on the report (State, Race etc.) could you please tell me how you would use the filter function or give me an example how you would use prompt parameters in the filter function.

Thanks,
Alfred
mgibson

Posts: 225
Registered: Feb 02, 2009 06:25:16 PM
Re: Need to display totals for selected as well as All values in CrossTab.
Posted: Nov 09, 2009 05:23:20 PM   in response to: Alfred,T in response to: Alfred,T's post 		 
Click to report abuse...   Click to reply to this thread Reply
For columns we actually use the Tuple function, eg.

tuple(
[Finance GL Balances].[Time Dimension].[Time Dimension].[Month] -> ?Month_Parm?,
[YTD Actual]
)


For rows, we use a Filter function (but this doesn't use a prompt parameter)

total(currentMeasure within set Filter( [Natural Account] , roleValue('_businessKey', [Natural Account]) between '6124' and '6189'))


I hope this helps.

Regards
Mick
Pages: 1
Go Back Back to Thread List
Threads: [ Previous | Next ]
 Tags
Help

Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular type of content or application that you're viewing.

My tags shows your tags for this particular type of content or application that you're viewing.

 
MoreLess 

Update your My developerWorks profile and start connecting with others.

Point your RSS reader here for a feed of the latest messages in all forums