Introduction

When faced with a table of data, what kind of techniques do we employ to extract information from it? Maybe the first thing we do is create summaries. We might total up the columns or we might chart the data. Perhaps, we might even create a PivotTable and drill down into subtotals.

All in all, summarizing data is useful. It’s easy to understand and easy to do.

Let’s say that we’re running a store and we have a small dataset of the sales we made in the first quarter. Using Excel we can easily create summary reports telling us how many products we sold to different types of people; how many to men and how many to women; how many to people over 40 and how many to people under 40.

All these totals are useful. We can compare them and search for interesting differences. We might notice that we sold more products to men than we did to women or that managers bought more of our products than other professions.

But can we get more information than this?

Did managers buy an unusually high number of products compared to other professions? Is this number so high that it might indicate a bias of some kind?

The answers to these types of questions enable us to make better business decisions. Perhaps if we know that our products are significantly more attractive to managers we might change our advertising policies to target managers more than other professions.

You may think that we need data warehouses and data mining tools to answer these types of questions. But that’s not necessarily true. Data warehousing and data mining are excellent tools, but so is Excel and Excel contains some sophisticated statistical functions which we can use to help us answer these types of questions.

Understanding the Basics

First of all, let’s take a look at our data set.

Our data contains customer information which we have gathered over the last quarter of trading. Each time we made a sale we recorded certain information about the customer. For instance, we recorded their Marital Status, their Gender, their Education level and their Occupation (amongst other fields). We have 1,000 records in our data set.

Now let’s say that we are interested in analyzing the Occupation field. It would be useful to know whether or not certain professions tend to buy more of our products than other professions. This would be a key piece of business intelligence for us.

But how do we decide this?

First of all, let’s build some summaries. Let’s count how many sales we made to each profession and each profession’s percentage of the overall sales and display these numbers in a table. We’ll also insert a pie chart to give some simple, visual indication of the proportions sold to each profession.

With this done, let’s now ask ourselves the question again: Do certain professions buy more of our products than other professions?

We can see that Professional and Skilled Manual occurred more frequently in the data than the other professions and that Manual occurred the least. But is this enough information to enable us to generalize and state that Professional and Skilled Manual buy more of our products in general than the other professions?

Well, not really.

Why? Because before we can state anything about these totals we need to have some benchmark to compare them to. We need to have some way of calculating expected numbers for each profession. If a profession had more sales than the expected number then that takes us one step closer to being able to state that there is an unexpected difference between the amounts of products sold to each profession.

To determine the expected values we have to think a little about probability.

For any sale, what is the probability of the occupation being Professional? There are 5 occupations and each sale can only be assigned one occupation so the probability of it being Professional is 1 in 5 i.e., 20%. In fact the probability for each occupation is 20%, assuming there is no bias in the data.

So, for any set of unbiased data, we would expect 20% to be Professional, 20% Clerical, 20% Management, 20% Manual and 20% Skilled Manual.

Thus, for 1000 records, we would expect each occupation to have 200 sales.

But common sense tells us that we’re very unlikely to get exactly 200 sales for each profession. So what we really mean is that we expect to get approximately 200 sales for each profession.

Looking at the actual data, we see that Clerical appears in 177 of the 1000 records. That’s a difference of -23 from the expected value. Is this close enough to 200 to be considered approximately 200?

Skilled Manual has 255 records; a difference of +55. Is this difference big enough to be significantly different?

In our data there are 255 Skilled Manual records. What is the probability of this? That is, what is the probability of there being 255 Skilled Manual records out of 1000 when each record could have any one of five occupations?

Also, since we expect 200 Skilled Manual records it seems logical to also expect that the probability of having 200 records is higher than the probability of having any other number. We’d also expect that the probability of getting 205 records would be higher than the probability of getting 220 (because 205 is closer to 200 than 220 is).

Thus, as we move away from 200 we would expect the probabilities to decrease and decrease towards 0.

Finally, we would expect that there would come a point when the probabilities are so small it would be extremely unlikely for these values to occur in unbiased data. Therefore, if these values did occur then our data has a high likelihood of being biased.

But, how do we calculate these probabilities?

We calculate them using something called the binomial distribution.

Using the Binomial Distribution

Data is expected to have a binomial distribution if:

• There are a finite number of records;
• The value of one record is not influenced by the value of any other record;
• Each record has a value of either true or false;
• The probability of a record having a value of true is the same for each record;

At first, it doesn’t seem like our data has these properties. But it does - we just need to look at it slightly differently.

We’re interested in the Occupation field and we know that there are five occupations. But instead of considering all five occupations at once, let’s consider just one occupation at a time. Let’s start with Skilled Manual.

We have a finite number of records (1000); the occupation of one record is not influenced by any other record; the probability that the record has a value of Skilled Manual is always the same (20%); and each record has an occupation of either Skilled Manual or not Skilled Manual.

Thus, the Skilled Manual data exhibits the required properties and therefore we expect that the Skilled Manual data is binomially distributed.

Using the same principles we can see that the same is true for the remaining four occupations. Instead of considering all five occupations at once, we consider them one at a time. Each occupation should be binomially distributed. If we find an occupation that isn’t, then there is a likelihood of bias in our data.

Excel has a function called BINOMDIST which calculates the probability of getting X true values out of a fixed number of records where the probability of each record being true has a certain, fixed value. In other words, BINOMDIST calculates the binomial probability.

For example, we can calculate the probability of getting 150 true records out of 1000 where each record has a 20% probability of being true by doing:

=BINOMDIST(150, 1000, 0.2, FALSE)

To illustrate how the probabilities are distributed (i.e., where the high values are and where the low values are) let’s calculate the BINOMDIST for each value between 1 and 1000. In other words, we will calculate the probability of getting 1 true record out of 1000, 2 true records, 3 true records etc. We’ll then plot these values on a chart.

Consider the following chart.

Notice that the highest value (the peak of the graph) is at 200? Also notice that as we move away from 200 the probabilities decrease towards 0?

