Drill thru from Cube Report to Relational Report

[Steve]

I have a Report made in Report Studio that is working from a Cube package.

Another Report is made form a Relational package (FM Package) which has detailed data.

So since this is not a Analysis Studio Cube to Report Studio report, I did not do the drill thru setting on the Cube Package.

I have setup the drill thru from the drill thru definitions of the Master report by passing Parameter and Data Item values to the detailed report. I tried using member Unique Names as well as member Caption.

The detailed report shows the parameter values when I display that using Layout calculations but does not show any data. When I run the detailed report by itself, it shows data for the same set of filtered values.

Any ideas?

[MFGF]

Hi,

When you configure a drillthrough from an OLAP to a relational package, the relevant member ID is passed through as the parameter value by default.  The Member ID is usually the very last piece in the member's MUN.  What is often confusing at first (well it was to me, anyway) is that when members are displayed in a report from an OLAP package, the member captions are shown, not the member IDs.

So, for example, you may have a report showing a particular product - and it appears as "Chocolate Teapot" in the source report.  Under the covers, this member is actually represented by its MUN "[Namespace].[Product Dimension].[Product Hierarchy].[Product Level]->:[Cubetype].[@MEMBER].[1234]"

If you were to drill through, passing this member, the last piece of the MUN (1234) would be passed to the target report.  The implication this has is that in your source report, you would click on "Chocolate Teapot" but in your target report, you would need to add a filter based on the Product Code (1234) rather than the Product Name (Chocolate teapot).

Does any of that help?

MF.

[Steve]

Hi MF -

So in the target report, we have to know what is the ID of the Chocalate Teapot?

Well, it makes sense but the Parameter Display Value was actaully showing the same descriptions that I had in the Source report !!

I figured the problem. I had two filters associated with the parameter in the target report. The problem was the filter based on the Year. When I remove that filter, it drilled fine and the target report showed the correct data. Took a while to resolve the filter for the Year.

Somehow the parameter value was still showing as 2009 in the Target report but it just wasn't filterng inspite of casting it to char/integer etc.

So, instead of using the Year Level in the Source Report, I passed the Year - Long Name from the Source to Target and passed it as Member Caption and it works now.

So I cast both the sides of the filter in the Target to integer and it works.

cast(year([Date]),integer) = cast(?year?,integer)

Would it cause any problems?

[cognostechie]

The detailed report shows the parameter values when I display that using Layout calculations but does not show any data. When I run the detailed report by itself, it shows data for the same set of filtered values.

Any ideas?

Ya know, I think it is adding some spaces before or after the value of the Year. Try this in filter -

ltrim(rtrim(cast(extract(year,[Query Subject].[Date]),char(4))) = ltrim(rtrim(cast(?ParamName?,char(4))))

[Steve]

Thanks ! That worked. I had made it work with Long Name but if I use the trim function , it  now works with the levels too.