Regarding: Filter Limitation of Excel Services (or Sharepoint )

Question

In my Excel report , i am populating data from cube.

Actually, “Sales Order number” column has  more than 50,000 distinct records in the source.But if we place this under ‘Report filter’ in ‘Pivot table Field List’, it shows only first 32000 values .That means, the limitation for filters (prompts ) in Excel 2007 is 32000 eventhough it has more than 32000 values (that’s the reason it shows “Not all items showing” in the drop down window).

Now, my question is,

Ø  If I publish this Excel 2007 report in ‘Excel Services’ of Sharepoint, when users open the report, will they see all the 50000 records of the “Sales Order Number “ column in the filter area or only first 32000 values like the Excel 2007 we published in the Sharepoint?..

       > Suppose, if it shows 32000 or all the values, it may hit the perfomance.Do we have any workarounds to improve the performance?

Answer 1

I've just deleted two further copies of this.

*Never* post any question more than once. That includes not posting the same question to different forums.

Whenever you post the same question more than once you waste everybody's time (and especially the time of people who answer you when someone else - in another forum - has already answered you.

---

WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx

Answer 2

It displays only first the 1000 items and message "Additional items hidden..." in Excel Service in my testing environment.

Answer 3

Thanks GuYuming!!

Is there anyways to increase it?...Actually I want to show all the values or atleast 5000 value...Do we have any workarounds?..

Answer 4

As a workaround, you can add a new attribute in your Sales Order dimension, the value of which can be the numeric part of sales order ID divided by 100. And you can build a dimension hierarchy with this added attribute in a higher level than the Sales Order ID attribute. This way, instead of having 50,000 sales order ids under the “All” dimension member, you separate them into 500 groups with 100 members in each group. In the report filter, you can first expand the group of the sales order id and then select the specific one.

 

Besides create an attribute of SalesOrderID/100, you can use any other way to group the sales order ids so that they can be easily found.