Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL - SSRS 2008 - LastFullMonth

Posted on 2010-09-02
6
Medium Priority
?
976 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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