Saturday, June 11, 2011

Handling the Multi-Valued paramters using XML

When comes to effective reporting, we always come across the multi-valued parameters and handling them in a stored procedure can be done in many ways.

We can use temp tables or a separate function that splits these comma separated values etc...
There is one other way that we can handle this. That is through XML data type which improves the performance of the query with out having to call another separate execution of a function or a procedure.

Let's see how to do this..

Let's say we have a multivalued parameter named 'String' in our report and the value of that parameter is passed to the stored procedure. The values in that parameter will be comma separated. Now we can declare an XML variable in our stored procedure and assign the value of the 'String' parameter to the newly created XML parameter..



as you see in the picture, what i am doing here is I am taking the ',' as delimiter and creating an XML with those values and then I am filtering my main query using the nodes in the XML that we just created.
In case you need the other process, creating a function that splits the comma separated values and inserts them to a temp table and calling that would be the next better option.

In summary, we are not only reducing the amount of code we are writing, but also the execution time..

Also make sure that the tables you are using in the main stored procedure are properly indexed. Mainly, if you are using a String valued column in a where clause, in order to achieve better performance, that column must have an index.

Show Negative numbers in the report in parenthesis

It is a quite common situation where we need to show negative numbers in some format rather than just showing a '-' sign before them. Most common is the '(' , ')'. This is very easy to achieve.

First, let's say this is the value in the expression box for the field..



Now, close the expression box, and open the properties dialogue box.. Then in the 'general' tab, and in the value field, add the same expression which is in the expression box..



Now in the Format tab, in the Format code field, add this expression..



Now when you run the report, you can find the parenthesis around the negative numbers..