.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

SSAS Excel Drill Thru : Number of Rows Returned don't match up

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

Hi, I have created an excel report which connects to my cube and have created drill thru action. When I double click on data cell in excel, what I would expect is to give details for that cell. For example, I have a cell in excel that represents 1000 Sales made. When I double click on it, it should only show me thousand rows, however for some reason it gives me more than 1000 rows. So basically the cell counts don't tie with drill thru row counts.....

Any idea on how I can rectify this?

View Complete Post

More Related Resource Links

Estimated number of rows does not always match actual number of rows


I am looking at some code that I am writing and keeping an eye out for performance, what i do notice is that on the for left of the query plan by the select/delete, if I place the mouse cursor on the arrow the actual number of rows hardly ever matches the actual number of rows.

Is there anything that I can do to resolve this, I do notice that one needs to update statistics or reorg/rebuild indexes but still the inconsistency exists. Is there a better explanation for this ?


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.

Gridview that exports selected rows to Excel

his seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet.

The number of members in the conceptual type ... does not match with the number of members on the ob



I've added a scalar property to an entity and now ASP.NET Dynamic Data scaffolding throws this error:

The number of members in the conceptual type 'TrainingModel.Users' does not match with the number of members on the object side type 'TrainingModel.Users'. Make sure the number of members are the same.

This happens at the following line in global.asax:
model.RegisterContext(typeof(TrainingModel.TrainingEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Please help!

Restricting number of rows in a set

Hi All, Is it possible to restrict the number of rows which may come in a set in MDX ? WITH SET set1 AS Order ( { Filter ( [DimContract].[Contract Acc].[Contract Acc].MEMBERS , (NOT IsEmpty([Measures].[Consumption])) OR (NOT IsEmpty([Measures].[SampleSize])) ) } ,[Measures].[Consumption] ,ASC ) Say if i have a set like above , then can we restirct the number of contracts which may come in this set ?

Enhance SSAS so that a calculation returns multiple cells at once (Excel fill not a single cell but

I wonder if somebody can help me with the following question. I would like to create an enhancement to SSAS in a way so that one can pass in multiple values, run a calculation and in return retrieves not one but multiple cell results at the same time e.g. retSet = functionABC(inSET) where inSet is somthing like 5 4 3 6 7 3 4 4 AND retSet is 4 6 7 8 9 3 4 4 All my calculated members in MDX return a single value BUT I need to have a calculation that if called returns multiple results (a set) at once and in a way so that Excel will properly pick up on it and consequently fills multiple cells in the Pivot table. Is that possible at all and if so how? I would not mind to write a little CLR assembly to enhance the SSAS but don't know how to pass the results back. Any ideas are highly appreciated. Dirk

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

Font didn't change in excel, after change was made on SSAS

Hello!   After I wrote MDX, I gave to my calculated field a format of “courier New” and font size of ‘9’. When I connected to SSAS using excel, and pulled my data but I don’t see the right format that I used. It is not working for any format. I used 2008 SSAS. What is the issue? Thx, Beney

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.

SSAS 2008 attritube hierarchy doesn't group records and repeat rows

We are having problems with the dimension attributes in lower level hierarchies not grouping under 1 single level. Here is the hierarchy: VP    College      Department         Departmental Course            Course Level               Course Number The top 3 levels are grouping correctly without duplicate rows and they don't have compoud keys. The lowest 3 levels are not grouped correctly and they have compounds keys because the attributes are not unique by themself. The result of the hierarchy looks like this: VP Academic Affairs    Business School of Management        International Business School            Mgm101                 Lower                       Course Number 123            Mgm101                Upper                

Repeat header rows on all pages when you export to excel sheet and print it

Hello , I need to export report into excel sheet. And when people try to print it . The header rows only appear on First page of the print . Other subsequent pages d onot have header rows. I saw another thread saying SQL server 2008 R2 will solve the issue. Can you tell me that is fixed and how to i go about configuring it on SQL server 2008 R2.   Thanks Madhavi

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

Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

ASP.NET Events and Pageload not firing when Datagrid containing large number of rows of data

Hi All, I have a datagrid in aspx page.Inside of datagrid i am using around 15 controls such as Button,dropdownlist and text box controls.Once the datagrid binds then the events in the aspx page not firing.Issue occured when the number of rowsgreater than 500.The number of rows less then its works fine.If anybody knows the solution please let me know."Platform i am working on ASP.NET1.1"Regards Hareesh

Count how many rows are returned from a stored procedure

Hi,   I have written a stored procedure for my database which takes two varchar parameters and returns lots of rows of data. This data will be passed back to my .NET application with a reader for parsing.   Imagine this scenario of calling a stored procedure:   MyDatabase.dbo.sp_MyStoredProcedure 'String 1', 'String 2'   Let's say that this command returns 12,434 rows of data when executed in SQL Server Management Studio Express containing the data of 5 joined tables which contains 2 or 3 unions (depending on the input of 'String 2') and the data within each union is correctly sorted.   How do I get a row count from the execution of the above stored procedure command? I need to pass this back to my application for the progress bar to function correctly.   I don't want to replicate the function and modify it for counting rows because the stored procedure consists of approximately 150 lines of SQL code.   Thanks in advance.   Sean

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.

GridView Pager not showing proper number of rows

I have created an out-of-the-box  (default everything) ASP.NET Dynamic Data Entities Web Site using VS2010, C# and the .NET Framework 4. Created an ADO.NET Entity Data Model based off a MS SQL 2008 R2 database and registered that EF data model for my dynamic data. Everything works great except for the one change I would like to make. By default, the GridViewPager.ascx file provides the values 5, 10, 15 and 20 in the dropdown for GridView's "Results per page" option. I would like to change that to 10, 20, 30 and 40 - with 40 being the default selected option. I went to the code behind file and modified it to have the values I am wanting. Those values now show in the dropdown, and work if I select them. Problem: when the page is first rendered only 10 rows will show. How can I change this? Any help would be greatly appreciated.   Thanks
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