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

Top 5 Contributors of the Month
david stephan
Asad Ali
Post New Web Links

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

View Complete Post

More Related Resource Links

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  

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.

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

SQL PIVOT Table to regular sql table.

Hello, I have a fairly complex SP which produce a pivot table with dynamic columns. I was wondering if there is a way to copy that result table to another one with same columns/rows/data, except that the second table is actually stored in the database rather than just produced at runtime. I tried something like: SELECT * FROM (runtime_generated_PIVOT_Table) INTO (new_Table) but that did not work.... (probably didn't work because of the PIVOT table query, not sure)Steve

Retrieving users from mapping/pivot table

I have three tables.CustomProfileUserID (pk)DisplayNameCompanyIDTeamsTeamRecordID (Pk)TeamIDCompanyIDEmployeesInTeamsUserIDTeamIDI want to assign employees to teams via a gridview control with a templated checkbox.I have a dropdownlist control to select the TeamID.I am having problems with the select statement.The gridview needs to select employees that are not assigned to any teams as well as assigned to other teams. An employee can be assigned to many teams.What I have been trying is:cp.UserID, cp.DisplayName, et.TeamIntID, cp.CompanyID FROM            EmployeesInTeams AS et CROSS JOIN                          CustomProfile AS cp WHERE        (et.TeamIntID <> @TeamIntID) AND (NOT EXISTS                              (SELECT        TeamIntID, UserID                                FROM            EmployeesInTeams                                WHERE        (cp.UserID = UserID))) AND (cp.CompanyID = @companyid)Select cp.UserID, cp.DisplayName, et.TeamIntID, cp.C

Filtering pivot table by KPI value

Hi, All ! I've been faced with following problem. I have a kpi that shows whether the dealer has send report in time. This KPI may return two values 1 - in time, -1 -  not in time. I built a simple pivot table in rows of which are dealer name, in column - KPI and in filter - month name. I'd like  to filter dealers by their status, to see only dealers, who haven't send report in time. I tried to filter in the pivot table by value, but without result. How I can doo it ? I thought may be worth  add a some attribute to dealer dimension that contain a KPI value and filter by this attribute ? Thanks in advance !

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. 

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,





Is pivot table alias an internal reference?


What is the purpose of requiring a table alias for pivot table if it is for internal reference only?

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. how to insert data present in different rows of same collumn of one table into same rows of differn



    I am have task that i have to insert data present in different rows of same collumn of one table into  same rows of differnt collumn of other table below shows the table structure source & distination

    c_date                c_balance

    11/1/2010          100

    11/2/2010          200

    11/3/2010          300

         ----                ----

      -----                ---

    12/1/2010          150

    12/2/2010          300

    12/3/2010          400

       ---                  ----

       ----                  ---

    like above i have thousands of data with other collumns

    now i have the detination structure  as

Excel pivot table



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)


Pivot Table Help


I have the following T-SQL Code:





V_SAMPLE.TEXT3 as [Position],

















OLAP Cube - Pivot Table



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!


How to Add Multiple Column's Sum in Pivot Table...???


hi Fredz..


My Final Table is #Example and in that table m adding Addition of 5 Columns but its not working Properly..???


declare @columnheader varchar(max)

Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)') 

FROM #Example



SET @query = '

SELECT * into fianltable

FROM #Example



 SUM(Col1+ Col2+ Col3Col4 + Col5)     ----In that line error will occur

----If m can change this line n taking only SUM(Col1then its Works, but i want addition of

---- FIVE columns

 FOR [MergeValue]

 IN (' + @columnheader + ')


 AS p order by ' + @columnheader + ''


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