I have a crosstab query that displays a bunch of data. I want to create a new data item that is just one of the current ones divided by a number.
The number is calculated as _days_between(?EndDate?, ?StartDate?) which are just the number of days between my prompt values. It remains the same through a single report.
However, when I try to create a new data item that is one of the current data items divided by _days_between(?EndDate?, ?StartDate?), I get this error: 'fn:_days_between' function is not supported in the context in which it is used.
I think it's because Cognos doesn't know how to calculate _days_between in the context of each column? I created a separate singleton to test the function and it returns the correct number for the prompt values.
I want it to look like this:
(Days between: 6)
Dean Bob Erin
Total Session Time 600 660 540
(New)Average Session Time per Day 100 110 90
Anyone know how I can create this query?
Haven't tried this before, but you probably need to create the _days_Between() expression in a Calculated Measure, before you try to use it in your new expression.
:/ I'm accessing a relational database so I can't use a calculated measure.
Ok, figured it out. This thing was a huge conundrum but after playing around with it I finally found the solution.
I created a new data item in my crosstab query called [days] with the expression _days_between(?EndDate?, ?StartDate?)
However, in the crosstab, this data item would come out as:
Dean Bob Erin
6 <blank> <blank> <blank>
It was obviously calculating the correct answer (6), but 6 only appeared at the row title because I guess Cognos didn't know how to calculate _days_between for Dean, Bob and Erin?
So I changed the property 'Rollup Aggregate' to "Total" and then this appeared:
Dean Bob Erin
6 6 6 6
Now I could use the new data item in the other data items in whatever arithmetic I wanted and it would calculate it correctly. Hope this helps someone.
Well Done!
Thanks for sharing your solution!