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? |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-08-11 : 16:13:45
|
I'm populating the date drop down from a query. |
|
|
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 orconvert the value to your date field format in your where clauseby 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. |
|
|
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) |
|
|
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? |
|
|
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 NullAND factAdmissions.BenefitPlanCode Is Not NullAND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@ContractCode)Group by factAdmissions.ContractCode, factAdmissions.BenefitPlanCode, factAdmissions.AdmitCCYYMMOrder by factAdmissions.ContractCode, factAdmissions.BenefitPlanCode, factAdmissions.AdmitCCYYMM |
|
|
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 NullAND factAdmissions.BenefitPlanCode Is Not NullAND 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.AdmitCCYYMMOrder by factAdmissions.ContractCode, factAdmissions.BenefitPlanCode, factAdmissions.AdmitCCYYMM
Thanks Sunitabeck for the footnote. |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-08-12 : 07:03:08
|
MuMu88, I tried your suggestion, and I got the tablix error. |
|
|
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. |
|
|
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 200703H112-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.
|
|
|
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 200703H112-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.
|
|
|
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 factAdmissionsWHERE factAdmissions.AdmitCCYYMM >='200701'AND factAdmissions.AcuteSNFIndAdmit ='a'AND factAdmissions.ContractCode Is Not NullAND factAdmissions.BenefitPlanCode Is Not NullAND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@ContractCode)Group by factAdmissions.ContractCode,factAdmissions.BenefitPlanCode,factAdmissions.AdmitCCYYMMOrder 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 200703H112-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.
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-12 : 13:43:24
|
How are you populating (@StartDate) AND (@EndDate) without a dataset? |
|
|
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?
|
|
|
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. |
|
|
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.
|
|
|
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.
|
|
|
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.
|
|
|
|