Looking at the chart we can state that, if our data is binomially distributed, we are extremely unlikely to have 150 or fewer records for any occupation. Therefore, if we indeed have fewer than 150 records for any occupation it implies that our data is unlikely to be binomially distributed.

Notice the careful use of unlikely in the previous sentence. When dealing with probabilities we can rarely be 100% certain of anything. Instead we state facts as having a certain likelihood of being true. This is called a confidence level.

The confidence level is the final tool we need in order to conclude whether our data is biased towards certain occupations or whether the differences between occupations occur purely by chance.

Before we draw conclusions about our data we need to decide what confidence level we are happy with. A 95% confidence level means that there is a 5% chance that we will draw the wrong conclusion; a 99% confidence level means that there is a 1% chance we will get it wrong.

How confident do we need to be?

The confidence level is something that we need to decide for ourselves. Typically it is 95% or 99%. For the purposes of this article, let’s stick with 95%.

Now that we have our confidence level we can calculate what’s called the critical values of the binomial distribution.

We will calculate two critical values. These are:

• Lower bound: the smallest number of true records for a binomially distributed variable (at a specific confidence level);
• Upper Bound: the largest number of true records for a binomially distributed variable (at a specific confidence level);

We calculate the critical values using the CRITBINOM function.

To calculate the lower bound we do:

=CRITBINOM(1000, 0.2, 0.025)

This returns a value of 176.

To calculate the upper bound we do:

=CRITBINOM(1000, 0.2, 1-0.025)

This returns a value of 225.

Notice that the critical values depend on the total number of records in our data (1000), the probability each record has of being true (20% or 0.2) and the confidence level we want to work with (0.5/2 = 0.025). If these values change, the critical values change. So, for example, if we had 2000 records instead of 1000, the critical values would be different.

Also notice that we specify the confidence level as an error level. That is, we don’t supply a 0.95 confidence level we supply a 0.05 error level.

What’s more, we are performing what’s called a two-tailed test which means that we are looking for differences that are significantly lower or significantly higher than the expected value. Therefore we have to halve our error level from 0.05 to 0.025.

So now we have the bounding values. These enable us to determine whether or not our data is binomially distributed.

We expect each occupation to occur 200 times out of 1000. These critical values tell us how close to 200 the actual numbers of occurrences have to be in order for them to be considered close enough.

Thus, if the number of records for a given occupation is between 176 and 225, that occupation has a 95% likelihood of being binomially distributed. Otherwise, there is 95% likelihood that the occupation is not binomially distributed.

If we discover that our data is not binomially distributed then something other than chance is determining the data.

Producing the Report

Armed with this knowledge, we are ready to build our report for the Occupation field. Let’s restate the question we’re asking ourselves: Do certain professions buy more of our products than other professions?

Here’s the report:

The Count column simply uses COUNTIF to count the number of occurrences of each occupation. We have added some conditionally formatted data bars to offer some visual indication of the distributions.

The Score column also uses conditional formatting. An up arrow means that that the occupation is more frequent than expected; a horizontal arrow means that the occupation occurs the expected number of times (i.e., is binomially distributed) and a down arrow means the occupation occurs less frequent than expected.

The formula in the Score column is as follows (use the color highlights to interpret the cell references):

That is, the formula compares the count to the lower and higher bounds and returns -1 (less than lower bound), 0 (between lower bound and higher bound) or 1 (greater than higher bound).

The functions in the Lower Bound and Higher Bound cells use CRITBINOM as before.

Here’s the function in the Lower Bound cell:

Interpreting the Results: What Does this Really Mean?

Let’s look again at the table of results:

According to this analysis, only Clerical is binomially distributed. All the other occupations are not. So, what conclusions can we draw from this? What does this analysis really tell us?

Let’s take Skilled Manual as an example.

Our analysis tells us we can be 95% certain that the Skilled Manual occupation occurs significantly more frequently in the data than we would expect it to.

Does this really mean that Skilled Manual workers prefer our products?

Well, the numbers alone do not tell us that. All the numbers tell us is that we can be 95% sure that an unexpectedly high number of Skilled Manual workers bought our products. But there may be various reasons for this. Maybe our shop is next to a construction site or maybe it is next to a factory.

These important real-world factors are not conveyed in the numbers and instead must come from ourselves, as information workers. We must take the numbers and apply our business knowledge to them.

The numbers don’t contain magical answers, but they do provide us with some interesting properties of our data that we should seek to explain and understand.

We can use the numbers as a tool to help us make intelligent business decisions, but the numbers alone don’t make the decisions for us.

Posted by Joseph Chirilov | 3 CommentsFiled under: How To

Attachment(s): Binomial Distribution.xlsx

 

Wednesday, April 02, 2008 9:11 AM

 

Office Open XML, a.k.a. IS 29500

As everyone who has followed this blog already knows, we introduced a new file format with Office 2007 (and Excel 2007) with the extension .xlsx called Office Open XML (OOXML). Microsoft submitted the new file format to Ecma International, and in December 2006, that file format was approved as ECMA-376, and Ecma submitted the standard to ISO/IEC JTC 1 for fast-track processing to become an ISO standard. Over the last 15 months, many conversations have taken place between Ecma and the National Bodies that make up ISO, and comments from those National Bodies were addressed at a Ballot Resolution Meeting during the last week of February 2008. Through the month of March, the National Bodies then had the opportunity to consider the changes made to OOXML at the meeting, and today ISO officially announced the results of the voting.

As stated on the ISO web site:

Approval required at least 2/3 (i.e. 66.66 %) of the votes cast by national bodies participating in the joint technical committee ISO/IEC JTC 1, Information technology, to be positive; and no more than 1/4 (i.e. 25 %) of the total number of ISO/IEC national body votes cast to be negative. These criteria have now been met with 75 % of the JTC 1 participating member votes cast positive and 14 % of the total of national member body votes cast negative.

The Excel team views this as a very positive development, and is committed working with both Ecma and ISO to continually evolve and develop OOXML as an international standard.

More information is available for those interested:

