Value prompt of a dimension subset (list) PLUS aggregate element

[SimonWM]

Hi all,

I am very new to the whole "Cognos-Reporting-thingy", and I am facing a Problem I can't handle (I am working with Cognos 10.2.2. on a TM1-Cube):

I've got a Dimension, consisting of about 20Elements, of which only 10 interest me for this particular report.
I want to insert a prompt, showing only my 10 relevant elements PLUS 1 artificial element "ALL". Selecting one of the elements should filter the complete report accordingly (hence, only the selection of a single or "all" elements should be feasible).

Sounds pretty straight forward for most reporting tools, but I just cant make it work with Cognos :(

Some help would be much appreciated!

Thanks and regards,
Simon

[charlie]

Simon,

Welcome to the "Cognos-Reporting-thingy" community  ;D

I can think of a quick solution (not the best definitely)

1. Create a prompt page with value prompt
2. In Query 1 of value prompt, select and filter the 10 elements to be displayed
3. Create Query 2 with 'ALL' hardcoded in a data item.
4. Create a Union Query 3 of Query 1 and Query 2. Select the relevant data item in union query. Now use this query to populate your value prompt. Thus your value prompt now displays ALL and 10 elements in the list. Use advanced case statement sorting if needed to bring ALL on top.

Next is filtering your data based on ALL and other values.
Assuming you are displaying a list with Query 4 as the List query

5. Add a filter to your Query 4 which will look somewhat like the below

[Data Item] in (case when ?Parameter1? = 'ALL' then 'Element 1' else ?Parameter1? end)
or
[Data Item] in (case when ?Parameter1? = 'ALL' then 'Element 2' else ?Parameter1? end)
or
.... and so on for 10 Elements

I did a quick test on my Cognos and this worked and produced desired functionality.

Hope this helps!

[SimonWM]

Hey Charlie,

thanks, for your help!

Concerning your Point 3, "hardcoding ALL" in a data item:
That means, something like ALL = set ([Element 1]; [Element 2] .. ; [Element 10]) ?


I also started a different approach based on
http://www.cognoise.com/index.php?topic=24263.0

In my prompt I am showing the 10 Elements based on a query (Display- & Use Value), plus a Static Choice 'ALL'. The users' choice gets assigned to a 'Parameter1'.
The query, that's retrieving the data for my actual output table is then being filterd (detail filter) via
if
(?Parameter1? = 'ALL')
Then (1=1)
Else ([Dimension]=?Parameter1?)

Unfortunately, the whole thing results in "Unable to resolve MDX element identifier: "Parameter1".

I read CognosPaul's statement on "dimensional data and detail filters vs. slicers", but I couldn't get it to work with those neither. :(
Also, I am not realy understanding the (1=1) part.

Thanks again,
Simon

[charlie]

Simon,

No, hardcoding in point 3 means.. Just drag a new Data Item in Query 2 and type 'ALL' in the Expression Definition. Simple :)