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

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

Filter out nulls in Excel

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server

HI All,


Is there a way to use non empty in excel(which is connected to cube) like the way we use when writing MDX? We have a couple of values that have empty metrics and we wan to filter them out.


Thanks in advance.

View Complete Post

More Related Resource Links

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

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

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

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

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.

Filter Excel Report based on MDX Filter


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  |                                                                  |

Multiselection in Excel filter doesnt work for my calculation

Dear all,

I'm having a problem with one of my calculations. It's working perfecty for one selected unit (e.g. year '2010', or quarter 'Q2' for year 2010, or month 'july' for year 2010).
But, in my report in Excel, I MUST have option to select multiple values of date (e.g. quarter 'Q1' and 'Q2', or months 'january' and'february') because some other of my calculation needs multiple values of date. 

What I need is to tell me how to change my calculation when I chose multiple values of date? Is it even posible?
-if you select five months: 'january', 'february', 'march', 'april', 'may' then I want last month 'may' to be saved in some variable (or if there is some other method, even better) that can be used for making SUM of Amount on period (from begining to latest selected date) described in code below. 

[Date].[Year - Quarter - Month - Date].CurrentMember,
[Date].[Year - Quarter - Month - Date].[Year]

IIF([Date].[Year - Quarter - Month - Date].CurrentMember.Level.Name="Year" 

Filter built in Excel 2003 different from OWC (huge performance difference)



Our customer uses Excel 2003 and the OWC as a client to report on data in a cube. We have defined a specific report (OWC) that the customer can export to Excel 2003 for customization. When running the OWC report with exactly the same filters as Excel 2003 the OWC report seems to runs much faster than the Excel 2003 report.

I have unselected a couple of values in the report and see the following differences in performance:

  • OWC: a query with those filters takes +/- 2 seconds
  • Excel 2003: a query with those filters takes +/- 2 minutes

Using SQL Server Profiler there's a big difference in how the filter is send to the cube:

  • OWC
    Looking at the trace, OWC selects all queues and inbox children, excepts only those values that are unchecked.


                    MEMBER [Tasks Open].[Queue Tasks].[Queue Tasks].[{91609408-F707-4B7A-94E0-9D5C60D8BA74}Pivot49Hier8MultiFilter__] AS


Excel services auto-reapply filter



I am fairly new to SharePoint.
I have created an Excel sheet that contains a filter.
This filter displays only rows containing a date that is in this week and the upcoming week.

I am using this excel sheet in an Excel services web part on SharePoint 2010 Enterprise.
However, the filter is statically applied to the Excel sheet. Only when the Excel file is changed, the web part is updated.

How can I make sure that the filter is always reapplied by Excel services, so the rows displayed in the web part are always up-to-date?
(always display rows containing dates of current and upcoming week)


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.....

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Excel Automation in .Net

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worsheets.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

GridView Multiple Filter AJAX Control

This AJAX control enables the user to filter data within any column inside a GridView. The user can add multiple filters and can delete existing ones too.

Export SharePoint List to Excel Spreadsheet Programmatically using C#

In SharePoint applications, Custom Lists are used to store business data and Document Libraries to store the documents. But for data manupulation and analysis, Microsoft Excel provides very rich features as compared to SharePoint Lists. That's why people still loves to work on Microsoft Excel Sheets.
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