I have a datasetcontaining datetime field. The data spans one entire day, which is three shifts 7am-3pm, 3pm-11pm and 11pm-7am. I have been tasked with listing the rows, and then in a total region, show averages based on the shifts, but I am unclear as to how to proceed. Can someone give me a hand up?ThanksThanks, Corey Furman @ Facebook ______________________________________________________ Please mark posts as answer or helpful when they are.

I'm thinking I could addseparate calculated fields to the dataset that calculates what shift the time is in, then reference those calc fields in the summary. Is this a good approach?Thanks, Corey Furman @ Facebook ______________________________________________________ Please mark posts as answer or helpful when they are.

I think you could use something along the lines of CASE WHEN SELECT cast(right(getdate(),8)as datetime)---Only showstime as a value, the rest is back to 1900/01/01 so no worries with the date. BETWEEN 1900/01/01 07:00:00AND 1900/01/01 15:00:00 THEN 'SHIFT1' ELSE(CASE WHENSELECT cast(right(getdate(),8)as datetime) BETWEEN 1900/01/01 15:01:00AND 1900/01/01 23:00:00 THEN 'SHIFT2' ELSE (CASE WHENSELECT cast(right(getdate(),8)as datetime) BETWEEN 1900/01/01 15:01:00AND 1900/01/01 23:00:00 THEN'SHift3' END) END)END ShiftSELECT cast(right(getdate(),8)as datetime) - displays time with date set to 1900This would display each shift for you. You could then use the SSRS to group on each shift...think this will do it. Code above not tested apart from the time bit. Good luck.

Hi Corey, Let me make sure I have understood the question. We have a field such as 2009-09-09 15:27:00 in the dataset. We want to split this field into three shifts, and then calculate the average data based on these shifts. If I have misunderstood, please do not hesitate to let me know. In this case, we can create a group based on the shifts to solve the issue. Use a expression for group expression to group the data by these shifts. The expression should be: =SWITCH( DatePart("h", Fields!CreateDateTime.Value) >= 7 AND DatePart("h", Fields!CreateDateTime.Value) < 15, 1, DatePart("h", Fields!CreateDateTime.Value) >= 15 AND DatePart("h", Fields!CreateDateTime.Value) < 23, 2, DatePart("h", Fields!CreateDateTime.Value) >= 23 OR DatePart("h", Fields!CreateDateTime.Value) <7, 3) After creating the group, the data will be grouped by the shifts. And then we can use the average function in the group header or footer to calculate the average value. =Avg(Fields!dataField.Value) Please feel free to ask, if you have any more questions. Thanks, Jin ChenJin Chen - MSFT

For whatever reason, I did not get notified that there were replies to this thread. Sorry for the delay in response...I solved this need as I had reasoned in my second post, with an implementation essentially the same as Mart offered. Jim Chen's reply was marked by me as an answer, but then unmarked, solet me explain why. This solution - at least at first glance - will produce the same results, it depends upon logic being placed into the report. I consider this bad practice. Reports are a UI, so whenever and where ever possible, logic should reside within the stored proc that drives the dataset. Still, I marked your post as helpful, as it technicaly solves the original question and includes the "=Avg(Fields!dataField.Value)", which would be required in the report regardless.Good job, guys, and thanks.Thanks, Corey Furman @ Facebook ______________________________________________________ Please mark posts as answer or helpful when they are.

i just go to this list for my problem but your problem was same as mine but i solved with sql query if u solved then ok other wise u can get answer from list question. from here <> ericcisco http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/60feb1b0-3782-4bce-b286-ef79737c3cb0/ it will solve your problem with parameters