SSRS - Varchar Date Field not working as Date Parameter in SSRS Report...?
Experts,
I need assistance with an issue. I have a Dataset in my SSRS report that uses a VARCHAR(50) data type field that contains a DATETIME. The field data is a DATETIME format BUT the actual datatype of the field in the database is VARCHAR(50).
I've created a dataset and attempt to use it to populate a report but data never populates.
Can someone help me with "how to configure" the parameters and/or SQL Script logic to correctly use parameters. I believe it all is centered around the need for me to cast/convert this VARCHAR(50) data type field...prior to using it as a data parameter in the SSRS report.
Thanks
I need assistance with an issue. I have a Dataset in my SSRS report that uses a VARCHAR(50) data type field that contains a DATETIME. The field data is a DATETIME format BUT the actual datatype of the field in the database is VARCHAR(50).
I've created a dataset and attempt to use it to populate a report but data never populates.
Can someone help me with "how to configure" the parameters and/or SQL Script logic to correctly use parameters. I believe it all is centered around the need for me to cast/convert this VARCHAR(50) data type field...prior to using it as a data parameter in the SSRS report.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
More info... I created a new VIEW that converts the "date" fields into true DATETIME formats.
When I click on the "Shared Datasource" and then click on "Query Designer" in the Shared Dataset Properties I am able to get data to populate within this window ONLY. When I try to run the report normally using the parameters for the report... it runs and processes..but does not populate any data....
Any assistance is greatly appreciated!
When I click on the "Shared Datasource" and then click on "Query Designer" in the Shared Dataset Properties I am able to get data to populate within this window ONLY. When I try to run the report normally using the parameters for the report... it runs and processes..but does not populate any data....
Any assistance is greatly appreciated!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<Wild guess>
I'm having a distant memory that the Microsoft Access default length for Text columns was 50, so perhaps this client took an Access app/report and converted it somehow but kept all the columns at Text-50.
I'm having a distant memory that the Microsoft Access default length for Text columns was 50, so perhaps this client took an Access app/report and converted it somehow but kept all the columns at Text-50.
ASKER
Thanks for the input, again...I now have a VIEW...that has the correct DATETIME column formats.
With that said... again.... the data still does NOT populate within the report.
AGAIN,.. When I click on the "Shared Datasource" and then click on "Query Designer" in the Shared Dataset Properties I AM ABLE TO GET DATA TO POPULATE THERE WITHIN THE QUERY DESIGNER WINDOW.
But still no data will populate within the report.
My params are set to DATETIME... and the field is a DATETIME.
WHAT AM I MISSING....?!?!
With that said... again.... the data still does NOT populate within the report.
AGAIN,.. When I click on the "Shared Datasource" and then click on "Query Designer" in the Shared Dataset Properties I AM ABLE TO GET DATA TO POPULATE THERE WITHIN THE QUERY DESIGNER WINDOW.
But still no data will populate within the report.
My params are set to DATETIME... and the field is a DATETIME.
WHAT AM I MISSING....?!?!
ASKER
Here is my very simple SQL Query:
SELECT DISTINCT TOP (5) trib_referfromfacilityname
, SUM(CASE WHEN [statecodename] = 'active' THEN 1 ELSE 0 END) AS Admission_Count
, SUM(1) AS Referral_Count
FROM v_Filteredtrib_referral
WHERE (trib_dateofreferral BETWEEN @RefBeginDate AND @RefEndDate)
GROUP BY trib_referfromfacilityname
ORDER BY Admission_Count DESC
SELECT DISTINCT TOP (5) trib_referfromfacilityname
, SUM(CASE WHEN [statecodename] = 'active' THEN 1 ELSE 0 END) AS Admission_Count
, SUM(1) AS Referral_Count
FROM v_Filteredtrib_referral
WHERE (trib_dateofreferral BETWEEN @RefBeginDate AND @RefEndDate)
GROUP BY trib_referfromfacilityname
ORDER BY Admission_Count DESC
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outside of the VIEW CREATION are you saying that there is really not a good viable way to handle this within SSRS...???