• ISO press release: ISO/IEC DIS 29500 receives necessary votes for approval as an International Standard
• Brian Jones blog: Open XML Overwhelmingly Approved as an ISO / IEC standard (IS 29500): the end of the file formats war

Posted by Joseph Chirilov | 0 CommentsFiled under: File Format

 

Friday, March 28, 2008 10:55 AM

 

Shadows on Charts and Cells in Excel 2007

Today’s author, Helen Hosein, a Program Manager on the Excel team.

Certain shadows created in Excel 2003 look different Excel 2007. In particular, you might notice that your old cell shadows, as well as shadows on things like Chart Titles might look a bit different when you open your workbooks in Excel 2007. This is because Excel treats these as shapes with no fill - a bit like a wire frame. The shadow that it casts is the rectangle of its outline. Only when the frame is filled in, does it cast a whole shadow. Similarly, Excel 2007 just draws a shadow on the border of a shape with no fill, instead of on the whole shape. Since many of you, our customers, miss being able to create these types of shadows, we are assessing the possibility of bringing back this capability in the next version of Excel. For now, though, there are ways to work around this and create great-looking shadows on cells and chart elements in Excel 2007.

An Example - Shadows in Excel 2003 vs Shadows in Excel 2007

Two of my very good friends, Rita and Alfie, are thinking of moving to London soon. Man, I’ll miss them. They’re shopping around for “flats” (they’re already learning the lingo) but aren’t sure which neighborhoods to consider. Rita, being web savvy, decides to download some of the 2005 rent data from the Greater London Authority and make an Excel chart so she and Alfie can gauge the relative prices.

Rita copy/pastes the average rent for a studio in each borough into Excel 2003. She also adds the overall average rent for comparison. To make the overall average stand out, she adds a Cell Shadow to that row.

Excel draws a rectangle shape around the selection and adds a shadow to it, giving that row a great effect that draws attention.

Now Rita adds her chart of the rents. She likes gray, so she makes the whole chart background that color. Since the shadow looks so good on the summary, she adds a matching shadow to my Chart Title, and sets the Area fill to None so that the chart area color shows through.

Once she’s done, Rita sends me the workbook to get my opinion. After all, I’m her best friend, and I’m hoping to visit her soon in her new neighborhood. I open her workbook in Excel 2007.

Wow! Do you see what I see? Rent in the City of London is really high. The second thing I notice, however, is that the shadows Rita put on “All Boroughs” and on the Chart Title are not quite what Rita might have intended. The good news is that these can be fixed in no time.

Chart element shadows in Excel 2007

Remember, when Rita added the shadows to the Chart Title and the Legend, she also set the Area fill to None. Excel 2007 sees these chart elements as empty rectangle frames, since they have no fill. Because of that, the shadows on them look just like a second empty rectangle. The quick, easy fix is just to give them a fill.

Step 1: Right-click on the Chart Title and choose Format Chart Title.

Step 2: On the Fill tab of the Format Chart Title dialog, choose Solid fill.

Step 3: Choose the same color fill as the background (in my case, the same shade of gray that Rita’s always on about).

Now the Chart Title looks the same as it did in Excel 2003, and it still blends with the Chart Area because I gave it the same fill. I could also have done that by using the Shape Fill menu on the Format tab under Chart Tools on the Ribbon.

Cell shadows in Excel 2007

Now the shadows on the chart are fixed, but the cell shadow still doesn’t look the way it used to. Fortunately, one of our testers, Jon Adams, wrote a convenient add-in that helps solve this problem. Jon’s add-in gives a great example of how you can use VBA to create your own cell shadows. You can download Jon’s by clicking the attachment at the bottom of this post. Here’s how you install the add-in.

Step 1: Click on the Office button and choose Excel Options.

Step 2: On the left, choose Add-Ins, and under Manage: choose Excel Add-Ins and click Go…

Step 3: An Add-Ins dialog will appear. Click Browse… and choose CellShadows (the file you just downloaded).

Step 4: Now Cellshadows will appear in your list of Add-Ins with a check mark next to it. Click Ok.

Once you’ve installed the CellShadows add-in, you’ll notice a new chunk called Cell Shadows on the Insert tab of the Ribbon. The Shadow menu allows you to insert a new cell shadow on your selection, just like you could in Excel 2003, whereas the Convert Cell Shadows button converts your old cell shadows so that they look right in Excel 2007. I want to convert Rita’s cell shadow, so I hit the Convert Cell Shadows button.

Just like that, Rita’s cell shadow is fixed.

So how did that work? If you try this yourself, you’ll find that if you click on the shadow, you can see that it’s actually a group of rectangles. The convertShadows macro looks for all the shapes in the workbook with an offset shadow and no fill, and replaces each of them with a group of two gray rectangles at the matching offset where the shadow would have existed. The CellShadowFromRange macro does the same thing, but uses the selected range as a starting point rather than an existing shape. When creating a new cell shadow, the add-in will also add a border around the selection to make it look more like Excel 2003’s shadows. If you’re converting old cell shadows that lined up perfectly with the original range that was selected (that is, you didn’t move the cell shadow shapes after creating them), you can optionally add a border to the range after conversion in Excel 2007 to complete the look.

Step 1: Go to the Home tab on the ribbon.

Step 2: On the Font chunk, click on the Border menu.

Step 3: Choose Outside Borders.

Now the shadows all look just as great in Excel 2007 as they did in Excel 2003. I’m still not sure to which neighborhood my friends will be moving, but Rita says Alfie is rather keen on Haringey.

Posted by Joseph Chirilov | 6 CommentsFiled under: Charting, How To

Attachment(s): CellShadows.xlam

 

Monday, March 24, 2008 4:11 PM

 

Setting up Excel Services to Refresh Workbooks When Opened.

Today’s author, Pej Javaheri, a Product Manager on the Excel Services team, talks about one of the more common configuration questions we hear about from customers.

One of the challenges in sharing Excel workbooks is ensuring that the right people see the right data. With Excel Services, this has become a lot easier ensuring user’s only see the information they are authorized to view and not sharing information that is privileged or out of their scope. With a few simple configuration steps, workbook authors can quickly setup their reports to only display the information that is authorized by user’s security privileges.

