January 20, 2010 at 9:51 am
Hello,
I have taken over a suite of ssrs reports using the following to generate a pull down list of possible dates. Regardless of the month/year chosen, the report is only picking up information for December, 2009.
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now)+1), 1)
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now)), 1)
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 1), 1)
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 2), 1)
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 3), 1)
......
= DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 12), 1)
Thanks in advance for any help.
John
January 21, 2010 at 6:49 am
Well, the expression you have provided will set the date to December 1st of the previous year whenever the current month is January. I have no idea why it would do that. What do you think the parameter should be set to?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 21, 2010 at 9:35 am
Jack,
The pull down looks something like
February 2010
January 2010
December 2009
....
The desired outcome is to have the pull down pass the value from the pull down and pass it the report.
John
January 21, 2010 at 12:46 pm
So where in the report is the expression that you posted?
Is there one parameter and then you are trying to get the entire month range?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2010 at 12:49 pm
Jack,
That dates are posted in the report header. They are also 'supposedly'
sent to the various queries which comprise the reports. An example is below -
SELECT DISTINCT PatientProfileID
FROM vERC_Goals
WHERE (EdMilestoneUID = '3783068F-6654-4027-9BF0-C45A96123C95') AND (EventDate BETWEEN CAST('7/1/' + CAST((CASE WHEN Month(@Month)
> 6 THEN Year(@Month) ELSE Year(@Month) - 1 END) AS varchar(6)) AS datetime) AND DATEADD(mm, 1, @Month) - 1)
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply