Business Intelligence Geeky/Programming SQLServerPedia Syndication

Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA

In a perfect world, your master data and master data management (MDM) is set up so everyone can see things how they want. Categories of Items, Regions, etc, etc are all defined in your dimension data, and you can create hierarchies, etc that make sense and everyone is happy. But, this is not a perfect world 🙂

Some users want to see “their” items, “their” regions, etc. And they ask and ask for you to add it to the cube, but you have to deny them. Why? Because if you add their named set, you have to add all of them that are requested, that don’t make sense to 99% of the other people using the cube.

You probably could even go about doing some crazy MDX or something in your cube to create the named sets per user, but then you still have to manage it all in your MDX script in your SSAS project.

I blogged a while ago about the OLAP Pivot Table Extensions on CodePlex , which, is an awesome toolkit, but it is geared around Calculated Measures. I downloaded the code and took a gander here and there and it would be pretty easily modified to work with Named Sets (change xlCalculatedMember to xlCalculatedSet , add a radio button on adding a new calc, etc) – but, its in VS2005, and I have VS2008 (2005 BIDS, not C#), and right now I don’t feel like mucking around with that, maybe someone with more time, and more ambition can do it 🙂 – Or maybe when I get some free time I will take a look, but for now here is a solution.

I took the idea outlined at the bottom half of this blog  about named sets and creating them in macros..

Sub AddNamedSet()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")

End Sub

That macro is pretty sweet, you can add a named set to your workbook. But, its pretty static. You have to edit the macro every time you want to add more items, it doesn’t update or remove the sets you might have, etc.

What I whipped up quick was an updated macro that lets you build customizable named sets based on data in another worksheet in your Excel file (you can imaging it coming from other sources – another Excel file, a SQL table, etc, etc). I did this with a cube we use at work, but for this example, I changed it to pull off the AdventureWorks DW SSAS DB, AdventureWorks Cube, (SQL 2005 edition)

First off, here is the macro: 

Text File of Macro: Custom_NamedSets_Macro.txt</a

Excel 2007 Workbook with Macro: Custom_NamedSets.xlsm

Sub AddNamedSet()

    ' data sheet top row must be set name [Set Name] and then item numbers after
    Dim sourceData As Worksheet
    Set sourceData = Worksheets("Data")

    Dim strName As String
    ' get the name of the set, including brackets []
    strName = sourceData.Range("A1").Value

    ' used range is the whole column, rangeVals is the item numbers
    Dim usedRange As Range
    Dim rangeVals As Range
    Dim maxRow As Integer

    Set usedRange = sourceData.usedRange

    maxRow = usedRange.Rows.Count

    ' get the item numbers
    Set rangeVals = sourceData.Range("A2:A" & maxRow)

    Dim strFormula As String
    Dim i As Integer

    ' loop through and build forumla
    strFormula = "'{"

    For i = 1 To rangeVals.Count
       strFormula = strFormula & rangeVals(i, 1) & ","
    Next i

    ' remove last comma and add last curly and tick
    strFormula = Left(strFormula, Len(strFormula) - 1) & "}'"

    ' get pivot table object
    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)

    ' Add a calculated member titled "[MySet]"
    pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet

    ' Add a set to the CubeField object.
    Dim cbf As CubeField
    Set cbf = pvt.CubeFields.AddSet(Name:=strName, Caption:=Replace(Replace(strName, "[", ""), "]", ""))

End Sub

Sub DeleteNamedSets()

    Dim pvt As PivotTable
    Set pvt = Sheet1.PivotTables(1)

    Dim i As Integer

    For i = 1 To pvt.CalculatedMembers.Count
    Next i

End Sub

Now, this was a 15-20 minute VBA hack. It could really use some cleanup, but works. What I did to make it work with Advendture Works was just put the whole “member” string in the data tab of the spreadsheet. In my case at work, I just was using item number and had some of the member string in the macro. But really you can see you could add a named set on each column, or something, you really could make this powerful. And since I added the delete, if they re-run the add, it will recreate the set, so they can modify the data and re-add. It should be expanded on and made a little more robust, but you get the idea. Now, think of how you could use this with your cubes, and how you could get your user’s thinking about ways to use custom named sets!

Business Intelligence SQLServerPedia Syndication

SQL Server Management Studio and Excel – Column Headings on Copy/Paste

Here is a tip about a setting that I think should be on by default, but it isn’t. How many times are you writing T-SQL in SQL Server Management Studio (SSMS) and you need to just copy the results out, paste to Excel and either do more analysis or send off to someone. What happens when you have a result set with 20 columns? 30? 50?

Well, the old way to get the column headings in Excel was to just type them in. Ouch. This gets old realllly fast. But there is a setting in SSMS that most people don’t even know exists, and then once they find out about it, they are like, dang! I wish I would have known about that.

