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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Analysis Services 2008 e Excel pivot table: different filters for each column

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

I have to create a report in excel 2010 based on a  analysis services 2008 cube.

I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

I think this thing is not possible in Excel (right?)

So I decided to use a mdx script to filter each product 

I tried so (for example, product A):
scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
[Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
end scope;



I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?



View Complete Post


More Related Resource Links

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

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

  
Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

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,

Nikki

 

 

 


PIvot Table in SSRS 2008

  
Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

Help!!!! Permission issue with Analysis Services on Windows 2008 R2

  
Hi I'm with a project using Sql Server 2008 on Windows Server 2008 R2. It seems I can not connect to Analysis Services server with SA acount but only with Windows Authentication (The 'Authentication' option is diabled). And when I connect to Analysis Services with my windows acount , I do not have the permission to either create a database (ERROR:Either '***' user does not have the permission to create a new object, or the object does not exist) or grant server privilege to my windows account (ERROR: Only an administrator can make changes to server properties). Also, I failed to remote connect to Analysis Services on Server with my windows account (But I can remote connect to Database engine). Any idea?  Thanks!

Excel 2003 pivot table - parent showing grand total than the sum of its children

  
All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

SQL Analysis Services 2008

  
Can any one help me for... How to create a new instance of SQL Analysis Services? I am  using SQL Server 2008. Thanks, Faheem Ahamed

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

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

SP2010 + Reporting 2008 R2 + Analysis Services 2008 R2

  
I want to create a solution based on sp2010 in which the users can create their own reports with Report Builder and have an Analysis Services cube as the data source I did is the following : i have activated the Site collection features for reporting, i have created a data source which is the cube, i've created the model and also added the reporting document types to a document library and now i am ready to create reports The problems comes when i want to create this from a location outside the server. It doesn't work and gives me the following error : Semantic query execution failed.  (rsSemanticQueryEngineError) ---------------------------- Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted) The location from where i want to access is a computer from another domain but i log in with a SP2010 user and also pertaining to the SP2010 domain. This user has Member Site rights for the Report Model, dataset, report. DataSet1 is the default name assigned by the Report Builder. The issues appears if I define the dataset before the report or during the report creation with the Report Builder. The authentication for the data source is windows authentication, the permissions for the model are inherited from the site, and the dataset credentials are set to Use

Analysis Services 2008 R2 Deployment - very frequent database overwrites

  
When regularly deploying an Analysis Services project from Visual Studio / BIDS to Analysis Services 2008 R2, the database must frequently be overwritten - on almost any change. For example, after a successful deploy and process, a seemingly minor change is made (e.g. changing the FormatString property for an existing Measure, or Arrange Tables in the Data Source View), and the next deploy and process attempt triggers the message: The 'ZZZ' database on 'ZZZ' has changed since the last time the project was deployed. If you proceed with deployment, the database will be overwritten. Would you like to continue? With earlier versions of SQL, this only occurred rarely, e.g. when deploying to a stale server. The impact is that testing almost any change requires a complete re-process of the database. Is anyone else striking this issue?  Thanks Mike

Uploading Excel 2007 table into WSS 3.0 - How does SharePoint decide the column that is used to edit

  
Hi, In Excel 2007, there's an option to upload (export) an Excel table into SharePoint.  The range of cells have to be identified as a table.  In the Table ribbon, there's an Export button.  When I upload the Excel 2007 table, SharePoint (WSS 3.0) decides which column in the table will be linked to the properties dialog box for each line in the table.  It seems to be a sporadic decision on the part of SharePoint. Does anyone know how WSS 3.0 decides which column will be linked to properties?  And, is there any way to control this when uploading the file? Thanks!Thanks! Patti N.

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. 

Cannot start Analysis Services 2008 service

  
Hi! I have Sql Server 2008 R2 and Analysis Services 2008. I could use it without problems for few days, but today, when I stop analysis service with Sql Server Configuration Manager and tried to start it again, it gives me error message: "The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details". The msmdsrv.log file doesnt have any entries related to this error. Both Sql Server and AS are installed on my local machine and log on as LocalSystem. My system is XP sp3 professional.

Excel services - web service and filters

  
Is is possible to use filters against excel services web service. I mean I have Pivot table in excel file. I would like to get this workbook by Excel Services web service and set filters.

Cannot Export SQL 2008 Table to Excel 2010

  

I'm trying to use SSIS in Visual Studio 2008 to export a table to Excel 2010 and get the following error:

=====================================================


The table cannot be created. (Microsoft Visual Studio)

===================================

Syntax error in field definition. (Microsoft Office Access Database Engine)

------------------------------
Program Location:

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at Microsoft.DataTransformationServices.Design.PipelineUtils.ExecuteSqlStatement(ConnectionManager connectionManager, String sqlStatement, IServiceProvider serviceProvider, Boolean getReader)
   at Microsoft.DataTransformationServices.Design.DtsTableCreator.okButton_Click(Object sender, EventArgs e)

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