May 24, 2011 at 4:56 am
Hi,
I wanted to know if the following statement is correct to be included in the sql code:
create_dt >= @From and create_dt < = @To
The problem is the report does not display data for the 'To month' but displays data for the 'From month'.
For e.g., if the user enters the 'From month' as Oct 2010 and 'To month' as May 2011, the data is displayed for the months Oct 2010 till Apr 2011 but does not include May 2011. This statement is included in the following SQL code :
-----------------------------------------------------------------------------
select CREATE_OFFICE_TEAM_CD, count(CREATE_OFFICE_TEAM_CD) as OFFICE_TEAM, SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8) AS create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
and create_dt >= @From and create_dt < = @To
group by CREATE_OFFICE_TEAM_CD, SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8)
ORDER BY datepart(mm,SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8))
-------------------------------------------------------------------------------
Am i doing something wrong here ? Could someone please enlighten me about what I am missing ?
Thanks and Regards,
Paul
May 24, 2011 at 6:29 am
What's the datatype of @From and @To?
It could be an (implicit) conversion issue.
-- Gianluca Sartori
May 24, 2011 at 6:32 am
I'd check to make sure that the @To is not set to 2011-05-01 and actually set to 2011-05-31.
I personnally preffer to use < 2011-06-01. I can't tell you why, but this seems more natural to me.
May 24, 2011 at 7:40 am
Gianluca Sartori (5/24/2011)
What's the datatype of @From and @To?It could be an (implicit) conversion issue.
Thanks for your reply. both @from and @to are set as String datatype in the report parameters section of SSRS. When I tried to change them to datetime datatype it gave an error.
May 24, 2011 at 7:42 am
Ninja's_RGR'us (5/24/2011)
I'd check to make sure that the @To is not set to 2011-05-01 and actually set to 2011-05-31.I personnally preffer to use < 2011-06-01. I can't tell you why, but this seems more natural to me.
Thanks for your reply. Actually the default values for both the parameters have been set up as NULL...the
<= comparison operator does not seem to be working and thats the problem I am facing.
May 24, 2011 at 7:50 am
What's the datatype of the parameter in the stored procedure?
What's the datatype of create_date?
May 24, 2011 at 7:59 am
Ninja's_RGR'us (5/24/2011)
What's the datatype of the parameter in the stored procedure?What's the datatype of create_date?
Thanks again.
The datatype of the create-dt field in the table is datetime. The parameter is not defined in the stored proc and as it has already been defined in the report parameter section, the same has been used within the stored procedure as:
-----------------------------------------------------------------
select CREATE_OFFICE_TEAM_CD, count(CREATE_OFFICE_TEAM_CD) as OFFICE_TEAM,SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8) AS create_dt from MF_FORUM
where WORKFLOW_ACTION_CD IN ('D', 'A')
and CREATE_OFFICE_TEAM_CD in ('KEU_TMD_IN', 'KEU_TMD_OUT', 'KEU_TMD_SSP','KEU_GOA_RTD', 'KEU_HAM_RTD', 'KEU_ANR_RTD', 'KEU_FXT_RTD')
and (create_dt >= @From) and ( create_dt <= @To)
group by CREATE_OFFICE_TEAM_CD, SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8)
ORDER BY datepart(mm,SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8))
---------------------------------------------------------------------------------
May 24, 2011 at 8:07 am
Then put it back as datetime in the parameter of the report.
If it still fails, dump all the converts in the query (to test) and it should work.
May 24, 2011 at 8:24 am
Ninja's_RGR'us (5/24/2011)
Then put it back as datetime in the parameter of the report.If it still fails, dump all the converts in the query (to test) and it should work.
Changing to datetime datatype doesnt work in the report parameter and gives an error. The thing is I need the convert in the query to format the datetime. also, it would change the other parts of the report and then that would have to be looked into.
May 24, 2011 at 8:29 am
What error are you getting?
Comparing a date to a date has never been an issue for ss.
May 24, 2011 at 8:39 am
Ninja's_RGR'us (5/24/2011)
What error are you getting?Comparing a date to a date has never been an issue for ss.
When I change the datatype to datetime in the report parameter, it gives an error- The property 'ValidValues' of report parameter 'From' doesnt have the expected type.'
Could this because of the reason that I am comparing the datetime (datatype of create_dt in the database)with SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8) ?
May 24, 2011 at 8:42 am
Definitely yes.
I'd also try deleting the parameter and remaking it.
May 24, 2011 at 8:50 am
Ninja's_RGR'us (5/24/2011)
Definitely yes.I'd also try deleting the parameter and remaking it.
I have tried remaking the parameter but still get an error. Also, the thing is the @From part still works okay and I am able to compare it with SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8) , its only the @to part I am having problems with. Even @to works okay partially, it only doesnt display the end month...
May 24, 2011 at 8:53 am
Try changing the query then creating a new report from scratch (just to see if the query runs... no need to rebuild the rest)
May 24, 2011 at 8:56 am
Ninja's_RGR'us (5/24/2011)
Try changing the query then creating a new report from scratch (just to see if the query runs... no need to rebuild the rest)
Actually that is the problem I am having. I have tried everything I could and have exhausted my options. This is the reason I am on this forum....
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply