Solved

MS SQL - SSRS 2008 - LastFullMonth

Posted on 2010-09-02
6
949 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Faetyl
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 33591112
Are you doing this via ssrs report or sql?
0
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 33593955
Hi

In the dataset try this

where (year(WORKDETAIL.PDATE) = year(dateadd(m,-1,getdate())) AND month(WORKDETAIL.PDATE) = month(dateadd(m,-1,getdate())))

Regards
Emil
0
 

Author Comment

by:Faetyl
ID: 33601833
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Expert Comment

by:itcouple
ID: 33602412
0
 

Author Comment

by:Faetyl
ID: 33621712
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...
0
 

Author Comment

by:Faetyl
ID: 33621759
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...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot 7 56
leave a 1 blank row if a dataset returns no records 5 48
SSRS 2005 Matrix Column Break 1 58
How can I setup ssrs report  as UTF-8 . 7 17
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now