Using SetParameters with the Excel Web Services APIs

One of the new methods I listed in the post about new features is the SetParameters() SOAP call. This allows callers to set multiple parameters at the same time in Excel Services. In this example I have a simple list containing items and sales of those items. I then use a PivotTable to aggregate the sales and get an average.

Here’s the workbook snippet:

On the right there’s the raw table and on the right the PivotTable, showing only the average of sales with a page field acting as a parameter of the workbook. The parameter was named ItemFilter and the average of Sales cell was named AverageSales. The following piece of code shows how to set the parameter and get the value back:

ES.ExcelService client = new ES.ExcelService();

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbook("https://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[1];

ES.ParameterInfo param = new ES.ParameterInfo();

infos[0] = param;

param.Name = "ItemFilter";

param.Values = new String[1];

param.Values[0] = "TV";

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

 

As you can see, first we go and set up the parameters objects – we create a parameter array (you can pass in multiple parameters with one call). In our instance, it has a single item in it. We then create the parameter we want to set up (called ItemFilter" ) and the value we want to put in (via the Value property).

Finally, we call SetParameters() with the array – when that call comes back, it will have filtered the PivotTable. Here’s the result:

Note that if you are using the more recent versions of C#, the code can look a little less verbose:

ES.ExcelService client = new ES.ExcelService();

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbook("https://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[]

       { new ES.ParameterInfo() { Name = "ItemFilter", Values = new string[] { "TV" } }};

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

Instead of instantiating each element in its own line, we do the initialization inline – makes the code look less complicated (though it does essentially the same thing as we did in the first sample).

Comments

• Anonymous
  March 11, 2010
  Trying to reproduce your code, I have a problem where I set parameters that correspond to slicers in an Excel PowerPivot sheet. They are correctly identified, with their GUID-like name, since I do not get the SoapException I have when tampering with their name. When I call setParameters, the values range I read has indeed changed, but it looks like the values I have set are not taken into account : I get the same results as if I had totally cleared the filters... Also, the performance seems terrible. I tried to add some RAM, and now have 8 GB (2.6 GB used), but it still takes something like 30 seconds to set the parameters. Any insight on this will be warmly welcomed !

• Anonymous
  June 28, 2010
  Hi.  Thanks for this article.  It is very helpful.  We have a situation where we have a lot of reports that we need to get the parameters from programmatically since we won't know the names of the params.  How would go about reading all the parameters of a report?  We will be storing these "preferences" and bringing them back the next time the user loads the report. Thanks