Well, fire up SSMS, Tools->Options, Query Results, SQL Server, Results to Grid. Then check the “Include column headers when copying or saving the results” Hit OK, and the any new query window you have will automatically copy the column headings from your result set with the data!



Business Intelligence SQLServerPedia Syndication

Microsoft BI Client Tools: Creating PivotTables in Excel 2007 that work in Excel 2003

Recently I blogged about easing the transition to Excel 2007 from Excel 2003, by changing your PivotTable design and options.

This post is going to be about creating PivotTables that connect to OLAP cubes (and PivotTables in general) that will work in either client, or sometimes called “Compatibility Mode

If you create a PivotTable in Excel 2007 (a “Version 12” PivotTable) and save the xlsx file to an xls (Excel 2003 or “Version 10” PivotTable), you can still open the spreadsheet in Excel 2003, but you cannot refresh the data – it will still work if you open the .xls in 2007 though.

What that means is that the version 12 PivotTables will not downgrade to version 10 PivotTables and won’t be refreshable in Excel 2003. This presents a problem if you still have people you want to share the PivotTable with that are using Excel 2003, and you are using Excel 2007. What you need to do is create the PivotTables as version 10 PivotTables, but from Excel 2007, and this is how you do that.

The easiest way to create the version 10 PT in Excel 2007 is create it in compatibility mode. To do that, open a new Excel 2007 workbook and save it as Excel 97-2003. Open that file in Excel 2007 again and you will be in compatibility mode. Once you create a PivotTable and save it, you can then share it with anyone that just has Excel 2003 and they can open it and refresh it. Simple, huh?

Business Intelligence SQLServerPedia Syndication

Microsoft BI Client Tools: Easing the Transition from Excel 2003 to Excel 2007 – PivotTable Layout and Design

Being that we are now in the year 2009, you would think that most people are currently using or have been using the Office 2007 suite for a couple of years now. The truth is that there are many businesses “in the wild” that are still standardizing on the Office 2003 suite.

Why? Well there are a variety of reasons. Some places might cite cost to upgrade (as in dollars), where others might cite backwards compatibility with legacy applications. Some others might just say that end users “won’t understand” the new ribbon interface, and they might think that the pain and time of training and helpdesk support outweighs the benefits of using Office 2007.

Over the past year I have been in three different places and they all are standardized on Office 2003, and it puzzles me that it isn’t a harder push to upgrade. The benefits of Office 2007 are huge, once you get used to the new interface, and I could go into the benefits but that is probably another blog post, but Outlook 2007 a GTD (getting things done) booster.

As a Business Intelligence guy, it really works for me if every user is on the same client tool, same interface, some quirks and same training, etc. Excel 2007 adds many things when using cubes and pivot tables, and especially with SQL Server Analysis Services 2005, it is a no brainer to use Excel 2007 with SSAS 2005.

In trying to get users of cubes using 2007, there are a few things that I have encountered that can make the transition easier, and today I am going to talk about PivotTable layout and design.

Users of Excel 2003 are used to a pivot table that is laid out in a tabular form, and no subtotals, and maybe grand totals or not. When they use Excel 2007 by default, the are shell shocked by the default pivot table layout and get confused and maybe even sometimes “scared” of what they have gotten into with 2007.

Well, the thing is, it is really easy to get your pivot table to look like a 2003 pivot table in 2007. When you insert a pivot table into Excel, you see this kind of layout.

You can see under the “Pivot Table” tools there is an “Options” and a “Design” tab. Click on the “Design” tab before you set up any dimensions or measures or filters on you pivot table.

These settings on the design tab you can set how you want your Pivot Table to look. To make it “2003 style”, on Subtotals, pick “Do not show subtotals”, On Report Layout, choose “Show in tabular form”. If you don’t to see Grand Totals, then you can turn those off as well. And you can fiddle with the various design options as well.

One thing not on this tab is changing the setting for the +/- on the rows. On the Pivot Table options tab, under the Pivot Table name way on the right, there is an options button.

Here you can tweak other various settings, but you can uncheck “Display expand/collapse buttons” to remove the +/-. As you can see, you can also make the Pivot Table a “classic pivottable” if you really want.

Moving from Excel 2003 to Excel 2007, at least in the Pivot Table and OLAP cube browsing area, shouldn’t be a hard move, and you shouldn’t be scared of it, as you can see you can make your pivot tables look like 2003, or go wild and shift to the new 2007 style.


Programmatically creating Excel (XLS) Files, as XML files – Things to Keep In Mind

I worked on a small project that required to export data to Excel. The spreadsheets needed to be formatted very precisely, and the best way to do this is with the XML format of an excel file. But I have found some gotchas throughout the project, which will cause the .xls files to not load.

