Not too often, someone asks for a strange request like wanting to pivot on every single value inside a table. No aggregations at all, just a listing of the columns as shown in the screenshot below. This happened for a colleague of mine recently. As such, a few of us quickly searched for some solution that could assist him and after many failed attempts, I came up with the below solution. Screenshot Step 1: Setting up the Temp Table create table #McValues ( value varchar ( 5 )) insert into #McVal...
Read More
A colleague of mine received an Excel file that he needed to load into a table for future lookup values. There were some crazy columns containing multiple values inside one cell, which he needed to split up into multiple columns. Seems pretty straight forward so far right? And a pretty common occurrence when working on Business Intelligence solutions for clients, because the data can come from many different sources and could include Excel files. After importing the spreadsheet into SQL Server, ...
Using the Report Items Collection in Reporting Services Sometimes, you may want to do calculations or use some value that has already been used in another object. By using the report items collection, you can get the value from whatever object you want and use it in a calculation. For example, suppose you had a report that retrieved the total sales by day from the adventureworks database. You could quickly add an expression like the one below to return some form of label to let you know that it ...
Creating a Reporting Services Template Many companies like to have a consistent look and feel across their applications. Some even make them part of the standards. As such, one may want to create a reporting services template that allows them to start off the report with objects or properties set accordingly. This saves a ton of time and gives that consistent look and feel. Creating a template that can be reused is very simple. All you have to do is create a new report, add your appropriate sett...
Sometimes one may need to see a running number that represents a quantity for a period of time. For example, let’s say you have a month to date report that breaks out the quantity of units sold on a daily basis. And you get a request from the Sales Manager stating that she wants a monthly report that shows sales quantity distribution by day, with an extra column that shows a running value for that month. At the beginning of the month, you will have 0 units sold right (unless you’re adding in yea...
This past weekend was SQL Saturday 38 in Jacksonville Florida. It was a great event and the turnout was incredible. My presentation was first thing in the morning and as such, I enjoyed the opportunity to pass on some of my experiences with SSRS. I had a ton of material and will need to split it up into multiple presentations next time, but I believe many attendees had plenty of take aways. I really wanted to show the R2 features since it was released last week. As such, I stayed up until 5 am o...
If you’re anything like me, you value every second of your day and you constantly try to find new ways to maximize your time. And since I find myself on the road between here and there; and there and here quite a bit lately, I really need to be as productive as possible when I’m away. That way, when I get home to my wife and two children, I actually get to spend time with them before heading back out on the road. Don’t get me wrong…I love to work, and those of you who know me can validate that. ...