This document describes how to setup SharePoint SharedServices for Excel Services, and how to setup your ODC connection to refresh workbooks on open.

Ensure the server has the right permissions to access data connections, and run Excel workbooks on the server. To do this, go to the Shared Administration page of your Shared Services.

First, modify the Trusted File Locations to include the locations that you will allow Excel workbooks to be accessed to display on Excel Services. In the Address field, enter the location of where the workbooks are stored. Most likely this will be in a SharePoint Document library. If you want to enable the whole SharePoint site, enter the root address of the SharePoint site, such as http://SharePoint. Under Location, ensure you select Children Trusted if you will be storing workbooks in folders from this location, especially if you are going to enable the whole site.

Under External Data, select either “Trusted data connection libraries only”, or “Trusted data connection libraries and embedded” to allow Excel Services to refresh data from workbooks that access data through ODC files.

If you will be calling User Defined Functions (UDF), then check the box under User-Defined Functions. You may also want to disable the Warn on Refresh option under External Data

Click OK to return to the Excel Services Trusted File Locations screen and apply all the changes otherwise a warning dialog box will require users to okay the action before refreshing the workbook in Excel Services.

You should have something like this, which identifies the location of the workbooks that will be accessed by Excel Services. You can add as many locations as required for each shared service environment you setup.

Next, we need to identify where the data connection libraries are stored for Excel Services to access and refresh data.
Go back to the SharedServices administration page, and click Excel Services Trusted Data Connection Libraries, and then click Add Trusted Data Connection Library.

Enter the location of the document library that the ODC files are saved in. In this example, I have added the default location under the Reports document library

Creating the ODC file in Excel

In Excel, select From Other Sources under the Data tab. Select the data source type and then enter the required credentials in the Data Connection Wizard. In the File Name field, select Browse and then type the location of the Trusted Data Connection Library (alternatively, you can save the ODC file locally, and the upload it to the Data Connection Library via SharePoint).

If the connection requires specific authentication requirements, or single-sign on select the Authentication Settings and set the appropriate item.

Now that you have the connection created, you need to modify the properties of the connection so that it is set to refresh the workbook when it is opened every time.

Still in the Data tab, select Properties and then check Refresh data when opening the file.

Create your Excel workbook like normal, and then publish to Excel Services. If data is coming from a relational source, publish it in a Pivot Table.

And there you have it. Whenever users open the workbook, the data is automatically refreshed using their credentials to access the data source.

Posted by Joseph Chirilov | 3 CommentsFiled under: Excel Server, Analysis Services

 

Monday, March 10, 2008 9:20 AM

 

SharePoint 2008 Conference Recap

The BI Blog does a quick recap of the SharePoint 2008 Conference that just passed with particular emphasis on the popularity of the Excel Services session:

Dan Parish, from the Microsoft Excel Services team, presented how you can extend the capabilities of Excel Services to a packed room of over 300 with tremendous feedback. Not to worry if you missed that sessions as we will be posting the content that was presented in a separate blog to showcase examples of how you can use Excel Services.

Posted by Joseph Chirilov | 0 Comments

 

Thursday, March 06, 2008 1:41 PM

 

Unusual (but cool!) Uses of Excel

Many of you who have been using Excel for a long time have most likely seen some of the creative, interesting, and rather unusual uses of Excel that people have conjured up. I’m talking about things like writing the Pac-Man game completely in Excel, stuff you wouldn’t expect to see in a critical business application like Excel, but thanks to the ingenuity of spreadsheet hackers we get to see how the power and flexibility of spreadsheets can be put to use.

I was reminded of this today when a colleague forwarded me this Slashdot article about someone who used Excel to create a 3D rendering engine (btw, this reminded me of a similar 3D animation engine written by a tester on our team that uses the new OfficeArt rendering platform in Excel 2007), and it got me wondering what other interesting uses have people come up with that I haven’t seen before.

I’ve seen a quite a few of these in my time here on the Excel team (we’ve even posted a couple of our own - check out Conway’s Game of Life and Memory), and I’m sure many of you have too. So let’s hear it! Tell me about the interesting, crazy, weird, unusual and cool spreadsheets you’ve come across or built that you never thought you’d see in Excel.

This could be interesting.

Posted by Joseph Chirilov | 14 Comments

 

Wednesday, March 05, 2008 12:45 AM

 

Handling Workbook Dependencies using Excel Services

Today’s author, Sanjay Kulkarni, a Program Manager on the Excel Services team.

We have heard from several customers about scenarios where they have workbook dependencies. So the output of one workbook is used in other workbooks for calculations. The dependencies could be a result of the business model meaning different people in the organization being responsible for the workbooks or it could simply be a way of isolating related logic in different workbooks. In this blog post we will look at a sample way of how these workbook dependencies can be handled by writing a little code using Excel Services Web Services API and SharePoint object model. Note that you will need MOSS 2007 with Excel Services configured to use what is described here.

To simplify things let us look at a scenario with two workbooks. At a high level workbook one (Rates.xlsx) has mortgage rates that are refreshed from the database. This workbook also adds the overhead and calculates the retail rate that can be offered to customers. The second workbook (Payments.xlsx) has a list of potential customers and information about them. The idea is to calculate the first workbook to get the retail rates and then use these rates to calculate the mortgage payment for the customers in the second workbook. We will use document properties to pass values from the Rates workbook to the Payment workbook.

The Rates workbook has a Pivot Table that has interest rates and a cell that stores the margin. To make the sample work independently we have removed the connection to the external database and instead we get the data from another sheet in the workbook however that can be easily changed. Here is what the Rates workbook looks like.

The Payments workbook has information about the clients, their mortgage as well as cells where the retail mortgage rates can be updated.

The following sections describe the steps involved with some code snippets. The complete code and the workbooks can be downloaded at the bottom of this post (see attachments). This is a console application and accepts the margin rate added for overhead as a parameter. We use a fixed constant in the workbook if no margin is specified. To simplify things I have omitted the error handling for the most part. You will definitely need to add robust error handling if you are planning to deploy it for a practical application.