First, since it is XML, you need to make sure you handle some special XML characters..

quotes “ should be &quot;

ampersands & should be &amp;

apostrophes ‘ should be &apos;

less than < should be &lt;

greater than > should be &gt;

Now, if you make a function or something in code to do these operations, there is one thing to make sure of. Replace the & first. If you do it last, like the quick and dirty function I wrote first, then you could end up replacing a less than with &lt; and then replacing that new ampersand with &amp; so you end up with &amp;lt; – whoops …

Another few little gotchas.. Excel tab names have to be distinct. If you have duplicates, the spreadsheet will still create just fine, you just wont be able to open it. Another thing with the tabs, you can have a division sign in them – / – same thing will happen.

Business Intelligence Geeky/Programming Ramblings

Excel 2003 vs Excel 2007

It is the year 2008, we are half way through. Excel 2003 is 5 years old. Stop using it please.

Why? Excel 2003 has the old “limits” – 65,000 rows, 256 columns, memory limits etc. Excel 2007 on the other hand, 1 million row limit, etc etc. That coupled with the way pivot tables work in Excel 2003 compared to 2007, and the SQL Server Analysis Services features with 2007, it is a no brainer to go to 2007.

Companies will say – “But we can’t move all our users to 2007, we can’t afford it” – well, think about just moving your power users. The users that have huge spreadsheet extracts, etc. It is worth it. They can save files in 2003 format if they need to share a smaller file or something, and the 2003 users can install a 2007 viewer.

Other options for huge spreadsheets and extracts are… Access – which your users need training on, or need to be able to adapt to, or another options is SSAS and Cubes, which you need executive buy in, and the infrastructure, and the training to get your users up to speed, and by that time you will want Excel 2007 to connect to the cubes, so…

just start using Excel 2007 – 2009 will be here soon!

Geeky/Programming SQLServerPedia Syndication

Importing Data From Excel Gotcha – The Top 8 Row Rule

I have ran across this a couple times now, I actually had a blog post draft written up for this but never got around to finishing it, well this week I ran into the issue again, so here it is.

Importing data from Excel (using whatever – C#, VB.NET, SSIS, Access, etc) – you have data scattered throughout, and when you go to import it, some of the values are not getting read, they are just missing – empty, or not correct. Something goofy is happening and you cannot figure it out.

Excel tries to figure out the data type of a column, or if there is any data at all in a column by looking at the Top 8 Rows. It will ignore the header row. But it looks at these 8 rows and tries to be smart about it, and this can really screw you up if you are importing data.

Here is my example.

Year | Value1 | Value2
1998 |             |   20  
1999 |             |   
2000 |             |   15
2001 |             |   20  
2002 |             |   
2003 |             |   15
2005 |             |   20  
2006 |             |   
2007 |     55     |   15

Now this might not work if you actually try it, but just bear with me for the examples sake. If you write a function in .NET to import this XLS sheet, the 55 in Value1 column will never get read. Why? Because the first 8 rows for Value1 are empty, and it thinks it is an empty column, just wacked. If you put a 0 in the 1998 row for Value1, then the 55 will get read. Since Value2 column has values in the first rows, it works just fine.

How can you get around this? Well, if you somehow read your XLS file without using the JET engine, then you are probably OK, also, you can tweak a registry setting to make excel look at more rows, say, 1000, which will usually get you by.

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet<version of jet>EnginesExcel

Where <version of jet> is usually 4.0

Change the TypeGuessRows value to something larger than 8 and then you should be good to go.

Just another Microsoft head scratchier…had me stumped for about an hour! 🙂

Technorati tags: , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS Exporting Report to Excel – Keep Formatting on Export Round 2

Earlier I blogged about exporting SSRS (SQL Server Reporting Services) Reports to Excel and keeping formatting. Well, my previous solution works, but not always (doh!)

I found in a new report that I created that the formatting was taking on all columns. The solution? Wrap those expressions in CDec() function and it works. It was for some currency and percentage formatted columns.

Nothing can ever be easy!

Technorati tags: , , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSRS Exporting Report to Excel – Keep Formatting on Export

When creating Reporting Services (SSRS) reports, I usually use FormatCurrency() and FormatPercent() around values to make the report look good. Today I found out that when you use those, and export to Excel, you lose the ability to sum the values, because everything is exported as text.

The right way (or at least the way that I have found that works) is this. Remove all the FormatCurrency() and FormatPercent() functions. On all your currency fields, highlight them, then in the properties area, there is a “Format” property. I set that to C2 (C for Currency, 2 for the number of digits after the decimal, so if you wanted zero, it would be C0). Again for Percentages, same thing, highlight all the fields, and set “Format” to P2 (P for Percentage, 2 for the number of digits after the decimal).

Technorati tags: , , , , ,