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..

Certificate of Appreciation

Connection String expression to save password in the Connection Manager in SSIS

Ever got into a situation where you are not using configuration Files for connection managers and everytime you need to enter the password in the connection manager to run the package? Obviously the package is going to fail if you schedule it as an agent job. A simple turnaround.. Use this expression in the "connection string" Expression box for the connection manager.. "Data Source=yourservername;User ID= youruserID;Password =your password ;Provider=OraOLEDB.Oracle.1" (In my example the provider is oracle) Now as the password in hardcoded in the connection string, when ever the package try's to access the connection manager, the password is available in the connection string expression, and the package will run smoothly even in the agent job.

SQL Server

Pivot In SQL Server 2008

PIVOT is a new feature included in SQL Server 2008 which can be used to rotate rows into columns and columns into rows adn create cross tab query.

This is one very interesting thing in SQL Server 2008. If we want to switch the rows and columns in a table, we can use Pivot.

Let us see this with an example..



Now the way I want to see this is a little different.



If you observe the above result set, I am interchanging the rows and columns in the original result set. This can be achieved by using PIVOT.

Below query will do this for me..

The FROM clause create the values that you want ot see in rows of the newly created columns. we used the for operator to list the values that we wanted to pivot in the 'Element' column.

Hide/UnHide Columns In SSRS Report while rendering

We know how to add filters in a report which filter the result set according to the filter expression.

Now, we will see how to Hide and UnHide the columns in a report by using a filter (Parameter).

For this, we need some Custom coding.

Let us see the report..

Now, I want to see only the columns "Sales OrderID", "OrderDate" and "CustomerID"

For this, we need to create a parameter in the report.

The name of the parameter can be anything, and I am naming it "Column"

we will have to First give all the column names as available values.

Then default the parameter to show all these columns.

Now, in the Code section in report properties, add the following custom Code.

Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean

Return 0 <>

End Function

Now, in the column group section in the report, when you click on the small arrow, shown in figure, you will see advanced Mode. Click on that, and you will see all the hidden Column Groups. One Group for every column in the report.

Click on each group and in the "Hidden" property of the group, add this code..

For 1st Column Group,

=Not Code.IsColumnSelected(Parameters!Column.Value, "SalesOrderID")

For 2nd Column Group,

=Not Code.IsColumnSelected(Parameters!Column.Value, "OrderDate")

And so on....

Now the report will look like this..

When you select the values in the parameter, the report will only show those columns which you selected in the parameter.

Example,