Creating a Grid Report

INTRODUCTION:

Bluestreak provides a reporting capability wherein the customers can create their own Grid reports provided they are knowledgeable with the Bluestreak database schema. This white paper will explain the steps needed to create a custom report.

NEW GRID REPORT:

The logged in Bluestreak user must have the role “ReportDefinition_Update” to create report definitions and see the “Report Definitions” module under Setup. In the Report Definitions module, click “Add” to create a new report definition. The following input fields must also be filled out in the editor screen.

1. Category: Grid-Based Reports

2. Name: Name of the report

3. Description: Description of the report

4. Sql: SQL query to be used in the report. The dynamic report parameters are explained later in this guide

5. Features: Not needed (Intended for future release)

6. Key: Unique name

7. Key/Procedure Name: Stored procedure name (if you would like the grid report to get data using stored procedure, otherwise it can be left blank).

8. Param List: The parameter names that are used either in the stored procedure or the SQL query. The parameters are separated by “|” sign and there are some parameters like start and end dates.needs “+” sign.

9. Criteria List: The criteria lists are separated by “|” sign and the number of items in the parameter list must match with the criteria list..

10. Icon: N/A

11. Display Order: N/A

12. Format Type: Grid-Sql (for SQL Query) or Grid (for Stored Procedure)

13. Report Engine: Grid

14. Active: Checked

The following section explains some of the fields specified above in detail.

SQL:

A simple SQL statement can be specified as such in the SQL field.

Example: SELECT * FROM CONTACT

To specify a dynamic SQL statement, the parameter index is specified within the parentheses “{}”. If there are 2 parameters specified in the param list, the first param is specified as {0} and the second is specified as {1}.

Example: SELECT * FROM CONTACT WHERE conAddedDate > {0}.

SELECT * FROM CONTACT WHERE conAddedDate BETWEEN {0} AND {1}

KEY/PROCEDURE NAME:

If a stored procedure is specified in the Key/Procedure Name field, the SQL Query must be left blank and the Format Type should be specified as “Grid”. In the above example , the SQL statement can be moved to a stored procedure and the procedure name can be specified in the Key/Procedure Name field.

PARAMETER LIST:

The parameter list must include the list of parameters that are specified either in the SQL query or the stored procedure. For SQL query, the param names can be anything meaningful but the number of parameters must match with the number of dynamic parameters used in the SQL query. But for the stored procedure, the param names must match with the variables defined within the stored procedure.

There are some predefined parameters that must include a “+” sign. Multiple parameters are specified by including the separator “|” between them.

Ex: @StartDate+@EndDate for the criteria “FromToDate”

@BeginString+@EndString for the criteria “Process_Master_List”.

If there is a need to include both the above parameters, then it will look like @StartDate+@EndDate|@BeginString+@EndString.

CRITERIA LIST:

The list specified in the Criteria list will show or hide the necessary fields in the editor. In order to get the necessary fields to be seen on the screen and input data, please use it in the Criteria list as shown in the list below separated by “|”.