Hello experts,
I am working on a report where I was suppose to show a 'Total' column in the end of a crosstab which would be a total of the values of all other columns on the left.
But now they want this Total to appear only when more than 1 selections have been made in the multiselection prompt.
Means,
1 entity selected in the prompt- Total column should NOT appear
More than 1 selections made in the prompt - Total column should appear.
I am using 10.2 version of cognos and datasource is TM1 cube.
Any help is appreciated.
Quote from: cognos4321 on 22 Jul 2016 09:55:43 AM
Hello experts,
I am working on a report where I was suppose to show a 'Total' column in the end of a crosstab which would be a total of the values of all other columns on the left.
But now they want this Total to appear only when more than 1 selections have been made in the multiselection prompt.
Means,
1 entity selected in the prompt- Total column should NOT appear
More than 1 selections made in the prompt - Total column should appear.
I am using 10.2 version of cognos and datasource is TM1 cube.
Any help is appreciated.
Hi,
You'd probably need two versions of the crosstab - one with the total and one without. You'd then conditionally show the appropriate crosstab based on the number of members selected in the prompt.
Cheers!
MF.
Thanks for the reply MF.
I was also going in the same direction but don't know how would I count the number of selections made in the prompt.
Thanks again.
Quote from: cognos4321 on 22 Jul 2016 11:07:42 AM
Thanks for the reply MF.
I was also going in the same direction but don't know how would I count the number of selections made in the prompt.
Thanks again.
Is the prompt returning a set of members? If so, you could use count([your measure] within set [your set of prompted-for members]) to count how many there are.
Cheers!
MF.
Cognos 10.2.1.
I have used the Prompt API to do this. Take a look at the attached report spec.
Keep in mind, JavaScript may not survive an upgrade.
Thanks to both of you for the reply.
This is how the issue got fixed.
I first created a data item 'COUNT SELECTIONS' for counting the number of selections made in the prompt using expression:
count([your measure] within set [your set of prompted-for members])
Then tried using this data item for conditinally rendering the crosstabs with/without 'Total'.
But somehow it showed me error every time.
I am not sure but I have always seen using 'data items' in expression of render variables always gives me error while it works with 'parameters' used in expression.
So, bottom line using this data item 'COUNT SELECTIONS' didn't work in render variable.
But it worked with the following expression in the Total column itself:
if([COUNT SELECTIONS] = 1) then (0)
else Total([measure] within set ([hierarchy])))
Then made the Suppression zero property on Columns.
Thank You.