.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

Range or List Filtering via SSAS cube in Excel 2007

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi All, A client has posed an interesting question, as current users of business objects webi they can filter the results of a cube/universe by copy and pasting a range or list of values seperated by a ';' into a list filter that is availble. e.g. product2;product533,;product029;product8389 etc etc. The list can sometimes be hundreds long. How is that same function acheived in browsing a ssas cube in excel 2007, all I can see is that a user has to use the drop down list and individualy select the values they want. This does not seem like a great method! Any ideas? They are using SQL / SSAS/ SSIS/ SSRS 2008 R2 Cheers DC

View Complete Post

More Related Resource Links

Cannot List SSAS Catalogs in Excel 2007

I am trying to access SSAS 2005 through excel on a client computer.  I get the following error message:  The data connection wizard cannot obtain a list of databases from the specified source.  It looks like I connect, but I cannot list any of the catalogs.  Both catalogs are migrated from a sql2000 database.    I ensured that the account has a browsable role through the roles in SSAS.  Interestingly if I create a UDL file with a connection to ssas and choose existing connections and specifiy the udl file I can access the specific ssas catalog.  In creating the udl file I cannot see the catalogs, but I can type in the catalog name which connects me and allows me to browse the cube using excel.  Unfortunatley, my users use other applications besides excel which don't allow access through udls.  Any help would be appreciated.

Excel 2007 connectivity to SSAS 2005 cube

Hello - I am using an excel 2007 odc file to connect to an analysis services 2005 cube (on windows server 2003 R2 OS). I receive the following error message   "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server" Have already attempted the following fixes but running out of ideas now 1. Added localeIdentifier to ODC file    <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=bisql;Initial Catalog=SLAM ACT BUD; LocaleIdentifier = 1033 </odc:ConnectionString> 2. Ensured that all dimensional attributes which are used in MDX calculations (named sets, calculated members) are set to "AttributeHierarchyEnabled  = True" Running out of steam so any help is greatly appreciated.

Exporting MOSS 2007 List to Excel 2007

When doing the export, the columns "Item Type" and "Path" are automatically displayed in the sheet.  Is there anyway to suppress these fields from exporting automatically?  I'm sure this has already been asked before, but I couldn't find anything after much searching (perhaps I'm using the wrong keywords).  Any help would be greatly appreciated.

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

Excel 2007 Cubeset function needs to pull list based on part of field value (ie contains, wildcardin


I have a Cube with a field called 'Item Code'.  This field is six characters in length.  The first two characters differentiate what kind of product it is.  I am building my reports with cube functions.  I do not want to use a pivot table to retrieve the list by using the 'contains' or 'begins with' label filters.  Is there a way to use the cubeset function with left, mid, right functions?  I realize that it can be set up in the cube as a separate field, but I am not able to update the cube.  Below are my examples - 'All Products' and 'Product R3189S' work fine, but I need to retrieve 'Products starting with R3'.

=CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].children", "All Products")

=CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].[R3189S]", "Product R3189S")

= CUBESET("Financials Sales Cube", "[Product Dimension].[Item Code].[All Product Dimension].[R3****]", "Products Starting with R3")

Thank you in advance.


SSAS 2008 R2 Cube being queried by Excel 2010 Pivot Table - 60x slower than with Excel 2003!


SQLServer 2008 R2

Excel 2010 x64

Hi All,

I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.

This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds

I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!

I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.

Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!

I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.

I've posted this in the Excel forum too.

Please can anyone help?

Many thanks,





Excel 2007/2010 - SSAS 2008 R2 Offline cubes


Hi all. I have a question regarding offline cubes (offline data files) in Excel 2007/2010 connecting to SSAS 2008 R2. When I try to make an offline data file in Excel I get a OLE DB error. The server I am trying to connect to is at a hosting partner on a different (but trusted) domain but I have admin rights on it. I then took a copy of the SSAS database and moved it to my own pc and could then create offline data file. I thought it might be a firewall issue on the server and tried turning it off, but the OLE DB error came again. I therefore think that there is something that happens over the network and is blocked by the hosting partner. I probably should tell that I am also SSAS admin, so it is not because of lack of rights on the SSAS that I get the error.

