Can I Use PIVOT / Cross Tab For This? If Not...?

May 8, 2008

I have the following data structure (simplified)


declare @log table (
date smalldatetime,
category char(3),
value1 int,
value2 int)

insert into @log(date, category, value1, value2)
select '2008-01-01', 'ABC', 11,12 union all
select '2008-01-02', 'ABC', 35,53 union all
select '2008-01-03', 'ABC', 38,62 union all
select '2008-01-05', 'ABC', 59,95 union all
select '2008-01-02', 'XYZ', 42,21 union all
select '2008-01-04', 'XYZ', 9,7 union all
select '2008-01-05', 'XYZ', 89,45 union all
select '2008-01-01', 'HHH', 70,52 union all
select '2008-01-03', 'HHH', 3,83 union all
select '2008-01-05', 'HHH', 26,77


where
1) date is always up to the day (no time variation)
2) date and category can be considered a composite unique key

Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:

date abc_value1 abc_value2 xyz_value1 xyz_value2 hhh_value1 hhh_value2
---------- ----------- ----------- ----------- ----------- ----------- -----------
01/01/2008 11 12 NULL NULL 70 52
01/02/2008 35 53 42 21 NULL NULL
01/03/2008 38 62 NULL NULL 3 83
01/04/2008 NULL NULL 9 7 NULL NULL
01/05/2008 59 95 89 45 26 77

Ideally, the results include
- every day in the date range (even if there is no corresponding data for that date)
- the columns values to be dependent on the categories found within the date range

I came up with this

-- to fulfill requirement "every day in the date range"
declare @dt table (d smalldatetime)
insert into @dt
select '2008-01-01' union all
select '2008-01-02' union all
select '2008-01-03' union all
select '2008-01-04' union all
select '2008-01-05'

-- to fulfill display all the categories (manually determined)
select convert(varchar(10),d,101) as date,
abc.value1 as abc_value1, abc.value2 as abc_value2,
xyz.value1 as xyz_value1, xyz.value2 as xyz_value2,
hhh.value1 as hhh_value1, hhh.value2 as hhh_value2
from @dt dt
left join @log abc on dt.d = abc.date and abc.category = 'ABC'
left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ'
left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'


just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.

Any ideas? Thoughts? Suggestions?

View 3 Replies


ADVERTISEMENT

Cross Tab/Pivot Table/UDF?

Mar 27, 2004

I have the following tables:


tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
-----------------------------------------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
----------------------------------------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2


From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.


SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1


The following results are returned:

Width | Height | Flange | Leg | LegCount
----------------------------------------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2


The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
----------------------------------------------
192 | 36 | 6 | 5 | 2


So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike B

View 1 Replies View Related

Cross Tab Or Pivot Issue

Jan 24, 2008



I have the following query:




Code Snippet
DECLARE @start_date datetime,
@weeks int

SET @start_date = '1/1/2007'/*Set this to the date you want your analysis to begin on. Be mindful of the date you pick. For instance, in this case we want the
beginning of the week to always be a Monday so we are OK picking 1/1/2007 because it is a Monday.*/
SET @weeks = 12

SELECT DATEADD(dd, (n.Num-1) * 7, @start_date) [Beginning of Week], /*This is creating the Beginning of each week. Looks at each record in the Number table,
subtracts one off of the Num field and then multiplies by 7. Takes the resulting number and adds that many days to our start date variable.*/
DATEADD(dd,(n.Num * 7)-1,@start_date) [End of Week], /*This is creating the Ending of each week. Looks at each record in the Number table,
muiltplies it by 7 and then subtracts one off of the result. Takes the resulting number and adds that many days to our start date varaible.*/
( /*This is where we are counting the number of opportunities in each week. This returns a reasulting count for each combination that is
generated in the outer query.*/
SELECT COUNT(*)
FROM op_opportunity op_o
WHERE op_o.open_date BETWEEN DATEADD(dd, (n.Num-1) * 7 , @start_date) AND DATEADD(dd, (n. Num * 7)-1, @start_date) /*Count if the
open date is between
what we've identified as the Beginning of Week and End of Week for each iteration.*/
) AS [Opportunities Open]
FROM Numbers n
WHERE DATEADD(dd,n. Num * 7,@start_date) >= DATEADD(wk, -@weeks+1, GETDATE()) AND DATEADD(dd,n. Num * 7,@start_date) <= DATEADD(wk, 1, GETDATE())
/*We are just limiting our evaluation to anything less than todays date and within the number of weeks in our @week variable.*/






It returns a result set that looks like this:



Code Snippet
Beginning of Week End of Week Opportunities Open
11/5/2007 12:00:00 AM 11/11/2007 12:00:00 AM 369
11/12/2007 12:00:00 AM 11/18/2007 12:00:00 AM 326
11/19/2007 12:00:00 AM 11/25/2007 12:00:00 AM 203
11/26/2007 12:00:00 AM 12/2/2007 12:00:00 AM 333
12/3/2007 12:00:00 AM 12/9/2007 12:00:00 AM 421
12/10/2007 12:00:00 AM 12/16/2007 12:00:00 AM 286
12/17/2007 12:00:00 AM 12/23/2007 12:00:00 AM 411
12/24/2007 12:00:00 AM 12/30/2007 12:00:00 AM 48
12/31/2007 12:00:00 AM 1/6/2008 12:00:00 AM 234
1/7/2008 12:00:00 AM 1/13/2008 12:00:00 AM 314
1/14/2008 12:00:00 AM 1/20/2008 12:00:00 AM 309
1/21/2008 12:00:00 AM 1/27/2008 12:00:00 AM 207




What I want to do is change this so that I can get a count of new opportunties by week but also by rep. In the op_opportunity table there is a field called sales_owner that I would use. Ideally, I also want to have it Pivoted so that the weeks are columns and the sales_owner are the rows. I tried using the PIVOT command but you have to explicitly indicate your columns. Because the number of weeks that are chosen could always be different I can't do that.

Ultimately, this will be presented through reporting services so I can probably use the Cross Tab tool to show it the way I want, so if I could just get the same result set but instead the rep added and the opportunities they created that would probably work. So if we had 12 weeks and 10 reps that would produce 120 rows.

I also hardcoded the @startdate to 1/1/07 because that is a Monday and it is back far enough in the past that I don't have to worry. As we get further away from that date will that cause any performance issues?

Any input would be helpful. Thanks.

View 3 Replies View Related

Cross Reference Or Pivot Table

Aug 23, 2007

Hi All,
The problem is about cross reference.
1. I have a third party cross reference store procedure SimpleXTab
CREATE       PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100)
set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + ']  ' + @XWhereString + ' Group By [' + @XField + ']'
/* select @sqlstrcur */exec sp_executesql @sqlstrcur
 
 declare xcursor Cursor  for  Select * from ##temptbl_Cursor
 open xcursor
 Fetch next from  xcursor  into @Col  
