Alternative Way To PIVOT Function?

Jan 14, 2014

We are running SQL2000, and I was trying to use the PIVOT function, as you are all aware 2000 does not support the PIVOT function.

is there an alternative to the PIVOT function

here my code I was trying to use

Code:
SELECT CostSavingsNo, CostSavingType

From (Select CostSavingsNo, CostSavingType, SavingsValue FROM CostSavingsDetails) CSD

PIVOT

(SUM(SavingsValue) FOR CostSavingType IN ([1], [2], [3], [4], [5], [6], [7], [8],[9], [10], [11], [12]], [13], [14])) AS PVT

View 7 Replies


ADVERTISEMENT

Power Pivot :: ALL DAX Function Not Overriding Filter On Pivot Table

Oct 14, 2015

I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.

The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.

The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.

Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]

View 13 Replies View Related

Alternative To Dynamic SQL In A Function

Jan 14, 2008

Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.
I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:
TableName              FieldName                Xpos             Ypos
----------               ---------                -----            -----
FruitTable                FruitName                10                20
VegTable                 VegName                 10                40
FruitTable                FruitColour               20                10  
(Thats not the real data of course)
What I need is a calculated field which returns the value of each field from each table – probably by constructing a dynamic sql statement(?) It would look something like this:
Select @FieldName From @TableName Where bla bla bla â€“ don’t worry about the where clause. The completed dataset will hopefully then look like this:
TableName              FieldName                Xpos             Ypos             FieldValue (calculated field)
----------               ---------                -----            -----            ---------      
FruitTable                FruitName                10                20                Oranges (result of: Select FruitName From FruitTable Where....)
VegTable                 VegName                 10                40                Parsnips (result of: Select VegName From VegTable Where....)
FruitTable                FruitColour               20                10                Green (result of: Select FruitColour From FruitTable Where....)
 
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function:
Main query:Select
TableName, FieldName, Xpos, Ypos,
dbo.GetFieldValue(TableName, FieldName) As FieldValue
From
tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))

RETURNS nvarchar(255)
AS
BEGIN

Declare @SQL nvarchar(max)
Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName

sp_executesql @SQL??

return ???

END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful.  Hope thats clear. Thanks in advance   

View 5 Replies View Related

Alternative To Dynamic SQL In A Function

Jan 15, 2008



Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.


I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:

TableName FieldName Xpos Ypos
---------- --------- ----- -----
FruitTable FruitName 10 20
VegTable VegName 10 40
FruitTable FruitColour 20 10

(Thats not the real data of course)

What I need is a calculated field which returns the value of each field from each table €“ probably by constructing a dynamic sql statement(?) It would look something like this:

Select @FieldName From @TableName Where bla bla bla €“ don€™t worry about the where clause. The completed dataset will hopefully then look like this:

TableName FieldName Xpos Ypos FieldValue (calculated field)
---------- --------- ----- ----- ---------
FruitTable FruitName 10 20 Oranges (result of: Select FruitName From FruitTable Where....)
VegTable VegName 10 40 Parsnips (result of: Select VegName From VegTable Where....)
FruitTable FruitColour 20 10 Green (result of: Select FruitColour From FruitTable Where....)

I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function:
Main query:Select
TableName, FieldName, Xpos, Ypos,
dbo.GetFieldValue(TableName, FieldName) As FieldValue
From
tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))

RETURNS nvarchar(255)
AS
BEGIN

Declare @SQL nvarchar(max)
Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName

sp_executesql @SQL??

return ???

END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance

View 7 Replies View Related

Pivot Function Question

Oct 12, 2007


Below is my query and returned rows. I am pulling unique dx codes for each account. In the case below I have 14 returned rows, each having a unique dx_key (second column), which I need to display across the report horizonaly versus vertical. (see below example)

DXCode1 DXCode2 DXCode3 DXCode4 DXCode5..etc..
20190 5789 5990 2768 2449

I came across the post below by Manivannan.D.Sekaran on using the PIVOT function but I can't quite get the hang of it. Can someone give me some help please?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1776397&SiteID=1



--Query


select

dxo.episode_key,

dxo.dx_key,

dxo.dx_code,

dxo.dx_desc

from srm.cdmab_dx_other dxo inner join

srm.episodes e on e.episode_key=dxo.episode_key

where dxo.episode_key is not null

and e.account_number IN ('11111111230')

order by episode_key, dx_key


--Rows returned from above query

12451538 117782 20190 Hodgkin's disease, unspecified site, extranodal and solid organ sites
12451538 117783 5789 Gastrointestinal hemorrhage, unspecified
12451538 117784 5990 Urinary tract infection, site not specified
12451538 117785 2768 Hypopotassemia
12451538 117786 2449 Unspecified acquired hypothyroidism
12451538 117787 28529 Anemia of other chronic disease
12451538 117788 3051 Tobacco use disorder
12451538 117789 53540 Gastritis without mention of hemorrhage
12451538 117790 53190 Gastric ulcer without hemorrhage or perforation, without mention of obstruction
12451538 117791 5589 Noninfectious gastroenteritis and colitis
12451538 117792 56210 Diverticulosis of colon without mention of hemorrhage
12451538 117793 2113 Benign neoplasm of colon
12451538 117794 V1259 Personal history of diseases of circulatory system
12451538 117795 V011 Contact with or exposure to tuberculosis

