In addition to letting users build their own Ad Hoc query for Reports, dotnet Report also lets developers use SQL Stored Procedures as a Report’s data source for complex operations where custom SQL code is required. Users are able to select Stored Procedure reports are as “Pre-Defined” reports in the Report Builder Wizard, versus the “Dynamic” report which lets users build their own query. Since these are coded reports, they are not as flexible as building the Ad Hoc query, but some customization is available and parameter values can be passed as filters or from the Global Filters.
In order to let a user use a Stored Procedure to run their report, you have to set it up first. To setup a Stored Proc in dotnet report, run the setup page and click on the “Stored Proc” tab:
To add a new Stored Procedure from your database, click on the “Add Stored Procs from database” button, and then provide a part of the name of the Stored Proc to search the database:
dotnet Report will load all the parameters and data columns used in the Stored Procedure’s result set. Please note that multi result set Stored Procedures are not support.
You can change the Display name for the Stored Proc and it’s columns/parameters to user friendly names, so that when it’s shown to the user in the wizard, it’s not confusing. For Parameters, you can also do the following:
- Setup a Relational Lookup List, similar to how lookup lists are setup in a regular Filter.
- Provide a Default Value. This can also be a “Global Filter” value.
- Change it to a “Hidden” field so that users do not see it and are not asked to provide the value for the filter. For Hidden Filters, you must provide a default value.
Using Global Filters and Hidden Parameters allows you provide an easy to pass a User or Vendor Id as a parameter value in the backend from logged in user’s claims to apply security settings.
Once you have setup your Stored Proc as needed, you can save the changes, and the user can use the Stored Procedure to create a “Pre-defined” report.
