Using Stored Procedure in Ignition Report

Ignition
1

I have a question about Data Sources in Reports. Has anyone been using a stored procedure instead of a direct sql statement for data sources in reports? Do you use Basic SQL Query query type or do you use a Script? What is suggested?

script
script1470×462 39.8 KB

Basic SQL Query
Basic SQL Query1516×363 27 KB

I’ve also noticed that the stored procedure runs fast in SQL Server Management Studio (SSMS) is is slow to load in the report. Has anyone encountered this?

2

We just use the stored procedure just like a normal sql query and pass it the required parameters.

ReportSP
ReportSP1599×858 41.3 KB

2 Likes
3
4

Will try that @PGriffith. Cheers.

5

CPU
CPU1579×257 18.3 KB

When running a report, the CPU usage goes up to above 90%. Is there a way to check what’s causing this?

6

How much data (number of rows, number of columns) is returned by the query? How many pages is the resulting report?

You can get a thread dump (from the gateway web UI, in the status → diagnostics → threads section) while the query is running/the report is generating, but it likely won’t tell you a whole lot.

7

10 rows, 131 columns. 10 pages for the report, each page has 4 charts aned 8 tables