View 8 Replies View Related

Advance PIVOT Function In SQL2005

Apr 13, 2007

Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?

I would like to use this for a daily report where the columns would be the dates?

Thanks.

GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

View 3 Replies View Related

SQL Statement Performs Pivot Table Function?

Apr 13, 2006

I have the following data:Product Type Hours Controllers Development 105.0Controllers Research 1.0Controllers Sustaining 24.0How do I use SQL statement to change it to the following?Product Development Research SustainingControllers 105.0 1.0 24.0Thanks.DanYeung

View 2 Replies View Related

Transact SQL :: Duplicate PIVOT Function In 2000

Jul 1, 2015

I have a query that uses the PIVOT function and works fine in SQL 2012.  I've been asked to move the query to a database that has the compatibility level set to 80(SQL 2000).  I receive an "Incorrect syntax near" error when I try to excute the query on the SQL 2000 database.  I would like to duplicate the exiting PIVOT functionality in SQL 2000.The existing query retrieves employee names and the order that the employee should be displayed from a table.  The names will appear on the report according to the order that is retrieved from the database.  Also, the users have requested that only 5 names appear on each row of the report.  This is why the PIVOT function was needed.  Below is an example of how the existing query works.

Table
CREATE TABLE [dbo].[EmpGuest](
 [Guest_ID] [int] NOT NULL,
 [Guest_Name] [varchar](80) NULL,
 [Display_Order] [int] NULL
) ON [PRIMARY]

[code]....

View 4 Replies View Related

Power Pivot :: How Does EVALUATE Function Work

Oct 24, 2015

playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model.  I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.

View 4 Replies View Related

Power Pivot :: Dates MTD Function And No Row Data

Oct 20, 2015

I have a DATESMTD function which is not working.  This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero.  See below my formula:

MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])

Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.

So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula.

View 8 Replies View Related

Power Pivot :: Using CUBEMEMBER Excel Function To Get A Member Value

May 28, 2015

In an Excel workbook I'm building a report using a PowerPivot data model.

I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.

View 9 Replies View Related

Power Pivot :: Switch Statement And Function Calls

Nov 24, 2015

I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.

=switch(HASONEVALUE('s1JudgeIds'[JudgeName]),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])

View 24 Replies View Related

SQL Server 2008 :: Pivot Function - Additional Final Column

Mar 11, 2015

I've managed to use the pivot function using the code below which gives me the following output:

Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2

This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.

[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf

[Code] ....

View 7 Replies View Related

Power Pivot :: RELATED Function Shows Empty Column?

Aug 16, 2015

experimenting with powerpivot and I use an simple example of the AdventureworksDW in Powerpivot.

If i use the RELATEd function with Product and ProductSubCategory the column is empty and I expect values.

Relationships are rightfully defined. What am i doing wrong?

View 3 Replies View Related

Power Pivot :: DAX Function ALLSELECTED Doesn't Refresh When Using A Date Hierarchy For Rows

Apr 3, 2015

I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer.  The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.

This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc. 

View 3 Replies View Related

Power Pivot :: LY Sales - Function Throws Error When Selecting Periods In Different Years

May 21, 2015

I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) .

The DAX code for calculating LY Sales is:

