Static numerical calculation in a crosstab query?

[kaevne]

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? 

[rockytopmark]

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.

[kaevne]

:/ I'm accessing a relational database so I can't use a calculated measure.

[kaevne]

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.

[rockytopmark]

Well Done!

Thanks for sharing your solution!