Issues passing multiple values into a target report

[sag4cognos]

Hello All,

I am building reports using Cognos 8.4 BI and TM1 9.5.

I have created a drill through report and I have an issue at hand. Can any one please suggest me any ideas.

Issue :
I need to pass multiple values ( the number of products a customer purchases)  into a target report. The target report has the IN criteria in the filter.  I tried passing Query Item values instead of Parameter values to the detail report via the drill-through.But, The child report does not run.

When I was testing the drill through, I Clicked on the Go-To Links to view the passed source values.

If I am passing a Parameter value ( i.e ?Products?) the table within the Goto Links has the correct Product value I am passing to the child report.

If I am passing a Query Item value ( i.e [Product] ) the table within the Goto Links does not have any value. And hence the child report fails to run.

I am not able to break this issue.  ??? Any help or suggestions on this ?

Thanks for your time,
Sagar

[MFGF]

Hi Sagar,

Is the target report a relational report, or is it also based on the TM1 cube?

MF.

[sag4cognos]

Hello MF,

Thank you for your time.

The parent report is built on the TM1 cube, while the child report runs on a relational model.

It's a report, which was built on a view created on a SQL Server database.

Thanks,
Sagar

[MFGF]

OK.  Thanks for the info.  A couple more questions if I may:

1. How is the filter defined in the target report?  I'm guessing [Item] in ?Parameter?  Is the item a product code or a description?

2. If you expand the package tree in your source report and find one of your product members then right-click and into Properties, does the value at the end of the MUN (Member Unique Name) correspond to the item you are filtering on in your target report?

3. Is the drill-through definition explicitly defined in the source report or is it defined on the TM1 cube package from Cognos Connection?

MF.

[sag4cognos]

Hello MF,

Answers to your questions.

1) How is the filter defined in the target report?  I'm guessing [Item] in ?Parameter?  Is the item a product code or a description?

Yes, you guessed it right. The filter is defined it as [Item] IN ?Parameter?. The Item is a Product Code - but the data type is a string.

2) If you expand the package tree in your source report and find one of your product members then right-click and into Properties, does the value at the end of the MUN (Member Unique Name) correspond to the item you are filtering on in your target report?

Yes, It does correspond. As I had mentioned previously, the drill through works fine when I am passing Parameter values ( one value ), the child report runs fine.

3) Is the drill-through definition explicitly defined in the source report or is it defined on the TM1 cube package from Cognos Connection?

I have tried both the options, MF. I did define it in the source report and also defined a drill through connection on the package. However, it did not work for me.

Guess, I am missing a point some where. I do not know where exactly.

I really appreciate your time on this.

Thank you,
Sagar

[MFGF]

Hmmm...

I just tried this with the samples shipped with Cognos 8.  I set up a relational report from GOSales based on Product Lines and Product Types (I used Product Line Code, Product Line, Product Type and Revenue as my list columns).  I added a detail filter: [Product Line Code] in ?pl? then saved the report.

Next I added a drill-through definition (from Cognos Connection) to the Great Outdoors Sales cube package.  I set the scope to be the Product Line level of the Product dimension, and the target as my saved report.  On the next page I mapped the Product Line level from the Product dimension to be passed to the pl parameter, then saved the definition.

When I now create a report based on the Great Outdoors Sales cube, provided I bring in Product Line values, I can select multiple Product Lines, right-click and Go To, click on Related Links, and choose my target report.  This then runs and displays the Product Lines I selected, so it all seems to work OK.

When you define the Drill-through definition in Cognos Connection, what are you mapping to the parameter in the target report?  Is it the relevant level from the dimension, or are you mapping an attribute?

MF.

[sag4cognos]

Hello MF,

Thank you for taking the time to do the report. I really appreciate your effort on this.

Well, as you had mentioned, in the creation of Drill through ( Created on the Package - using Cognos Connection ), I had not mapped it to a dimensional source. When I changed it to the dimensional source, the prompt value gets passed.

I guess, I made a mistake in describing my problem. Let me give you the picture. My dimensional source has a level called - Total Products. The member elements are plenty, but each manager, based on his access, gets to see only few products.

Total Products
|-> Product 1
|-> Product 2

When the user selects a single product, the product gets passed as a parameter to the report built on the view. But when he/she does not select any product and clicks on Total Products, "Total Products" is passed on to the child report as the parameter value.

The data in the view is at a Product granularity, which means you would not be able to find the word "Total Products". What the user wants to see is all the data pertaining to all the products he has access to, when he chooses "Total Products".

To implement this,in the parent report, I created a data item - Descendants([Total Products],1,Self) and introduced a filter in the parent report query ----> [Reporting Cube].[Products] = ?Products?.

My belief is this, when the user selects one product, the filter in the query eliminates other products and hence the data item will have only that product, which he had selected. If he did not select anything, the filter will not eliminate anything, which means, all products he has access to, will flow into the data item.

I had used that data item into the drill through, created at a report level. It is this drill through, which is not allowing me to pass the values to the child report. The drill through which I had created using a Parameter value ( where i had directly passed the prompt value works fine). Please advice.

Thanks a ton for your efforts on this.
Sagar

[sag4cognos]

Just to elaborate on the design.

The entire work-flow comprises of three reports.

1) The main summary report comprises of "Total Products"
2) The detail report lets the user to view the data at a lower granularity - either at Total Products or at individual Product level.
The above mentioned two reports are built on a TM1 cube.
3) The transactions report - built on a view on a relational source.

The issue is between the stage 2 and 3, where the prompts move from a report (dimensional source) to a report (relational source).

Thank you for your time,
Sagar

[MFGF]

What happens if you change the filter in the target report to be optional?

Does this allow all products to be displayed when selecting the Total Products member in the source report?  Is this what you need? (not sure!  :) )

MF.

[sag4cognos]

Hello MF,

Thank you for your time.

Answer to your question -  If I do not have the filter or make it optional in the target report, I will end up seeing data for all products.

The view, on which the report has been built, has been created on an OLTP system. The data is real time ( almost ) and there is not much of a security ( I mean, restricting user access on a row basis )

This means, I end up getting all products for that specific time period, even though, I may not have access to those products in the cube.

To put in another way, cubes have data security in them and hence the user sees the products, he has access to. But, the view does not have such security and hence if the filter is removed, he gets to see all products.

So, its highly imperative for me to have that filter in the target report.

Any suggestions?

Thanks,
Sagar

[MFGF]

Would it make sense to define the relevant security filters in Framework Manager (in line with the cube security) so that users only see products they have privileges for?  That way, you could leave the filter as optional in the target report and get the result you need.

MF.