Percentage of calculated field over a threshold

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Percentage of calculated field over a threshold

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2011-05-06 : 11:10:09

Hello all,

I have the following in a select statement thet I am using in SSRS 2005

SELECT COMPANY.DEPARTMENT,
--ORDER SHIP TIME
convert (varchar,(CAST(COMPANY.OrderShipped as datetime))
- (CAST(COMPANY.OrderPlaced as datetime)), 108) as Order_ship_time

From COMPANY

Where COMPANY.DEPARTMENT = 'ORDER'

Group By COMPANY.DEPARTMENT

This works great and gives me the following

Department Order_ship_Time
ORDER 01:00:00
ORDER 00:45:00
ORDER 01:15:00
ORDER 01:30:00

Now I am trying to right a report that will show me the percentage of orders that take longer than 01:00:00 to ship

But when I try the following expression
=sum(iif(Fields!Report_turnaround_time.Value > 01:00:00, 1, 0))
\ (sum(Fields!Report_turnaround_time.Value))

It doesn't like the 01:00:00 it puts a red squiggle under the first :

Thanks in advance for any help with this

Joshua

Bazalddo
Starting Member

22 Posts

Posted - 2011-05-24 : 11:53:26

You would probably be better off writing it within the SQL

try this

SELECT ((CASE WHEN Order_ship_time > 01:00:00 THEN COUNT(1) END) / COUNT(1)) AS OrdersPlacedAfter1Pct
FROM ......
WHERE ......

Shahbaz Ahdi
Apps Developer
ODEON/UCI Cinema

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 12:47:05

If you are trying to compare times (i.e., turnaround_time is a datetime or time column) you will need to do something like this:


TimeValue(Fields!Report_turnaround_time.Value) > TimeValue("01:00:00")

If you are trying to count the number of hours then instead of the TimeValue function, use the Hour function. Expression editor in SSRS lists all the functions avaialable to you.

Similary, in the denominator, if you are trying to add up the hours, then use the Hour function. You cannot sum up time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources