How to Create prompt value for multiple cubes

[BubuReloaded]

I have a report using three cubes with common dimensions(month, dealer name, model). Is it possible to have one prompt page that passes a prompt to three different cubes. I'm new to Cognos Report Studios, I tried everything I found on the internet including using a macro prompt but I can't get it to work, especially when I can't link the value prompt to a specific query in the prompt page properties since I'm working with multiple cubes.

Thank you in advance. 

[MFGF]

I have a report using three cubes with common dimensions(month, dealer name, model). Is it possible to have one prompt page that passes a prompt to three different cubes. I'm new to Cognos Report Studios, I tried everything I found on the internet including using a macro prompt but I can't get it to work, especially when I can't link the value prompt to a specific query in the prompt page properties since I'm working with multiple cubes.

Thank you in advance.

Hi,

Yes, but you need to understand how the Member Unique Names (MUNs) vary from cube to cube.

You will probably find they conform to the following:

[Cube1].[Dimension].[Hierarchy].[Level]->:[identifying path to the member]
[Cube2].[Dimension].[Hierarchy].[Level]->:[identifying path to the member]
[Cube3].[Dimension].[Hierarchy].[Level]->:[identifying path to the member]

In this case the trick is to build a prompt that returns just the ID or identifying path to the member, and construct the appropriate MUN from it in the query for each cube using a macro.

If you can tell us what a corresponding member's MUN looks like in each cube, we may be able to give you an example of an expression to use...

Cheers!

MF.

[BubuReloaded]

Thank you so much for replying.

Brief on how my report looks like:

it has about six charts on a single report layout, two of the charts use the [Retail Cube],
three of the charts use the [FTZ Cube],
the other chart use the [Services summary Cube]

The data item where I created the macro prompt for month looks like:
the Month its one of the levels inside the hierarchy.

[Retail Cube].[Time].[Time].[Month]->#prompt('pMonth')#
[FTZ Cube].[Time].[Time].[Month] ->#prompt('pMonth')#
[Services Summary Cube].[Time].[Time].[Month]->#prompt('pMonth')#

the MUN for each of those looks like:

[Retail Sales Cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[20060101]
[FTZ Cube].[Time].[Time].[Month]->:[PC].[@MEMBER].[20060101]
[Services Summary Cube].[Time].[Day]->:[PC].[@MEMBER].[20060101]

I want to be able to choose any date on the prompt page,

I realize your time is valuable but if you could offer up any more suggestions around this that would helpful.

[MFGF]

Thank you so much for replying.

Brief on how my report looks like:

it has about six charts on a single report layout, two of the charts use the [Retail Cube],
three of the charts use the [FTZ Cube],
the other chart use the [Services summary Cube]

The data item where I created the macro prompt for month looks like:
the Month its one of the levels inside the hierarchy.

[Retail Cube].[Time].[Time].[Month]->#prompt('pMonth')#
[FTZ Cube].[Time].[Time].[Month] ->#prompt('pMonth')#
[Services Summary Cube].[Time].[Time].[Month]->#prompt('pMonth')#

the MUN for each of those looks like:

[Retail Sales Cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[20060101]
[FTZ Cube].[Time].[Time].[Month]->:[PC].[@MEMBER].[20060101]
[Services Summary Cube].[Time].[Day]->:[PC].[@MEMBER].[20060101]

I want to be able to choose any date on the prompt page,

I realize your time is valuable but if you could offer up any more suggestions around this that would helpful.

Hi,

For each cube you would use a query calculation to build the required MUN, eg

#'[Retail Sales Cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[' + prompt('pMonth','token','20060101') + ']'#

#'[FTZ Cube].[Time].[Time].[Month]->:[PC].[@MEMBER].[' + prompt('pMonth','token','20060101') + ']'#

#'[Services Summary Cube].[Time].[Day]->:[PC].[@MEMBER].[' + prompt('pMonth','token','20060101') + ']'#

As long as your prompt returns values that correspond to the YYYYMMDD format, your MUNs should then retrieve the required month member from each cube.

An alternative would be to prompt for the entire MUN from the first cube, then in your other cubes use the substitute() macro function to change the initial part, eg

If your prompt returns a MUN of [Retail Sales Cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[20060101] then in your Services Summary Cube you could use an expression such as

#substitute('Retail Sales Cube','Services Summary Cube',prompt('pMonth','token','[Retail Sales Cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[20060101]')#

A couple of approaches for you to try...

Cheers!

MF.

[BubuReloaded]

Hi,

Thanx again for replying.

I tried what you suggested, when I add a value prompt on the prompt page, the parameter I had created doesn't show. When I create a new parameter with same parameter name(pMonth), then I run the report the drop down list shows the pMonth only. Í have never created a value prompt without linking it to an existing query or creating a new query for it. I do I then link the pMonth parameter I had created for each cube, to the value prompt so it displays the [Month] from all cubes?

Please bear with me, I am new to Cognos Report Studio

[MFGF]

Hi,

Thanx again for replying.

I tried what you suggested, when I add a value prompt on the prompt page, the parameter I had created doesn't show. When I create a new parameter with same parameter name(pMonth), then I run the report the drop down list shows the pMonth only. Í have never created a value prompt without linking it to an existing query or creating a new query for it. I do I then link the pMonth parameter I had created for each cube, to the value prompt so it displays the [Month] from all cubes?

Please bear with me, I am new to Cognos Report Studio

Have you included the query calculation in the reporting container (crosstab?) for each cube? If not, the calculation will get ignored.

MF.

[BubuReloaded]

It works!!!!!!!!!!!!!! ;D ;D ;D ;D ;D Thank you sooooooooooooooo much:D

[MFGF]

It works!!!!!!!!!!!!!! ;D ;D ;D ;D ;D Thank you sooooooooooooooo much:D

You are welcome! :)