First print the margin rate and some initialization

if (args.Length > 0)
Console.WriteLine(“Margin rate is = “+ args[0] + “%”);

// Instantiate the Web service and create a status array object.
ExcelService es = new ExcelService();
Status[] outStatus;

string ratesWorkbook = “http://server-name/Site/doclib//Rates.xlsx”;

// Set credentials for requests
es.Credentials = System.Net.CredentialCache.DefaultCredentials;

Now we open the workbook and set the margin rate in the Rates workbook, refresh data and calculate the retail rates.

// Open the Rates workbook, get new rates by refreshing the external data and // calculate. string sessionId = es.OpenWorkbook(ratesWorkbook, “”, “”, out outStatus);

//Set the input overhead margin rate if specified omcommand line.
if (args.Length > 0)
{
es.SetCellA1(sessionId, “RatesSheet”, “Margin”, args[0]);
}

// Refresh data and recalculate the workbook.
es.Refresh(sessionId, “”);
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);

Now that the retail rates are calculated; we get the retail rates and set them as document properties on the Payments workbook. The advantages of using the document properties for storing values are firstly the properties are associated with the document so if you update the workbook they will still be preserved. Secondly since they are stored by SharePoint the process of calculating rates and payments can be done at different times if needed by the business process.

//Get the calculated rates from the workbook
double rate1Year, rate3Year, rate5Year, rate30Year;
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate1Year”, false, out outStatus);

rate1Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate3Year”, false, out outStatus);

rate3Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate5Year”, false, out outStatus);

rate5Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate30Year”, false, out outStatus);

rate30Year = Convert.ToDouble(valObj);

// Close the session for Rates workbook.
es.CloseWorkbook(sessionId);

string paymentsWorkbook = http://server-name/Site/doclib/Payments.xlsx;

//Add the different rates as document properties for the payment workbook.
SPSite site = new SPSite(paymentsWorkbook);
SPWeb web = site.OpenWeb();
SPFile file = web.GetFile(paymentsWorkbook);
Hashtable tab = file.Properties;

tab[“rate1Year”]= Convert.ToString(rate1Year);
tab[“rate3Year”] = Convert.ToString(rate3Year);
tab[“rate5Year”]= Convert.ToString(rate5Year);
tab[“rate30Year”]= Convert.ToString(rate30Year);

//Update the SPFile object so that the properties are stores in the database.
file.Update();
site.Close();

The next step is to open the Payments workbook, get the retail rates from the document properties and update the Payments workbook with the new rates. Note that since the document properties are stored by SharePoint we can do the following steps at a different time than the updates to the rates that we did above. That could be critical is some business processes since the two could be owned by different people or business units in the organization.

//Open the payment workbook, update the rates from the document properties.
sessionId = es.OpenWorkbook(paymentsWorkbook, “en-US”, “en-US”,out outStatus);

//Read the rates from the document properties.
SPSite site1 = new SPSite(paymentsWorkbook);
SPWeb web1 = site.OpenWeb();
SPFile file1 = web.GetFile(paymentsWorkbook);
Hashtable tab1 = file1.Properties;

rate1Year = Convert.ToDouble(tab1[“rate1Year”]);
rate3Year = Convert.ToDouble(tab1[“rate3Year”]);
rate5Year = Convert.ToDouble(tab1[“rate5Year”]);
rate30Year = Convert.ToDouble(tab1[“rate30Year”]);
site.Close();

//Set the rates in the Payment workbook.
es.SetCellA1(sessionId, “Payments”, “Rate1Year”, rate1Year);
es.SetCellA1(sessionId, “Payments”, “Rate3Year”, rate3Year);
es.SetCellA1(sessionId, “Payments”, “Rate5Year”, rate5Year);
es.SetCellA1(sessionId, “Payments”, “Rate30Year”, rate30Year);

Finally we calculate the Payments workbook and print out the monthly payments. To simplify the sample we just print the monthly payments here but this can be easily updated to perform different tasks like sending email to the clients with the information. Or better yet we could compare their existing monthly payment to the new calculated payment and send an email if the new payment is less (It is time to refinance!).

// Recalculate the workbook to get new payment infomration.
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);

// Get the payments and print them. In real life you could add logic here
// to save the workbook or to iterate through the table and send an emai
// using the email id in the workbook.
valObj = es.GetCellA1(sessionId, “Payments”, “JackPayment”, false, out outStatus);
double monthlyPayment = Convert.ToDouble(valObj);
Console.WriteLine(“Jack’s Payment is= {0:C}”, monthlyPayment);
valObj = es.GetCellA1(sessionId, “Payments”, “JudyPayment”, false, out outStatus);

monthlyPayment = Convert.ToDouble(valObj);
Console.WriteLine(“Jaudy’s Payment is= {0:C}”, monthlyPayment);

// Close the session for Payments workbook.
es.CloseWorkbook(sessionId);

This logic can be extended to scenarios where the output of one workbook serves as the input for several workbooks with some modifications. In the example above we are using specific properties on the workbook. Note that if there are dependencies between multiple workbooks then properties written while updating one workbook may get overwritten by another. Also one may want to use a specific namespace for the properties by prefixing them so that they are easily identifiable.

Posted by Joseph Chirilov | 3 CommentsFiled under: Excel Server, Programmability

Attachment(s): WorkbookDependencies.zip

 

Tuesday, March 04, 2008 6:48 PM

 

Example file for PivotTable / Data Validation Trick

Recently I posted an article discussing how PivotTables can be used on Excel Services to mimick the Data Validation feature. The author, Dany Hoter, sent me a copy of the file he used in the example screen shots and I never got around to posting it for our readers. Here’s a short description of the example file from Dany:

The example implements a scenario of calculating the pricing for car insurance based on the details entered in a form. The form can be collected on paper and entered manually into Excel or collected online and fed into Excel services to perform the calculation.