While @@Fetch_Status = 0Begin  set @Sqlstr = @Sqlstr + ", "  set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +                           "' then [" + @XFunctionField + "] Else 0 End) As [" +  @Col + "]" ,'')  set @Sqlstr = @tempsql  Fetch next from xcursor into @Col End
 /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */
 set @tempsql = 'Select  '  + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable +                             @XWhereString +  ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql
 Close xcursor Deallocate xcursor 
  set @tempsql = N'Drop Table ##temptbl_Cursor'  exec sp_executesql @tempsqlprint @tempsql /*  Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr   exec sp_executesql @Sqlstr
if @@rowcount = 0  select 'No Records found'GO
2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.    how can i modify SimpleXtab to avoid using math function but still can generate cross reference.
   exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
 
Many Thanks!
 
 

View 2 Replies View Related

Need Help With Pivot/cross Tab Or Matrix Table

Mar 18, 2008

Hi I need to transform this table below

QRT qt_yr TA AVG_MA AVG_MP TMP
--- ----- --- ------ ------ ---
33Q076248.5957.5462
2 2Qo7 0 0.00 0.00 0
11Q0839620.9643.54396
44Q0744338.8356.51443

into this format.

A_YP 2Q07 3Q07 4Q07 1Q08
---- ---- ---- ---- ----
TA 0 62 396 443
AVG_MA 0 48.59 20.96 38.83
AVG_MP 0 57.54 43.54 56.51
TMP 0 62 396 443

Please help. Thanks.

View 1 Replies View Related

Maybe Pivot/Cross/CSV String Combo????

Oct 8, 2007


I am looking help on the following.

Currently my select will return multiple districts for each of the users. I would like to combine the multiple districts into one string field.

Current proc:







ADName
DistrictID

Glenn Stalions
9

Bob Smith
9

Pam Cassidy
-1

Shannon Sanchez
1234

Shannon Sanchez
1355

Change to:







ADName
DistrictID

Glenn Stalions
9

Bob Smith
9

Pam Cassidy
-1

Shannon Sanchez
1234, 1355

If the user is managing more than one district the proc would return a single feild with a csv string of all districts.

I have tried pivot's and it returns multiple columns which I don't want. In addition, I have to hard code 300+ district id's to get the pivot to work correctly.

Something like this will work but I want to call it each row and not for the entire proc:
declare @csv varchar(max)
(SELECT @csv = coalesce(@csv+', ','' ) +
cast(districtid as varchar) FROM #district)
select @csv



Any help would be very much appreciated.

View 3 Replies View Related

Help With (Pivot/Cross-Join???) Query To Select A Result Set

Jan 20, 2005

I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.

Current structure is:

Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000

I want to produce this result set:

Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000

There could be any number of sizes or colours.

Is this possible? Can anyone give me any pointers?

Thanks in advance

greg

View 8 Replies View Related

PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns

Aug 3, 2007

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:













CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount

1
1
Panel
Jan
5
10
50

1
1
Panel
Feb
5
15
75

1
1
Panel
Mar
5
20
100

1
2
Frame
Jan
10
30
300

1
2
Frame
Feb
10
25
250

1
2
Frame
Mar
10
20
200

1
3
Glass
Jan
20
10
200

1
3
Glass
Feb
20
20
400

1
3
Glass
Mar
20
30
600

I would like it to be converted into following result set:















CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar

1
1
Panel
5
10
50
15
75
20
100

1
2
Frame
10
30
300
25
250
20
200

1
3
Glass
20
10
200
20
400
30
600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

View 1 Replies View Related

Power Pivot :: Can Measure Be Effected By Cross-filtered But Unselected Slicer Item?

Aug 17, 2015

My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.

I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:

Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))

And it ends up looking like this:

Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:

I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.

View 2 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 :: 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

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

T-sql Cross Tab

Jul 20, 2004

I should've posted this thread here....

http://www.dbforums.com/t1005070.html

View 1 Replies View Related

Cross Tab?

May 18, 2004

I need to format the following tables:


tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
---------------
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET


To the following view :


vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith


Any Ideas?
Mike B

View 1 Replies View Related

Cross Tab

Jun 17, 2008

Is it possible change to cross tab using code?

Before code:

NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198

After code

NAMEHEIGHTWEIGHT
AAA66198

View 5 Replies View Related

Cross Tab

Oct 20, 2006

Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.

I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.

It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.

AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED

I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this

Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1

Can anyone help me with the the extra columns please.

View 12 Replies View Related

Cross Tab??

Jul 20, 2005

thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new

View 2 Replies View Related

Cross Tab Query

Sep 7, 2007

 Hi!
I m using sql 2005.I have a table as showing below.










year
Region
loan_amt
pur_id
purpose


1981
Andhra pradesh
$20,000.00
     1
Animal Husbandary

1981
Arunachal Pradesh
$110,000.00
     1
Animal Husbandary

1981
Assam
$240,000.00
     1
Animal Husbandary

1981
Bihar
$75,000.00
     1
Animal Husbandary

1981
Chhattisgarh
$55,500.00
     1
Animal Husbandary

1981
Gujarat
$77,500.00
     1
Animal Husbandary

1982
Goa
$44,888.00
     1
Animal Husbandary

1982
Himachal pradesh
$4,000.00
     1
Animal Husbandary

1982
Himachal pradesh
$20,000.00
     1
Animal Husbandary

1982
Jammu and kashmir
$30,000.00
     1
Animal Husbandary

1882
Jharkhand
$35,000.00
     1
Animal Husbandary

1982
Karnataka
$40,000.00
     1
Animal Husbandary

1982
Kerala
   $20,000.00   
     1
Animal Husbandary

1982
Madhya pradesh
$5,000.00
     1
Animal Husbandary
 
 
I want to produce report as  by using crosstab query as showing bellow
 
Year      Asam     Hyadrabad   goa  arunachal pardesh etc............
1981      1000.00    2000.00       8000.2  00000        000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
 
Thanx in advance.
Abhishek
 
 
 
 
 

View 6 Replies View Related

Hi Need Help In Cross Tab Query

Apr 14, 2008

I wanna develope a report using cross  tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test                   45          50            25          10            11
 
Please can any one help me how to do this this is very urgent ?

View 2 Replies View Related

Cross Tab Problem

Oct 14, 2004

I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.

Many thanks.


SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1,
(CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2,
(CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3,
(CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4,
(CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5,
(CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6,
(CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7,
(CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8,
(CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9,
(CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10
FROM tblTest f WHERE (f.fID=219)

View 1 Replies View Related

Cross Database

May 23, 2005

" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.

what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.

i have created a view in db1 with the same name as

'create view tableOne as select * from db2..tableOne'

now i can access.
Is there some othere way with out creating view?

View 7 Replies View Related

Reverse Cross Tab

Feb 19, 2002

Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE

View 2 Replies View Related







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