Prompt Page – Select 1 Member and apply to multiple dimensions?

[antya76]

I need some advice on building a report with prompts. The data is in a multi-dimensional format, and a similar member is part of 2 different dimensions. Therefore, the prompt I've built has 2 different trees, even though the user only wants to select member.  Ideally I'd like for the prompt page to have only to 1 tree that the user enters, and that member is cascaded to the other tree. It doesn't necessarily have to be a tree; it could also be a Text Box if needed.

Example
User wants to pull all instances where Company A either manufactured the product or sold the product.

The structure for the member Company A in MANUFACTURING company dimension is:
[detail_cube].[All Manufacture Company].[ All Manufacture Company]->:[TM].[ All Manufacture Company].[ All Manufacture Company].[@MEMBER].[Company A]

The structure for the member Company A in SELLING company dimension is:
[detail_cube].[ All Selling Company].[ All Selling Company]->:[TM].[ All Selling Company].[ All Selling Company].[@MEMBER].[Company A]

Currently, I have 2 prompt trees: 1 hitting the All Manufacture Company Dimension and the other hitting the All Selling Company. 
1.   [detail_cube].[All Manufacture Company].[ All Manufacture Company]-> ?MFR?
2.   [detail_cube].[ All Selling Company].[ All Selling Company]->?SELL?

What I want is 1 prompt where user simply selects 'Company A' and that cascades to both the All Selling Company and All Selling Company dimensions.

Any suggestions?

[MFGF]

How about setting up the items as prompt macros with the same parameter:

Manufacturing item:

#'[detail_cube].[All Manufacture Company].[ All Manufacture Company]->:[TM].[ All Manufacture Company].[ All Manufacture Company].[@MEMBER].[' + prompt('COMPANY','token') + ']'#

Selling item:

#'[detail_cube].[ All Selling Company].[ All Selling Company]->:[TM].[ All Selling Company].[ All Selling Company].[@MEMBER].[' + prompt('COMPANY','token') + ']'

All you need then is a prompt that has a use value based on the caption of the member from one of the hierarchies (assuming the captions tie in with your Company A values below).

Cheers!

MF.

[antya76]

THANK YOU SOOOOOOO MUCH. It workes great! :)

[cognosbeginner]

Hello

I have a similar issue, except that, i need to display the next levels of the members. I will be more precise-

Scenario – I'm building a multi-page crosstab report based on multiple cube datasources. I', creating the cubes in analysis studio, opening with report studio and adding necessary functionality. I have 2 dimensions with 'State' member and the hierarchy for both dims looks like -

Dim 1 - State/Territory - the structure looks like  - [Cube Name].[Rat Nam].[Rat Nam].[State]->:[PC].[@MEMBER].[Statename]

Dim 2 - State/County - the structure looks like -  [Cube Name].[Map Nam].[Map Nam].[StateCd]->:[PC].[@MEMBER].[01]

I want to create one prompt on the 'state' , but display 'Territory Code' level detail on one page and 'County' level detail on another page for the selected 'State'.

I initially made two prompts - one visible and the other hidden, and pass selected value to the hidden prompt using javascript. But this passes the state value as a 'mun' instead of a 'string' and gives the 'slicers cannot be from the same dimension' error.

The issue that i'm talking about is while building pages from just the 1st cube package. One i start building more report pages based on the 2nd cube package, i will run into this issue again. Because, i need to use the same 'State' prompt for the state dims from 2nd cube package.

Does anyone have any ideas please?

[cognosbeginner]

