# Report with SQL Query in Perspective **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [August 14, 2024, 12:52pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643 "2024-08-14T12:52:08Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![JoseJardelLuciano](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@JoseJardelLuciano](https://forum.inductiveautomation.com/u/JoseJardelLuciano)\ **Post date:** [August 14, 2024, 12:52pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/1 "2024-08-14T12:52:08Z")
I am creating a report based on start\_date and end\_date. To get the dates, I used **startInputDate** and **endInputDate** , each in a Date and Time picker in Perspective. For testing, in the report header I used Start Date: @start\_date@ End Date: @end\_date@ When I return to the report, N/A appears in these fields (because they are passed within the Report Viewer). And when I go to the report view, these dates appear correct. In other words, the logic of getting the dates is correct (I am getting the formattedValue field). ![imagem01](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/2/b2ff23d37a0c57df3a63a15f54a59102e2274ae1.png) Back to the Report Designer. I use a SQL Query, which returns 10 columns from a table. If I use ```python DECLARE @startDateTime DATETIME = TRY_CONVERT(DATETIME, '08/01/24 12:00:00 AM', 1); DECLARE @endDateTime DATETIME = TRY_CONVERT(DATETIME, '08/02/24 12:00:00 AM', 1); ``` The query runs correctly, and I can work by assembling the table within Report Design. ![imagem02](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/3/23f3a78b819f7c3ea318bc2c5c8175ad34afbafe.png) But if I use ```python DECLARE @startDateTime DATETIME = TRY_CONVERT(DATETIME, @start_date@, 1); DECLARE @endDateTime DATETIME = TRY_CONVERT(DATETIME, @end_date@, 1); ``` A query error appears (since start\_date and end\_date are data from the Report Viewer) and when you return to the Report Viewer, the data is not obtained. I have also tried ```python DECLARE @startDateTime DATETIME = TRY_CONVERT(DATETIME, ?, 1); DECLARE @endDateTime DATETIME = TRY_CONVERT(DATETIME, ?, 1); ``` In the parameters, put ```python {start_date} {end_date} ``` But none of them have been successful yet. I would be very grateful for any tips to improve the logic or get around this error. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 14, 2024, 12:56pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/2 "2024-08-14T12:56:43Z")
Why are you passing in formatted strings and converting to a datetime when you can use parameters of type `Date`? ---
**Author:** ![JoseJardelLuciano](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@JoseJardelLuciano](https://forum.inductiveautomation.com/u/JoseJardelLuciano)\ **Post date:** [August 14, 2024, 1:08pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/3 "2024-08-14T13:08:03Z")
I've also tried to get "value", now I'm trying "formattedValue". And in the query adjusting it to not convert, to convert, I've tried several ways. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 14, 2024, 1:11pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/4 "2024-08-14T13:11:33Z")
Can you show more of your report data tab? I use date params all the time without issue. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/c/fc044ad819ebd78984bc06c7a1fb678cb0f5d022.png) ---
**Author:** ![JoseJardelLuciano](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@JoseJardelLuciano](https://forum.inductiveautomation.com/u/JoseJardelLuciano)\ **Post date:** [August 14, 2024, 1:38pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/5 "2024-08-14T13:38:12Z")
In this case, you are using the default {StartDate} of the "Report" parameters, correct? I need to get the value that the user passes in the Report Viewer. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 14, 2024, 2:17pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/6 "2024-08-14T14:17:34Z")
In the report viewer component, you have to add the parameters to the `props.params` object. So your props would look something like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/0/c0bddef567f663a7ea11b37880e7f3b9d049ce41.png) Then just bind or set these to the dates that you want to use in the report. In the report editor, you want to set your SQL as @dkhayes117 has shown where the `?` is used as a parameter substitute. Do not "DECLARE" variables, just use a place holder. ```python SELECT * FROM someTable where t_stamp >= ? or t_stamp < ? ``` When creating the parameters in the report you can provide an expression to use as the default value for the parameters. These will be replaced by any value supplied by the report view component or used if no values are supplied. ---
**Author:** ![JoseJardelLuciano](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@JoseJardelLuciano](https://forum.inductiveautomation.com/u/JoseJardelLuciano)\ **Post date:** [August 14, 2024, 2:20pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/7 "2024-08-14T14:20:47Z")
"_When creating the parameters in the report you can provide an expression to use as the default value for the parameters. These will be replaced by any value supplied by the report view component or used if no values are supplied._" Could you give me a tip on how to do this part? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 14, 2024, 2:22pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/8 "2024-08-14T14:22:13Z")
> [@JoseJardelLuciano](#): > > Could you give me a tip on how to do this part? There is nothing to do, that's just the way it works. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [August 14, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/9 "2024-08-14T14:38:14Z")
> **[Reports in Perspective Sessions Video at Inductive University](https://www.inductiveuniversity.com/videos/reports-in-perspective-sessions/8.1)** > > Learn how to embed your reports directly in a Perspective Session. ---
**Author:** ![JoseJardelLuciano](https://avatars.discourse-cdn.com/v4/letter/j/35a633/32.png) [@JoseJardelLuciano](https://forum.inductiveautomation.com/u/JoseJardelLuciano)\ **Post date:** [August 22, 2024, 10:56am UTC](https://forum.inductiveautomation.com/t/report-with-sql-query-in-perspective/94643/10 "2024-08-22T10:56:32Z")
Everything was 100% correct. The problem was my approach to solving the problem of the report not recognizing the startDate and endDate that were configured in the Report Viewer. I ignored the error and accessed the Report directly through the browser and everything was there, working perfectly. This is what happens when you don't have much experience in development (I work in Cybersecurity).