Quick Tips & Tricks « PowerPivotPro

PowerPivot field list and Excel field list at same time!

June 26, 2010

-Posted by Rob Collie

OK, ignore my last post, that was just silly.

Yesterday one of my fine colleagues at Pivtostream mentioned that he was seeing both field lists simultaneously. Then followed an exchange where I said “BS,” he said “bet me some cash, oh PowerPivot boy” and, well, he was right.

One should be careful never to wager against one’s own boss.

Anyway, in beta releases of the addin, it was IMPOSSIBLE to bring up the Excel field list without disabling the addin. Apparently, the team decided at the last minute that allowing the user to bring up the Excel field list could compensate for the lack of certain features.

It’s a brilliant move on their part. So here it is, both field lists, living together in harmony:

You don’t have to disable anything. Just go to the Options tab on the ribbon when you have a pivot selected, and click the field list button there:

Implications Galore!

OK, so… you can do the defer layout update trick which is excellent for long-running queries, without turning off the addin, that’s huge.

But wait, there’s more :)

You can also adjust the layout of your measures. Try dragging the little Sigma “Values” capsule around the Excel field list (shown here at right) and you will see what I mean – move it to Row Labels for instance.

This also comes in handy when you have other fields on Column Labels and want to change whether Measures are nested inside of those fields or vice versa.

But the last trick that this side by side field list stuff enables is truly my favorite…

Batch Creation of Measures Without Refresh!

Follow along, this is fantastic.

Step one: turn on defer layout update:

Two, go create a measure:

Three, ignore this error you get when you enter the formula and click OK:

Four, observe that the measure was created anyway:

This changes my life in rather significant ways.

9 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Performance | Permalink
Posted by powerpivotpro

Quick tip: disable addin for rapid layout

June 23, 2010

-Posted by Rob Collie

UPDATE: It turns out that all of this is unnecessary, there is no longer a need to disable the addin to get these benefits. (I was still stuck on what was possible in the Betas). See the latest post on this topic for details.

If you’re like me and you work with large data sets and/or complex measures all the time, you may be annoyed by the time it takes to layout 6-10 fields on a pivot – a handful of measures, a field or two on rows, a couple of slicers… even with the performance of PowerPivot, you can be burning a reasonable amount of time in the click-and-wait game.

I have a trick for you.

First, disable the PowerPivot addin. Yep, you heard right. On the File tab, go to Options:

Then select Addins. At the bottom of that screen, open the dropdown for Manage, and pick COM Addins from the list:

And then in the dialog that comes up, uncheck the PowerPivot addin:

Now go to the Options tab on the PivotTable ribbon and enable the Field List:

OK, now you see the native, built-in Excel field list. If you are familiar with normal Excel pivottables, this will appear foreign to you, because this is the “OLAP” field list – designed strictly for use with OLAP db’s like Analysis Services, which PowerPivot happens to be under the hood.

Anyway, at the bottom of that field list, you will see the “Defer Layout Update” checkbox. Check that, then you can make a bunch of changes to the layout of the pivot WITHOUT them refreshing the pivot. Click Update and all of your changes will run in a single batch while you go get a fresh cup of coffee:

Cool huh? A few things to note:

You can’t create measures without the addin enabled.
You also don’t get the fancy slicer auto-layout.
The field checkboxes are organized very differently, split into measures and non-measures AND by table – you will see what I mean
There are a few “dummy” measures visible in this field list that are hidden in the addin

OK… I should also mention that we might have had this feature already built-in to the PowerPivot v1 field list if it weren’t for me :) It kept coming up during development – “should we build that defer layout feature in?” And I kept saying “no, not yet.”

Why would I do such a thing, you ask? Two good reasons. One, we were running WAY low on time and resources. By NOT doing that feature, the team was able to do several other important things that today we’d be complaining about instead.

Two, I knew that if we added that feature, as we worked on PowerPivot internally, we would all turn it on whenever we hit something that was slow. As a result, we’d not feel the pain of performance issues as acutely as we should, and not as many things would be identified and fixed. So, like Cortez burning his ships, I wanted to deprive us internally of this crutch.

Of course, now that I no longer work at MS, and I find myself in a job where I crunch hundreds of millions of rows daily, irony has come home to roost :) Just kidding really – I still think it was a good v1 decision. I’m positive that engine query performance is much better for it, and it will be easy for the PowerPivot team to add this feature in v2.

3 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Performance | Permalink
Posted by powerpivotpro

Calling a Stored procedure in PowerPivot

June 9, 2010

While building a new PowerPivot workbook i wanted to use Time Intelligent functions. One of the golden rules of PowerPivot time intelligent functions is to create a seperate related time table. I have created a stored procedure which creates a new table in PowerPivot. The important thing to do is when you want to call a stored procedure withing PowerPivot is set the “SET NOCOUNT OFF” at the beginning of your sproc. This will make sure your stored procedure will return only one dataset.

You can use this code to create the range:

/*
Get a range of dates in the range @fromdate until @todate
*/
create procedure getDateRange
@fromdate datetime, @todate datetime
as
begin
SET NOCOUNT OFF
/*Declare @todate datetime, @fromdate datetime
select @fromdate = '2005-01-01'
select @todate = '2008-12-31'*/

;With DateSequence( [Date] ) as
(
                Select @fromdate as [Date]
                               union all
                Select dateadd(day, 1, [Date])
                               from DateSequence
                               where Date < @todate
)

select
                               YEAR([date]) as Year,
                               Month([date]) as Month,
                               DAY([DATE]) as Day,
                               [date] as Date,
                               CASE Month([date])
                                                WHEN 1 THEN 'Jan'
                                               WHEN 2 THEN 'Feb'
                                               WHEN 3 THEN 'Mar'
                                               WHEN 4 THEN 'Apr'
                                               WHEN 5 THEN 'May'
                                               WHEN 6 THEN 'Jun'
                                               WHEN 7 THEN 'Jul'
                                               WHEN 8 THEN 'Aug'
                                               WHEN 9 THEN 'Sep'
                                               WHEN 10 THEN 'Okt'
                                               WHEN 11 THEN 'Nov'
                                               WHEN 12 THEN 'Dec'
                               END as [MonthShort],
                               DATENAME(MONTH,[Date]) as [MonthName],
                               max(DATEPART(DAYOFYEAR,[Date])) as DayInYear,
                               DATEPART( wk, [date])  as Weeknumber
from DateSequence
group by YEAR([date]),Month([date]),DATENAME(MONTH,[Date]), [date]
option (MaxRecursion 10000)

end
GO

Importing this

will give me:

Great tip got from Dave Wickert :)

7 Comments | Quick Tips & Tricks | Permalink
Posted by Kasper de Jonge

Review new PowerPivot workbooks on SharePoint using approval workflows

June 1, 2010

Posted by Kasper de Jonge

Now something not really about the PowerPivot engine but something i think will be very nice to set up on yourSharePoint environment.

At the excellent linkedin discussion on PowerPivot, someone suggested that we might make review of a PowerPivot file possible. I answered we can because the PowerPivot gallery is a document library and we can manage approval workflows on this gallery. But i also wanted to check it out how I can do this.

In this blog post i’ll show you how to configure a PowerPivot gallery so that all new PowerPivot workbooks published to a PowerPivot gallery are reviewed by IT. I used a blog post i found which explains howto configure approval workflows step by step.

We start at our PowerPivot Gallery:

To enable workflows we go to Workflow settings in the library tools:

Select the current library and click add new workflow, we now get to our workflow settings:

We want to use the Approcal – SharePoint 2010 workflow, set a appropriate name ”PowerPivot approval”, we want a new task to be created at the reviewers task pane for every document that is added to the library. Next, we define the history list for the workflow. History lists are special logs which monitor the execution of the workflow. At each step of the workflow, messages at written to the log upon execution of the step. I want the workflow only to be executed when i upload a new file to the gallery:

We now can set up who the approvers are responsible of reviewing the PowerPivot workbooks, the settings are pretty straightforward.

Assign approvers, enter a request they will see at the workflow. The other settings aren’t necessary to complete the workflow.

We now can save our workflow and it will be in effect immediately.

When i now upload a new document to my PowerPivot gallery, we can see that the PowerPivot approval is in progress:

When i would look at my tasks lists within the same site collection i would see my new PowerPivot sheet is awaiting my review:

And we can open this workflow and comment on it, we can also open the document to look inside it:

We just approve my workbook now. When we check back at the gallery to see our workbook is approved:

So using workflow we can make an additional step in our Managed self service BI, and it is very easy to set up.

Leave a Comment » | Non-Football Examples, Quick Tips & Tricks | Permalink
Posted by Kasper de Jonge

Up for air… briefly

May 27, 2010

“A little less blogger nation, a little more action please.”
-The King

Folks, I’ve been up to my eyeballs in PowerPivot these days. Which ironically has resulted in less writing about PowerPivot.

Quite simply, the release of PowerPivot v1 to MSDN was like a huge starting gun going off. Suddenly, all that theory became “ok, let’s put it into practice… on 10 different fronts.”

I’ve been on so many exciting phone calls and web meetings lately. Lots of very cool customer and partner opportunities.

Oh, and tons of “real” work, too. Which has left less time for blogging.

But I do love the nifty community we’ve got going here and will never neglect it for long stretches. So, while I burn the candle at all three ends, I thought I’d share some quick observations and experiences, rapid-fire style:

Microblogging has its place – when I don’t have time for a full blog post, I tend to drop something into my Twitter account. If you’re not a Twitter person, you can see what I mean here. And if you are a “tweeter",” well, you can click there, too :)
I fit 200 Million rows, a 63 GB SQL database, into a 1.3 GB PowerPivot Workbook. Yeah, no kidding. 50x compression. And some of the queries against the resulting workbook ran in less than 3 seconds.
Another db with 120 Million rows exceeded the 2 GB file size limit – different data set. See? There really is no set rule of thumb for how much compression to expect.
Removing irrelevant columns can dramatically shrink your file – this is well documented elsewhere, but removing columns from your data before importing them can have a HUGE impact on file size. It can be surprisingly non-linear, too – remove one column out of ten and file shrinks by 50%.
Seems like deleting columns after the fact does NOT shrink the file by as much – I have not verified this scientifically. But I *seem* to get better file size savings by never importing columns in the first place, as opposed to deleting them post-import.
Early response to PivotStream’s new offering on the PowerPivot platform has been VERY positive – yesterday we demo’d a new set of interactive dashboards, built on PowerPivot, to an existing customer who currently is served by one of our other technologies. It got a big thumbs up, we are moving ahead with a full conversion. And another customer is likely just a few days away from making a switch from their existing on-premise solution, too.
In general, MS might not need viral adoption – when we were building the product we often talked about how bottom-up adoption by Excel users was crucial, that top-down adoption would be slow. So far, I am seeing zero indication of that. Everywhere I go, everywhere I demo PowerPivot, the organization I’m talking to does a collective “yes, please.” Maybe MS could have charged for the client addin after all. Shhh, don’t tell them.
I’m seeing more willingness than in the past to embrace the new Office wave – normally, the new wave of Office products takes years to seep into corporations. That will still be true in a lot of places. But about 2/3 of the places I go, I find eager willingness to roll out the new products, even if only on a limited, departmental basis, in order to get PowerPivot.
Any serious work on PowerPivot measures requires Notepad++ – seriously, I don’t know how I ever got by without it.
Running a PowerPivot server farm is hard work – I have newfound respect for IT pros everywhere. Don’t go it alone, folks. We’ve brought some heavyweight talents on board to help out with SharePoint, farm architecture, and Integration Services. I can’t imagine where we’d be without them.

-rob out

5 Comments | Observations, Trends, and Wild Speculation, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Automating the “Site Visitors” XL Report

May 6, 2010

“What’s in it for me?”

-Average Excel Pro

OK, the average Excel pro has not caught on yet. Heck, most of them haven’t even heard of PowerPivot yet – it did just release this week after all.

Let’s say you don’t have much need for handling large data volumes, your organization isn’t large enough to worry about spreadsheet robustness/business intelligence/one version of the truth, and you haven’t yet seen what DAX can do for you in pivots.

Or maybe you get some of that, but don’t really understand this whole SharePoint thing.

If you are in either of those boats, here’s an example from my work this weekend.

My First Production, Scheduled SharePoint PowerPivot Report!

You know that chart of PowerPivot visitors by industry that I posted earlier this week? For months now, I’ve been logging that stuff in an Excel workbook:

Site Visitors in Excel

Over time, it’s grown to 500+ rows. No big deal.

Well, OK, there are a few problems.

Workbook editing on many machines. I have a desktop, a netbook, and two laptops. Guess which one always has the latest version of the workbook? Yep, that’s right, whichever computer I am NOT using at the moment I want to edit the file. So I get workbooks on every machine. That leads to merge fun, duplicate records, and sometimes even lost records. It also makes the process not a lot of fun, so I tend to neglect it at times.
No time/date stamping. I’m too lazy to enter a date every time, so the best approximation of “date first logged” is the sort order of the spreadsheet. Not so precise, especially once I sort and accidentally save :)
Sharing with others. I like to keep my colleagues at PivotStream apprised of the nature of the visitors I get on my sites. We’re always evaluating different technologies and I want to make sure they are constantly reminded of what a good decision it’s been to adopt PowerPivot :) And, well, emailing workbooks around is also clunky. (I won’t bore you with why it’s clunky, but ask me for details and I will oblige.)

Ideally, what I want is a central, unified copy of the list, that is also centrally editable, constantly timestamped, backed up, as easy to view as a web page… and all with the editing ease of Excel. No webforms, please.

Tall order. But like Inigo Montoya, I may know something you don’t know.

SharePoint List With DataSheet View!

Have you ever seen a SharePoint list? If you’ve visited the FAQ, you have, because, well, that’s just a vanilla SharePoint list.

Well, I transferred the list from Excel to a SharePoint list on our new PivotStream (internal) SharePoint 2010 farm. With a few clicks in the web UI, I get the list looking quite spiffy:

Visitors List in SharePoint HTML View

But how did I get the data out of Excel and into the list? That’s where DataSheet View comes in. Here’s the SharePoint ribbon button you click to switch into DataSheet View, at right:

Once you click that button, you get an editing experience that is VERY similar to Excel or Access, as seen below:

It’s no accident that it looks a lot like Excel or Access really, since this view was first built by a mixture of engineers from both of those teams about six years ago. This is an ActiveX control that is installed with Office client, and it’s one of the hidden little gems of Office / SharePoint integration.

Anyway, using this view, I was able to do a bulk copy/paste of all 500+ rows from Excel, directly into the grid, and then they just streamed up to SharePoint in the background. Took less than 30 seconds.

Now, when I want to add a new item, I come to this view, and start entering data as if I were in Excel. I even get autocomplete :) SharePoint adds a date/time stamp behind the scenes.

Now it Gets Fun: Importing the List into PowerPivot

OK, great, now I have a place to edit my list that suits all of my requirements. No more multi-machine, multi-workbook merge hassle.

But now that my list is stored on SharePoint, naturally, I want to show some charts in SharePoint!

Here’s another feature you may not have heard of: every SharePoint 2010 list is now also a valid Data Feed source. Why should you care? Because PowerPivot LOVES Data Feeds.

Time to click another button in the SharePoint ribbon, “Export as Data Feed,” circled in green below:

I get the usual series of helpful security dialogs. Yay, I feel safe! Doubly safe, since there are two dialogs, heh heh.

Right now, PowerPivot is the only client app (at least on my computers) that is registered to handle data feeds, so after that, I get a dialog from the PowerPivot addin that asks me which workbook I’d like to add this data into, followed by the import wizard:

I can also preview and filter the incoming data in the import wizard if I want :) But I want all records, so I just click Finish, and the data gets imported into the desired XLSX, as data in the PowerPivot window:

Building the chart at that point is of course pretty trivial.

Note also that I can refresh the source data by clicking the PowerPivot Refresh button at any time, since PowerPivot remembers where the data feed is located up on SharePoint. So from time to time I can update my report to reflect the latest visitor statistics.

But why do this manually? I’m too lazy for that.

Upload to SharePoint

Since our SharePoint 2010 farm is actually MOSS Enterprise with PowerPivot for SharePoint installed, I can upload my XLSX now and have it render on the server instead via Excel Services.

Here is the chart from the workbook, rendering as an embedded web part on our internal team SharePoint site’s home page:

As I said though, I don’t want that chart to be stale all the time. I don’t want to have to upload a new file every time I update the data.

With PowerPivot, I can schedule the refresh. Go to the report gallery view where the workbook was uploaded, and click the highlighted little calendar icon (circled in orange):

Now I get this page that lets me schedule data refresh to happen automatically, like for instance, once a night:

The first time you do this with a new workbook, I also recommend setting the “Also refresh as soon as possible” checkbox so you can verify that it worked:

Summary

So, there you have it. My simple list didn’t have large data volumes, multiple tables, complex calc requirements, or any of those things we typically talk about when we discuss the strengths of PowerPivot.

But even so, the combination of PowerPivot and SharePoint turned out to be the best solution to my 100% real-life problem. It even took me longer to write this blog post than to set it up :)

5 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Data Refresh Data Feeds SharePoint Lists | Permalink
Posted by powerpivotpro

DAX.xml file from Colin

April 5, 2010

Rather than do the email dance forever, Colin has provided a copy of his DAX.xml file (for Notepad++ autocomplete support, as described in the previous post) in the PowerPivotFAQ samples library:

http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx

Leave a Comment » | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX Notepad++ | Permalink
Posted by powerpivotpro

Notepad++ and DAX – Guest Post from Colin Banfield

April 5, 2010

“Excuse me, sir, you don’t need 20 men, you need ~~Miller~~ Banfield.
He’s an expert with ~~explosives~~ Excel, sir. He’s probably the best in England.”

That’s right folks, after prowling the comment threads for months now as one of the most insightful contributors, Colin Banfield of BIExcel.com is taking the plunge and sharing a full guest post with us. This is indeed a very positive development for us all :)

Today, Colin is giving us more info on using Notepad++ to write PowerPivot formulas. Take it away Colin :)

Using Notepad++ as an editor for creating, reading, and troubleshooting DAX formulas

Rob recently wrote an article about the difficulty he experiences editing complex formulas in PowerPivot, even though he helped to create editor used in the Measure Settings dialog box. You can read Rob’s article here http://powerpivotpro.com/2010/03/27/two-observations/. In the article, Rob noted the following as shortcomings of PowerPivot’s built-in editor:

· Can’t indent formulas
· Can’t save formulas
· No find and replace option
· Can’t change the font size

To address these issues, Rob has been using Notepad as his PowerPivot formula editor. In the above-mentioned article, Rob solicited opinions on alternative text editors. Some folks, including me, suggested Notepad++. Notepad++ is a free text editor that might be viewed as Notepad on steroids. However, the point is not that Notepad++ has more features than Notepad – it’s that you can customize the program to work specifically with DAX formulas. The purpose of this article is to explain how the customization is done, and the many benefits you gain from the effort.

You can download the latest version of Notepad++ from here http://notepad-plus.sourceforge.net/uk/site.htm. During the installation, I strongly suggest that you don’t check the option to bypass using the %APPDATA% folder for storing Notepad++ settings (the option is unchecked by default is used mainly for portable installations). If you have already installed the program and checked the option during installation, I recommend that you completely uninstall the program (including everything under the Notepad++ installation folder) and start over.

Figure 1 shows the Notepad++ main window. The file manager window on the left is a plug-in that you can install through the Plugins menu. The plug-in is called Explorer. The built-in file manager is useful when you want to quickly retrieve saved formula files.

Figure 1 – Notepad++ main window

The first step in customizing Notepad++ is to create a custom language for DAX. The purpose of a custom language is to provide special syntax highlighting for various elements of the language. Choose ViewàUser -Defined Dialogue…The User-Dialog box appears as shown in figure 2.

Figure 2 – User-Defined dialog box

Click Save As… and enter “DAX” (without the quotes) in the Name text box. After you click OK, you will notice that extension (Ext) box appears at the top right of the User-Defined dialog box. Type an extension in this box (txt, dax, foobar, whatever), and save your formula files with this extension. This ensures that when you subsequently open the file, Notepad++ applies the custom language for editing the file. You can associate more than one extension with a custom language by separating the extension names with a space in the Ext box.

After you enter an extension, you can explore the various tabs in the dialog box. My personal setup is as follows (it has changed over time):

· Folder and Default tab – As shown in figure 3.

Figure 3 – Folder& Default tab

The folder open and close keywords are used expand/collapse a block of code. This might be useful for delineating parts of a DAX formula that can be used as intermediate measures in a PivotTable (see figure 7).

· Keyword Lists tab – See figure 4.

Figure 4 – Keyword Lists tab

In the “1st Group” list box, I pasted an alphabetized list of DAX functions.

· Comment and Number tab – See figure 5.

Figure 5 – Comment & Number tab

· Operators tab – See figure 6.

Figure 6 – Operators tab

Naturally, each user will personalize these dialogs to suit his or her taste. Notepad++ saves the configuration after you exit the dialog box. To use your custom language, you must choose it from the Language menu. Custom languages appear in a separate section at the bottom of the menu. Currently (version 5.6.8 at the time of this writing), there is no way to make your custom language the default language. You must select the custom language every time you open Notepad++ and every time you open a new file. This is a very annoying and stupid limitation. If you don’t assign an associated file extension for the custom language, the syntax highlighting for the default built-in language will be applied after you open the file. However, there are many user requests to make a custom language the default language, so it’s possible that the feature will appear in a future version of the product (hopefully, very soon).

Figure 7 shows an example of using your new DAX language to edit a DAX formula. Notice the vast improvements in readability over editing the formula in the PowerPivot Measure Settings dialog box, or in Notepad. The bottom left of the windows displays the language in use.

Figure 7 – Example DAX formula with syntax highlighting, bookmarks (blue blobs) & folding blocks

Pretty cool, no? However, we’re just getting started. The next step in customizing the language is adding function auto-complete, function parameter tooltips and function description tooltips. You heard that right. It takes some time to create the text for the auto-complete XML file, which must have the same name as the language (is this case the name of the auto-complete file would be DAX.xml). You must place DAX.xml in the Notepad++\plugins\APIs folder, and the turn on the appropriate Auto-Completion options in the Preferences dialog box. These options are shown in figure 8.

Figure 8 – Auto-Completion options

I’ve created a DAX.xml file, and anyone interested in using it can download it here:
http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx

The next couple of figures illustrate how function auto-complete and tooltips work.

Figure 9 – Function auto-complete

Figure 10 – Function parameter and description tooltip

The only functionality you lose from PowerPivot’s built-in editor is the very useful table and field names auto-complete. You can use the PowerPivot editor to auto-complete table and field names that you paste into Notepad++. In many cases, like that shown in figure 7, you end up reusing the same fields in different parts of the formula – which is then a simple cut and paste operation.

I encourage you to explore the many other features of Notepad++ not covered in this article. Other features I find useful include:

· FileàLoad Session & File Save Session. Because Notepad++ uses a multiple document interface (each document appears in a separate tab), you can save all the formula files associated with a project as a session.

· Bookmarks. You can toggle a bookmark on and off in several ways, for example: 1) click the right-side of the line number in the margin, 2) select a line and enter Ctrl+F2. You can also bookmark all lines containing specific text. Bookmarks are useful if you want to include folding keywords (as shown in figure 7), or comments inside the DAX formula. After you bookmark the folding keyword lines and comment lines, you can delete these lines by choosing SearchàDelete Bookmarked Lines. After you delete the bookmarked lines, you can paste the formula into PowerPivot.

As a final note, I’d like to say that Notepad++ has helped tremendously in managing my calculated measures. With complex formulas, I tend to create many intermediate formulas so that I can see the calculated results in the PivotTable. I don’t want to expose these intermediate calculations to the end user, so before deploying the PowerPivot model, I have to amalgamate the intermediate formulas into a single formula. It’s very unfortunate that PowerPivot doesn’t allow you to hide calculated measures.

15 Comments | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX Notepad++ | Permalink
Posted by powerpivotpro

PowerPivot and Sparklines .. the easy way

March 30, 2010

Another guest post from Kasper… our man in Amsterdam!

Kasper Vega Crunching Numbers on His Laptop

Today we have another guest post from Kasper de Jonge, our man in Amsterdam! He’s been experimenting with Sparklines, a feature that’s new in Excel 2010, and integrating it into his PowerPivot work.

Take it away Kasper…

In Excel 2010 MS introduced Sparklines, as described on the Excel blog:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence. Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing.

Too bad the sparklines aren’t really tightly integrated with PowerPivot as are other charts. In this post i’ll describe an easy way to implement sparklines on PowerPivot data, with use of slicers.

Of course we start by loading data into PowerPivot, first we create a workbook in PowerPivot:

To gain more insight in sales per country per month I would like to use the sparklines per country. Lets start by adding data using a pivottable in a new sheet

Make sure you connect this Pivottable to the slicers from my other sheet by using the connect slicers option as posted in this blog post.

Ok next we are going to add the country’s to the original workbook. Select the country’s and copy them. Paste hem into the original sheet:

Now for the sparklines, go to insert and click Line. we now have to select the source for the sparklines, in our case this is the pivottable in our newly created workbook, and select all data in the months:

next we have to select where we want to put the sparklines:

This will result in the sparklines being showed:

We want them to be a little wider so we select the cell with the sparkline and the adjacent cell and click Merge and center to let our sparkline span two cells, next we select another sparkline design, I also like to use the markers.