Suppressed Parameter

Cory Fifield -

Trouble viewing images? Click on images to enlarge.

 

What Does A Suppressed Parameter Do?

A suppressed parameter will allow you to run a view and either select a value to filter on or a value that will suppress the filter statement. This gives the user the ability to run a view for a single value or include all values if nothing was selected.

How Does It Work?

When the view is ran, a default value will be loaded into the parameter selection. If the view is ran with the default value it will match the suppress value, telling VDM to suppress that parameter. If a different value is selected instead of the default value, the Suppression SQL statement will be used.

 

Creating A Suppression Parameter

There will be two scenarios for creating a suppression parameter. Scenario one is needing to filter on either a single value or no filter. The other scenario is filtering on multiple values or no filter. The steps for setting this up are below.

Single Value

1. Go to the Parameters tab and Add a parameter.

mceclip1.png

 

2. Set the Type of the parameter to Value.

mceclip2.png

 

3. Set the Datatype for the parameter. In this example EmployeeID is an integer.

mceclip3.png

 

4. Set the default value. This will be the value used when you don't want to filter the view.

mceclip4.png

 

5. Set Allow Suppress to True.

mceclip5.png

 

6. Enter the SQL Statement you wish to use when a parameter value is entered. If you need help creating the SQL Statement, go into FSGS and create the filter there. Once created, copy the statement into the parameter Suppression SQL Statement without the parentheses.

*IMPORTANT NOTE: Delete the filter statement in FSGS once you have it copied*

mceclip6.png

 

mceclip7.png

 

 

7. Set the Suppress Value to the exact value of the Default Value. In this example the Suppress Value is ALL

mceclip8.png

 

8. In the FSGS tab turn on advanced filter and type the name of the parameter in the filter area. For this example it is ?Parameter1?

mceclip0.png

 

9. Run the View.

 

Multiple Values

The multiple value approach leverages an IN Type parameter as this allows multiple values to be selected. There are a few key differences using the IN Type parameter instead of a Value Type.

 

1. Go to the Parameters tab and Add a parameter.

mceclip10.png

 

2. Set Dynamic to True and enter the Dynamic Query to get the value(s) needed or add them to the values list manually.

mceclip11.png

Example Dynamic Query: 

This query will select all distinct EmployeeIDs from the Orders table.

SELECT DISTINCT field FROM table
SELECT DISTINCT EmployeeID FROM Orders

 

3. Set the Type of the parameter to IN.

mceclip12.png

 

4. Set the Datatype for the parameter. In this example EmployeeID is an Integer. 

mceclip13.png

 

5. Set the Default Value. This will be the value used when you don't want to filter the view. *IMPORTANT NOTE: The parentheses around the default value ALL are needed since  the parameter type is set to IN.*

mceclip14.png

 

6. Set Allow Suppress to True.

mceclip15.png

 

7. Enter the SQL Statement you wish to use when a parameter value is entered. If you need help creating the SQL Statement, go into FSGS and create the filter there. Once created, copy the statement into the parameter's Suppression SQL Statement without the parentheses.

*IMPORTANT NOTE: Delete the filter statement in FSGS once you have it copied*

mceclip1.png

 

mceclip16.png

 

 

8. Set the Suppress Value to the exact value of the Default Value. In this example the Value is (ALL). Keeping in mind the parentheses - () are needed around the value for this to work. 

IMPORTANT NOTE: If you are using a string datatype, the default value will need the object identifier around it.

Example for String Datatype: ('ALL') or ('value')

Example for Integer Datatype: (ALL) or (value)

mceclip17.png

 

9. In the FSGS tab turn on advanced filter and type the name of the parameter in the filter area. For this example it is ?Parameter1?

mceclip0.png

 

10. Run the View.

 

Have more questions? Submit a request

Comments