showing a date interval as a fact (CRN) 2

[rtveen]

Hello,

Within a Report I'm substracting two datetime fields to calculate the interval. This works fine in the tabular model. But when I want to put this interval in a crosstab as a fact ReportNet displays it in milliseconds.
I've tried to adjust the dataformat to interval with showing the hours etc. This doesn't work.
The strange thing is that when I add the field as a dimension, ReportNet shows the interval.

How do I solve this?

 

[mayo04]

Hi,

I have never used reportnet so I am just giving a guess here but I remember doing something like this for a cube. I had a problem similiar but what i did was create the iqd by writing the sql myself and I never used impromptu at all.

I did something like for months: Year(date) - year(anotherdate) * 12 + month(date) - month(anotherdate).

It gave me figures back like 20, 36 etc and the the interval worked a treat as a fact.

 

[qusai50]

try using the datediff function.

eg: datediff({day},[Date1],[Date2])

This will give the difference in days

 

[rtveen]

Hi all,

Despite your solutions... it doesn't solve the real problem.
When doing [20/01/2005 14:25]-[19/01/2005 12:00] in the tabular model, the result is 1 day 2 hours and 25 minutes. This is fine, but when I drag this calculation into the fact-area of the crosstab it shows 95.100.000.
When creating a list with the same calculation it shows 1 day 2 hours and 25 minutes.

Is there a way I can show the days, hours AND minutes as facts in a crosstab without using all kind of nice calculations? It should be possible because there is a data format option for intervals.

Please help me out!

 

[mayo04]

Hi,

Again I dont use ReportNet but I see why you are getting that amount 95.100.000. I think if you could get it in seconds 95100 you may be able to do something like below

In seconds
Day=86400 seconds (60*60*24:seconds:minutes:hours)
hour=3600 seconds (60*60:seconds:minutes)
minute=60 seconds


(A) Days = Round((95100/86400),0) = 1

(B) Hours = Round(((95100 - (Days(see A result) * 86400))/3600),0)= 2

(C) Minutes = Round(95100 - ((Days(see A result) * 86400)+ (Hours(see B result) *3600))/60,0) = 25

I have never used this tool but I hope this might give you an idea to sort this out.

 

[rtveen]

Thank you all for your reactions.

At this moment I got a reaction from the cognos support desk. They told me the thing I want isn't available in Crosstabs. The reason is dat crosstab-facts can't display text.

Thanks to that I have to create some calculations. For your interest, this is how I solved the case:
hours=[milliseconds]/3600000
minutes=(mod([milliseconds];3600000))/60000