Each value is connected to a numeric value. High values means less risk to insure this car to this driver. More driving experience, safer car, better neighborhood etc. Of course all data is completely fake and does not represent any real data.

Each field might have a different number of possible values, some have only Yes/No and some might have 10 different values. All list of values where extended to the last row so to avoid blank appearing as an option for input. We can’t really prevent the user from choosing multiple options or the all option so an error message is displayed when the user does that.

The file can be downloaded from here.

Posted by Joseph Chirilov | 3 CommentsFiled under: Excel Server, PivotTables, Data Validation

 

Thursday, February 21, 2008 11:36 AM

 

Chart Templates

Today’s author, Ben Rampson, a Program Manager on the Excel team.

Customers who author charts in presentations and reports often spend a significant amount of effort modifying their charts to get them looking just the way they want. While the new Chart Style and Chart Layout features in Office 2007 provide some great one-click options to quickly alter the appearance of a chart, there are still times when one needs to modify a chart even further. A company policy may require that a chart use particular colors, a project may demand a necessary chart layout, or a complex chart may necessitate the setting of many specific element properties. While there is always a onetime cost required to set up a chart for the first time, there is an easy way avoid these costs in the future when you need to re-create a similar chart.

One frequently underutilized feature in charting is the Chart Template. A chart template allows a user to quickly save and reapply settings from a previously created chart. Chart templates are standalone .crtx files that can be applied in a similar manner as chart types. In addition to saving the chart type, template files also contain chart element setting and formatting information. This information includes settings such as line and fill properties, axis settings, and element placement.

It also should be noted that chart templates do not save customer data, preventing customer information from being copied and saved into template files (customer data includes: data point and series values, text on titles, etc.). Although data is not saved, the existence, formatting, and layout of all elements is stored in the template. Assume a template is created from a chart that contains a title. When the template is applied the resulting chart will have a title in same location and with the same formatting as the chart used to generate the template, however, the default text of “Title” will appear in the element since the title’s data has not been stored in the template.

Steps to Create a Chart Template

1. Insert a chart to use as a starting point for your template. Note that chart templates can be created and used in any of the three Office applications that have the shared charting engine: Excel, PowerPoint, and Word.
2. Format the chart the way you normally would until the chart has the desired appearance. In my example I have made the following modifications to the chart:
   1. Applied a custom fill color to the series
   2. Deleted the legend
   3. Removed the horizontal axis line
   4. Set the horizontal axis maximum to 10
   5. Changed the line color of the gridlines and vertical axis line
   6. Reversed the categories order on the vertical axis
   7. Set the horizontal axis crosses at setting to maximum value
   8. Added a chart title and entered the title text
   9. Changed the chart titles font to be the same color as my series
      The resulting chart:
      
3. Save the chart template.
   1. With the chart selected, click on the Design tab in the Ribbon
   2. Click on the “Save As Template” button located in the Type chunk
   3. Enter a template name and click SaveNote: Saving chart templates as standalone .crtx files is a new feature for Office 2007. By using standalone files, chart templates can easily be shared amongst different users. The saved template location for Windows Vista users is C:\Users\<user_name>\AppData\Roaming\Microsoft\Templates\Charts (C:\Documents and Settings\<user_name>\Application Data\Microsoft\Templates\Charts for Windows XP users). This folder location can also be found using the “Manage Templates…” button in the Chart Type dialog. Once a template file is saved in this folder it can be accessed and applied in any of the three charting applications.

Steps to Use a chart template

1. Select the data for your chart
2. Click on the Insert tab in the Ribbon
3. Open the Insert Chart dialog
   1. If you are using Excel then select the “All Chart Types” option from the bottom of any dropdown in the Charts chunk
   2. If you are using PowerPoint and Word then select the “Chart” button.
   3. Tip: if you have an existing chart you can select the “Change Chart Type” option to launch the same dialog
4. Select the Template tab from the top of the left navigation menu
   
5. Select a chart template from those listed on the right side of the dialog
6. Select “OK” to apply the templateThe resulting inserted chart created from a different data set. While not completely finished it comes very close:
   
7. Complete the chart by adding back the text elements that were not saved in the chart template
   1. In my example all I need to do to complete my chart is to modify the chart title’s text. In other cases it may be necessary to make additional minor changes such as tweaking axis settings stored in the template that do not work for the current data set.

Creating this chart was simple and only required two steps, inserting a chart using the desired template and entering the title’s text. This is a significantly easier process when compared to the list of steps I completed to build this chart for the first time. The finished chart:

Note that the above chart has fairly complex formatting and takes great advantage of the template feature; however, even templates with limited formatting can still save significant time in the chart creation process.

In summary, when creating a complex chart that you may use again in the future, remember to take advantage of chart templates. This powerful tool in charting can help you avoid duplicating effort and allows you to easily generate consistently formatted charts.

If you have any ideas on how we can improve chart templates in the future please feel free to leave your suggestions in the comments section.

Posted by Joseph Chirilov | 6 CommentsFiled under: Charting

 

Wednesday, February 13, 2008 12:50 AM

 

A PivotTable Trick That Brings Data Validation to Excel Services

Today’s author: Dany Hoter, a product planner who works on the Excel Services team.

Excel has a feature called Data Validation that controls the possible values a user might enter into a cell or a range of cells.

As you can see there are many options for validating data entry. The most popular is probably validating against a list of values. The list can be included in the validation definition or can be a region in the sheet referenced from the dialog.

Recently I saw an example created by a customer that validates input using a PivotTable instead of data validation. The idea is to use only the report filter area of the PivotTable and to use the values selected in multiple filter fields as inputs for a model:

In the example you see multiple input fields and when clicking the filter icon the user will see a list of values and can choose one.

The advantage of this method is easier maintenance of multiple lists of values used for validating multiple fields. The range that the PivotTable is based on looks a bit odd because usually PivotTables are based on rectangular ranges and not something that looks like this:

In order to avoid the “(blank)” value appearing in the PivotTable drop-down list of values, fill the last value in each category all the way down to the last row of the region:

Use of PivotTable for validation in Excel Services

