Microsoft Reporting Services SQL for Financial Year

[beebass]

Hi There

My company is in the middle of moving away from Crystal to start reporting on MSRS. When moving the reports across I write the SQL in Microsoft SQL Server Management Studio before building the report in Visual Studion. A lot of the clients we deal with have different financial years to our own. I need some SQL that will allow me to pull the data relevant to the current financial year of another company based on the date an order was raised. In this case the financial year is 01 April until 31 March. Any ideas on how to incorporate this in to an SQL?

Regards

Beebass

 

[xlbo]

Lookup table or custom function that acceptsa parameters would be the options I would go for - surely you would have had this same issue in Crystal though......how did you get round it before?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[beebass]

I created two formula fields:

@Fiscal Year: year((currentdate)+61)
@ Financial Year:

if {table.date} in date ({@Fiscal Year}-2,11,1) to date
({@Fiscal Year}-1,10,31) then
"Financial Year"+" "+right(totext({@Fiscal Year}-2,0,""),2)+"/"+right(totext({@Fiscal Year}-1,0,""),2)
else
if {table.date} in date ({@Fiscal Year}-1,11,1) to date
({@Fiscal Year},10,31) then
"Financial Year"+" "+right(totext({@Fiscal Year}-1,0,""),2)+"/"+right(totext({@Fiscal Year},0,""),2)

I then filtered on the table date as follows:

{table.date} in date ({@Fiscal Year}-2,11,1) to date ({@Fiscal Year}+1,10,31)

In this case the financial year ran between 01 Nov to 31 Oct

 

[xlbo]

I would create a stored function in SQL server that replicates that logic then

You should be able to call that from your stored procedures

Don;t really see how that helps with companies having lots of different financial years though unless table.date is referencing a lookup table of some description

Eitehr way, stored function in SQL Server should be able to do what you need - keep it out of the report itself as much as possiblew for SSRS

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question