Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

calulate start of financial year in Sql Reporting Services

Posted on 2010-01-11
5
Medium Priority
?
1,769 Views
Last Modified: 2012-08-13
Hi
We use Microsoft Sql Reporting Serives 2005.  Some of our reports require a calculation to be made that works out the difference in days between the start of the financial year (i.e. 1st of July) to a date stored in in each record.  Can anyone think of an expression that calculates the date of the start of the current financial year.  ie. this year the date would be 1/7/2009 and next year it would be 1/7/2010 etc
I don't want to have to manually change the date everytime the year changes.

Many Thanks
Scott
0
Comment
Question by:protocol1
  • 2
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 26289334
I haven't worked with Reporting Services, but would this need to be a SQL expression?

~bp
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26289343
I think you want an SSRS Expression, don't you?  I am trying to work one out.
0
 
LVL 1

Author Comment

by:protocol1
ID: 26289413
Yes I'm after an SSRS Expression as the caculation is being done by a report filter rather than a SQL query

Thanks
Scott
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 26289429
Try this, it looks like it works for me, it returns the date equivalent to July 1, 2009 if I run it on my machine right now.
One note, I have "7/1" because my machine is in US format, you may need to switch that if yours is some other default format.

=CDate("7/1/"&Cstr(IIF(Month(Today)<7,Year(Today)-1,Year(Today))))
0
 
LVL 1

Author Closing Comment

by:protocol1
ID: 31675865
Excellent, that's exactly what I needed.

Thanks
Scott
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question