Flexible grouping on data from your DW in Excel 2007

May 15, 2009

I have recently built a data mart containing all the data of budgeting and accounting in SQL Server 2005 and created a Report Model in Report Builder for it. My users were looking for a flexible way to define different grouping on the data, without having to ask for my assistance. Defining new formulas in Report Builder isn’t the most comfortable thing to do (specifically if they get complex and long), so we had to find another way.  

There was a suggestion of creating a view in the database with their definitions for the grouping straight to SQL, but I admit that’s not the solution I’m going to be talking about here. Two months back, Dany Hoter from Microsoft had given us a brilliant lecture about Excel 2007 abilities against a SSAS cube and the new abilities in Excel 2007 in general. It really turned me on to Excel 2007, and I wanted to try and implement a solution with Excel 2007 which we have in the IT department (or even Excel 2003 which my users have).

The main thought was to let the users get all the data they want from a report in Report Builder. They can download the data from a table they constructed in Report Builder down to a CSV file, which is then turned to an Excel file. By the way, I do recommend exporting the data from Report Builder to CSV and not straight to Excel as then you don’t have in your file all the extra styling (which is usually unnecessary and takes some resources). The user can then create the grouping in Excel itself. 

But I was stuck. Because all I could think of was nested “If” expressions which made the whole thing very hard to read and understand. Boris Kogan, our wonderful DBA came with (his usual) genius idea that solved my problem:

Left(Substitute(Concatenate(

If(x=”613″, “1801”),

If(x=”614″,”1804″)

),”FALSE”,””),4)

First of all we do the basic “If” sentences. We concatenate the values we get from the If sentences. If a value didn’t fall into any of the conditions met in the If sentence, then it should get “FALSE”, which we replace by an empty string. In case a value meets more than one condition in the If sentences, then with the Left we take only the first If sentence it met.
After we got the different code groups, I added a sheet to my Excel which held the names for the code groups. Using the usual VLOOKUP, I gave the different group codes their relevant names. After that, I took all my data (the one from the data mart and the groups I defined on it) and created a pivot table on it.

You can implement the above formula in Excel 2007 or Excel 2003 without a problem. Still, there are a few things in Excel 2007 which I really liked:

1. The formula bar in Excel 2007 can be dragged down almost the entire length of the spreadsheet – how brilliant is that?! I really think that makes all your formulas so much more readable and easy to understand. In Excel 2003 you still need to scroll up and down, having to remember what came before and after the current sentence.

2. Excel 2007 enables you to define a table on the data you created. How is that good for you? Well, you can reference a specific column name in the table. Even if that column (p3 for instance) was in the spreadsheet in column A and then moved to column C, that won’t matter for the formula to run properly.
The closest you get to that in Excel 2003 is by creating a list of a column – not the same ease of use as this time you need to define it for each and every column instead of Excel doing it automatically for all the columns of a table.
It also means that when you want to add a column next to the table in Excel 2007, it automatically detects it as a column which belongs to the existing table.

And if you defined a calculation in the first cell of the column, it automatically also applies it to all cells in the column.   

3. Excel 2007 also has very nice design styles for your use, with a very simple UI for you to choose the report layout and some great conditional formatting which makes it all very easy on the eyes.

        

   

Anyways, even if you’re not sold on Excel 2007, I think I really wanted to stress another point. If you have a power – user who wants to have flexible grouping on his data, without having to approach a developer from IT, he can get it through Excel. Excel can this way even elevate the usability of the model you developed in Report Builder (the new source of data for your user). No need for him to contact you, no need for you to define or alter anything in the database – it’s all down to the tips of his fingers as he can define it all in the formula line in Excel. And I think it’s a great way for you to further empower your power users who usually feel pretty good on working on it all by themselves.
So give Excel another look when you develop something in BI.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*