Single Date - easy one for u guys...

  • I have this query (below) & I want the user to be able to pick one date from the calender in the report. I open the BI studio and create my report, works just fine.

    I then go 'report data' and add a parameter. I call it Date, the data type is date/time. That gives me a nice single calender control (what I want).

    My problem is that when I select the date from the calender control it doesn't effect the report - it's blank, no data.

    I'm assuming that I'm not passing the value selected in the calender to my query in the report.

    Here's the date part of the query. Do I need to add something to the query?

    I thought I could reference the @DATE parameter? I tried but can't get it to work. The @DATA_DATE & PEOM_DATE are used to check a table for holidays. So I only want the @date to be passed from the calender.

    DECLARE @DATE AS DATETIME

    DECLARE @DATA_DATE AS DATETIME

    DECLARE @PEOM_DATE AS DATETIME

    SET @DATE = '03/01/2008'

    --SELECT MONTH(@DATE), YEAR(@DATE), Day(@DATE), IS_HOLIDAY, IS_BUSINESS_DAY, IS_WEEKDAY,BUSINESS_DAY_NEXT,*

    --FROM

    --LPB_REPORTINGSERVICES.DBO.DIM_DATE

    --WHERE

    --DAY_IN_MONTH = Day(@DATE)

    --AND MONTH(DATE) = MONTH(@DATE)

    --AND YEAR(DATE) = YEAR(@DATE)

    SET @DATA_DATE =

    (

    SELECT DISTINCT

    (CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT

    WHEN IS_BUSINESS_DAY = 1 THEN DATE

    WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT

    END)

    FROM

    LPB_REPORTINGSERVICES.DBO.DIM_DATE

    WHERE

    DAY_IN_MONTH = Day(@DATE)

    AND MONTH(DATE) = MONTH(@DATE)

    AND YEAR(DATE) = YEAR(@DATE)

    )

    SET @PEOM_DATE = DATEADD(M,-1,@DATA_DATE)

    SET @PEOM_DATE =

    (

    SELECT DISTINCT

    (CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT

    WHEN IS_BUSINESS_DAY = 1 THEN DATE

    WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT

    END)

    FROM

    LPB_REPORTINGSERVICES.DBO.DIM_DATE

    WHERE

    DAY_IN_MONTH = Day(@PEOM_DATE)

    AND MONTH(DATE) = MONTH(@PEOM_DATE)

    AND YEAR(DATE) = YEAR(@PEOM_DATE)

    )

    --print @DATA_DATE

    --print @DATE

  • You shouldn't need to declare it, just use it..

    SELECT DBName, Size=SUM(Size), ColDate=CONVERT(varchar(10),ColDate,101)

    FROM dbo.Tracking

    WHERE ColDate BETWEEN @StartDate AND @EndDate

    GROUP BY DBName, ColDate

    ORDER BY DBName, ColDate

    @StartDate and @EndDate are defined in report parameters..

    CEWII

  • I'm confused now.

    I don't wat a start date between and an end date.

    I just want the user to pick one date from the calender and have it use that value in my query (as @date).

  • I wasn't solving your problem directly, I was showing you how I had actually used it in one of my reports where I have a start date and end date defined as report parameters, I am showing them being used and not declared. I think a good portion of your problem is that in the code you showed you show a declare statement..

    CEWII

  • oh okay my mistake sorry

    excuse my ignornace, so I shouldn't use a declare statement?

    what should I use for the @date parameter?

  • Ihink it is effective declared when it is entered in as a report paramter, so just use @Date if that is how you declared it in the report parameters. I have included a screen shot of one of my report parameters..

    CEWII

  • I'm using the BI..

    Looks like that screen shot is from report manager?

  • The screenshot was from BIDS.

    CEWII

  • Guess it's different because I'm using BIDS 2008

  • Perhaps, that could explain it..

    CEWII

  • In BIDS 2008, go to the "Report Data" tab and right click on the "Parameters" folder to add a new parameter. It will take you through all the steps of assigning where the parameter value(s) come from.

    See this MSDN article on adding report parameters in SSRS 2008.

    http://msdn.microsoft.com/en-us/library/aa337287.aspx

  • Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply