Solved

MS SQL - SSRS 2008 - LastFullMonth

Posted on 2010-09-02
6
942 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

23 Experts available now in Live!

Get 1:1 Help Now