Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with date parameter in SSRS

Author  Topic 

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-11 : 13:28:05
Hi guys,


I need a little help with a date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast funtion. Would it be in the data paramete itself, or a part of the query before the @start date and @End date?

Thanks in advance

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-11 : 13:55:05
how are you populating the drop down date range?
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-11 : 16:13:45
I'm populating the date drop down from a query.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-11 : 20:13:05
you can do one of the following:
either convert the date range that is being selected from the drop down to your date field format or
convert the value to your date field format in your where clause
by doing something like this:
[CODE]
DECLARE @DateTime DATE = getdate();

SELECT CAST(DATEPART(year, @DateTime) as VARCHAR(4)) + FORMAT(DATEPART(month, @Datetime), '00');

[/CODE]

If you need more help, post your queries and data types of your date fields.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-08-11 : 21:09:04
Just a quick footnote to MuMu88's suggestion that the FORMAT function is available only in SQL 2012. If you are using an older version of SQL Server, an alternate is this:
CONVERT(CHAR(6),@DateTime, 112)

Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-11 : 21:47:08
MuMu88 and Sunitabeck thank both for replying. I am using SQL 2008, so the format function would not work for me.
For "CONVERT(CHAR(6),@DateTime, 112)", where would I enter this?
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-11 : 22:33:41
Here is the code from one of my dataset:

SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions

WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factAdmissions.ContractCode Is Not Null
AND factAdmissions.BenefitPlanCode Is Not Null
AND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)
AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@ContractCode)


Group by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM
Order by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-11 : 22:49:27
Try this:
quote:
Originally posted by Briceston

Here is the code from one of my dataset:

SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions

WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factAdmissions.ContractCode Is Not Null
AND factAdmissions.BenefitPlanCode Is Not Null
AND AdmitCCYYMM BETWEEN CONVERT(CHAR(6),@StartDate, 112) AND CONVERT(CHAR(6),@EndDate, 112)
AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@ContractCode)


Group by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM
Order by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM



Thanks Sunitabeck for the footnote.
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-12 : 07:03:08
MuMu88, I tried your suggestion, and I got the tablix error.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 10:04:53
quote:
Originally posted by Briceston

MuMu88, I tried your suggestion, and I got the tablix error.



Can you post some of your data (input and expected output) and the exact error you are getting.
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-12 : 11:47:04
Input would be:
(1)Drop down for start and end date.
(2)Drop down to select contract code. I have this in place, it works fine.

Output would be a matrix:
Contract code as rows, dates as columns, and my values.
My problem lies in, my date format does not match the datepicker.

Example:
Contract code 200701 200702 200703
H112-09 298 900 990
H113-10 199 292 181
H114-11 198 209 305

quote:
Originally posted by MuMu88

quote:
Originally posted by Briceston

MuMu88, I tried your suggestion, and I got the tablix error.



Can you post some of your data (input and expected output) and the exact error you are getting.

Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 12:23:14
Can you show your dataset query for date picker.

quote:
Originally posted by Briceston

Input would be:
(1)Drop down for start and end date.
(2)Drop down to select contract code. I have this in place, it works fine.

Output would be a matrix:
Contract code as rows, dates as columns, and my values.
My problem lies in, my date format does not match the datepicker.

Example:
Contract code 200701 200702 200703
H112-09 298 900 990
H113-10 199 292 181
H114-11 198 209 305

quote:
Originally posted by MuMu88

quote:
Originally posted by Briceston

MuMu88, I tried your suggestion, and I got the tablix error.



Can you post some of your data (input and expected output) and the exact error you are getting.



Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-12 : 13:05:32
Do you mean if there is a specfic dataset for just the dates? If so, then no. I'm pulling my dates from the query below. I can pull my dates fine if I leave the data type in the start and end date properties to "text", but then I would have to manually type in the dates.
My problem arise when I select data type "Date/Time", then the format is not recognized.


SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions

WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factAdmissions.ContractCode Is Not Null
AND factAdmissions.BenefitPlanCode Is Not Null
AND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)
AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@ContractCode)


Group by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM
Order by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,

quote:
Originally posted by MuMu88

Can you show your dataset query for date picker.

quote:
Originally posted by Briceston

Input would be:
(1)Drop down for start and end date.
(2)Drop down to select contract code. I have this in place, it works fine.

Output would be a matrix:
Contract code as rows, dates as columns, and my values.
My problem lies in, my date format does not match the datepicker.

Example:
Contract code 200701 200702 200703
H112-09 298 900 990
H113-10 199 292 181
H114-11 198 209 305

quote:
Originally posted by MuMu88

quote:
Originally posted by Briceston

MuMu88, I tried your suggestion, and I got the tablix error.



Can you post some of your data (input and expected output) and the exact error you are getting.





Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 13:43:24
How are you populating (@StartDate) AND (@EndDate) without a dataset?
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-12 : 14:50:18
I might be wrong, but I'm populating my (@start date) and (@end date) from the dataset given above. The AdmitCCYYM is my date field.
So you're saying I should have a seperate dataset with only the AdmitCCYM field? This is my first week using SSRS, so I'm a little confused.

quote:
Originally posted by MuMu88

How are you populating (@StartDate) AND (@EndDate) without a dataset?

Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 21:51:12
I prefer to create a data set for each parameter, it gives more flexibility to format and display the values as they seem fit.

In your case one data set might suffice for both (@start date) and (@end date).

Your query might be something like
SELECT DISTINCT AdmitCCYYMM as DateRange from factAdmissions;

In "report parameter properties" window, choose "Available Values" tab, choose 'Get Values From a Query" option and select the data set you just created, select the value and label fields.
Repeat the process for both @Start and @end dates.


Hope this helps.
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-12 : 22:34:16
I pretty much have what you wrote in place, my problem is the datepicker format is not in line with my date field. How can I format the datepicker to "200701" from the default format?


quote:
Originally posted by MuMu88

I prefer to create a data set for each parameter, it gives more flexibility to format and display the values as they seem fit.

In your case one data set might suffice for both (@start date) and (@end date).

Your query might be something like
SELECT DISTINCT AdmitCCYYMM as DateRange from factAdmissions;

In "report parameter properties" window, choose "Available Values" tab, choose 'Get Values From a Query" option and select the data set you just created, select the value and label fields.
Repeat the process for both @Start and @end dates.


Hope this helps.


Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 22:52:51
Edit the query as below:

quote:
Originally posted by MuMu88

I prefer to create a data set for each parameter, it gives more flexibility to format and display the values as they seem fit.

In your case one data set might suffice for both (@start date) and (@end date).

Your query might be something like
SELECT DISTINCT CONVERT(CHAR(6), AdmitCCYYMM , 112) AS DateRange
FROM factAdmissions;

In "report parameter properties" window, choose "Available Values" tab, choose 'Get Values From a Query" option and select the data set you just created, select the value and label fields.
Repeat the process for both @Start and @end dates.


Hope this helps.


Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-24 : 14:07:16
Thanks for the insight, I now have the date format that I wanted, oppossed to the date picker format. I really appreciate the help.

quote:
Originally posted by MuMu88

Edit the query as below:

quote:
Originally posted by MuMu88

I prefer to create a data set for each parameter, it gives more flexibility to format and display the values as they seem fit.

In your case one data set might suffice for both (@start date) and (@end date).

Your query might be something like
SELECT DISTINCT CONVERT(CHAR(6), AdmitCCYYMM , 112) AS DateRange
FROM factAdmissions;

In "report parameter properties" window, choose "Available Values" tab, choose 'Get Values From a Query" option and select the data set you just created, select the value and label fields.
Repeat the process for both @Start and @end dates.


Hope this helps.




Go to Top of Page
   

- Advertisement -