SSRS: Using Parameters to Add Documentation to a Report

In certain situations, it may be desirable to make additional documentation available to the user at runtime. SSRS parameters can be leveraged for this purpose.

In our example we use a report with two uncommon features: (1) use of wildcards in the name parameters, (2) use of Soundex to increase the flexibility of the search.

Our example report allows for the use of SQL wildcards and Soundexing (Soundex is a method used phonetically match names). Neither of these methods are common and end users may need information on their use.  The two Help parameters provide documentation.

Additional information about the parameters.

• These parameters may be ‘selected’ by the user but are ignored by the report.
• The width of the help parameters is driven by the length of the text lines.

Steps

• Add two datasets to the SSRS report: WildcardHelpDs, SoundexHelpDs.

       We use the SQL table value constructor to create our help datasets.

• To build the table construct we first create the help text in a text application such as Notepad or Textpad. Use spaces instead of tabs and use a space in any blank lines.

Soundex converts a string to an alphanumeric code based on phonetics. A Soundex code is a four-character string of one letter followed by 3 digits based on the following rules.

                  The first character of the string must be a letter.

                  The first letter converted to uppercase.

                  The next three numbers assigned as follows:

Number  Represents the Letters

1  B, F, P, V

2  C, G, J, K, Q, S, X, Z

3 D, T

4 L

5 M, N

6 R

The letters A, E, I, O, U, H, W, and Y are ignored.

• Copy & paste the text into Excel.

In column B add a series 1 – n to label each row of text.

In column C use the formula: =",("&B2&",'"&A2&"')" (you may want to remove the comma in row 1) for each row.

• Create a shell query in SSMS.

• Copy & paste the text from column C in Excel in the blank line following “(VALUES” in the shell query.

• Execute the query to test.

• In SSRS create the dataset SoundexHelpDs. Select a data source and the Text option and paste the query into the text pane and click Refresh Fields.

• Create a parameter for SoundexHelp.
• • Make the parameter Visible and use the Data Type Text.

• • In Available Values set the dataset to SoundexHelpDs and use Textline for both Label and Value fields.

• • In Default Values set the dataset to SoundexHelpDs and the Value field to Textline.

• Create a parameter for WildcardHelp following the same steps as above.
• For SSRS 2016 and above, position the parameters on the grid.
• Preview the report.

The Stored Procedure can be found here and the Report’s RDL file can be found here.

If you ever need help with MEDITECH DR optimization, reporting, extract, index creation or analysis please feel free to give your iatricSystems Account Executive a call!