Passing Relational value to dimensional Parameter

[twister]

Hello, I have a burst table (relational table) with MUNs and caption for selected members and will be using this table as master to pass values to the detail query which has dimensional parameter.

For example, my burst table will contain MUNs and caption below
MUNs : [Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]
Caption: Chicago

This MUN has to be passed to the detailed dimensional query which has the store parameter,
[Retail_Reporting].[Store].[Store] -> ?pStore?

How do I do it?

Thanks for your help.

[CognosPaul]

What type of cube is this? Can you have more than one member with the same caption? If so, try the following:

filter( [Retail_Reporting].[Store].[Store].[Store], [Retail_Reporting].[Store].[Store].[Store].[Long Name] = #prompt('Store Name','string',sq('Random value to stop the MDX validator from freaking out'))#)

In the master/detail link, set the connection from the burst table to the parameter. You can then slice by that data item, or use it in the layout.

[twister]

Hello Paul, Thanks a lot for replying back. This is a TM1 cube and it will have more than one member with the same caption. What is that Long name that you have specified in your response?

If it is going to be more than one member with the same caption, how am I going to pickup the right member? Can I pass MUN?

Thanks.

[CognosPaul]

Without knowing the type of cube, I guessed it might be a power cube. In a powercube, the long name is the attribute of each member that contains the caption.

In TM1, by default, level labels aren't exposed to Cognos. If you can't see each individual level, I strongly recommend enabling them.

Since there may be more than one member with the same caption, you need to determine the logic for choosing the correct one. Do they have different keys or different parents? For example, you might have a London, England and a London, Canada. It is very reasonable to expect those two cities to have different keys.

If they have different keys, can you pass the key instead of the caption? Is it possible you don't want to separate them, rather each detail should get all of the members with that caption?

Next, will you need to search the entire hierarchy, or only a specific level?

Assuming that you need all of the members matching a specific caption, and assuming that you need to search the entire hierarchy, you could do something like:

filter([Retail_Reporting].[Store].[Store], caption(currentMember([Retail_Reporting].[Store].[Store])) = #prompt('Store Name','string',sq('Default'))#)

Drag that into the slicer and see what happens.

[twister]

Thanks a lot Paul.

Your example bring back all the members matching the specific caption. I want only the members in the burst table. Can I create a master detail (between the burst table and parameter) based on MUNs rather than caption? Is it possible?

Re. levels, I don't see the levels here. I see the members directly. I have been told that the levels are not created as the hierarchy is completely unbalanced.

Thanks.

[CognosPaul]

Does the burst table contain the actual member string, [Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N], or only the caption?

If it has the full string, you can pass that easily:

Instead of the filter, do: #prompt('Store','mun','[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]')#

If you can only pass the key, you could do something like:

#'[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].['+prompt('StoreKey','token','Store^123N')+']'#

[twister]

The burst table contains the actual member string. This one is not working,

#prompt('Store','mun','[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]')#


the master detail is not working. The prompt always picks up the default value "[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]" rather than what is there in the burst table. I have a master detail (master: Relational table MUN, detail: parameter in the parameter query) specified in a list but somehow the master's value is not passing through.

[CognosPaul]

Try using
#prompt('Store','token','[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]')#

Cognos may be validating the datatype going in and dropping it as it's not an actual mun.

[twister]

I have noticed one interesting thing.

The following one always picks up the default value (master detail is not linking up).
#prompt('Store','token','[Retail_Reporting].[Store].[Store]->:[BC].[Store].[Store].[@MEMBER].[Store^123N]')#

However, I tried the following and it looks like it is working but there is a caveat.
#prompt('Store','token','[Burst_Query].[StoreMUN]')#
I am not sure whether this is a good one to use. However, the paramdisplayvalue for store is not working. The master detail is in the first page of the report which means that the parameter will be assigned based on the relational master. I assume that the same parameter value should be used by all the pages. In my last page of the report, I have a paramdisplayvalue('store') which returns null..So, I am not sure how the report is working.

Any ideas?

[CognosPaul]

The ParamDisplayValue would be blank, because you're not actually populating the parameter with a value. Do you have multiple queries and data containers in your report? Can you post the XML?

[twister]

Unfortunately I am not allowed to take anything out of here.

I can explain.

I have the following queries,

main report query - Cost_Query (In this query, I have a dataitem pointing to the parameter [Retail_Reporting].[Store].[Store] -> ?pStore?)

Relational table burst query - Burst_Query

Prompt Store Query which has got only one date item [Retail_Reporting].[Store].[Store] -> ?pStore?

What I am doing is,
Inserted a dummy page for the master detail.
Make the burst query as master and the Prompt Store Query as child, pass the burst query MUN to the parameter pStore. I presume that will assign the value for the parameter pStore.

After this I have 5 pages using the COst_Query which uses the pStore parameter for tuples and other stuffs.

Hope this makes sense.

Any ideas/approach?

Thanks.

[CognosPaul]

I get what's happening now.

The data containers are all using the prompt that was defined earlier. Each of those data containers need to have a master/detail in order to populate the prompt.

The way I normally build a burst report is to

1. Build the burst query
2. Build a pageset query.
3. Join the two in the burst options.
4. Every page of the report is added to the details of the page set. Every data container is set with a master detail to the page set query.

Instead of using paramDisplayValue, you can simply drag the item from the page set query.

[twister]

Thanks a lot for all your help Paul. Adding my details to your approach.

1. Build the burst query  -  YES I am doing it.
2. Build a pageset query - Using the Burst Query itself as Page set query and grouping the pages by Burst Key which will be the caption of every member from burst table
3. Join the two in the burst options. Why are you splitting burst query and page set query? Can't you use the same query for both like in my case?
4. Every page of the report is added to the details of the page set. Yes I am doing it already
5.Every data container is set with a master detail to the page set query. I have lot of crosstabs and charts in each page. So do you want me to set master detail for each crosstab and chart to the page set query? WHy do we need to do this as everything is already linked up using the parameter?

[CognosPaul]

3. Join the two in the burst options. Why are you splitting burst query and page set query? Can't you use the same query for both like in my case?
It's more for the purpose of demonstration. While the same query will work for both, I find it easier to explain to people, specifically when I diagram the order of operations: the burst, to the page, to the data container.

5.Every data container is set with a master detail to the page set query. I have lot of crosstabs and charts in each page. So do you want me to set master detail for each crosstab and chart to the page set query? WHy do we need to do this as everything is already linked up using the parameter?
This is where it's going wrong. The parameter is not being populated. The parameter is only being populated for the purpose of the query in the page set, because of the master detail to it. Hook up the data containers to the query in the page set, and see what happens.

[twister]

Paul, You are right. Once the master detail relationship has been created between the burst query and each data container, the report started bursting fine. Is there anyway that I can set the parameters once so that the data container picks up the parameter automatically rather than individual master detail relationship for each data container? The performance is really bad as the master detail links are more in this report.

Thanks.

[MFGF]

Since there may be more than one member with the same caption...

Hi Paul,

This isn't permitted in TM1. Every member in a dimension needs to have a unique unique caption (alias attribute) if specified. Not sure whether that simplifies anything?

Cheers!

MF.

[CognosPaul]

MF,

I really need to download the TM1 server onto my laptop. I'm basing all of my advice on other OLAP technologies. I had no idea the captions had to be unique.


Twister, I can't think of any other way of doing this.  Are you on CQM or DQM? DQM does have some master/detail optimizations - but even then, I'm not sure it would help with this.

[CognosPaul]

Actually, MF, question about that. What do you do in a geo dimension when you have Country -> England, City -> London, and Country -> Canada, City -> London?

[twister]

Cheers Paul. I am on DQM. How can we improve the master detail performance of DQM?

[MFGF]

Actually, MF, question about that. What do you do in a geo dimension when you have Country -> England, City -> London, and Country -> Canada, City -> London?

The simple answer is you concatenate the parent caption(s) to make the city names unique. Blech :)