Expand and Collapse without DMR

[CognosNani]

Hi,

I have been asked to do a report with Expand and Collapse feature that has 9 levels. The following example worked perfectly fine, until I realised the underlying data set has too many rows (150K) for a month (typical report selection) and the report is either failing to run (due to memory issues) or taking forever to render as it needs to fetch all rows first.
http://www.pmsquare.com/main/custom-javascript/custom-javascript-in-cognos-expand-and-collapse/

User also has a requirement to drill through to detailed reports (already built) depending on the level.

We do not have any DMR models, however I quickly tested the following but I was not able to get it 100% working (not sure if this is due to the incompatible with Cognos Analytics 11.0.6), even if its working I am not sure if its easy to drill through to existing reports that are based on non-DMR model.
http://cognospaul.com/2014/09/29/expand-collapse-cognos/

Thank you in advance for any input.

[CognosPaul]

The first solution does require all of the rows rendered on the page. It's not at all suitable for anything more than a thousand rows (depending on the browser and available ram).

The second is using a combination of JavaScript and standard dimensional functions. When the user clicks on the + icon, it adds the memberuniquename of the selected node to a hidden prompt. The expression then returns the children of each of those members to the main hierarchy. It's much more efficient because it only requires the open nodes to be rendered and pulled from the database. Without an OLAP or OLAP-like environment, like DMR, this solution won't work at all. DMR isn't that hard to implement, so this might be your best solution.

If you're absolutely stuck on relational, how about a series of drills? You could make a self-drill, passing the level expression and the key. This really works best if every member in your hierarchy has a unique ID. If the levels aren't unique it should still be possible, but it will be significantly harder. There won't be a hierarchical layout, the rows will always show the children of the member the user drilled on. But if you have it open in a new window, the user can ctrl-tab back and forth between the open tabs easily.

[CognosNani]

Thanks Paul for quick reply.

I agree DMR can solve the rendering issue, and have started working on it, however I am finding it difficult to drill through to non-DMR reports.
If its static Hierarchy, I can pass appropriate property (level unique name), however since the underlying cross tab only has one level (parent) and not knowing where the drill through request come from, not sure how I can setup drill through definitions. Any ideas?

Thanks in advance.

[CognosPaul]

Are you using DQM or CQM? DQM is a little easier because you can use a macro case statement, but a regular case will work here too.

You need to use a prompt macro with a case statement to create the rows, and the code behind the rows and the filter.

I'm having a little trouble getting past the malicious code checker on the forum, so I just created an example report.

[CognosNani]

Thanks Paul. Report really helps.

I have extended it to 2 more levels and is working great.

However I am now in the process of enabling drill throughs for the actual measure values which should take to the detailed report. Since my hierarchy is not natural and have same values in different levels, I am not sure how I can preserve all of the parent values and assign them to my detailed report.

I think you already mentioned above that it is difficult but possible. any ideas? hoping it will finalize my solution.

Thanks..

[CognosPaul]

In this case you need to construct the filter expression on the fly. In the attached example I've added it as a text item in the crosstab node, but you can remove that and add it as a property of the node.

prompt(filter expression,default 1=1) + ' and ' + code case statement as a string + ' = ' + cast(code,string)

Each time it drills it passes that filter expression, and extends it to include the rows.

You can create a filter display just as easily.

[CognosNani]

Hi Paul,

Thanks for the updated report, however it seems you extended the previous relational report. I have verified with the business user and are happy with workspace solution using a DMR package(i.e. parameter enabled expand and collapse). Expand and collapse works fine, however I am now struck with drill throughs where MUN is getting passed from DMR report to relational report. As I mentioned earlier my levels are not unique therefore will need to be filtered on all the levels.

For e.g. I have this MUN getting passed from DMR report to relational [DIMENSIONAL].[Region].[Region].[Postcode]->[all].[VIC].[ABC].[123]
I will need to split this into State = 'VIC' and City = 'ABC' and Postcode = '123' filter in the relational report.

I cannot figure this out using Cognos and SQLServer functions.

Can you help?

Thanks,
Nani

[CognosPaul]

Instead of passing the mun, modify the model to include the various codes as properties of the Postcode level. Then in the report pull those data items into the query. On the crosstab node add them to the properties, and reference those when defining the drill.