I want to create a report with a list and a value prompt. In the value prompt I want to display the values of Col1 of the list and a static choice "All" which would show up only the summation of all the items in Col1 [ not individual items ] .
In the list there is 2 columns,
Col1 | Col2
---------------
A | 1
B | 2
C | 3
selection of 'All' would give
Col 1 | Col 2
All | 6
I did try it by creating the hierarchy and it was not as useful as when I select an item i.e A, it displays :
All | 1
and
only display: A | 1
when I click on the All.
So, could anyone help me with achieving this.
try this, it will help u.
col-1 expression
--------------
case
when(?parameter1?='ALL') then "ALL"
else [col1]
end
col-2 Expression
----------------
case
when(?parameter1?='ALL') then count(distint [col1] )
else
[col2]
end