Generic target report should render based on the selection of measure in master

[xyz]

Hi All,

I have a drill through report in which in master report there are multiple measures, which have links to different target chart reports, data source is MSAS cube.

Now my requiremnt is, I have to create a generic or single target chart report, where it has to render the different measure charts selected in the master report.

For that I creaded one parameter in target chart report called as ?p_line_chart? with the following condition in target report filter.

case when (?p_line_chart? = 'c')
then ('1'='1')
end

In the master report I created one data item called as linechart, in which I have written expression as 'c'.

I created a variable in target report and was trying to render the two different chart based on the below condition.

case when ParamDisplayValue('p_line_chart') is not null then 'A' else 'B' end

But report is not working as expected.

Actually value 'c' is not getting passed from the master report. I am unable to understand what is going wrong.

Can you guys help me, what is the mistake I am doing, is my way of approach is wrong, your help will be much appreciated.


Thanks & Regards,
xyz

[navissar]

We're all doing our best :)

Let me start by saying that A. you should NOT be using case statements over dimensional, and B. you should DEFINITELY NOT be using filters over dimensional.
Having said that, the way I understand your problem is this: You have 3 objects in your source reports (e.g. three crosstabs) which are essentially identical except for the measure.
You wish them to drill to the same source report which will then show a chart for the relevant measure.
OK, here's a suggestion based on your line-of-thought:

In TARGET report:
1. create a text box prompt, with the parameter "measurePRM". set its visibility to none.
2. Create a String variable with paramValue('MeasurePRM'). Its possible values are 1, 2, 3.
3. Since you already created different chart objects, just assign them the variable in 2 as Render variable, rendering each of them only if 1, 2 or 3 respectively.
4. SAVE.

In SOURCE Report:
1. For each query add a data item "Drill", set its value to 1, 2 or 3 respectively. (Numbers will work so much better than text).
2. For each crosstab (or whatever object is there) add the "Drill" data item in properties of rows or columns.
3. Set the drill through to pass "Drill" into the 'MeasurePRM' parameter.
4. Save.
5. Test.

[xyz]

Thanks for the reply Nimrod,
Your help is much appreciated.

Actually I have a summary report which is built using table as it has multiple queries, in each cell different queries with different query items are used. So in my requirement there is a column for micro charts and column for measure's where column with measure chart is a text item like 'Net Sales', 'Labor Hours' text with drill through links, but actually micro charts and measure columns have drill through's. Micro charts have drill through to target report, which brings up line chart and Measure column with brings up detail list report.

I followed your steps, but the dataitem value from source report is not getting passed, 'null' is getting passed. I don't understand, why value is not getting passed, in the drill through definition in parameters window 'Method' property to target report used is 'Pass data item value' and 'Propert to Pass', I tried Default, Member Caption and Member Unique Name but no luck.

Can you please, let me know, where am I doing wrong.


Thanks & Regards,
Abdul

[xyz]

One more thing, I forgot to inform I tried the drill through to the same report as well as to the seperate report.

[xyz]

Can any one please help me on this, as this has to be solved in another one hour.

I tried the in Cognos 10.1 as well as Cognos 10.2 versions, but no result.



Thanks & Regards,
xyz

[navissar]

Hi Abdul,
Sorry, we're all doing our best to answer as quickly as possible, but I'm afraid no one in this board - not even Sir Muppet himself - can guarantee to meet your deadlines...

As for your question, since NULLs are passed instead of whatever's in the data item value, it means Cognos cannot access whatever's in the data item. The usual reason is that the data item isn't a part of the query sent to DB. That's why you should make sure that your data item is checked in the properties of the crosstab's column/row or in your list's properties or in the properties of the x axis/series in you chart (depending where you drill from). Please verify that your data item is indeed checked in the properties.
For crosstabs: Highlight the relevant crosstab row/column node (If this is a drill from the intersection, either row or column should be fine).  In the properties pane look for "Properties", click on the ellipsis (three dots) - a list of all data items in the query will open up, check the mark for your data item and click OK.
For List: Highlight the entire list, in the properties pane look for "Properties", click on the ellipsis (three dots) - a list of all data items in the query will open up, check the mark for your data item and click OK.
For charts: Highlight the relevant chart x-axis/series node.  In the properties pane look for "Properties", click on the ellipsis (three dots) - a list of all data items in the query will open up, check the mark for your data item and click OK.

[xyz]

Hi Nimrod,

Thank you very very much, the solution which you said is worked out for me, when I assigned the data item in the properties of the chart. Similarly for the measure column which is text and kept in a table cell, I have used a singleton and assigned the data item in the properties of the singleton. It worked as expected as per my understanding. Measure text will navigate to detail list report
and micro chart will navigate to line chart.

But when I had a call with my on site counter part they were saying we do not want to have multiple chart reports in single report and render based on the selection made in the master summary report rather they want a single report which render based on the selection made on the master report.

So my thought is to use 'case statement' to render the single report based on selection in the master summary report, but the problem is we made the package to DQM for performance improvement. so using 'case statement' against DQM might not work.

So can you please suggest me how to go about it :)

Thanks & Regards,
xyz

[navissar]

Hey there,
You're not making my life any easier, are you?  :D

Anyway, I'm not sure I followed all your scenario - maybe a screenshot or an XML would help - but anyway, here's what I got:
Suppose there are two objects you drill from - one is showing Measure1 and the other is showing Measure2.
Your target report has one chart/crosstab/whatever, and the measure needs to be fluid.
Also, this is a dimensional data source, so NO CASE STATEMENTS!

Here's the setup:
In your target report, you pull both measure into the query. You rename the data items to [1] and [2] accordingly. Why? Because we're going to trick Cognos, that's why.
You drag in the query a data item and set it up like this: #sb(prompt('measure','token'))#
You use this data item as your measure.

Source report: add a calculated member on one of the hierarchies, set its value to be 1 for measure1, 2 for measure2.
MAKE SURE IT'S IN THE PROPERTIES.
Set the drill to pass this data item to the measure parameter in target.

This should work. And I know this, because I have a working sample over 10.1. Restore target as target and source. You may have to define the drill through (because it is relative).

[xyz]

Hi Nimrod,

Thank you very much for the reply.

As suggested by you, I followed your steps but could not get the result as expected.

I am unable to see the full code of yours as I don't have samples installed.

Two drill throughs are happening from source summary report, one of 13 Week Trend column micro chart, which drill through to the Line chart target report and one on Measure column, which drill throughs to the target list report.

I tried creating a data item in the target detail list report with the expression, #sb(prompt('measure','token'))#, but the values are not coming correct on the list report.

Please find the screen shot of source report and target report. My target report contains calculations on different measures. Basically the measure in the detail list report i.e (target) is giving some junk value, which is not correct.

Hope the screenshots help you regarding the report requirement.

Is it possible to pass the pass the string values like Net Sales, Total Transactions to target report, instead of numbers.


Thanks & Regards,
xyz

[xyz]

Hi Nimrod,

I am eagerly waiting for you reply though mean while I have used the Case statement to achieve the requirement, as we upgraded to 10.2.1.1. Case statement is working in 10.2.1.1 version even though package is DQM enabled.


Your suggestions will be greatly appreciated.


Thanks & Regards,
xyz