What I like to know if there is anyone who knows this problem or knows what happens when you try to create offline data file against SSAS in Excel? Specifically what is transferred between the client and the server, what ports and are there any programs run or code executed to create the offline cube on the client that might be blocked by typical network/domain settings/rules?

SSAS KPI stauts in excel 2007


My question is about the useage of a KPI status icon in excel 2007.

I have created a KPI in SSAS which will calculate the difference of two measure which is Actual FH FC ratio and Average FH FC Ratio..
These two measures combined with the KPI value AND the Date dimension gives only those records back for that fact and Date. IN this examples it will return from the year 2006 thry 2009 facts (DimDate is from 19300101 thru 20301231) and this is what i want and get in excel. again the status of this KPI is unchecked from the PivotTable Field list.


How to intercept an Excel PivotTable call result from an SSAS cube?


We have a SSAS Cube (2008 R2) on a server, which end-users are viewing with Excel 2010’s PivotTable interface.

We now want to stop end-users dynamically from drilling down to sensitive data.

Specifically, we want to dynamically block the return of any pivot table in which any element has a count of less than k  (and ideally: an error to be displayed ‘query results would return sensitive data – please modify your query’)

This feature should be implemented on the server-side cube not at the level individual end-user's Excel (e.g., Excel add-in).


With Excel pivot table client using SSAS cube, how to change the measures to rows?

I want the measures as rows instead of columns in Excel pivot table on a SSAS 2008 cube. How do I do that? Thanks.

SSAS 2008 data refresh problem in Excel 2007



My problem sounds like that:

I have some cube created with SSAS 2008 and I'm using Excel 2007 as browser to view cube data.

I made pivot table with nessesary data, everything works fine till I save excel file and sent it to my client.

When client uses some filter, some row measures disapear. Dimension data are shown, measures are blank cells, but calculations from those measures is filled.

e.g. I see difference of sales between years in percent, but I can't see measures, which were used to count that difference, because in excel I see blank cells.

Any ideas?

Poor Performance when filtering using Excel 2007 vs Excel 2010.


I've have a cube with a flat dimension called portfolio which is simpy a number.  Right now there are approx 15,000 members in the dimension. The cube is running on a 2008R2 box with approx 20GB RAM and is partioned along a different dimension (Month) and we are using Excel 2007/2010 to browse the cube. 

Today we noticed that if you include the portfolio dimension in the filter of pivot in 2007 Excel and try to select a portfolio to filter (drop down the portfolio list that is), the list of portfolios never returns and eventual hangs Excel.  When we try this same thing in Excel 2010, no problem.  Is a flat dimension with 15K member too large for excel 2007 to process?  Is the mdx different?  We are going to try to trace both and look for differences.  Anyone else notice this issue?


Linked Cube - Issues with filtering members through Excel


I've got a linked cube, which is pointing to a MOLAP cube. (SSAS 2008)

The cube has a parent child hierarchy on one of the dims.


In excel I create 2 pivot tables for balancing purposes:

1 - Pointing to the normal cube.

2 - Pointing to the linked cube (which is in turn pointing to the same cube as in 1).


Now I included a measure, and a filter which is fine.


The problem I have, is when I add the parent-child dim to rows on both pivots and then try and filter to select only 1 of the members the pivot ignores me and returns all of the members.


The strange thing is that it works perfectly on the pivot that is based on the normal cube.

But it won't work on the linked cube.


Does anyone know what's going on?

Initially I thought that excel had some kind of bug, but then why would it work on a non-linked cube?








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.

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Excel 2007




I want to develop an application which supports Server Side Excel Automation using a template(xltx). I am able to acheive most of the automation except(using excelpackage.dll - OfficeOpenXml), i am stuck up identifying the checkbox controls in my excel work sheet.

Any help on this is really appreciated.


My sample code.




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