Display the Date Range Parameters in Reportnet

[bobg4u]

Hi,
I need to create a drill-through Reportnet report from a PowerPlay cube.The PP cube sends a range of dates,which I am using as parameter to the Reportnet filter. I am using the in_range function(Schedule_Date in_range ?Sched_date?). The data seems fine, but I am unable to display the dates on the report header. I want to display the start and end dates of the date range. I tried the DisplayParamValues but it does not work. I tried to store the date parameter in a calculation field and parse it to get the dates,but only the beginning date is being passed.

Can someone please help me.

Thanks
Bob

 

[JGirl]

Create a text item in the header and use the report expression

ParamValue(?Sched_date?)

That should display the range for you.

J

 

[bobg4u]

Hi JGirl,
ParamValue(?Sched_date?) dispalys value as '2005-08-27:2005-09-30'. I want to display "From 2005-08-27 To 2005-09-30".

How can that be done?

Thanks

 

[JGirl]

Yes - that is what is displayed, because that is what is being held as the parameter.

The parameter values are stored as strings, but you can't apply too many functions to them when they are a text item in the header.

To get around this, you'll need to create a new dummy query in the report (something that only returns one record as you'll need to have the record displayed as a list with one cell in the header), and have query item(s) which calculate the display value from the parameter. Only here can you use all the functions that you will need to format the parameter. For example, you would use something like

'From ' || substring(cast(?Parameter1?,varchar(50)),1,10) || ' to ' || substring(cast(?Parameter1?,varchar(50)),12,10)

Of course, this assumes that you always receive a range as the parameter, formatted like yyyy-mm-dd:yyyy-mm-dd, which may not be the case if you drill through for a single day, or if you can select multiples, so your logic will also need to cater for that - maybe through a case statement.

J

 

[bobg4u]

Thanks for the reply.
But, when I tried to do that, there is only one date in the parameter. I turned on the Drill-though assistant and it shows the values being passed as '2005-08-27:2005-09-30',but when I display it on the report it shows as '27-AUG-05'.If I do a substring of the parameter, I only get the first date and not the end date. The length of the parameter is showing as 9.I am using the parameter as a filter in the report and I am using it as "Schedule_Date in_range ?Parameter1?.

Also, I am unable to cast the parameter as a varchar(50) or even as char. What could be the problem?

 

[Nuffsaid]

Hi,

Have you thought about using 2 parameters such as ?from? and ?to? in your main report and then pass both to your drill through? I mention this because I screwed around for almost 2 days trying to do exactly what you're doing with no success. Pass ?from? and ?to? and then create an expression like;

'For the period '+ParamDisplayValue("from")+' to '+ParamDisplayValue("to")

Nuffsaid.

 

[Draoued]

Nuffsaid , it is not possible to use ?from? and ?to? for a drill-through report from Cubes to CRN .

 

[Nuffsaid]

Draoued, Well that's unfortunate because as far as I know (I'm no expert mind you) you can't display "range" parameters in report studio.

Nuffsaid.

 

[bobg4u]

Nuffsaid,
You should be able to dispaly the range parameters. The ParamDisplayValue should techinically return "Between ?from? And ?to?", without us having to specify "From ?from?" and "To ?to?". ParamValue will display the parameter as it is(for eg 2005-08-27:2005-09-30). In my case the ParamDisplayValue is not showing any values and ParamValue is only showing the first part(i.e 2005-08-27).And this happens only in the drill-through and not if I run the report by itself.
I hope I did not overlook something.
Contacted the cognos support - waiting to hear from them!