July 26, 2010 at 8:15 am
Hi, I'm new to Reporting Services and looking for some advice/tips on how to automate an existing report. I have a report that I imported from an Access database. When it converted from Access to Reporting Services, it brought over 2 input boxes and created them as parameters. They are called "Beginning Date" and "Ending Date". One requirement I have for these reports is to automate them so that they run at the beginning of the month and provide detail about the prior month. Question I have is....what can I do to automatically populate these parameters with the first day of last month and the last day of last month? How do I handle this report in January when it has to report on data for December of the prior year? Should I write some code to do all this? Or, should I eliminate the parameters and write some code in the report itself? I'm using Visual Studio 2008 to do my development and the data source is currently looking at the Access database though this will change over to SQL Server in the future. This is my first report in Reporting Services so if I'm not providing enough/correct information, please let me know. Any replies are greatly appreciated!
Thanks!!!!
July 26, 2010 at 8:32 am
From the menu, Report > Report Parameters...
Pick each of the parameters in turn, and give them a default value. Specify the default value at the bottom of the dialog, usually using Non-queried. You can pull your default values from a query as well.
The easiest function to use for date parameters is dateserial(). It expects a year, month, and day in that order. You can use year(now), month(now), and day(now) for the current date, and use arithmetic operators to add or subtract days, months, or years. They will wrap around, so you don't need to worry about doing the extra calculations required. If you use month(now)-1 and it's January, it'll actually return December (12) and reduce the year by one automatically.
Here are some examples:
Returns today's date (strips out the time):
=dateserial(year(now), month(now), day(now))
First day of the month:
=dateserial(year(now), month(now), 1)
Last day of the month:
=dateserial(year(now), month(now)+1, -1)
(The -1 in the day will actually give you the last day of the month previous. Since I added 1 to the month, you get the current month)
First day of calendar year:
=dateserial(year(now), 1, 1)
Last day of calendar year:
=dateserial(year(now)+1, 1, -1)
And you can use iif() to do some more complex calculations:
First day of fiscal year (assuming fiscal year starts in July):
=dateserial(iif(month(now) >= 7, year(now), year(now)-1), 7, 1)
Hopefully you can extrapolate any other needs you have from there.
--J
July 26, 2010 at 8:43 am
This is awesome, it works great. Thanks so much!!!
July 26, 2010 at 8:45 am
dateserial() is a very cool function. I'm glad I could help.
--J
July 26, 2010 at 8:48 am
You did, a lot. One thing....I'm trying to get the last day of last month and not getting it. I keep getting May 30. The formula I have is "=dateserial(year(now), month(now)-1, -1)". What am I doing wrong?
July 26, 2010 at 8:52 am
Sorry, it should be:
=dateserial(year(now), month(now)+1, 0)
You've got a -1 after the month, when it should be +1, thus the May. I forgot that 0 is actually the last day of the previous month (the day before day 1), so you need 0 as the day.
--J
July 26, 2010 at 8:57 am
Ah, there we go, now it's working. Thanks again!!!
July 27, 2010 at 7:46 am
Whoops, I spoke a little soon. The dateserial coding works, (doing the right dates) but the dateserial code is doing "MM/DD/YYYY : time". When I run the reports in Access, the date is formatted as "MM/DD/YYYY". (the dates in the table are "MM/DD/YYYY"). How do I change the dateserial to just "MM/DD/YYYY"?
Thanks!
July 27, 2010 at 7:53 am
Where are you seeing dates in this format? In the parameters at the top or in the actual report? If you're using dateserial for the parameters, the parameters themselves should be in MM/DD/YYYY format with no time. But the report itself typically defaults to date and time. There is a function to fix this, however: formatdatetime(). The first parameter should be the date field, and the second parameter should be 2 for the format you are looking for:
=formatdatetime(Fields!DateField.Value,2)
July 27, 2010 at 7:56 am
The date/time value shows up in the parameter boxes. (not the report)
July 27, 2010 at 7:58 am
Interesting. Is the data type for the parameter DateTime or String? It should be DateTime, because it gives you a great calendar control.
July 27, 2010 at 8:13 am
Ah, good point; I didn't see that. It was set to text but I just changed it now to Date/Time. I did a Build, but I'm still getting the same results.
July 27, 2010 at 8:18 am
Weird. I've never seen it do that for me. Try wrapping the formatdatetime(<date>,2) around the dateserial function:
=formatdatetime(dateserial(year(now),month(now),1),2)
July 27, 2010 at 8:21 am
OK. I'll let you know how it goes. Thanks again for all the help!
July 27, 2010 at 8:26 am
Glad to help. I remember having all these same questions when I first started working with SSRS. It took me some time to sort it all out. It's my privledge to help someone else find their way.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply