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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jul 20, 2004
I should've posted this thread here....
http://www.dbforums.com/t1005070.html
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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