Blogs Home
Tuesday, June 16, 2020 12:00 PM

SSRS: Using Parameters to Add Documentation to a Report

Written by Thomas Harlan, Reporting Services Team - iatricSystems

RWS-Blog-Header-Using-Parameters-to-Add-Documentation-May 2020

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.

Report 2 Uncommon Features image 1

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.



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

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

Steps text image 2

  • 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.

Query in SSMS image 6

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


  • Execute the query to test.

Query to Test image 7

  • 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.

RWS June blog image 4

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

Data Type Text image 5

    • 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.
Preview the Report image 8

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!