So far I explained how a PivotTable could be used as a validation method in Excel. The impact of using this technique is not significant until we apply it to Excel services. Excel services does not support data validation and any workbook containing data validation will not be loaded by Excel Calculation Services (ECS). In addition, entering inputs to a model in Excel Services is not as user-friendly as Excel since values cannot be entered directly into cells. Using PivotTable filters for validation is fully supported in Excel Services and can solve these two problems. We have a friendly way to input values into a model and also to apply a validation against a list of values. The same example might look like this in the browser:

So if you are looking to build a spreadsheet that can be consumed by Excel Services and provides user-friendly, validated, input, give the PivotTable report filter a try.

Update: sample file can be found here.

Posted by Joseph Chirilov | 17 CommentsFiled under: Excel Server, PivotTables, Data Validation

 

Tuesday, February 12, 2008 11:41 PM

 

New Blog Focused on BI

If you are passionate about business intelligence (BI), you may be interested to know that a new Microsoft blog has started that is all about BI. It is aptly named The BI Blog.

One interesting recent post talks about how Microsoft was recently placed in the Leaders Quadrant for BI platforms in Gartner’s Business Intelligence Magic Quadrant.

Posted by Joseph Chirilov | 5 Comments

Tuesday, February 05, 2008 10:00 PM

Common Questions Around Excel 2007 OLAP PivotTables

Today’s author: Allan Folting, a program manager who works on the Excel team.

I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

1. Filtering individual calculated members
2. Showing non-visual totals when filtering

I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

1. Adding calculated members/measures
2. Adding named sets

Today I’ll discuss these topics and outline current ways and workarounds to address this.

Behaviors in Excel 2007 specific to Analysis Services 2005 Service Pack 2 (SP2) cubes

Let’s start with a bit of background information.

With Analysis Services 2005 SP2+, Excel 2007 uses subselects in the MDX queries generated when filtering is applied to a PivotTable.

