How to create report with different end dates with one date parameter

Question

How to create report with different end dates with one date parameter

jvElecenter

SSCarpal Tunnel

Points: 4502
More actions
January 13, 2011 at 6:47 am

#232199

Hi there
I have a report which works fine :
select 'Openstaand' as 'Openstaand', year(DCLE.[Posting Date]) as Jaar, month(DCLE.[Posting Date]) as Maand, DCLE.Amount as Bedrag from dbo.[FIN$Detailed Cust_ Ledg_ Entry] as DCLE
where DCLE.[Posting Date] <= @date
and DCLE.Amount > 0
order by year(DCLE.[Posting Date]), month(DCLE.[Posting Date])
but this only results in one sum.
Now I need this info for the last year per month.
So eg.
@date = 30/11/2010
The result is 1.000 €
but in my report I need
30/11/2009 31/10/2009 ..... 31/10/2010 30/11/2010
999 € 1150 € 2500 € 1000 €
How to coop with this one ?
Kind regards

JV

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Linda Claes Old Hand Points: 308 More actions · Answer 1

Hi JV,

It is unclear to me where you do the sum, but does the solution not lie in: GROUP BY year(DCLE.[Posting Date]) as Jaar, month(DCLE.[Posting Date]) as Maand ?

Kind regards,

Linda

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 2

hi there

no, strangely enough it doesn't work that way.

I have found now a solution like this :

sum(case when DCLE.[Posting Date] <= @date then DCLE.Amount Else 0 end) as M1 ,

sum(case when DCLE.[Posting Date] <= dateadd("month",-1,@date) then DCLE.Amount Else 0 end) as M2 ,

JV

Linda Claes Old Hand Points: 308 More actions · Answer 3

JV,

You have to be carefull as doing so you will not end up with the exact last day of each month. See following code sample:

DECLARE @date datetime

SELECT @date = '2010-02-28'

SELECT M0 = dateadd("month", -1, @date)

,M1 = dateadd("month", 1, @date)

I guess what you try to achieve is a cumulative sum per month. Within reporting services you can achieve this using the RunningValue function. With a bit more work you can achieve this in your sql as well. The following code sample will illustrate:

DECLARE @date datetime

SELECT @date = '2010-02-28'

SELECT @date = CONVERT(datetime, CONVERT(varchar, year(@date)) + '-' + CONVERT(varchar, month(@date)) + '-01')

CREATE TABLE #dates_past_12_months (last_day_of_month datetime)

INSERT INTO #dates_past_12_months

SELECT dateadd(day, -1, dateadd(month, 1, @date))

UNION SELECT dateadd(day, -1, dateadd(month, 0, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -1, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -2, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -3, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -4, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -5, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -6, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -7, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -8, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -9, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -10, @date))

UNION SELECT dateadd(day, -1, dateadd(month, -11, @date))

--SELECT * from #dates_past_12_months

select 'Openstaand op ' + CONVERT(varchar, last_day_of_month, 106) as 'Openstaand'

, sum(DCLE.Amount) as Bedrag

from dbo.[FIN$Detailed Cust_ Ledg_ Entry] as DCLE

JOIN #dates_past_12_months dt ON DCLE.[Posting Date] <= dt.last_day_of_month

WHERE DCLE.Amount > 0

order by year(DCLE.[Posting Date]), month(DCLE.[Posting Date])

DROP TABLE #dates_past_12_months)

Hope this helps.

Kind regards,

Linda

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 4

Linda

thx for reply.

I made the simular but without creating a tabel, so immediately in a select statement. I will try yours though.

But you've mentioned the running value in reporting services. I'm curious how this works...

Could you give an exemple ?

Many thanx

JV

Linda Claes Old Hand Points: 308 More actions · Answer 5

Hi JV,

As attachment you find a sample rdl report for RunningValue. I guess this is self-explanatory but in case you have questions please get don't hesitate to ask.

Kind regards,

Linda