Drilling through from OLAP cube report to Relational report

[leahmarie]

Hey guys,

I was hoping that some of you have some experience in drilling through reports that have different packages.  Both of the reports work fine by themselves, and I have even gotten the parameters to pass through, but even though they are passing they are not populating my target report?  Does anyone have any idea why that would be?  The reason I know that they are passing is because I used a layout calculation to see.  I feel like I have tried everything I can think of to make this work.  Any help at all would be tremendously appreciated!

I should mention I am trying to pass a date field and a company field; from the cube report to the relational report.

Thanks! :D

[Arsenal]

gee, I did cube to relational drill through reports a good 3 years back. In fact, I betcha if you search on my username, you'll find posts going back asking questions about cube to relational drill throughs  ;D

I am trying hard to remember the issues...cubes pass date range so your detail report will have to have an in_range filter for the date. There was also something about the drill definition on cognos connection. Think it has to do with setting the scope to the entire package or something. Cube must also have the Allow drill property set on the cube properties (right click the powercube and select properties). Also, you have to make sure that the category codes in the dimensions match what the report is expecting, i.e. the source code of the dimension and the filter source in the report must have the same values, else you'll get a blank drill report!


These are some of the things I remember off the top of my head..like I said, you can search for my username and find older posts. Cognoise folks were very helpful at the time. I'm sure others have also posted questions about this issue so a search can help you.

Also, if you post the error message you're getting (such as generated prompt page coming up when drilling or drill report shows up blank etc.) folks will be able to help you better

[leahmarie]

This is what I was afraid of! I don't have access to set the scope on the package, and also do not have access to allow the drill property on the cube. Ugh. I think that my definitions are correct, but like you said the codes are not exactly the same, so even though the parameters are passing the report is still empty.  So unfortunately; no error message, but blank drill report. I was hoping there was a workaround for it! Thanks so much for the quick response:)  The weird thing is I disabled using one of the parameters in the drill through definition and made it required on the target report to see if the other prompt was filtering correctly and it filled the value prompt correctly, but when I selected the other "required" prompt that I did not allow to pass from the source, it still didn't work, so I thought that was weird.  Because if it is filling the value prompt with the correct value and I manually select the other it should populate right?

[MFGF]

Hi,

What are the data types of the values you are filtering on in the target report? Are any of them dates?
How have the parameter filters on the target report been coded?  Can you show us the expressions?
What version of Cognos 8 are you running?
How are your drill through definitions defined - are you passing the key values or captions or... ?

There were some specific issues in early builds of 8.4 that resulted in problems with passing dates from OLAP to relational when drilling through. Is it possible you are running into these?

Regards,

MF.

[leahmarie]

I'm using version 8.3 but I did read about some of the problems with 8.4.  One of the parameters is a date, so I am using the in_range function on my prompt on my target report, and passing a data item from the query in the source report.  Example:

[Accounting.period] in_range ?period?

In drill through definition: 2011 August, tried passing default, member caption, and business key; all passed the value but did not populate the report.  Here I could only pass data item in the drill through definition because the source report doesn't prompt for the date.  Again, it passes the parameter I ask it to; just does nothing to populate the report.

[Company]=?company?

Which I used both the parameter value from the source report for company and also tried using a data item from a query in the source report.  Both passed through the parameter correctly, just didn't populate the report again.  Here, I tried all of the options available and got the same results; parameter passing but not populating report.

I really think that it has something to do with the two packages not having the same category codes or descriptions or whatever it is that must be the same in order for it to work.  I don't have any experience except for the reporting side though; being I'm just a business analyst.  But I have been working with Cognos long enough I feel like to know that it can be particular.

[MFGF]

Is the [Accounting.period] item in your relational package a date-time datatype?  If you run the target report standalone, do you get a date prompt with calendars and clocks to allow you to specify the start and end dates and times for the range?

If you want to see what is being passed to the target report when you drill through, you might try temporarily adding a couple of layout calculations into the page header of the target report, each one using the ParamValue() function to display your two parameters. You should see a date range being passed for the ?period? parameter - it should look something like  2010-01-01 00:00:00.000 and 2010-01-31 23:59:59.999 if you select January 2010 in the source report.  This might also help you to see what is going on with your Company parameter too - it may be that your target report is filtering on Company name but the parameter being passed is the Company Code?

