Two Data Range - SQL Server Forums

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)

Two Data Range

Author

Topic

Johnho008
Starting Member

24 Posts

Posted - 2008-05-27 : 07:42:01

Hi Everyone,

I am new to reporting servies and need some help.
My dataset is doing a simple select Details,amount,paidindate, from table where paidindate>=startdate <=enddate.
I have done a matrix report I would like add YTD as well as amount in each month. i.e

| Month + Year | YTD
Details | =amountmonth |=Amount for 01/04/year (finacial
year) to displayed month

having problems doing the YTD as it need to pull a different paidindate range.

Thanks,
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 07:51:40

calaculate it seperately in query:-

select Details,amount as Amount,paidindate as Date, from table where paidindate>=startdate and paidindate<=enddate
union all
select 'YTD',sum(amount) ,NULL
from table
where YEAR(paidindate)=YEAR(getdate())

and in report use like this

=IIF(Fields!Detail.value="YTD",Fields!Amount.value,0)

Johnho008
Starting Member

24 Posts

Posted - 2008-05-27 : 09:56:04

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 10:01:19

Please explain what "May_YTD" is doing if the current date is "june 11th"?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 10:53:03

quote:
Originally posted by Johnho008

Hi Visakh16

Thank you for the reply, however I want this to happen for a every/range of month i.e

|MAY |MAY YTD |JUNE |JUNE YTD |nxt month
1/5/08-30/5/08|1/4/08-31/5/08|1/6/08-30/6/08|1/4/08-31/6/08|...repeat

May be this:-

SELECT MONTH(t.paidindate),SUM(t.amount) AS MonthTotal,t1.MonthYTD
FROM table t
CROSS APPLY (SELECT SUM(amount) AS MonthYTD
             FROM table
             WHERE MONTH(paidindate)<=MONTH(t.paidindate))t1
WHERE paidindate>=startdate and paidindate<=enddate
GROUP BY MONTH(paidindate)

Johnho008
Starting Member

24 Posts

Posted - 2008-05-27 : 10:54:10

Hi Peso

The user would select a date range, start date - end date, so the current date is sort of irrelevant.

The report need to show
1. All the months (between start date and end date)
2. YTD (Finacial Year) for each month (between start date and end date)

so
"May_YTD"= would have the date range "01/04/08" (start of the finacial year) to "31/05/08" (May).
"May"= would have "01/05/08" to" 31/05/08"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 12:23:12

How is then MAY_YTD different from APRIL_YTD when current date is June 11th?

E 12°55'05.25"
N 56°04'39.16"

Johnho008
Starting Member

24 Posts

Posted - 2008-05-28 : 03:38:17

Hi Peso

APRIL_YTD="1/04/08" (start of the finacial year) to "30/04/08"
MAY_YTD ="01/04/08" to "31/05/08".
JUNE_YTD="01/04/08" to "30/06/08".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources