July 11, 2017 at 3:16 am
Hello together,
is it possible to Group in SSRS by an expression between two datetimes like =Dateadd("h",5,DATETIME.VALUE) and =Dateadd("h",26,DATETIME.VALUE)?
Currently my Report is group by day... But i want to group between datetime 1 and datetime 2.
best regards
July 11, 2017 at 3:31 am
Without knowing exactly, what your aim is, providing an expression is hard. For example, if you wanted to group in sets of 2 hours, then something like the following should work:=DATEADD("h", DATEDIFF("h", "01/01/2000", Fields!YourColumn.Value) - (DATEDIFF("h", "01/01/2000", Fields!YourColumn.Value) Mod 2), "01/01/2000")
(Note the above is untested, as I don't have SSRS on the PC I am using right now, you may need to make some corrections).
If you have different interval groupings, then you would be better doing this in your dataset by using a groupings table. In simple terms, something like this:WITH Dates AS
(SELECT DATEADD(h, 0, GETDATE()) AS LowerDate, DATEADD(h, 5, GETDATE()) AS UpperDate
UNION
SELECT DATEADD(h, 5, GETDATE()) AS LowerDate, DATEADD(h, 10, GETDATE()) AS UpperDate
UNION
SELECT DATEADD(h, 10, GETDATE()) AS LowerDate, DATEADD(h, 20, GETDATE()) AS UpperDate
UNION
SELECT DATEADD(h, 20, GETDATE()) AS LowerDate, DATEADD(h, 30, GETDATE()) AS UpperDate
UNION
SELECT DATEADD(h, 30, GETDATE()) AS LowerDate, DATEADD(h, 50, GETDATE()) AS UpperDate)
SELECT D.LowerDate, D.UpperDate,
YT.SomeField,
COUNT(YT.OtherField) AS Records,
SUM(YT.MonetaryField) AS TotalValue
FROM YourTable YT
JOIN Dates D ON YT.YourDateField >= D.LowerDate AND YT.YourDateField < D.UpperDate
GROUP BY D.LowerDate, D.UpperDate, YT.SomeField;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 11, 2017 at 4:39 am
I tried to use your example expressions like this:=DATEADD("h", DATEDIFF("h", 5, Fields!trdtutc2.Value) - (DATEDIFF("h", 26, Fields!trdtutc2.Value)), "01/01/2000")
they displayed an #Error
My aim is that i want to group by
...last 30 days
10.07.2017 05:00:00 until 11.07.2017 02:00:00
11.07.2017 05:00:00 until 12.07.2017 02:00:00
and not group by
...last 30 days
10.07.2017 00:00:00 until 10:07:2017 23.59:59
11.07.2017 00:00:00 until 11:07:2017 23.59:59
July 11, 2017 at 4:48 am
i756311 - Tuesday, July 11, 2017 4:39 AMI tried to use your example expressions like this:
=DATEADD("h", DATEDIFF("h", 5, Fields!trdtutc2.Value) - (DATEDIFF("h", 26, Fields!trdtutc2.Value)), "01/01/2000")they displayed an #Error
My aim is that i want to group by 11.07.2017 05:00:00 until 12.07.2017 02:00:00
and not group by 11.07.2017 00:00:00 until 11:07:2017 23.59:59
As I said, my code was untested as I don't have SSRS with me right now, so may need a slight correction. I noticed, however, that you dropped the Mod.
If you want groupings like that, however, i would suggest doing so in your dataset, as I suggested in the second example. The data engine will probably be better at doing the task than SSRS, and (personally, I find) it's much easier.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 11, 2017 at 4:57 am
Ok, you've editted your post, which is actually provides a little more explanation.
You could do this with SSRS expressions. You'll need to group on the value:=Dateadd("h", 2,Dateadd("h", datediff("h", "01/01/2000", Dateadd("h", -2, Fields!YourColumn.Value)), "01/01/2000"))
Again, the above is UNTESTED.
I would personally add this column as a derived column in your dataset on your report, as you can refer to it. let's say you call it "DateGroup".
You can set your table then then group on the value of DateGroup and can then have your two columns, one with the value of DateGroup, and the other with an expression along the lines of:=Dateadd("s",1, Dateadd("d",1,DateGroup))
The above is also UNTESTED.
If you didn't create a derived column, then you'll need to do the above expression on the top expression. (hence why I said use a dervied column, as that gets messy).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 11, 2017 at 4:59 am
Hmm well I did it now like this:=DATEADD("h", DATEDIFF("h", "01/01/2000", DateAdd("h",5,Fields!trdtutc2.Value)) - (DATEDIFF("h", "01/01/2000", Dateadd("h",26,Fields!trdtutc2.Value)) Mod 2), "01/01/2000")
Now he Displays 12.07.2017 09:00:00 What does it mean?
It is never the time between the both values.
Can you explain me the "MOD 2"?
July 11, 2017 at 5:01 am
Thom A - Tuesday, July 11, 2017 4:57 AMOk, you've editted your post, which is actually provides a little more explanation.You could do this with SSRS expressions. You'll need to group on the value:
=Dateadd("h", 2,Dateadd("h", datediff("h", "01/01/2000", Dateadd("h", -2, Fields!YourColumn.Value)), "01/01/2000"))
Again, the above is UNTESTED.
I would personally add this column as a derived column in your dataset on your report, as you can refer to it. let's say you call it "DateGroup".You can set your table then then group on the value of DateGroup and can then have your two columns, one with the value of DateGroup, and the other with an expression along the lines of:
=Dateadd("s",1, Dateadd("d",1,DateGroup))
The above is also UNTESTED.
If you didn't create a derived column, then you'll need to do the above expression on the top expression. (hence why I said use a dervied column, as that gets messy).
Why you say dateadd 2 hours?
July 11, 2017 at 5:02 am
i756311 - Tuesday, July 11, 2017 4:59 AMHmm well I did it now like this:=DATEADD("h", DATEDIFF("h", "01/01/2000", DateAdd("h",5,Fields!trdtutc2.Value)) - (DATEDIFF("h", "01/01/2000", Dateadd("h",26,Fields!trdtutc2.Value)) Mod 2), "01/01/2000")Now he Displays 12.07.2017 09:00:00 What does it mean?
It is never the time between the both values.
Can you explain me the "MOD 2"?
See my above post, as you provided more explanation with your edit. This enabled me to provide a more concise answer.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 11, 2017 at 5:04 am
i756311 - Tuesday, July 11, 2017 5:01 AMThom A - Tuesday, July 11, 2017 4:57 AMOk, you've editted your post, which is actually provides a little more explanation.You could do this with SSRS expressions. You'll need to group on the value:
=Dateadd("h", 2,Dateadd("h", datediff("h", "01/01/2000", Dateadd("h", -2, Fields!YourColumn.Value)), "01/01/2000"))
Again, the above is UNTESTED.
I would personally add this column as a derived column in your dataset on your report, as you can refer to it. let's say you call it "DateGroup".You can set your table then then group on the value of DateGroup and can then have your two columns, one with the value of DateGroup, and the other with an expression along the lines of:
=Dateadd("s",1, Dateadd("d",1,DateGroup))
The above is also UNTESTED.
If you didn't create a derived column, then you'll need to do the above expression on the top expression. (hence why I said use a dervied column, as that gets messy).Why you say dateadd 2 hours?
This is just an example, however, I actually noticed, rather foolishly, that your times go from 05:00 to 02:00 to following day? What about 02:00 - 05:00?
I would, again, suggest the table groups then. I misread the edit to show times 02:00 - 02:00 the following day. My bad.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 11, 2017 at 5:27 am
Thom A - Tuesday, July 11, 2017 5:04 AMi756311 - Tuesday, July 11, 2017 5:01 AMThom A - Tuesday, July 11, 2017 4:57 AMOk, you've editted your post, which is actually provides a little more explanation.You could do this with SSRS expressions. You'll need to group on the value:
=Dateadd("h", 2,Dateadd("h", datediff("h", "01/01/2000", Dateadd("h", -2, Fields!YourColumn.Value)), "01/01/2000"))
Again, the above is UNTESTED.
I would personally add this column as a derived column in your dataset on your report, as you can refer to it. let's say you call it "DateGroup".You can set your table then then group on the value of DateGroup and can then have your two columns, one with the value of DateGroup, and the other with an expression along the lines of:
=Dateadd("s",1, Dateadd("d",1,DateGroup))
The above is also UNTESTED.
If you didn't create a derived column, then you'll need to do the above expression on the top expression. (hence why I said use a dervied column, as that gets messy).Why you say dateadd 2 hours?
This is just an example, however, I actually noticed, rather foolishly, that your times go from 05:00 to 02:00 to following day? What about 02:00 - 05:00?
I would, again, suggest the table groups then. I misread the edit to show times 02:00 - 02:00 the following day. My bad.
Ok maybe you miss understand me and I miss understand you.
I will try to explain you my problem and what I did already.
First off al my Groups of my reports Looks like this.
GROUP: Month
GROUP: Week
GROUP: Day
GROUP: Item
GROUP: Details
I want to manipulate the DAY group that i got the Dates like this: 11.07.2017 05:00:00 until 12.07.2017 02:00:00 (last 30 Dates).
I have the feeling that i m on the wrong strategy to find a solution.
July 11, 2017 at 6:45 am
Thank you for your Inspiration. I found a solution!!!
July 11, 2017 at 3:17 pm
What was it? Please share!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply