Hi,
you can look up the definition of the prompt macro online or in the query expression editor of Report Studio
https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=macros-syntax
As you can see 'Retailers' is the name of the prompt parameter I've created to send the selected static choice to the query calculation.
'Token' then is the datatype. You can also use string, integer or memberuniquename - depending on what you want to have as prompt value. Usually 'token' does it.
I have no knowledge what you want to achieve with those selected members from the hierarchy. If they should just filter a query, I sugguest do add a query calculation to your query which holds the prompt macro and thann add this to the slicer field (instead of using detail filters). Depending on the choice the user made you have individual filtered values from your query for your data containers. Each static choice has to be a member set expresion built with set, union, except, filter or any other combinatiom of dimensional member set expressions. This expression then returns the members and used as slicer, it reduces the values of the query to those members.
------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------
Original Message:
Sent: Wed December 08, 2021 06:06 AM
From: p sripadh
Subject: selecting multiple members from cube to detail filter
Hi Robert,
thanks for the solution.
can you please specify what is 'Retailers';'token'; in the expression definition
and using set members in the query can we write a case statement in detail filter for static choices which are defined on the prompt page
how to achieve it
------------------------------
p sripadh
Original Message:
Sent: Wed December 08, 2021 05:20 AM
From: Robert Dostal
Subject: selecting multiple members from cube to detail filter
Hi,
we also use static choices with dimensional data sources. The key is to write the multidimensional member set expression into the "Use:" field. Then add a query item that gets the choice from the parameter with a prompt macro.
In my exmaple I used GO cube and Choice A is:
set([great_outdoors_sales_en].[Retailers].[Retailers].[Retailer type]->:[PC].[@MEMBER].[2];[great_outdoors_sales_en].[Retailers].[Retailers].[Retailer]->:[PC].[@MEMBER].[1293],[great_outdoors_sales_en].[Retailers].[Retailers].[Retailer]->:[PC].[@MEMBER].[1123],[great_outdoors_sales_en].[Retailers].[Retailers].[Retailer]->:[PC].[@MEMBER].[1474];[great_outdoors_sales_en].[Retailers].[Retailers].[Retailer site]->:[PC].[@MEMBER].[5828])
The expression for the data item in the rows is:
Depending on your report you have to specify a default value or not. Keep in mind that the dimensional set expression must be valid. I reccomend to try each one of them in asingle data item before adding them to the static value prompt box. I used this approach in a few reports with set, union and except function
------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
Original Message:
Sent: Wed December 08, 2021 03:52 AM
From: p sripadh
Subject: selecting multiple members from cube to detail filter
If the user selects "Banking finance" then highlighted in yellow color companies should be selected in the report
please tell me how to achieve it
------------------------------
p sripadh
Original Message:
Sent: Wed December 08, 2021 03:36 AM
From: Christian Seiser
Subject: selecting multiple members from cube to detail filter
Still my recommendation is to create 1 crosstab/list for each scenario, and use the render variable to display the right one. You don't use if-then-else with a dimensional source like you would with a relational source.
------------------------------
Christian Seiser
Original Message:
Sent: Wed December 08, 2021 03:30 AM
From: p sripadh
Subject: selecting multiple members from cube to detail filter
Thanks, Christian Seiser for your reply
but my req is to select multiple cube members,
if the user selects 'A' some parents and children from the hierarchy
if the user selects 'B' only parents
similarly for 'C' and 'D'
I am facing an issue in selecting those members for the report
Eg: a single data item should consist of multiple members( parents + children or only parents or only children ) from the hierarchy
that I can use that for a filter condition
hope you understood my req
------------------------------
p sripadh
Original Message:
Sent: Wed December 08, 2021 03:11 AM
From: Christian Seiser
Subject: selecting multiple members from cube to detail filter
My recommendation: create 3 crosstabs set up as A,B,C and a variable with condition A,B,C that would only display 1 of those crosstabs depending on your choice of ABC. Don't use filters with dimensional sources.
Christian
------------------------------
Christian Seiser
Original Message:
Sent: Tue December 07, 2021 09:38 AM
From: p sripadh
Subject: selecting multiple members from cube to detail filter
Hi,
I have a challenge with one of the DQM reports,
My report is using a cube as the source and has static choices like A, B, C, D .
if the user selects 'A'
then report should produce data for only parent members
if the user selects 'B'
then report should produce data for only child members
if the user selects 'C'
then report should produce data for some parents and children's from the hierarchy
can someone suggest to me how to implement this in detail filter using a case statement?
Thanks in advance.
------------------------------
p sripadh
------------------------------
#CognosAnalyticswithWatson