DateTime Parameter

Reporting Services 2005 seems to have a problem with DateTime parameters.
One report has default datetime parameters. It uses the navigation feature
to call a second report, with the same parameters passed to the second
report. In the designer this works fine but when deployed the second report
crashes with an rsReportParameterTypeMismatch error.
This only happens when the dates being passed are non/ambiguous US/English.
In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly 6-Jan-06
works, 16-Jan-06 doesn't.
So it's not really a type mismatch error but a dataconversion issue.
Both reports are set to language en-GB
Any help welcome!
Bob
--
--
BobBut if the parms are being passed as DateTimes instances, then there should
be no string format issues. Am I missing something?
--
William Stacey [MVP]
"Bob" <bmorris@.swiftkenya.com(nospamm)> wrote in message
news:9A881BF6-AFD2-4213-889A-F26EC8209F74@.microsoft.com...
| Reporting Services 2005 seems to have a problem with DateTime parameters.
|
| One report has default datetime parameters. It uses the navigation feature
| to call a second report, with the same parameters passed to the second
| report. In the designer this works fine but when deployed the second
report
| crashes with an rsReportParameterTypeMismatch error.
|
| This only happens when the dates being passed are non/ambiguous
US/English.
| In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly
6-Jan-06
| works, 16-Jan-06 doesn't.
|
| So it's not really a type mismatch error but a dataconversion issue.
|
| Both reports are set to language en-GB
|
| Any help welcome!
|
| Bob
| --
| --
| Bob

Datediff and Record navigation

Hi:

I need help to find the difference between two times within the same table as follow

For every alarm incident (alarm will repeat within the table at different times)

Alarm acknowledge time:AkTime = AkAlm – InAlm

Alarm repair time:ReTime = OutAlm – Akalm

Alarm DownTime:AdtTime = OutAlm – InAlm

The table contains a decent number of records (> 10,000,000) and is indexed by the timestamp.

The table looks like this:

Alarm

AlarmType

tsTimeStapm

3030

InAlm

1/11/05 9:11:00 AM

3030

AkAlm

1/11/05 10:48:00 AM

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

AkAlm

1/11/05 2:52:35 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

3030

AkAlm

1/11/05 3:26:30 PM

3030

OutAlm

1/11/05 4:15:15 PM

Additionally

I need to calculate the mean time between alarms i.e. the time difference from the OutAlm to the next InAlm

Ex: Data from the sample table

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

Thanks in advance

Jorge

Jorge:

Maybe something like this:

declare @.alarm table
( Alarm integer,
AlarmType varchar (7),
tsTimeStamp datetime,

primary key (Alarm, tsTimeStamp),
unique (tsTimestamp, Alarm)
)

insert into @.alarm values (3030, 'InAlm', '1/11/05 9:11:00 AM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 10:48:00 AM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 1:32:00 PM')
insert into @.alarm values (3032, 'InAlm', '1/11/05 2:51:00 PM')
insert into @.alarm values (3032, 'AkAlm', '1/11/05 2:52:35 PM')
insert into @.alarm values (3032, 'OutAlm', '1/11/05 3:14:00 PM')
insert into @.alarm values (3030, 'InAlm', '1/11/05 3:24:00 PM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 3:26:30 PM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 4:15:15 PM')
--select * from @.alarm

select Alarm,
inAlm as [Alarm Timestamp],
datediff (mi, inAlm, akAlm) as [Alarm Acknowledgment Time],
datediff (mi, akAlm, outAlm) as [Alarm Repair Time],
datediff (mi, inAlm, outAlm) as [Alarm Downtime]
from ( select a.alarm,
a.tsTimeStamp as InAlm,
( select min (b.tsTimeStamp)
from @.alarm b
where a.alarm = b.alarm
and b.tsTimeStamp > a.tsTimeStamp
and b.alarmType = 'AkAlm'
) as AkAlm,
( select min (c.tsTimeStamp)
from @.alarm c
where a.alarm = c.alarm
and c.tsTimeStamp > a.tsTimeStamp
and c.alarmType = 'OutAlm'
) as OutAlm
from @.alarm a
where alarmType = 'InAlm'
) alm

-- Alarm Alarm Timestamp Alarm Acknowledgment Time Alarm Repair Time Alarm Downtime
-- -- -- - -- --
-- 3030 2005-01-11 09:11:00.000 97 164 261
-- 3030 2005-01-11 15:24:00.000 2 49 51
-- 3032 2005-01-11 14:51:00.000 1 22 23

select avg (datediff (mi, lastOutage, nextOutage)) as [Mean Time Between Alarms]
from (
select a.Alarm,
a.alarmType,
a.tsTimeStamp as [lastOutage],
( select min (tsTimestamp)
from @.alarm b
where b.alarmType = 'InAlm'
and b.tsTimestamp > a.tsTimestamp
) as [nextOutage]
from @.alarm a
where a.alarmType = 'OutAlm'
) x
where nextOutage is not null

-- Mean Time Between Alarms
--
-- 44