In the following example of MDX generated by an Excel 2007 PivotTable, the user is filtering a geography hierarchy to only show United States and Australia which is represented by the subselect part of the query (in bold font).

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM
(

SELECT ({[Customer].[Customer Geography].[Country].&[United States], [Customer].[Customer Geography].[Country].&[Australia]}) ON COLUMNS FROM [Adventure Works]

)
WHERE ([Measures].[Internet Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Subselect support in the OLAP server is required in order for the new Label, Date and Value filtering features to be available in Excel 2007 PivotTables. The reason is that the subselect feature is designed for making Analysis Services calculate visual totals while filtering which was also an explicit design goal for Excel 2007 PivotTables.

When multiple filters are applied to different hierarchies at the same time, the filters are applied in the order that the user added them, using subselects, which ensures an intuitive filter experience where filtering is always based on the totals currently displayed in the PivotTable.

The reason why subselects are only used with Analysis Services 2005 SP2 or newer versions is that Analysis Services 2005 introduced a provider property in SP2 so that client applications can determine whether the provider supports subselects. The provider property is MDPROP_MDX_SUBQUERIES.

By reading this provider property to determine whether subselects are supported, Excel exposes the new filtering features with any OLAP provider that implements this property (and supports other fundamental OLAP provider capabilities needed, I included a link to more information on provider properties that Excel reads in the additional resources section below).

Now, let’s discuss the specific features affected by using subselects for filtering. I’ll start with filtering individual calculated members.

Filtering individual calculated members with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes

Calculated members are not supported in subselects with Analysis Services 2005 so Excel 2007 does not offer filtering on individual calculated members for Analysis Services 2005 SP2+ servers.

However, you can choose to display all calculated members, or no calculated members at all, in the PivotTable Options dialog box using the “Show calculated members from OLAP server” option.

Note that this does not apply to calculated members in the Measures hierarchy (calculated measures); those are always displayed regardless of the option.

PivotTable Options dialog with the “Show calculated members from OLAP server” option

The screenshot below illustrates the filter drop down in a PivotTable for a hierarchy with calculated members (”Budget Variance” and “Budget Variance %”).

Note that the checkboxes for the calculated members are disabled and therefore cannot be unselected.

Calculated members cannot be individually selected when subselects are used for filtering with Analysis Services 2005 SP2+

Non-visual totals with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes

Another implicit behavior of subselects with Analysis Services 2005 is to always generate visual totals.

For this reason, PivotTables created using Excel 2007 do not allow the user to turn off visual totals with Analysis Services SP2+ cubes.

The first screenshot below illustrates a PivotTable with visual totals. The total for United States is the sum of the states currently in the filter (California, New York and Washington).

PivotTable displaying visual totals

The second screenshot illustrates a PivotTable with non-visual totals. The total for United States is not affected by the fact that some states are filtered out; it is still showing the total sales amount for all existing states.

PivotTable displaying non-visual totals

Workarounds

Individually filtering calculated members and generating non visual totals are important features for common scenarios. The Excel and Analysis Services teams are working together on a future solution for lifting these limitations and this section outlines current workarounds for enabling these features in Excel 2007 with Analysis Services 2005 SP2+.

The workaround for creating PivotTables that support both non visual totals and filtering individual calculated members with Analysis Services 2005 SP2+ cubes is to create PivotTables in Excel 2007 with a pre-2007 version.

PivotTable versioning was introduced in Excel 2000. PivotTables created with a certain version ensures that the PivotTable is compatible with the corresponding version of Excel in that it is generally created with the same default settings as in that version of Excel and it exhibits the same behaviors. This is also true when using the Excel PivotTable object model through code. A benefit from this is that users can expect macros created in previous versions of Excel to still work exactly the same way in newer versions of Excel.

The table below outlines the different PivotTable versions supported in Excel as well as which version is the default created in different versions of Excel.

Version of Excel	Version number	Default PivotTable version created
Excel 2000	9	PivotTable.Version = 0 (xlPivotTableVersion2000)
Excel 2002	10	PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2003	11	PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2007	12	PivotTable.Version = 3 (xlPivotTableVersion12)

There is an additional PivotTable version supported in Excel 2007, namely version 2 (xlPivotTableVersion11). However, there is no functional difference between version 1 and version 2 PivotTables. Since no version of Excel creates version 2 PivotTables by default, it isn’t included in the table above. Version 2 was added in Excel 2007 for completeness although Excel 2003 creates version 1 PivotTables.

For PivotTables with a version number less than 3, Excel will generate Excel 2003 style MDX queries (no use of subselects) and will support filtering calculated members individually and toggling visual totals.

As indicated above, the drawback of working with old version PivotTables is that the new filtering features are not available.

There are two ways in Excel 2007 to create PivotTables with a specific version:

1. Work with Excel 2007 in compatibility mode (this mode is on when you open xls files) since then Excel automatically creates version 1 PivotTables
2. You can explicitly specify the version of a PivotTable when creating it if you create it using the object model (example below)

When Excel 2007 is in Compatibility Mode, version 1 PivotTables are created

The VBA code below creates a version 1 (PivotTable.Version = xlPivotTableVersion10) PivotTable, turns on the new Excel 2007 default layout and turns off visual totals. It is using the sample Adventure Works cube that comes with Analysis Services 2005. You’ll need to change the OLAPServerName to your actual OLAP server name and make sure there is a worksheet named Sheet1 which is empty in the area where the PivotTable is placed to try out this code.

Sub CreateV1PivotTable()

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion10)

.Connection = Array(”OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW;Data Source=OLAPServerName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error”)
.CommandType = xlCmdCube
.CommandText = Array(”Adventure Works”)
.MaintainConnection = True
.CreatePivotTable TableDestination:=”Sheet1!R1C1″, TableName:=”PivotTable1″, DefaultVersion:=xlPivotTableVersion10

End With

ActiveSheet.PivotTables(”PivotTable1″).RowAxisLayout xlCompactRow
ActiveSheet.PivotTables(”PivotTable1″).InGridDropZones = False
ActiveSheet.PivotTables(”PivotTable1″).TableStyle2 = “PivotStyleLight16″
ActiveSheet.PivotTables(”PivotTable1″).VisualTotals = False

End Sub

The screenshot below shows the PivotTable Options dialog box for a version 1 (xlPivotTableVersion10) PivotTable in Excel 2007 with the option to toggle visual totals using the “Include filtered items in totals” checkbox. Non-visual totals are displayed when the checkbox is selected.

OLAP PivotTables with a version less than 3 in Excel 2007 allow users to turn off visual totals in OLAP PivotTables using the PivotTable Options dialog box (”Include filtered items in totals”)

Note that if you save to an Excel 2007 file format while Excel is in compatibility mode, all PivotTables with a version number less than 3 will be marked for upgrade to version 3 (xlPivotTableVersion12) and the next refresh you perform on each PivotTable will do the actual upgrade. Once a PivotTable is upgraded to version 3, you’ll lose the ability to filter on calculated members individually and toggling visual totals.

If you want to avoid the automatic upgrade, before refreshing the PivotTables, go to each one and set PivotTable.PivotCache.UpgradeOnRefresh to False using the object model. For example, in the immediate window (press ALT+F11 and then CTRL+G to display the immediate window) type and run Activecell.PivotTable.PivotCache.UpgradeOnRefresh = False with the active cell inside the PivotTable.

Turning off PivotTable version upgrade for the selected PivotTable in the Immediate window

Additional resources

Excel 2007 and SQL Server Analysis Services 2005 Service Pack 2

http://blogs.msdn.com/excel/archive/2007/05/06/excel-2007-and-sql-server-analysis-services-2005-service-pack-2.aspx

Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

http://www.microsoft.com/downloads/details.aspx?FamilyId=2D779CD5-EEB2-43E9-BDFA-641ED89EDB6C&displaylang=en

OLE DB for OLAP Properties Used by Excel 2007

http://msdn2.microsoft.com/en-us/library/bb407625.aspx

Creating calculated members/measures and sets in Excel 2007 OLAP PivotTables

Excel doesn’t offer a user interface for creating calculated members/measures and sets in PivotTables connected to OLAP cubes. However, in the object model there is support for creating personal calculations and sets in PivotTables with OLAP data sources.

The CalculatedMembers collection holds calculations and sets private to a PivotTable and it has an Add method for creating new calculations and sets.

Below are examples of creating a calculated measure, a calculated member and a named set using the CalculatedMembers collection of the PivotTable object. All the examples are using the sample Adventure Works database that comes with Analysis Services 2005.

Example of adding a calculated measure in an Excel PivotTable connected to an OLAP data source:

Sub AddCalculatedMeasure()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String

Set pvt = Sheet1.PivotTables(”PivotTable1″)
strName = “[Measures].[Internet Sales Amount 25 %]”
strFormula = “[Measures].[Internet Sales Amount]*1.25″
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember

End Sub

Calculated measure “Internet Sales Amount 25%” added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a calculated member to the Adventure Works Product level of the Product Categories hierarchy in an Excel PivotTable connected to an OLAP data source:

Sub AddCalculatedMember()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String

Set pvt = Sheet1.PivotTables(”PivotTable1″)
strName = “[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]”
strFormula = “[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25″
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
pvt.ViewCalculatedMembers = True

End Sub

The screenshot below shows the PivotTable with the calculated member (”Mountain-100 Silver, 38 25%”) added to the Product level of the Product Categories hierarchy.

Calculated member “Mountain-100 Silver, 38 25%” added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a named set in an Excel PivotTable connected to an OLAP data source:

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

The screenshot below shows the PivotTable with the named set (”Mountain Bikes”) added.

Named set “Mountain Bikes” added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Note that Excel 2007 only support named sets consisting of members from a single hierarchy.

Also note that even though Excel Services 2007 doesn’t support Excel workbooks with macros/code, you can still use code as illustrated here to create calculated members/measures and named sets, remove the code after creating these and then publish the workbook to Excel Services. This way the calculations created in Excel can be exposed through Excel Services.

By Saleh Galiwala

Computer Engg

Mob :- +971508556770

Email :- salehgaliwala@yahoo.com