Link to home
Start Free TrialLog in
Avatar of Faetyl
FaetylFlag for United States of America

asked on

MS SQL - SSRS 2008 - LastFullMonth

I've converted a Crystal Report to SSRS 2008 and setup the date range parameters as @BeginDate and @EndDate.

Now I want to build another SSRS report from this and change the date range to automatically get the LastFullMonth when the report is ran.
(The overall goal is to make the report so it can be scheduled to run automatically monthly.)

Here's what I have currently for the date section:
(WORKDETAIL.PDATE >= @BeginDate) AND (WORKDETAIL.PDATE <= @EndDate)

To clarify: I only want to return the results for the last FULL month.
Therefore if the report is ran 09/25/2010 it should return all data for August 2010 only

Thanks in advance
Avatar of Emes
Emes
Flag of United States of America image

Are you doing this via ssrs report or sql?
ASKER CERTIFIED SOLUTION
Avatar of itcouple
itcouple
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Faetyl

ASKER

Works perfect itcouple
Thank you.

This adds me to another question though.
How can I display the date range in an expresion now?
Previously I just dispayed the parameters @BeginDate and @EndDate to show the date range on the report.
I'll see if I can award more points rather than open up another question.

Thanks in advance
Avatar of Faetyl

ASKER

FYI for anyone else who stumbles upon this post...
This also works:
Create a Parameter as StartDate, visibility should be hidden and default value set as the following expression: =DateSerial(Year(today()),Month(today())-1,1)

Create a Parameter as EndDate, visibility should be hidden and default value set as the following expression: =DateSerial(Year(today()),Month(today()),0)

You can then refference @StartDate and @EndDate in the DataSet Query and also show the actual reported date range in the report results [@StartDate] - [@EndDate] for example...
Avatar of Faetyl

ASKER

Oops,
Correction on EndDate
Expression should read: =DateSerial(Year(today()),Month(today()),1)
otherwise you miss the last day of month since the time cuts off at 12:00:00 AM on the last day with the previous expression...