Using SetParameters Web Service to modify slicer value in PowerPivot file

Question

I guess everything is said in the title : is it possible to use the Excel Services web service method called SetParameters to modify the values of a slicer contained in a .xlsx file with PowerPivot published in SharePoint ?

Practically, how can we create a parameter on the slicer, if it is possible ?

Thanks in advance for any help.

Answer 1

We will get back to you on this

Answer 2

I have a bit more detail to make my question clearer. I solved the second part of my question by publishing the PowerPivot from Excel to SharePoint : it then provides options to create parameters, that one can map onto the slicers. Back to my client code, I can use the SetParameters to call the Excel Web Service, and I got the right names for the parameters. Indeed, if I use some random name for the filter, I get a SoapException, whereas if I use the strange GUID-like names I found in the slicers settings, I do not have this exception. So, for this part, it is OK.

Now the real problem is that, when I set parameters with the code below, the results I get have been modified, but they are the results I would get if I had not set any parameter (ie as if I had clicked on the remove filter icon on the slicers in excel).

                ES.ParameterInfo param = new ES.ParameterInfo();
                infos[0] = param;
                param.Name = "SC3207abcf2d874e4f9658e35a79d9d6e9----";
                param.Values = new String[1];
                param.Values[0] = "COUCOU";
                param.Values[1] = "BIDULE";
                param.Values[2] = "MACHIN";

                param = new ES.ParameterInfo();
                infos[1] = param;
                param.Name = "SC4944d2572f3443edaea21166ffe86c95";
                param.Values = new String[1];
                param.Values[0] = "2005";

I guess I am wrong in the way I pass the values to the filters...

Also, I seem to have a performance problem : when using an Excel client program, changing the values take one second, at most, to refresh the results. When I use the above method with a .xlsx file under SharePoint, it takes 15, then 25 seconds the second time. I am investigating the machine, because it "only" has 4 GB. But since the file is about 100 MB, that still sounds surprising... Maybe it is related to the way I send the values, which is apparently wrong ?

Again, thanks in advance for your time.

Answer 3

What about test your client program with a regular Excel file published to SharePoint server, instead of PowerPivot workbook file? Would your program be able to set the slicer in the regular Excel file?

Thanks,
Lisa

Answer 4

When you published the workbook did you expose those slicers as Parameters?

To expose slicers as parameters see bring up the publish dialog from Excel client and use the Excel Services Options button. In the dialog that comes up go to the Parameters tab and use the Add... button and select what slicers you want to be exposed as parameters. After that you can access them using the name that is shown in that dialog to set them progrmatically.

HTH,
Ovidiu Burlacu

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 5

Re. Lisa

I can try to do this on a non-PowerPivot Excel, to see if the problem comes from there, but my goal really is to access PowerPivot results from a remote client, so I will have to address a PowerPivot Workbook in the end...
I did not know slicers were available outside PowerPivot, but I will try this as soon as I can to see if it changes anything. I am on a training course this week, so that may be a bit long, but I will keep you posted with the results.

Cheers,

Answer 6

Re. Ovidiu

Yes I did. I guess you just saw the first part of my message, but I sent some more details in a second part, and this is precisely what I did. I had some problems with copying the GUIDs (no copy-paste available), but in the end, the name I used is correct, as I get a different error (namely, an exception), if the name is incorrect.

The problem is that, even with the correct web service call, the slicers are not set...
Thanks in advance for any further help.

Answer 7

Back on track...

I tried using a slicer with a non-PowerPivot workbook, but got the following message :

"This type of workbook connection cannot be used to insert a slicer. To insert a slicer connected to a PivotTable using this workbook connection, select the PivotTable and then insert the slicer."

So, I added a PivotTable pointing to an SQLServer connection with a simple table. I was then able to add the slicer, then publish the workbook under SharePoint, without forgetting of course to use the options to associate a parameter to the slicer.

I used the same kind of source code with the setParameters web service, and it works fine !

So, you put the finger on the right point of failure : the setParameters Excel Web Service works fine on a slicer that is associated with a PivotTable, but not on a PowerPivot. Maybe something coming from the strange names that are associated with the slicers when they are generated by PowerPivot ???

Thanks in advance for your conclusions.

Answer 8

Back on track...

I tried using a slicer with a non-PowerPivot workbook, but got the following message :

"This type of workbook connection cannot be used to insert a slicer. To insert a slicer connected to a PivotTable using this workbook connection, select the PivotTable and then insert the slicer."

So, I added a PivotTable pointing to an SQLServer connection with a simple table. I was then able to add the slicer, then publish the workbook under SharePoint, without forgetting of course to use the options to associate a parameter to the slicer.

I used the same kind of source code with the setParameters web service, and it works fine !

So, you put the finger on the right point of failure : the setParameters Excel Web Service works fine on a slicer that is associated with a PivotTable, but not on a PowerPivot. Maybe something coming from the strange names that are associated with the slicers when they are generated by PowerPivot ???

Thanks in advance for your conclusions.

EDIT, 30 minutes later :

In order to be sure, I made another test, by creating a PowerPivot workbook that would be as close as possible to the PivotTable I just made to answer your question. And this confirms the previous conclusion. I have exactly the same data from the same source, with the same slicer with the same value selected. I publish on the same SharePoint server. The result : web service setParameters works fine if there is a PivotTable, and does not work with a PowerPivot.

EDIT, another 15 minutes later :

I tried inserting a Slicer from Insert instead of from the PowerPivot Task Pane by dragging a field in the Slicers Vertical zone, and this allowed me to use a more user-friendly name (in my example, "Slicer_typaxe" instead of a GUID prefixed by "SC"). But it did not change anything in the result : with PowerPivot, setParameters does not work.

Again, I will be glad with any direction you can provide. I can make some more test here if you have ideas on a workaround.

Answer 9

I'm checking into this.  In theory I believe you should be able to do this with a PowerPivot workbook... Unfortunately there are no examples of this (as you have found) I'll let you know what I find.

Answer 10

I'd love a code sample when you have one available.

Answer 11

Hello,

Any news on this subject ? We are building quite a big project on that technology and this bug is a show stopper for us. The deadlines are coming, so if there is a need for us to escalate to another bug tracking system, please tell me, so we can do what is necessary to get this fixed. Thanks in advance.

 

JP Gouigoux

Answer 12

Again, any activity on this issue ? I am installing a final version to see if it is getting better, but would very like to have some feedback on this problem.

 

JP

Answer 13

Hello,

There is one above in this thread. Please tell me if this is not enough and you want also all the code around, but this is just copied to any web page on Excel Services.

JP

Answer 14

Well, since there is no answer or even thread activity from Microsoftees, I am going to deliver some information found by myself : the bug I talked about is solved in the final release of SharePoint / PowerPivot. I reinstalled the complete plateform with all the non-beta versions, and the behaviour is now normal. Also, the slicers are now called with human-readable names instead of GUID-like non-copiable generated names.

One question, though : what is the point in raising beta bugs to Microsoft if Microsoft does not communicate in return when the problem is solved ? Why has there not been any feedback from Microsoft on this thread ? I certainly will be less motivated in sharing issues next time...

Answer 15

JP Gouigoux,

 

Can you post a sample of your code?  I am still not able to get this to work.  Also are you able to read the slicers from a report? What I am trying to do is allow a user to save his/her preferences.  The preferences get stored in a db and the next time that user opens the report, it brings back the preferences it saved.

Thanks,

Rhonda

 

Answer 16

Anything on sample code for this?  Please help.

 

Rhonda