Regards,

MF.

[leahmarie]

Thanks for the reply again,

But I actually already have the layout calculations on the report, that's how I knew that the parameters were passing but not actually populating the report.  In the layout calc the company says exactly what it should; I even tried removing the date prompt completely just to see if the company by itself would work and no such luck.  The date range passes almost like what you mentioned, but no times.  I definitely think that there's a problem with the two date functions not matching up; the default for the cube is 2011 August while the default for relational is 2011-08.  So I think that that is the major problem with the dates, but I just really don't understand why the company part will not pass. The spacing, capitilization and everything is exactly the same.  But the answer to your first question accounting period is not date time.  It just simply year-mm.  But I have tried passing tons of different combinations to get that to work.  I talked to our developers and they say that since my source report isn't filtering for dates then it is passing all accounting periods and that's why it's not working, but like I said; even without the accounting period it still doesn't work....soooo...I am stumped.....  :-[

[Arsenal]

for the company, suppose you run the drill through as a standalone and on the generated prompt you put in the exact same value that the cube is passing along then does the report work?
I'm thinking that perhaps the source that the cube is using for the company dimension is different than the company column that the report is using. If that is right, then the above standalone way should also not work.

For the date, as the Muppeted One suggested you might want to look at what the date column in the report is expecting. I think...... as in MF's example if you're drilling from the day level of the cube but the date column that is being used in the filter of the target report is just a  Month and year column then that might be a problem in which case you'll have to drill from the month level of the cube's date dimension or have the target report filter on a full date column...at least I think this might be an issue. Sorry, I can quite recall all the issues I had faced :'(
but I definitely remember the date passing part being of particular frustration. Keep at it.

Also you may want to ask the development team to let you know the source fields for the date and company dimensions and the corresponding columns in Framework so you can compare them

[bdbits]

I thought of a couple of things I found useful dealing with drill-throughs, which can be frustrating at times. Hopefully I am not talking beneath your level of expertise but I figured it was better to brain dump a few things in the hopes something might click. Also I am assuming this is straight report-to-report drill-through. If it is a package drill-through there can be other issues.

Are you using the "related links" dialog when drilling through (right-click the drill-through link)? If so and you have enough capability permissions, you may have two options. On the lower right check for "view passed source values" - these are the source display/use values selected from your cube report. Second, the report name itself should be a dropdown from which you can choose "view target mapping" - these are what your relational target report is going to receive. You will sometimes find differences between display and use values. You can also see this difference with ParamDisplayValue() versus ParamValue() in your layout calculations. I point these out because sometimes you are being passed a MUN but it might be displaying a value of some kind, or a similar unexpected display/value pairing.

When actually defining the drill-through from the source report, you may have some options. There are multiple methods, "pass parameter value" versus pass "data item value". As well, you can pass a variety of source properties - MUN, caption, business key, etc. Make sure the right things are selected.

If the target report is complicated, it may be useful to create a simpler report just based on the parameters you are working with to work out the issues.

I am not sure if we would be able to tell anything by the XML specs alone, but you might export those for the two reports and upload them here.

If you have access to the Transformer and Framework Manager models, it can be revealing to see how the columns you are working with were modeled.

That's all I have at the moment. I hope something in my ramblings helps.

[leahmarie]

Thanks everyone for all of the help.  I think I got somewhere further today with it; although still not resolved.  The problem with the company code not passing was not as I believed; but rather a gotcha moment where my relational report has three different prompts for the different levels and the cubed report only has one because it is a heirarchy; I had believed that passing the parameter level would automatically work but instead just tried disabling the district and bottom level and only passing the division that i was testing and the report populated.  Which I guess means that since the end user is going to be using this report for all different levels at different times means I will have to instruct them to follow a different prompt for each individual level. Annoying but oh well at least that part is working.  Also- my accounting period in the relational report is not actually a date but rather a string which is why the date is not passing from the cube report.  But I found another date field that I can use from the relational report that works just as well and am now attempting to make the parameter pass from the cube.  So hopefully I will have it resolved by the end of the day.

bdbits- I don't have access to edit related links:(  I tried though and thanks for the suggestion!