Tuesday, August 16, 2011

Rendering SSRS reports from Command Prompt

Subscriptions is one interesting topic in SSRS. We can create subscriptions that run the report, send them over the email or place the reports in any specific path. But what if the report has a parameter and there are more than 100 values for that parameter? Definitely, creating 100 subscriptions is not the best idea. Now, we can do the same thing using some VB script and render the report through command line.

Below is the VB script that I used to run the report, save the report as excel in a folder, so the user can access it.

I named the Vb script as testreportautomation4.rss and saved it in the temp folder.
now, in the command prompt, I give the path .rss file and the link to my report server.

When the above script is executed, the report will be executed for all the parameter values that were given in the script, and the excels are saved in the path that was given in the 'filename' variable.
For more effective reporting, we can create a link to the databse and get the parameter values from the database instead of hard coding in the script.

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

Tuesday, April 5, 2011

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.

Tuesday, November 23, 2010

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.