Report with SQL Query in Perspective

Ignition
1

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

Back to the Report Designer.

I use a SQL Query, which returns 10 columns from a table.

If I use

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

But if I use

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

DECLARE @startDateTime DATETIME = TRY_CONVERT(DATETIME, ?, 1);

DECLARE @endDateTime DATETIME = TRY_CONVERT(DATETIME, ?, 1);

In the parameters, put

{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.

2

Why are you passing in formatted strings and converting to a datetime when you can use parameters of type Date?

3

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.

4

Can you show more of your report data tab? I use date params all the time without issue.
image

5

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.

6

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

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.

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.

1 Like
7

"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?

8

There is nothing to do, that's just the way it works.

9
1 Like
10

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).