Hello All,
I've built 100's of Reports using the Telerik tools but I've never used a SQL query where I've used a DECLARE function for the data source.
Here is my query:
DECLARE @CaseNum as NVARCHAR(12) = '20604003'
DECLARE @Enc as INTEGER = '743212'
DECLARE @START_Date as DateTime ='2024-10-08 00:00:00.000'
DECLARE @Enc_Date as DateTime= '2024-10-17 00:00:00.000'
DECLARE @Location as NVARCHAR(75)= 'SHOULDER/ARM'
SET @Location = (SELECT Location FROM tblVisitLog WHERE Encounter_code = @Enc AND CaseNumber = @CaseNum AND Discipline = 'PT' AND VisitType <> 'M')
SET @START_Date = (SELECT TOP 1 TimeIN
FROM tblVisitLog
WHERE Encounter_code = (SELECT MIN(Encounter_code) FROM tblVisitLog WHERE CaseNumber = @CaseNum AND Discipline = 'PT' AND VisitType <> 'M' AND TimeIN > '2014-12-31 00:00:000' AND Location = @Location) AND CaseNumber = @CaseNum
AND Discipline = 'PT' AND VisitType <> 'M')
SET @Enc_Date = (SELECT TimeIn FROM tblVisitLog WHERE Encounter_code = @Enc AND CaseNumber = @CaseNum AND Discipline = 'PT' AND VisitType <> 'M')
SELECT COUNT(Encounter_code)
FROM tblVisitLog
WHERE CaseNumber = @CaseNum AND TimeIN BETWEEN @START_Date AND @Enc_Date AND Discipline = 'PT'AND VisitType <> 'M' AND Location = @Location
This works great in SQL Server, but this is throwing the following error when used as the Report Source: for this sub-report.
Invalid value of report parameter 'Location'.
I've ran this report specifying the values (shown here) in my SQL and using the Parameter.Location.Value on the Configuring data source parameters page. I've left them blank and specified the values on the Configure Design Time Parameters, in other words I've tried many many things.
I need help with this. Thank you,
Jack
1 Answer, 1 is accepted
Hello Jack,
Thank you for the shared code and the additional information!
Depending on the data provider used by the selected SQL connection, the @ may be used to specify SQL parameters in the query. I suspect that this is the case here as well, the variables that you are creating are being treated as parameters instead.
If you need to declare local variables in the query, you would need to turn the query into a stored procedure - How to implement local variables in a SQL query used by the SqlDataSource component - Telerik Reporting. Alternatively, you may create additional SqlDataSource components for executing the selection for the parameters that need to be retrieved dynamically from the database, such as @START_Date. Those data source components can be connected to Report Parameters who can then be used in the main query.
The information that I have shared above is about dealing with this scenario in general. However, the error that you are getting, about the invalid value of the Location Report Parameter suggests that the issue is currently with the report parameter values rather than the query. There may be a problem with the query as well, such as the error mentioned in the above article, but if the report parameters have invalid values, the processing will fall before getting to the stage with pulling the data.
You mentioned that that error is thrown inside a SubReport item. The first test that I recommend doing is rendering the report used within the SubReport item alone, not as a sub report. This test should tell you whether the issue is within the report design of the sub report or with the values passed from the main report.
If it is the former, inspect the default value of the Location report parameter, verify that the selected type of the parameter is correct. If it is the latter, please open the main report, then open the ReportSource property of the subreport and inspect what values are passed to the parameters of the subreport - Subreport: Defining the Parameters.
In case you need further assistance, please send me copies of the two reports so that I can preview them locally and investigate the issue.
I look forward to receiving an update from you.
Regards,
Dimitar
Progress Telerik