.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Filter Excel Report based on MDX Filter

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :SharePoint

Hello guys!

Im in trouble when trying to use a MDX Filter to filter an excel report.


I created an excel report and in the pivot table I added a filter based on one Attribute, and set that to me a parameter. I published it at the site document library.
Now with sharepoint designer i create an MDX filter that uses an SSAS Assembly that i created to return the Member Key Set(EX: [Seller].[ID].&[5]). The filter do work right alone, like when i create one dashboard and add two web parts, one for the mdx filter and other to the report. Both of them works nice alone but the excel parameter field dont get the value of the filter.

Example of dashboard:

| ID <5>(here it shows the value requested by the mdx)   | Filter Web Part
|  ID  | All  |                                                                  |

View Complete Post

More Related Resource Links

Excel pivot table report filter selections from a cube

There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.


I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.

how to put a FILTER on whole pivot excel report?


Hello guys,

                  I just created total 8 excel pivot reprots from external sql analysis sources. Now i need to deploy those reports on SHAREPOINT SERVER, Now manager wants to me to put filter on top of all reports so if end user wants to see perticular reprot, for example NO.1 then he/she can click on that and can see only that reprot of if he/she selects all from there then, can see all the reports. How can i do that thing ? Kindly help..I'ts very urgent.....

How to filter the search result based on a column value?

I do have search scope scoped at a list which contains attachment. I will use this scope to search (full text search) on this list then I need to filter the result based on a column which is in the same list. How do I do that? Thank you.

Excel 2007 Filter on multiple values

Hi Everyone,How do I use Excel 2007 Pivot Table's label filters/value filters to filter with multiple conditions? for example, I want to get all customers with customer name start with "G" and doesn't include "E" and with measure>100 and measure between 50-60.Regards,George

report builder 3.0 How do I set a dataset property filter to null

seems to be every other imaginable option ...  "NULL" is not accepted, [NULL] returns an error ... this should be obvious!

Error when programmatically connect filter webpart with excel webpart

