Drill Up and Down and Drill Through in Cognos Report 10.2.2 Dynamic Cube

[GeethaKL]

Hi,
I have a requirement to perform both Drill Up and Down and Drill through on a Cognos Stacked Column Chart.
Environment: Cognos 10.2.2 Report Studio
DataSource : OLAP (Cognos Dynamic Cube)
DAtabase: SQLServer

I have developed  so far
1. A Cognos Report(Stacked Column Chart) with 10 or measures on Series and a set consisting of Children of two member levels from the same level and same Hierarchy.
Categories: Set (children of two Members of the Level from the same Hierarchy)
Series: 10 Measures.

2. Applied Drill Behaviour to go down the levels(Drill down) and Drill Up(to go to the levels up) using Data Menu --> drill Behaviour

3. Created HTML Item and showed as an icon to click to go the Target report(to see other details) on the Top of Report page

All good so far

Requirement:
1. When the user clicks any of the categories(Children of member levels)of the Summary Report to drill down,  its going to lower levels, which is good. The requirement now is when the report is at that level(Drill down) , if the user wants to see details at that level, in the target report, how to achieve it?
  Is it achievable? If yes, how to do it?
2. Is it possible to put a button next to the report to mention Drillup, instead of right clicking the item and click Drill up

Please let me know whether these two requirements are achievable?

Thanks in advance

Regards
geetha

[MFGF]

Hi,
I have a requirement to perform both Drill Up and Down and Drill through on a Cognos Stacked Column Chart.
Environment: Cognos 10.2.2 Report Studio
DataSource : OLAP (Cognos Dynamic Cube)
DAtabase: SQLServer

I have developed  so far
1. A Cognos Report(Stacked Column Chart) with 10 or measures on Series and a set consisting of Children of two member levels from the same level and same Hierarchy.
Categories: Set (children of two Members of the Level from the same Hierarchy)
Series: 10 Measures.

2. Applied Drill Behaviour to go down the levels(Drill down) and Drill Up(to go to the levels up) using Data Menu --> drill Behaviour

3. Created HTML Item and showed as an icon to click to go the Target report(to see other details) on the Top of Report page

All good so far

Requirement:
1. When the user clicks any of the categories(Children of member levels)of the Summary Report to drill down,  its going to lower levels, which is good. The requirement now is when the report is at that level(Drill down) , if the user wants to see details at that level, in the target report, how to achieve it?
  Is it achievable? If yes, how to do it?
2. Is it possible to put a button next to the report to mention Drillup, instead of right clicking the item and click Drill up

Please let me know whether these two requirements are achievable?

Thanks in advance

Regards
geetha

Hi,

Not sure if you can figure out how to adapt it, but I outlined a technique for this using a crosstab a few years ago:

http://www.cognoise.com/index.php/topic,19458.msg76382.html#msg76382

Cheers!

MF.

[Stepharia]

I've been able to do this with a crosstab by passing through the Level Unique Name property to and/or filter on the drill-through.
e.g.
(?pLevel? = '[Cube].[Customer].[Address].[Country]' AND [Country] = ?pArea?)
OR
(?pLevel? = '[Cube].[Customer].[Address].[State]' AND [State] = ?pArea?)
OR
(?pLevel? = '[Cube].[Customer].[Address].[City]' AND [City] = ?pArea?)

In the above example I would pass the same data item to ?pLevel? and ?pArea? but the Property to Pass would be different (?pLevel? using Level Unique Name and ?pArea? using either Member Caption or Business Key depending on the data).
For this method to work you will need to check what the relevant level unique names are so you can hardcode the options in your drillthrough filter.

[GeethaKL]

Thank you MFGF and Stepharia.. Will try to apply the technique and post the outcome
Regards
geetha

[GeethaKL]

Hi All,

Got busy with other tasks and tried to solve the above task as below

Just a recap -  Source: Cognos Dynamic Cube  10.2.2 Report Studio --> Requirement - Apply DrillUp Drill Down and Drill Through from any Level to any Level.
Summary Report - Stacked Column Chart(Source)
Detail report - List(Target)

Initially I tried this in Crosstab (Source Report) and List (Target Report)
Drillup and Down: Data -> Drill Behaviour
DrillThrough: Used DrillThrough Definitions on Crosstab Node Member(Rows)
and passed the parameter whatever i used in Target Report
Its working all good

I could not apply same logic on Charts(Correct me if i am wrong) as crosstab. I could not apply both Drill Up and Down and Drillthrough at a time on Charts. Drillup and Down takes the precedence.
Hence I used Javascript for drill through to pass the parameter
DRillup and Down - Data--> Drill behaviour.
Drill Through : Used Javascript on Source report to pass the parameter
'<script>'

+' var paramStockString = "' + [Queryname].[DataitemName] + '";'
+' var paramStockStringArray = paramStockString.split(", ");'
+' for (i = 0; i < paramStockStringArray.length; i++) {'
+' cognosLaunchString = cognosLaunchString + ", " + "\"p_parameter\"";'
+' cognosLaunchString = cognosLaunchString + ", " + "\"" + paramStockStringArray + "\"";'
+ '}'
+'</script>'

Its working all good

Cheers
Geetha

[GeethaKL]

Hello All,
Again issues on this.
Drill through using HTML item on the top of the chart working fine in Report Studio as expected.
There is an updated requirement on this Drill Up and down and Drill through task and the user wanted to see this as a widget in Cognos Workspace.

I thought it is very easy update. To my surprise, Drill through via HTML item is not working as expected in Cognos workspace.
Is there any issues with Data Item Values or Labels in Cognos Workspace?

'<script>'

+' var paramStockString = "' + [Queryname].[DataitemName] + '";'    --> Dataitem
+' var paramStockStringArray = paramStockString.split(", ");'
+' for (i = 0; i < paramStockStringArray.length; i++) {'
+' cognosLaunchString = cognosLaunchString + ", " + "\"p_parameter\"";'
+' cognosLaunchString = cognosLaunchString + ", " + "\"" + paramStockStringArray + "\"";'
+ '}'
+'</script>'


Please let me know whether it is a bug in Cognos workspace as it is working fine in Report Studio??
Pls advise whether there is any workaround to get the HTML working in Cognos Workspace??

Thanks in advance
Regards
Geetha