Setting up Stored Procs

This guide will walk you through the process of setting up and using Stored Procedures in Dotnet Report. Stored Procedures allow for complex operations and custom SQL code to be executed within reports. While these reports are more rigid than ad-hoc reports, they are still highly customizable and can integrate with Global Filters for dynamic reporting.

Prerequisites:

Before you begin setting up stored procedures in Dotnet Report, ensure the following prerequisites are met:

1. Database Connection: Your database should be properly connected to Dotnet Report and accessible from the Manage Database section.
   
2. Stored Procedure Availability: Ensure that the stored procedures you wish to use are already created in your database.
   
3. Permission Setup: Ensure that your database user has sufficient privileges to access and execute stored procedures.
   

Step-by-Step Guide for Setting Up Stored Procedures:

Step 1: Open the Stored Procedures Tab

Navigate to Manage Database > Stored Procs to begin setting up stored procedures. This tab allows you to configure how stored procedures will be used as data sources for reports.

Step 2: Add a Stored Procedure

To add a new stored procedure to Dotnet Report:

1. Click on the Add Stored Procs from Database button.
   
2. A search bar will appear. Type a part of the name of the stored procedure to search your database.

3. Dotnet Report will load all the parameters and data columns used in the stored procedure’s result set.
   

Note: Currently, Dotnet Report does not support multi-result set stored procedures, so ensure your stored procedure returns only one result set.

Step 3: Configure the Stored Procedure

Once the stored procedure is loaded, you can customize it by modifying its display name and columns/parameters to make them more user-friendly. This is especially useful if you want to ensure that the report looks clear when presented to the user in the Report Builder Wizard.

Here’s how you can configure the stored procedure:

1. Display Name: Change the name of the stored procedure to something more intuitive if necessary.
   
2. Columns/Parameters:
   
   • For each parameter or column, you can edit the name to make it more understandable for end-users.
     
   • Relational Lookup Lists: If a parameter requires a predefined set of values (e.g., a list of vendors), you can set up a Relational Lookup List for that parameter. This will allow users to choose from a set list when building reports.
     
   • Default Values: You can set default values for parameters. These can be fixed values or can be dynamically pulled from Global Filters.
     
   • Hidden Fields: If you want to hide certain parameters from the user (e.g., User ID for security purposes), you can mark them as Hidden. For hidden fields, you must provide a default value, which can come from the user’s session or Global Filters.
     

Tip: Using Global Filters and Hidden Parameters is a great way to apply user-specific filters, like a Vendor or User ID, automatically from the logged-in user’s session. This can be especially useful for securing data in reports.

Step 4: Save the Stored Procedure Setup

After making the necessary adjustments, click Save to store your changes. This stored procedure is now ready to be used in the Report Builder Wizard.

Step 5: Using Stored Procedures in Report Builder

Once the stored procedure is set up and saved:

1. When creating a new report, select the Pre-defined Report option in the Report Builder Wizard.
   
2. You will see the stored procedure listed as a data source. Choose it to create a report based on the results of the stored procedure.
   
3. Any parameters defined in the stored procedure (such as date ranges, user IDs, etc.) will appear as filters in the report builder, where you can adjust them as needed.
   

Note: While the stored procedure report is not as flexible as a Dynamic Report (where users can build custom queries), it provides a powerful and efficient way to reuse predefined queries for consistent reporting.

Example Scenario: Using a Stored Procedure for a Vendor Report

Let’s say you have a stored procedure called GetVendorSales that retrieves sales data for a particular vendor. Here’s how you might set it up:

1. Stored Procedure Name: Change the name from GetVendorSales to something more user-friendly like “Vendor Sales Report”.
   
2. Parameters:
   
   • VendorId: This could be a parameter that is passed from the user. You can set a Global Filter for the Vendor ID, so the report only shows data relevant to the logged-in user’s vendor.
     
   • StartDate and EndDate: You can configure these parameters as date pickers for the user, or you could set default values based on the current date range.
     
3. Hidden Fields: If you don’t want the user to see certain parameters (like the VendorId), mark this field as Hidden and assign it a default value pulled from the user’s session.
   

Step 6: Viewing and Editing Stored Procedures

Once stored procedures are set up:

• You can always go back to the Stored Procs tab to view, edit, or delete any stored procedure configurations.
  
• If you need to update the stored procedure in the database (e.g., adding new parameters or columns), repeat the process by adding it again or modifying the existing setup. 

By following these steps, you’ll be able to efficiently set up and use stored procedures in Dotnet Report, allowing for more sophisticated and efficient reporting.