?
Solved

calulate start of financial year in Sql Reporting Services

Posted on 2010-01-11
5
Medium Priority
?
1,546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 57

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 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