SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Set date parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 09/02/2013 :  09:29:24  Show Profile  Reply with Quote
Hi,

I have an SSRS parameter called Ordermonth, the current display "Sep-2013" combination of month and year.
This order month will change every 27th day of the month. So on Sept 27 this will be change to "Oct-2013". Evry 27th day will be the cut off date for the month. How will i do this in SSRS? When i tried this in my expression upon reaching the September , the result display Oct-2013 
=MonthName(Month(DateSerial(Year(Now), Month(Now)+1, 27)),3)&
"-"&Year(Today)


Here is what my SSRS looks:

From Date: 9/1/2013 To date:9/2/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

Edited by - Villanuev on 09/02/2013 09:33:07

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/02/2013 :  10:07:27  Show Profile  Reply with Quote
=IIF(Day(Now()) > 27, MonthName(Month(Now())+1),MonthName(Month(Now())))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 09/03/2013 :  08:25:03  Show Profile  Reply with Quote
Thanks Visakh its working now. just made some adjustment.



=IIF(Day(Now()) > 26,MonthName(Month(Now())+1,3)&"-"&Year(now()),MonthName(Month(Now()),3)&"-"&Year(now()))


Btw, Its possible to use case statement to loop the dates from Where clause instead of using while and Begin statement.
I need to work on if the current dates falls in Monday need to less 1 day else no changes from the date.
Or it possible to check if no records extracted and inserted to temporary table then need to less 1 day from the date.

below is my query.



see below sample:

From Date: 9/1/2013 To date:9/2/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

 
SELECT *
FROM DATA1 PR1
---Loop back this date if there's no record found from Query, sunday has no transaction
--or use case statement

WHERE ??? if false on monday

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,1,@PFromDate) AND  DATEADD(DAY,1,@PToDate)

ELSE

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,0,@PFromDate) AND  DATEADD(DAY,0,@PToDate)

ENDCASE





-----------------My Main Query--------
 --Temporary Table
IF OBJECT_ID('tempdb.dbo.#PROD') IS NOT NULL

 

  DROP TABLE [dbo].[#PROD]

        CREATE TABLE  #PROD

      (
            XWRKCTRID NVARCHAR(20)                      
             ,XCREATEDBY NVARCHAR(10)
 
          ,XASUSTATUS INT
              ,XOPRNUM INT
              ,XCREATEDDATETIME Datetime
              ,XMODEL NVARCHAR(35)
      )



--Insert records      
INSERT INTO  #PROD
(XWRKCTRID  ,XCREATEDBY
                  ,XASUSTATUS
                  ,XOPRNUM
                  ,XCREATEDDATETIME
                  ,XMODEL
)

 

--Main Query
SELECT PR1.XWRKCTRID
                  ,PR1.XCREATEDBY
                  ,PR1.XASUSTATUS
                  ,PR1.XOPRNUM
                  ,PR1.XCREATEDDATETIME
                  ,substring(PT1.XITEMID,11,CHARINDEX('-',PT1.XITEMID,11)-11) as MODEL

FROM DATA1 PR1


---Loop back this date if there's no record found from Query, sunday has no transaction

--or use case statement
WHERE ??? if false on monday
     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,1,@PFromDate) AND  DATEADD(DAY,1,@PToDate)

ELSE

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,0,@PFromDate) AND  DATEADD(DAY,0,@PToDate)

ENDCASE

Edited by - Villanuev on 09/03/2013 08:29:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/03/2013 :  09:25:49  Show Profile  Reply with Quote
if the current dates falls in Monday need to less 1 day else no changes from the date

you could use a where condition like


WHERE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,SIGN(DATEDIFF(dd,0,datefield) % 7) ,@PFromDate) AND  DATEADD(DAY,SIGN(DATEDIFF(dd,0,datefield) % 7),@PToDate)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

440 Posts

Posted - 09/04/2013 :  02:11:24  Show Profile  Reply with Quote
Hi VIsakh,

From Date: 9/2/2013 To date:9/3/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

could not understand your given solution. kindly please explain. thanks.

My requirements for this query. if today is Monday i need to go back or less of 2 day to get the saturday records.
in my sample date parameter from 9/2 this falls as Monday 6:00am which we dont have yet transaction. that'w why i would like to extract the data of Saturday 8-31.
if tuesday will get the data for monday and so on.




Edited by - Villanuev on 09/04/2013 08:04:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000