When using a report parameter to filter a list based on the values of a choice column (ie: status, priority,…), you may want to consider two approaches:
- You will only display values that are already in use. Say you are filtering issues based on a specific “Priority” that the user can select when running the report. Even though the “Priority” is a SharePoint choice column that contains the following 3 values (High, Normal and low), if you don’t have any issues with a “low” priority value, you may not want to display “Low” as a possible choice for filtering out issues.
With that approach, you consider that it is not necessary to bother the user with some value that does not return any items.
- You will display all possible values regardless of its selection will return any values. If we take the previous example, you want to display “low” as a possible choice for filtering issues even if you don’t have any issues with low priority.
With that approach, you are considering that displaying all the possible values and not returning any items for a specific value brings some meaningful information to the user.
Both approaches are perfectly legitimate. It all depend on the business case.
In version 3.7 and earlier of Enesys RS Data Extension, only the first scenario can be implemented. To implement this approach, you will select distinct values from the specific field you want to filter and use them for the report parameter values.
Enesys RS Data Extension 3.8 offers the possibility to retrieve the choice values from a SharePoint choice columns. With this new feature, you can therefore implement the second scenario described earlier and present all the possible values for the column you are filtering as part of a report parameter.
Retrieving choices from a column of type "Choice”
Enesys RS Data Extension 3.8 provides a new statement for retrieving “choices” from list or site columns of type choice:
When adding “Column Choices” statement to your query, you are proposed to either retrieve choices from a specific list column or from a column defined at the site level:
When selecting the option, “Retrieve choices from a list column”, you get the usual dialog box where you can select the desired list:
When selecting a list or a site, you will only be proposed the choice columns available in the list or the site. As part of the statement, you can only select a single column to retrieve choices values from (it would not make many sense to return value from multiple choices columns in a single result set). Executing the statement will return choices associated with the column.