Report SQL

To develop the SQL for the report, use the information in the following sections.

Develop a Stored Procedure in the netFORUM database.

Not all reports require a Stored Procedure. SSRS allows you to create queries and joins embedded in the report itself, so you do not technically need to create an SP. However, Abila endorses the use of stored procedures for reports, since they provide increased performance and flexibility.

Stored Procedures must be owned by [dbo]. For example: 

CREATE procedure dbo.rpt_client_gwsae_mb_membership_roster

Naming the Stored Procedure

The stored procedure must be named in the format: rpt_basetablename_word1_word2

For a committee position report, name the SP rpt_mb_committee_x_customer_by_position.

If you are not sure which is the "basetablename", it should be the one that is most closely associated with the data result set.

For custom reports for a specific client, the naming convention is as follows:

rpt_client_clientacronym_basetablename_word1_word2

For example:

rpt_client_ctam_ev_event_confirmed_sponsor_list

Parameters

When you ultimately name the SSRS report file, give it the exact same name as the SP only leave out the rpt prefix. (See more below.)

You may need nullable parameters for "Start Date" and "End Date" e.g. "cmc_start_date" and "cmc_end_date". If parameters are left null then don’t exclude any records based on these dates; select all records. The "Start Date" and "End Date" columns exist in many tables and signify when the record is active.

Because of the way SSRS runs through netFORUM, the datatype of all parameters must be varchar or nvarchar. Even a GUID or a Date must be varchar. For GUID, make it varchar(38). For dates: varchar(20).

Important Concern with GUID Parameters

When netFORUM passes GUID parameter values to a report, the value is passed to the report format and then on to the stored procedure, e.g.:

exec "netFORUM"."dbo"."rpt_ac_invoice_detail_InvoiceByCustomer";1 ’5d59b56e-69b5-400a-8f90-910485e2cffe’, ’’, ’’, ’’, NULL

Be sure to account for this in your SP. If your query runs like this 

SELECT * 
FROM ev_event 
WHERE evt_key = @evt_key

Then this will work.

If, however, your query needs to retrieve ALL records when the parameter is left blank, then this approach may be made: 

IF isnull(@evt_key,’’) = ’’
 
SELECT @evt_key = ’%’
 
SELECT *
FROM ev_event
WHERE evt_key LIKE @evt_key

Then your query will run, and pull all events, when @evt_key is equal to ’%’, but it will not return records if @evt_key is an actual GUID value such as ’5d59b56e-69b5-400a-8f90-910485e2cffe’.

You could rewrite your query in one of the following 2 ways: 

IF isnull(@evt_key,’’) = ’’
 
SELECT @evt_key = ’%’
 
SELECT * FROM ev_event
 
WHERE
 
(@evt_key <> ’%’ AND evt_key = @evt_key)

or 

(@evt_key LIKE @evt_key)
IF isnull(@evt_key,’’) = ’’
 
SELECT @evt_key = NULL
 
SELECT * FROM ev_event
 
WHERE
 
((@evt_key IS NOT NULL AND evt_key = @evt_key)

or 

(@evt_key IS NULL))
Alternately, you can manipulate your parameter value to be able to run in the original query 
SELECT * FROM ev_event WHERE evt_key LIKE @evt_key
in the following way: 
SELECT @evt_key = [dbo].[av_PrepRptGuidParam] (@evt_key, ’returnall’)
 
SELECT * FROM ev_event WHERE evt_key LIKE @evt_key

The UDF av_PrepRptGuidParam(<param name>, <option>) can also be issued as ’returnnull’ if you wish for the UDF to return your parameter value as NULL if the incoming @evt_key is null or empty. The code segment in #2 above could then be changed to: 

SELECT @evt_key = [dbo].[av_PrepRptGuidParam] (@evt_key, ’returnnull’)
 
SELECT * FROM ev_event
 
WHERE ((@evt_key IS NOT NULL AND evt_key = @evt_key) OR (@evt_key IS NULL))

Naming Parameters

All parameters for the report should have the same name as the table column it is most closely associated with. If you are setting a parameter for Expire Date, please call your parameter "mb_expire_date" and not "ExpireDate." This makes it easier for SQL developers to easily understand what the report is querying.

Deleted Column Consideration

Be mindful of the "Deleted column" that exists in every table, e.g. "cst_delete_flag". A value of 1 means the record is marked for deletion and should therefore be excluded from the report in MOST situations, but not all. For example, it is possible to create a price entry for a product in netFORUM, sell it, and then delete the price entry. If you then had an accounting report with the following logic: 

SELECT * FROM ac_invoice_detail
JOIN oe_price ON ivd_prc_key = prc_key
WHERE ivd_delete_flag = 0 AND prc_delete_flag = 0

This could cause a line item (ac_invoice_detail record) to be excluded from an accounting report because it holds the sale of a price entry (oe_price record) that has been deleted.

Association Code

Also be mindful of the Association code (for example, the code that exists in some tables that flags a record as for being part of a specific association). In all likelihood nearly all of your reports have an association code as a parameter.

Stored Procedure Coding Tips

Keep the following tips in mind as you develop a stored procedure:

  • Use LEFT JOIN to join to tables on columns that are NULLABLE in the main table to ensure you don’t leave out records
  • Use (NOLOCK) on the tables in the FROM clause to enhance SP speed
  • If you are forced to use VARCHAR datatypes for parameters for data that is really a date or GUID, convert them to the proper data type before you run your main query. This can make the main query run faster and is also easier to manage and understand if you do not need to convert data types in the middle of a query.
  • When you filter on the delete flag or other fields on your JOINed tables, it is best to do this in your JOIN clause instead of the WHERE clause, especially if you have LEFT JOIN.

For example, this statement will have the (probably) unintended effect of leaving out any event records that do not have a session: 

SELECT *
 
FROM ev_event (NOLOCK)
 
LEFT JOIN ev_session (NOLOCK)ON ses_evt_key = evt_key
 
WHERE evt_delete_flag = 0 AND ses_delete_flag = 0

The proper way to do this is as follows. Note how the last line of the WHERE clause has been moved into the LEFT JOIN clause 

SELECT *
 
FROM ev_event (NOLOCK)
 
LEFT JOIN ev_session (NOLOCK)ON ses_evt_key = evt_key AND ses_delete_flag = 0
 
WHERE evt_delete_flag = 0
  • Avoid having too many JOIN conditions in a query (5 or more tables), especially if on large tables. If you see this happening, consider designing your SP with a series of smaller queries in TEMP tables.
  • If you use TEMP tables then avoid running UPDATE statements on TEMP tables. Remember that when SQL sees an UPDATE, it actually runs a DELETE and then an INSERT which consumes resources.
  • If you are collecting large amounts of data in a TEMP, consider adding an index on the TEMP table on the COLUMN that is used later on for JOIN or WHERE expressions.
  • Try using exec(@sql) statements for conditional or dynamic SELECT statements.
  • Avoid sub selects if possible
  • Output dates as select convert(varchar(10),zyx_date,101) when you wish to suppress the output of the timestamp
  • Be sure to test your SP on larger databases. Even if it works on 5 to 10 records, the speed may be too slow for larger databases.
  • Consider creating a VIEW if you see yourself writing subtle variations of a report.

Granting Rights to Roles for the Stored Procedure

You must grant rights to certain SQL Roles for each SP. Those groups and the syntax for this are: 

GRANT EXECUTE ON rpt_ev_event_sponsor_list TO netFORUMReport
 
GO

This ensures that the report can run. If you overlook this step, the report will not run because of inadequate rights.