Hello, On MOSS 2007 SP2 64bit I am trying to programmatically connect a SPSlicerTextWebPart with an ExcelWebRenderer but I am getting the error: "The connection point "IFilterValues" on "g_046273fe_af27_4064_86b1_70f2c57c326c"(the excel webpart)   is disabled" and I can't find a way to enable it... I'm using the folowing code: using(SPLimitedWebPartManager webPartMgr = web.GetLimitedWebPartManager("Stats.aspx", PersonalizationScope.Shared)) { Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart filterWebPart = (Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart)webPartMgr.WebParts[0]; if (filterWebPart.ConnectionID == Guid.Empty) { filterWebPart.ConnectionID = Guid.NewGuid(); } webPartMgr.SaveChanges(filterWebPart); ProviderConnectionPoint providerCP = webPartMgr.GetProviderConnectionPoints(filterWebPart)[0]; Microsoft.Office.Excel.WebUI.ExcelWebRenderer excelWebPart = (Microsoft.Office.Excel.WebUI.ExcelWebRenderer)webPartMgr.WebParts[1]; if (excelWebPart.ConnectionID == Guid.Empty) { excelWebPart.ConnectionID = Guid.NewGuid(); } excelWebPart.AllowConnect = true; webPartMgr.SaveChanges(excelWebPart); ConsumerConnectionPoint consumerCP = webPartMgr.GetConsumerConnectionPoints(excelWebPart)[2]; TransformableFilterValuesToFilterValuesTransformer t = new TransformableFilterValue

Olap parameterized report using a calculated member as filter

I'm in the following situation : olap parameterized report - using an Analysis Services data provider  - with 4 filters and parameters.   One of the filters - Time dimension and one of its hierarchies - includes 2 calculated members called:  Primary and Secondary, which basically describe a time interval of a day based on some business rules. An Excel pivot tabel retrieves data without any problems. But a SSRS 2008 matrix report fails when picking up one of the calculated members , as it does not support it, just like olap browseren in AS 2008 does not support filtering by any calculated member. So it's difficult to find a good explanations to the users ... I'm not quite sure that using the ole db provider for analysis services in stead of analysis services will solve my problem. That's why following question : Are you absolutely sure that using the ole db provider for analysis services will solve my issue ? If not, is it any other work around ? If yes, are there any tips and tricks of using / typing within the ole db provider for analysis services editor when using parameters. ( I tried it once without any succes and found it quite complicated when it comes to handling expressions with parameters). Thank a lot for your answer. Best regards, Mihai   

filter a dropdown based on a check box value

Hi I created a dropdown in the infopath that will lists all the ID's from a sharepoint list. Then I added a checkbox in the form. When a user selects/checks the check box, the dropdown list should display only the student ID's Sample list I used ID    category 1      student 2      master 3      student 4      student 5      master How to do this. Any ideas, I am new to infopath 2010.

Choice Filter Web part and Excel Web Access web part

I have a choice filter web part where in I have months like Jan,Feb,Mar etc and a excel web access web part. The choices in the filter web part should determine the URL of the workbook for the excel web access web part. In the filter web part I gave the choices as Jan;http://sitename:port/ExcelList/MyExcel.xlsx where "ExcelList" is the library that stores the excel files for each of the months. But the excel web part always says that "The file selected couldnt not be found". Is it possible for me to know the actual URL getting passed to the excel web access web part? Pl suggest if there is a better way to do this. Cutloo

How do you filter a list in a Meeting workspace based on the meeting date?

I have a customer who is using a meeting workspace, and has a list on said workspace (we'll call it "Meeting Instance List"). The workspace houses this list, and of course after each meeting, the workspace, in essence, resets itself in preparation for the next meeting - so it looks like it removes the items from the list, when in actuality, they are there, just on the previous meeting instances. Some of his stakeholders are finding this limiting because they perceive it as losing historical information (you know how some folks can be, if they don't see it, they think it's gone). His solution is to replace the non-series list, with a series list, and incorporate legacy records from another older list. Only problem is, now there's not a way to filter the series list so that it shows items from a parcticular meeting date. For instance - the [Today] variable will only return a current system date value. Surely there ought to be a similar variable that would all you to filter the list based on meeting date, right?

Report Builder 2.0 Filter Dataset NOT LIKE

I need a NOT LIKE filter in a dataset in ReportBuilder2.0. There is only a LIKE Operator to filter a dataset. The users who worked at the end with the ReportBuilder does not write custom code, iif or something else. Is there an easy way?

Parameter value filter based on user in SSRS (SharePoint Integration mode)


Hello..we have a reuirement where we have to filter the parameter value in the drop down based on the user who is accessing the report. We have form and Windows authenication enabled in the SharePoint site (some users will be from inside the network using windows and some will be accessing the site using internet using the Form Authentication).

Please let me know what would be the best way to handle this reuirement? Do we need to create a separate table  and store the User Information along with the parameter values they have access to, pass the userid from Report to db and then fileter the records using this table? OR Can we create a SharePoint List to maintain the user access information and then directly fetch the user information from the list using T-SQL to filter the records?

Please let me know your suggestion...


filter condition on dates report builder 1.0


Hello I am using reort builder 1.0 . I am connecting to oracle database using a report model

I would like to get data from the tables.. the condition in pl/sql looks like this how can we convert it into .. report builder.. functions or should i create query in report model itself and use it in report builder




Thanks in advance..

Best Practice for connect SSRS Report Viewer Web part with custom filter web part (With Dropdown\Lis


To make a filter provider web part, I implemented the interface ITransformableFilterValues.
Implemented the various properties of the ITransformableFilterValues interface
and method that creates an instance of our filter provider.

[aspnetwebparts.ConnectionProvider("AccountFilter", "ITransformableFilterValues", AllowsMultipleConnections = true)]
public ITransformableFilterValues SetConnectionInterface()
return this;

Is this a best practice .Please share your suggestion.


Best Practice for connect SSRS Report Viewer Web part with custom filter web part (With Dropdown\Lis


To make a filter provider web part, I implemented the interface ITransformableFilterValues.
Implemented the various properties of the ITransformableFilterValues interface
and method that creates an instance of our filter provider.

[aspnetwebparts.ConnectionProvider("AccountFilter", "ITransformableFilterValues", AllowsMultipleConnections = true)]
public ITransformableFilterValues SetConnectionInterface()
return this;

Is this a best practice .Please share your suggestion.


Is there a way to query entity based on multiple filter criteria? WCF Data Services, Linq to Entiti


Instead of:

DW_CMSOPEN dwc = new DW_CMSOPEN(new Uri("http://acctdev02/WCFDataService/EmployeeService.svc"));

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

var employees = from emp in dwc.Employees 
             where emp.DEPT == "123"
             select emp;

I'd like the linq query to resemble:

var employees = from emp in dwc.Employees
              where emp.DEPT // in {"123", "456", etc}
              select emp;
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend