August 17, 2009 at 12:53 pm
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
August 17, 2009 at 1:28 pm
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
August 17, 2009 at 1:58 pm
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).
August 17, 2009 at 2:08 pm
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
August 17, 2009 at 3:24 pm
oh okay my mistake sorry
excuse my ignornace, so I shouldn't use a declare statement?
what should I use for the @date parameter?
August 17, 2009 at 4:28 pm
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
August 17, 2009 at 5:18 pm
I'm using the BI..
Looks like that screen shot is from report manager?
August 17, 2009 at 6:49 pm
The screenshot was from BIDS.
CEWII
August 18, 2009 at 6:49 am
Guess it's different because I'm using BIDS 2008
August 18, 2009 at 2:01 pm
Perhaps, that could explain it..
CEWII
August 19, 2009 at 9:27 am
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.
August 19, 2009 at 12:50 pm
Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply