Solved

MS SQL - SSRS 2008 - LastFullMonth

Posted on 2010-09-02
6
963 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 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 DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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