i tried using macro functions like - csv, split and substitute, but cannot get it to work. :(

[cognostechie]

There is a 'children' function available which will give you the Territories within the selected state.

[cognosbeginner]

I have the data items within the queries altered to display the territories and counties in the report output using the function descendants in a macro prompt function. My issue is not with the display of levels.

I want to have one prompt on the 'state' , say parameter is 'StateName' and pass the same selected value (say Ohio)  to the 2 dimensions (dim 1 and dim 2 mentioned above) whose member structure is different (  [Cube Name].[Rat Nam].[Rat Nam].[State]->:[PC].[@MEMBER].[Statename] AND  [Cube Name].[Map Nam].[Map Nam].[StateCd]->:[PC].[@MEMBER].[01] ). I am populating the prompt values with caption of the member from one the hierarchies. So, using the same parameter 'StateName' in macro prompt on the different member structures will return territory codes in both pages.

That was the reason i thought of making an invisible prompt and passing the first prompt value but it passes it as a 'mun' instead of a string. Tbus is get the 'slicers cannot be from the same dimension' error.

[cognosbeginner]

I have another question - referring to the 1st post in this thread by 'antya76' and the response given by 'MFGF' -

If the 'company' dimension is from 2 different cubes ( not 2 dimension from the same cube), will the approach still work? I created the prompt to have the use value as caption of the member from the 1st cube hierarchy. From the approach described my 'MFGF', i think the items to be defined are slicers? It is giving me a parsing error. Any suggestions?

This is how i defined them -

for cube 1 -  #'[Cube1 Name].[Comp].[Comp].[CompNam]->:[PC].[@MEMBER].[' + prompt('Company','token') + ']'#
for cube 2 -    #'[Cube2 Name].[Comp].[Comp].[CompNum1]->:[PC].[@MEMBER].[' + prompt('Company','token') + ']'#

[antya76]

So I have a twist. I'd like to to a 'PromptMany'

Thanks to MFGF, who is my personal hero, I have the following macro that applies at value from a Text Prompt to a Parameter to be used to select a member [Partner] in the [By Agreement Type] level:

#'[leg_detail_cube].[All Partners].[By Agreement Type].[Partner]->:[PC].[@MEMBER].[' + prompt('Partner','token') + ']'#

Now, I would like to change this to a promptmany so I can have the user select multiple partners, and not sure the format.  I tried the following but it didn't seem to work (admittedly I know it was a long-shot):

#'set([leg_detail_cube].[All Partners].[By Agreement Type].[Partner]->:[PC].[@MEMBER].[' + promptmany('Partner','token') + '])'#

Can anyone help me with the correct syntax? Much appreciated

[MFGF]

So I have a twist. I'd like to to a 'PromptMany'

Thanks to MFGF, who is my personal hero, I have the following macro that applies at value from a Text Prompt to a Parameter to be used to select a member [Partner] in the [By Agreement Type] level:

#'[leg_detail_cube].[All Partners].[By Agreement Type].[Partner]->:[PC].[@MEMBER].[' + prompt('Partner','token') + ']'#

Now, I would like to change this to a promptmany so I can have the user select multiple partners, and not sure the format.  I tried the following but it didn't seem to work (admittedly I know it was a long-shot):

#'set([leg_detail_cube].[All Partners].[By Agreement Type].[Partner]->:[PC].[@MEMBER].[' + promptmany('Partner','token') + '])'#

Can anyone help me with the correct syntax? Much appreciated

Oh! See - now you've made me blush! Saying nice things like that runs the risk of making me believe I know stuff, which is a dangerous and horrifying thought for the rest of humanity! :)

If you're building up a MUN like this, using just the final part of the expression from the prompt, then a promptmany() isn't going to work. You could either have the prompt deliver the entire mun for each selection, or else if you are only retrieving one level, ditch the macro and use a query calculation with the expression
set([your partner level] -> ?Partner?)

Did you really mean a Text Prompt or are you using a Value Prompt?

MF.

[antya76]

HAHA MFGF!

I need to use a Text Prompt with a Macro because the value 'Company A' is a member in multiple dimensions (Partner and Operator). My goal is for the user to select, for example, "Company A" and "Company B". When the report runs, there should be only 2 outputs:

1. Partner Company A and Operator Company A
2. Partner Company B and Operator Company B