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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Excel pivot table report filter selections from a cube

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
 
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. 


View Complete Post


More Related Resource Links

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

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

 

 

 


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.

Users use Excel-Pivot table to query the cube, how to capture those queries?

  

Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?

 

Any suggestion/help is very much appreciated,

P


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


excel pivot table linked to cube, unable to expand/collaps single field

  

I’m re-posting question. I’m using excel pivot table to browse a cube, for example the adventuresworks example. I drag dimension ‘Product Categories’ and ‘Sales Territory’ to Row Labels area, a date dimension to column, and any measure to Values area. Now I only want to expand ‘Europe’ under ‘Bikes’, but the excel  will expand ‘Europe’ under every ‘Product Categories’, is there any way to avoid that? In visual studio it works fine.


SSAS Cube & Pivot table

  
Hello, I am connecting to my SSAS cube through excel Pivot table. I have the following questions: 1) I have a filter for Company. However, instead of selecting multiple companies from a drop down list, I would like to have a range e.g. Company between 100 and 200. We have this "Filter expression and operator" option in SSAS but I dont see in excel. 2) I have 90 users who will be using my cube via pivot tables. How do I automatically have the data in excel refresh automatically such that when the user comes in, in the morning and open the pivot table, they have the refreshed data. Instead of them manually refresh it as it runs the query for a long time. Thanks in advance for all your help and suggestions! KJ

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

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.

Excel pivot table

  

hi,

I have an Excel file with Pivot Table connected to MS SQL Analysis Services.

Is it possible to programmatically get this Pivot table (refreshed data) and save it as image (gif, wmf, emf, etc)

 


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.

Scenario:

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

OLAP Cube - Pivot Table

  

Hello,

I have a cube that I am accessing through excel 2007 pivot tables. I have a field that shows values with reverse signs. The negatives show as positive and the positive show as negative. Is there a way in Pivot table to reverse the sign? I can create a formula to do this, but I want this calculated column to appear in the middle of pivot table columns as the client would like a certain order of columns. Any ideas?

Thanks in advance!

KJ


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

  
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?

How to automatically refresh pivot table in excel web access?

  

Hi,

I have created UDF which takes parameters and returns data from the SQL back end. On the data returned to excel, i have created a pivot table and pivot chart. When i pass in the parameter from excel web acess, the data in the pivot table and chart dosent gets refreshed until i click on "Refresh All Connection" in excel web access webpart. Is it possible to refresh the pivot table automatically wheh i pass in the parameters from excel web access webpart?


mallikarjun

filter table data to report

  

hello,

I have a table which captures the updated data. I need to generate a report on updated fields.

Could you please tell me how to filter the updated data from the table to display in the report.

I'm new to this so i need the sql code or stored procedure to filter and show only the updated data.

 

Well I have time stamp as well as a column for last changed date.

 

i need to produce all the fields pertaining to the updated cell in a single cell of the report.

for instance i change the last name of the person - i need to produce the persons firsname , updated lastname, his address and other details in a single cell of a report.

 i also need to show the records in both ascending and decending way - like most recent update of the day and first update of the day.

so how do i code for this.

 

thank u for your help


filter the data from a table to a report

  

hello,

I have a table which captures the updated data. I need to generate a report on updated fields.

Could you please tell me how to filter the updated data from the table to display in the report.

I'm new to this so i need the sql code or stored procedure to filter and show only the updated data.

 

Well I have time stamp as well as a column for last changed date.

 

i need to produce all the fields pertaining to the updated cell in a single cell of the report.

for instance i change the last name of the person - i need to produce the persons firsname , updated lastname, his address and other details in a single cell of a report.

 i also need to show the records in both ascending and decending way - like most recent update of the day and first update of the day.

so how do i code for this.

 

thank u for your help


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