[Solved] [AS+RS] How do I create a drill-through from AS to RS?

[Blue]

I need to create some drill-throughs on C8BI.Ã,  The documentation is unclear and even a Cognos consultant (from Cognos) couldn't help me!

1. I need to be able to drill-through from one Analysis to another by one of more dimension categories.
2. I need to be able to drill-through from an Analysis to a Report Studio report using a value from the time and another dimension.

The non-time dimension drill-through seems to work fine to RS but the time category doesn't.Ã,  There doesn't appear to be any way in C8BI to view what is being passed.

Does the drill-though pass the Category Code or the Source value from the cube?Ã,  It seems to be passing a string like "20060301,20060331".

Any help is appreciated

[MFGF]

Hi Blue,

I seem to remember answering a similar question to this back in March, but from the looks of things it got lost in the recent crash :(

From memory, when a drillthrough is defined from a Cognos Powercube in C8, the default is to pass the Category Code.  You can change this behaviour by making a small change in Transformer - go to the properties of the cube, and on the Drill Through tab turn on the 'Allow drill through for this PowerCube' checkbox.  If you now rebuild the cube and republish to Cognos 8, the C8 drillthroughs should start passing through the underlying data source values rather than the category codes.

HTH,

MF.

[Blue]

Thanks MF

I did a search in the posts before creating this new one but found nothing relevelent to C8BI.  Nothing in the Cognos Knowledgebase either.  Adn the documentation seems to have been written by someone for whom English is not their first language.

The cubes I am using all have the "Allow drill through ..." check box set already.

The problem with time is that the cube passes "20060301,20060331" (the category code) to the report prompt but the report uses values from the Month dimension table in the filter.  As only months are used the report is looking for a value like 200603 (the source value).  The Time dimension in the cube was built using the wizard though it only goes down to month at the lowest level.

[ford]

As a work around, have you tried to trim the parameter in the filter statement.  That should allow you to pass the correct values to the month dimension.  Because of the datatype you may have to do some tochar casts, but it should work.

Ford

[Blue]

Thanks

I learnt a lot about what WON'T work for drill throughs lately.  So far 3 current and 1 ex-Cognos consultants have not been able to get it working for me.

The RS report has two prompts: #1 is a string (business unit) and #2 is supposed to be a month in the form YYYYMM.

When setting up the drill through definition in Connection I've tried using both the Long Name and the category name.  (the category code business was a "red herring" idea from one of the Cognos people).  In the former case the report presents the prompt page and waits for the values to be selected.  In the latter case the prompt page doesn't come up and the report runs but no data is presented. 

I have run the report independently using the same values selected in the analysis and get two pages of data. 

This leads me to think that either A) no data is being passed to the report or B) something is being passed but is not something the report expects.

[MFGF]

Hi Blue,

Out of interest, if you open up the category diagram for your Time Dimension in Transformer and go into the properties of one of the month categories, what is shown for a) the Category Code and b) the Source Value?  Either one or the other of these should be passed via the drillthrough in Cognos 8 - the Category Code value if the 'Allow Drillthrough' property of the cube is not checked, and the Source Value if the prior property is checked.

Just curious...

MF.

[Blue]

MF.

Category Code shows a value like "20060101-20060131" and Source value is like 200601.

The latest tests show that the report is receiving the value "2006/Aug" which is the label generated by Transformer when the Time dimension categories are populated.  The report parmater expects "2006/AUG" so I have used an UPPER() function on both sides of the filter equation. 

I put the parameter into the header block using a layout calculation and the passed parameter values are display correctly but no rows are returned.  When I run this report and enter the same values manually when prompted the report returns two pages of records.

It seems something is going wrong in the filter or the conversion to SQL.

[MFGF]

Hi Blue,

A couple of other things for you to check:

1) How is the filter defined in your report?  It should be using a proper date column (not just a year/month column), and the in_range operator (eg [Orders].[Orders Fact].[OrderDate] in_range ?mm? )

2) How is the drillthrough definition defined?  It should map the Month Level of your Time Dimension (not the long name or short name attributes within it) to the parameter (eg the mm parameter above would be shown as type Date and map to a value such as [Blue Cube].[Order Date].[Order Date].[Month])

I knocked together a simple cube in Transformer, set the 'Allow Drillthroughs' property, then published it into Cognos 8.  My category codes and source values appeared exactly as yours do (ie Category Code 20060101-20060131" and Source value 200601).

I then wrote a Report Studio report against a relational package based on the same database, filtering on OrderDate (which is a date-time column in the database) with an in_range operator and a parameter as above.  I added a layout calculation into the page header to display the value contained in the parameter too.  Finally I added a drillthrough definition from the cube package to allow drillthrough to the relational report, mapping the Month level from the time dimension to the parameter.

The drillthrough works fine, and in the page header the layout calculation shows the parameter contains "Between 2006-01-01 00:00:00.000 and 2006-01-31 23:59:59.999"

If you're still struggling to make this work, let me know, and I will email my example to you so you can compare what I've done with your setup.  I already have your address (assuming it's the one you use over on the PowerHouse List).

Best regards,

MF.

[Blue]

Thanks MF

1) There is no "proper" date column in the database or the cube.  The Time dimension has Year+Month as the business key (YYYYMM). i.e. the fact data does not have day as the grain but rather month.  The Time diemsnion in the cube was built and populated using the Date dimension wizard in Transformer.  This sets the month label as YYYY/Mnn by default.

2) The drill is defined using the Month level.  I tried using the Long Name but it just brings up the report with the prompts.  The layout calculation containing the prompt value in the report header shows that the data (e.g. 2005/Aug) is being passed to the report however no records are returned.  There IS data for 2005/Aug.

The filter is defined as UPPER(RTRIM(LTRIM([Month])))=UPPER(RTRIM(LTRIM(?p_Month?))).  When the report is run in RS and I enter "2005/Aug" as the parameter value 2 pages of records are returned.

The drill through is defined as Name: p_Month, Type: Text, Required: Y, Value: [FR Ledger Time Cube].[Time].[Time].[Month]

The drill through name is "Staff By Business Unit and Period DT".  However in the "Go to" list displayed in Analysis Studio the drill through name is "Staff By Business Unit and Period".

The source cube and target report are different published packages.  The latter uses the relational tables not the cube as the data source.

The report displays "2005/Aug" in the header but no records are returned. 

This indicates to me that the Layout Calculation reads and displays the parameter as passed from Analysis Studio correctly but the filert does not. 

So far 5 current Cognos people, 1 ex-Cognos support person, and current Cognos support have not been able to figure out the problem. 

[MFGF]

Hi Blue,

I reckon your problem stems from the very first sentence - "there is no proper date column"...

If you are drilling through with a value from the Time Dimension of your cube, you need to be filtering on a date value in your report.  It doesn't matter that you used the wizard to construct the time dimension, or that it only has data to the month level, as long as it's a Transformer Time Dimension - you should still be passing date data, not text.  The trick is to construct a calculation returning a proper date column in your Report Studio report using the _make_timestamp function - plug in the year and month from your data, and put the day part in as 1.  Then filter on this using the in_range operator.

Based on the info you provided, I used Data Manager to create a relational table containing a year/month column (integer data type, format 200601), some other dimensional info on Products, and a couple of measures.  I then pointed Framework Manager at this and created a relational package from it (and an IQD).  I used the IQD in Transformer to build the data into a cube, and I created the Time Dimension using the Date Dimension Wizard based on the year/month column (Year, Quarter and Month levels).

I turned on the "allow Drillthroughs option, built the cube, then published it to Cognos 8 using Framework Manager.

Next I opened Report Studio and created a report from the relational package. My I added a calculation as above to create a date column [DateConstructed], then used this in my filter [DateConstructed] in_range ?mm?.

Finally I set up a drillthrough definition from the Cube package to the report, selecting the Month level from the Time Dimension to pass through to the parameter.  It works fine.

Give it a try - hopefully then you will be able to amend your last sentence! :-)

Again, if you're struggling to see the problem, I'm happy to email what I've done if it helps.

Best regards,

MF.

[Blue]

IT WORKS!!!!  :D

Thanks MF.  Please send a copy of your resume! :)

Now I have to go and edify a few Cognos people.