SYMPTOM:
If a user wants to create and apply security filters in bulk for many users, then Command Manager can be used to perform this administrative process in a more efficient fashion than creating them within Strategy Developer. An issue can arise if the security filters are being created using the ID form of an attribute that does not reference a numeric data type column, because the syntax to pass a parameter to an attribute ID in a security filter expression is to do so without the parameter within any quotation marks. For example:
Php
CREATE SECURITY FILTER "security filter test" IN PROJECT "project test" EXPRESSION "region@id=1";
Notice that the expression is within double quotes per the syntax required for the script, however the parameter, 1 in this instance, is not enclosed within any other quotation marks. This is because the parameter being passed is using a numeric data type in this instance.
Within Strategy, parameters can be passed in a few ways, some of which include: parameters not enclosed within any quotes, parameters enclosed within single quotes, and parameters enclosed within double quotes.
Parameters that are not enclosed within any quotes are passed as a numeric data type.
Parameters that are enclosed within single quotes are passed as a text data type.
Parameters that are enclosed within double quotes are passed as a date data type.
CAUSE:
The issue can arise if the manner in which the parameter is being passed from Command Manager does not sync up with the data type that the column of the attribute form uses. The manner in which the error in this instance was encountered shows that the parameter is trying to be passed from Command Manager as an integer, but it appears that the column data type is set to varchar, which would be a text data type.
Sample Code/Error
Conversion failed when converting the varchar value 'xxx' to data type int.
Since the data types do not match there is an error since it appears that the database cannot resolve the issue itself. Furthermore, attempts to place single quotes around the parameter within Command Manager will give an incorrect syntax error because it is expecting a numeric value, and the single quotes appear to throw off or error out the syntax.
The error message referenced above was experienced in SQL Server 2008 R2, and is not meant to be identical for all databases. Therefore, a different error message may result depending on the database for this particular issue.
ACTION:
A few actions can be done to rectify this issue:
1. Define the ID column to use a numeric-type data type so that the data types will match up when passing the parameter for the security filter script in Command Manager.
2. Escape out the parameter enclosed in single quotes by using the carat double quotes syntax (^"escaped out parameter^"). For example:
Php
CREATE SECURITY FILTER "security filter test" IN PROJECT "project test" EXPRESSION "region@id=^" '1' ^" ";
2b. Refer to KB9964 After modifying the expression of a filter using the ALTER FILTER statement in Strategy Command Manager 9.x the SQL generated for the report does not include quotes around the elements in the Between operator. for more details on this process
3. Edit the security filters that were created and applied to the users manually within Strategy Developer by deleting the parameters within the qualification(s), re-adding the same values, saving the security filter, and then testing them by running reports. This option appears to work because the security filters appear to match the data type of the attribute form's column when values are manually entered in or when elements are selected. The Command Manager syntax is specific to numeric data types for ID form expressions and text data types for DESC form expressions, unless the parameters are escaped out.
After applying one of these steps to resolve the issue, test to see if the steps taken actually resolved it by running reports with the security filter. If the issue still persists, contact Strategy Technical Support for further assistance troubleshooting this issue.
414363
