OpenDocument String to Stored Procedure Universe

[dustinkramer]

To invoke reports from our application we use the OpenDocument interface. Some report parameters allow for multiple selections. In the OpenDocument url, multiple values for a parameter are specified by separating each value with a semicolon, and the parameter is prefixed with lsM (to specify that the parameter supports multiple values). The problem occurs with some of our WebI reports that use a Stored Procedure universe (sp_unv). Our stored procedures use a varchar stored procedure parameter to receive the multiple values, a single string that contains the different values separated by semicolons. However, it seems SAP BO 4.3 is unable to map the multiple values to a stored procedure parameter of nvarchar type. 

While we can change the parameter prefix to lsS, and pass all the values to the stored procedure as a single string, the problem is that we use the same variable in other native WebI queries too. We can modify our application code to specify variables that support multiple values twice, and append "AsStr" to the parameter name. Then modify the universe to map stored procedures parameters to the "AsStr" prompt variable. For example, if ProviderID supports multiple values, we can append two parameters to the OpenDocument url, one called lsMProviderID=1;2;3 and another one called lsSProviderIDAsStr=1;2;3. The stored procedure parameter in the stored proc universe would then be changed from ProviderID to ProviderIDAsStr.

We will contact SAP support to see if there is another way to handle this, maybe there is a backwards compatible setting that would behave the same as SAP 4.2, and pass multiple values to the stored procedure as a string (nvarchar)? We did not seem to have this issue with version 4.2. By passing the values separated by ";" it would pass the list to one query, and send it as a string to the stored procedure universe.