=
CALCULATE (
    [Sales $],
    FILTER (
        ALL ( dCalendar ),
        dCalendar[IRIYearNumber]
            = VALUES ( dCalendar[IRIYearNumber] ) - 1

[Code] ....

The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report. 

The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;

ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected. 

View 5 Replies View Related

&"Pivot&" Function Or Analysis Service For SS2K??

Jun 7, 2007

I am relatively new to SQL-Server. I was working in a company that used SS2005. Now I am with a company and client where SS2K is my only option. I know SS2005 had Analysis Services which allowed building hypercubes but I never got the chance to learn about it or use it. I am not that familiar with what SS2K has.

I have no access to any OLAP tools. I have MS-Access and MS-Excel. My SQL-Server database has to crunch a table with thousands of rows.

Having worked with MS-Access and MS-Excel for a long time, I know how powerful creating a pivot table is. I heard that SQL2005 had a T-SQL PIVOT function but never had the opportunity to use it.

Know I have the need to do this in SQL2000.

I have a lot of data so the method shown in BOL for cross-tab won't help me. The client changes the "column" and "header" categories on a regular basis, so I need something that will allow me to NOT hard code them but choose them dynamically like one would do in a hypercube.

I have more than 5 categories for "row headers". I have currency for the data to aggregate. I have "pay periods" (26 of them) for my "column" headers, but not all 26 will be present until Dec 31, so that part has to be dynamic.


Question:
Is there any feature in the standard S2K that allows one to create anything like a pivot table without massive coding (T-SQL function or technique or something like a hypercube ability)?

Can you point me to something on the web that would explain how to get started?

Many thanks ahead of time from a newbie!

View 1 Replies View Related

SSMS Express: Using PIVOT Operator To Create Pivot Table - Error Messages 156 &&amp; 207

May 19, 2006

Hi all,

In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO

A desired Pivot Table is like:

MW2 MW6S MW7 TripBlank

Acetone 1.00 1.00 1.00 1.00

Dichloroethene 1.00 1.00 1.00 1.00

Trichloroethene 20.00 1.00 1.00 1.00

Chloroform 1.00 1.00 1.00 0.76

Methylene Chloride 1.00 1.00 1.00 0.51

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:

USE MyDatabase

GO

USE TABLE dbo.LabData

GO

SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank

FROM

(SELECT SampleName, AnalyteName, Concentration

FROM dbo.LabData) p

PIVOT

(

SUM (Concentration)

FOR AnalyteName IN ([1], , [11], [16])

) AS pvt

ORDER BY SampleName

GO

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I executed the above-mentioned code and I got the following error messages:



Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'TABLE'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'AnalyteName'.

I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.

Thanks in advance,

Scott Chang

View 6 Replies View Related

Power Pivot :: One Slicer To Control Two Pivot Tables That Have Different Source Data And Common Key

Jul 8, 2015

I have two data tables:

1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost

The tables have a common key named obviously as Key. The data looks like this:

Key
Facility
Line
Time
Output
Alpha

I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.

View 5 Replies View Related

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

Oct 13, 2015

Can I force the following measure to be visible for all rows in a pivot table?

Sales Special Visibility:=IF(
    HASONEVALUE(dimSalesCompanies[SalesCompany])
    ;IF(
        VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
        ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
        ;[Sales]
    )
    ;BLANK()
)

FYI, I also have other measures as well in the pivot table that I don't want to affect.

View 3 Replies View Related

Power Pivot :: How To Apply Min Formula Under New Measure Within A Pivot Table

Aug 17, 2015

How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".

View 3 Replies View Related

Power Pivot :: Displaying Cumulating Numbers In A Pivot Table When There Is No Value

Mar 11, 2015

I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:

1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:

=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))

View 24 Replies View Related

Power Pivot :: Measures Not Reflected In Pivot Table

Sep 18, 2015

I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem. 

What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?

View 3 Replies View Related

Power Pivot :: Difference Between Two Pivot Table Sums?

Nov 23, 2015

I have a data table that contains budget and actual data by month.  I use the data to create a pivot that shows actual results next to budgeted results.  I need a column that shows that variance between those columns.  I think my issue is that the "Type" field contains actual and Budget.  I sum on "Type".  I can't seem to create a sum since those items are in the same field or am I missing something?

Table design

Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100

Output Design

DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt

View 4 Replies View Related

Power Pivot :: Slicer And Pivot Table Value Order

Oct 9, 2015

How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?

I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?

<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102

View 8 Replies View Related

Power Pivot :: Printing From Pivot Table With Slicers

Apr 13, 2015

I am using excel 2010 and creating pivot table from Power Pivot.  I created a pivot table with department slicers.  All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I  click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.

All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data.  How do I resolve this.

Below are the steps I go thru to print 

1. Select slicers in unfiltered position (ALL)
2. Select entire pivot table
3. Select Page layout  and select print area.
4.  Save
5. Click on Print Preview to preview the print
6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.

Do I need any other step? 

View 2 Replies View Related

Pivot Task Error - Duplicate Pivot Key

Jul 5, 2006

I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.

All worked fine, but now I get this error message:

[ytd_pivot [123]] Error: Duplicate pivot key value "6".

The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.

Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

View 3 Replies View Related

Pivot Example When You Don't Know The Exact Values To Pivot On

Sep 21, 2007

Say, I have the following temporary table (@tbl) where the QuestionID field will change values over time

Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2


I'd like to perform a pivot on it like this: select * from @tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable

...however, I can't just name the [1], [2] values because they're going to change.

Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])


I tried something like this:
for QuestionID in (select distinct QuestionID from @tbl)

but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @tbl Pivot (min(Answer) for Question_CID in (select distinct @QuestionID from @tbl)) as PivotTable

or does anyone know another way to do it?

View 3 Replies View Related

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

Jul 8, 2015

Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)

View 2 Replies View Related

Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh

Apr 29, 2015

I have a pivot table that connects to our data warehouse via a PowerPivot connection.  The data contains a bunch of comment fields that are each between 250 and 500 characters.  I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.

However, whenever I refresh the data, the text wrapping un-sets itself.  Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text.  This is very burdensome on the user, and degrading the experience.

Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?

View 2 Replies View Related

Alternative For DTC

Jul 30, 2007

Hi,
i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same

Thanks

View 17 Replies View Related

Alternative Way

Jul 20, 2005

SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC

View 3 Replies View Related

@@IDENTITY Alternative

Dec 30, 2007

Hi everyone,
I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.

Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY")
Proposed solution:
lock(MyDataLayer)
ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X")
if(ID==null) ID=1
MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)")
unlock(MyDataLayer)
(This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)

Do you think the preceding solution is equivalent to the original?
Do you know something better?

Equivalent should mean here, not necessarily generating the same ID's,
but maintaining functionality and consistence all over the database.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved