Drill through from PowerPlay to CRN

[bobg4u]

Hi,
I am building a drill through from a PowerPlay cube to a CRN Report. I am facing a couple of problems, and I hope someone can help me.
I created a drill through report that has prompts for all possible situations(say for all dimensions and hierarchies).I did this assuming that I had to set the prompts for all possible situations, because the user could select a set of dimensions and click on the drill-through button. Now, how can a make these prompts as optional and use a default value when the user does not input any value. I tried to change the properties for prompts,making them optional. I also made the filter optional.But still, the user is prompted for the values when all values are not passed. How to work around this?
Another question is - when a user selects a cell and clicks on the drill-through, sometimes PP sends the parameter values as a range and sometimes as a single value. How do I make it to send a single value,based on the cell selected?

Thanks for your help
bob

[Draoued]

If filters are  optional it should be enough.

Have you created a prompt page ?? Where do you define the default values to be used ?

In general a drill-through report contains only a report page with as many optional filters than levels in the cube.
No prompt page at all, and no default value defined.
When I get the prompt page, it s because a required filter is not set.

Concerning ranges or single value.
If you have alternative drill-down path in your cube, when drill-through from the alternative path , it will send a range of the lowest uniq value, this is how the cube is defined to work. I guess this is what is happening to you.
In 73 you can deny the drill-through from some cells and some times it s a good idea to exclude all  the alternative paths.

[bobg4u]

Thanks for the answer.
You were right, I had a prompt page for the drill-through as well as default values. Later, when I removed the prompt page, it still prompted for parameters, becuase I had only one filter for all parameters. When I separated them and made them optional, it worked well.
I have one problem though. One of the parameters is a date, and I am using it in the drill-through report as in_range( Schedule_Dt in_range ?param1?). The filter works fine, but I am unable to display the values of the date range,in the report. If I use the ParamValue, it shows as '2005-08-27:2005-09-30'. I want to display as 'From 2005-08-27 To 2005-09-30'. I tried to use the ParamDisplayValue and it shows as 'Between And ' - there are no values. Do you have any solution to this?
Thanks a bunch for your help.

[Draoued]

I guess you are the same "bobg4u" as in tek-tips, if not then same nickname with the same problem :-)

If the JGirl proposal doesn't work, what you can try it's adding to the query a fake field containing ?param1?
then you ll be able to do the trunc on the field and not on the Paramvalue.

But then Date will be mandatory when drilling-through.

[bobg4u]

Yes, I am the same guy - searching for answers everywhere!!
Anyway, I tried that option, but only the first part of the parameter is showing in the field. so , substr(?param1?,1,10) is returing a value but substr(?param1?,12,10) is not returning any value. The length(?param1?) is 9.
Any clue?

[JGirl]

Strange that the parameter is only 9 characters in length!

The substring worked for me, but I wasn't really testing a drill through, i was taking a text parameter with the value you supplied ('2005-08-27:2005-09-30')

My logic may not be 100% correct (particularly if your parameter is only 9 characters long!), but you will need to use some type of query formatting.

For the moment, in your 'fake' query, add two additional columns, one with a derivation of ParamValue("param1"), and one with a derivation of ParamDisplayValue("param1").  Add these items to the report header list & run the report to see the actual values that you are dealing with, and then alter the substr.... column derivation to be something more appropriate....

J

[bobg4u]

I tried that.ParamValue("param1") returns only the first part of the date, and ParamDisplayValue("param1") returns 'Between  And  ' ,with no date values. The strange part is that the report works fine by itself, but it gets messed up when I use it in a drill-through.
I contacted the Cognos support and am waiting to hear from them.

Thanks for your suggestions!

[mikegreen]

Thanks for the answer.
You were right, I had a prompt page for the drill-through as well as default values. Later, when I removed the prompt page, it still prompted for parameters, becuase I had only one filter for all parameters. When I separated them and made them optional, it worked well.
I have one problem though. One of the parameters is a date, and I am using it in the drill-through report as in_range( Schedule_Dt in_range ?param1?). The filter works fine, but I am unable to display the values of the date range,in the report. If I use the ParamValue, it shows as '2005-08-27:2005-09-30'. I want to display as 'From 2005-08-27 To 2005-09-30'. I tried to use the ParamDisplayValue and it shows as 'Between And ' - there are no values. Do you have any solution to this?
Thanks a bunch for your help.

Just saw this... if you have found a solution let me know....

I have a case with Cognos on this... it is an enhancement request and has a # for a future release. I have not tried it in Cognos 8 yet, but I dont think it made it in the Nov release of 8.. maybe the March release.

I ended up doing a min and max of the date for the data returned to display the range of the report... Kinda a hack that might not always be accurate (in our case it is, as we have data for every day in a calendar table regardless if data is returned).

Cheers,

Mike

[bobg4u]

I haven't yet found a solution. Did not hear from cognos though it has been about 3 weeks.
Will post the solution if I find one!

[gilad]

I haven't yet found a solution. Did not hear from cognos though it has been about 3 weeks.
Will post the solution if I find one!

Hi ,
I have a similar problem drilling through with subset dimension.
In the drill though assistance window I can see the application is using ââ,¬Å"in_rangeââ,¬Â
I thinkââ,¬Â¦Ã¢â,¬Âinââ,¬Â with all subset values will be more appropriate.
Please let me know if you have some news about this issue