Drill through from Relational to Cube

[CognosAdmn]

Happy Thanksgiving All!  :D


This might seem a bit unorthodox to some report developers, but it is my requirement to drill through from a relational report to a transformer cube based report.

My source report (relational) has a prompt for MonthKey and a crosstab report. The crosstab's query does not have the MonthKey or any other month related fields but it does have a filter for the MonthKey. The MonthKey and MonthName are part of the prompt Query in this report.

My target report which is based on a transformer cube contains a prompt for MonthKey and crosstab report. Similar to my source report, the crosstab's query does not have the MonthKey. However, I have a slicer in the crosstab's query (shown below in red):
(#prompt('p_MonthKey', 'memberuniquename', '[Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level]->:[PC].[@MEMBER].[MonthKey]')#)

In my source report I have a text (eg. "Please click here for detail") which I am using as a link to drill through to my target report by "Passing Parameter value" MonthKey.

When the report is run and the link is clicked, the target report is successfully opened. However, the report is not filtered for the MonthKey passed from source report. Instead it aggregates all available Months.

In target report, I've tried using a detail filter instead of Slicer, which did not change the outcome.  Detailed filter: [Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level] = ?p_MonthKey?
Next, I've tried adding a calculated member in the crosstab's query (target report) with a filter function:
filter(   
   [Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level],
   caption([Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level]) = ?p_MonthKey?
   )

Now, I am seeing the correct Month in the dropdown prompt in the target report. However, the crosstab itself is still not being filtered for the month pass through from parent. It is still displaying the aggregated numbers of all available months.

It would be greatly appreciated if someone can shed some light in this issue.

Thank you All.

[MFGF]

Happy Thanksgiving All!  :D


This might seem a bit unorthodox to some report developers, but it is my requirement to drill through from a relational report to a transformer cube based report.

My source report (relational) has a prompt for MonthKey and a crosstab report. The crosstab's query does not have the MonthKey or any other month related fields but it does have a filter for the MonthKey. The MonthKey and MonthName are part of the prompt Query in this report.

My target report which is based on a transformer cube contains a prompt for MonthKey and crosstab report. Similar to my source report, the crosstab's query does not have the MonthKey. However, I have a slicer in the crosstab's query (shown below in red):
(#prompt('p_MonthKey', 'memberuniquename', '[Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level]->:[PC].[@MEMBER].[MonthKey]')#)

In my source report I have a text (eg. "Please click here for detail") which I am using as a link to drill through to my target report by "Passing Parameter value" MonthKey.

When the report is run and the link is clicked, the target report is successfully opened. However, the report is not filtered for the MonthKey passed from source report. Instead it aggregates all available Months.

In target report, I've tried using a detail filter instead of Slicer, which did not change the outcome.  Detailed filter: [Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level] = ?p_MonthKey?
Next, I've tried adding a calculated member in the crosstab's query (target report) with a filter function:
filter(   
   [Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level],
   caption([Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level]) = ?p_MonthKey?
   )

Now, I am seeing the correct Month in the dropdown prompt in the target report. However, the crosstab itself is still not being filtered for the month pass through from parent. It is still displaying the aggregated numbers of all available months.

It would be greatly appreciated if someone can shed some light in this issue.

Thank you All.

I think the issue here is that your original slicer prompt macro was expecting a complete MUN to be returned from the prompt, whereas your relational report will be returning only a month key value.

First we need to understand what a Month level member MUN looks like in your cube. Can you right-click on a month member in the package tree and choose 'Properties', then copy and paste the MUN of the member here? What we need to know is how it's constructed, so we can use a macro to build a MUN of the same structure using your passed month key value.

Oh and *please* don't use detail filters in a report based on a cube. It's an awful practice, and can led you into very deep trouble :)

MF.

[BigChris]

Oh and *please* don't use detail filters in a report based on a cube. It's an awful practice, and can led you into very deep trouble :)

Really?? I hadn't heard that...  ;D

[CognosAdmn]

Thank you MFGF for looking into this issue and helping us out.

here is the MUN: [Customer Cube].[TIME PERIOD].[TIME PERIOD].[Period]->:[PC].[@MEMBER].[Q3 2015]

I am not familiar with macros but look into it.

Thank you again.

[Mpotla]

Hi,

you can add data item in the query with below expression and add to slicer.

[Customer Cube].[TIME PERIOD].[TIME PERIOD].[Period]->:[PC].[@MEMBER].#sb(prompt('p_Month','token'))#

Thanks,

[CognosAdmn]

Works like a charm Mpotla. Thank you so much. :)

Thank you MFGF for your advice about using filters in cube based report. I will keep that in mind.  :D

[CognosAdmn]

I was a bit to fast with my last reply.....

The Drill through works now. The correct Period is being passed from parent report to child report.

However, there are prompts for periods in the child report, which allows the users to view the report for a different period than what was passed from the parent report. Upon selecting a different period from the dropdown in the child report, it fails with the following error message:

OP-ERR-0181
"At least one invalid member reference was encountered in the query.'[Customer Cube].[TIME PERIOD].[TIME PERIOD].[TIME PERIOD]->:[PC].[@MEMBER].[[Customer Cube]].[TIME PERIOD]].[TIME PERIOD]].[Period]]->:[PC]].[@MEMBER]].[Q2 2015]]]' "

Is that expected? Do I have to get rid of the dropdown prompt?

Thank you in advance for your input.

[Mpotla]

Below expression works, only when u pass valid quarter to p_Month parameter which would be a valid MUN in the cube.But when you select something in Dropdown passes MUN. which would be substituted in the below expression in place p_month, which give invalid MUN in the cube.
[Customer Cube].[TIME PERIOD].[TIME PERIOD].[Period]->:[PC].[@MEMBER].#sb(prompt('p_Month','token'))#

In the dropdown prompt, the UseValue by default uses MUN i.e,[Customer Cube].[TIME PERIOD].[TIME PERIOD].[Period]->:[PC].[@MEMBER].[Q2 2015]. To make this work, We should set Use value for the Dropdown prompt as MemberCaption of the  '[Customer Cube].[TIME PERIOD].[TIME PERIOD].[Period]->:[PC].[@MEMBER].[Q2 2015]', which would be 'Q2 2015'.

Add one calculated data item with expression roleValue('_memberCaption',QuarterLevelDataitem) in the drop-down prompt query and  set this as Use-value for the prompt.


Thanks.

[CognosAdmn]

Hi Mpotla.
Thank you for getting back to me with the solution and for providing detailed explanations of the error and solution. This is very helpful.

Unfortunately, I was not able to get it to work per your solution. But that's probably due to an issue with my expression in the data item.

I had to roll back the changes (macro data item and slicer) in order to allow the end users to run the report (Yes on DEV environment) without the drill through functionality.

I added back the following data item:

filter(   
   [Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level],
   caption([Cube_Package].[Month_Dimension].[Month_Hierarchy].[Month_Level]) = ?p_MonthKey?
   )

and a slicer:
(#prompt('p_MonthKey', 'memberuniquename', '[Cube_Package].[Month Dimension].[Month_Hierarchy].[Month_Level]->:[PC].[@MEMBER].[MonthKey]')#)

Then tried to drill through and it works. Now I can drill through with passing the correct parameter and change the period in the child report using the prompt. I am pretty sure, I have tried this before. I don't know why it's working now. But glad it is. :)

Thank you all for your help.  :D