Trouble With Pivot Task (multiple Problems) - Long

Oct 5, 2007

All,

I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting.

Background:
A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns.

Problem 1:
This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code)

Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail?

Problem 2:
When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates?

Problem 3:
When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more?


I am monitoring the package execution by:
1) logging all available output from SSIS to a file.
2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result.
3) Placing data viewers on all directional arrows from original db query and pivot


Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information.

Any help on one or more of these questions would be a great help...

Thanks.







-1071636293









-1071636293

View 7 Replies


ADVERTISEMENT

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 arent available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

View 3 Replies View Related

Trouble With Execute SQL Task And Variables

Mar 2, 2007

I have 3 Execute SQL tasks to find the max value of some fields. In two tasks the SQL

SELECT MAX(field bigint)
FROM TABLE

Is returned and stored as a string variable. My 3rd execute SQL task is

SELECT MAX(field int)
FROM TABLE

Is returned and stored as an object type. If I try to save it as a string, the execution fails with a mismatch.

WHY? Why aren't they all strings? What am I missing/doing wrong? This is very frustrating behavior. The queries are running against tables on SQL Server 2005 the field types are listed in the SQL above.

Thanks,
Matt

View 7 Replies View Related

Trouble Saving XML Output From SQL Task

Sep 7, 2007

I am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task.
Here is the stored procedure
Create Procedure [dbo].[HRGCSpace]
(@HRGSpace nvarchar(max) = ''Output)
as
Begin
Set NOCOUNT On
set @HRGSpace = (select
CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id',
WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id',
FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id',
SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id',
StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id'
from HRGCvCorrigoUnits
order by BuildingName, SpaceName
For XML Path ('Space'), Root('HRGSpaces'))
End
--------------------------------------
Here is the execute from the SQL Task
DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output

If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0
, then use in the SQLTask
General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False,
Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0
the Progress is
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

If I change it so that ResultSet = XML, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."

Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with
"[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!



View 4 Replies View Related

Trouble With Transfer SQL Server Objects Task

Feb 28, 2007

I am struggling to copy a 2000 DB to 2005 using transfer sql server objects task.

I can get it to work, but without the foreign keys, which I also need.

When also copying the foreign keys, I get the following message:"



[Transfer SQL Server Objects Task] Error: Execution failed with the following error:

"ERROR : errorCode=0 description=There are no primary or candidate keys in the referenced

table 'SVS' that match the referencing column list in the foreign key 'FK_WRM_SVS_WRM_SVS'.

helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".



Apparently, SQL tries to create a foreign key on the WRM table, while the primary key on SVS is not there yet.

Since it used to work in 2000, I am pretty sure I am doing something wrong. I can harldy believe this is by design.

(By the way, the CopyAllSchemas is set to True).

Can anyone please help?



Regards,

Pipo

View 3 Replies View Related

I'm Having Trouble With Doing Multiple Joins

Mar 7, 2001

Here is what I am currently doing:
********************************************
SELECT Hours.Hours, Hours.Comments
FROM Hours INNER JOIN Employee
ON Employee.UserID = Hours.UserID
INNER JOIN Task
ON Hours.TaskID = Task.TaskID
INNER JOIN Project
ON Hours.ProjID = Project.ProjID
WHERE Hours.Date <= EndDate
AND Hours.Date >= StartDate
AND Hours.Date <= EndDate;
********************************************
Am I doing something wrong here?
Any help would be greatly appreciated!

View 2 Replies View Related

Trouble Converting A Varbinary (Object) To A String Through Script Task

Dec 9, 2006

I have an Execute SQL Task that queries for the contents of a file (which is declared as a varbinary type). My SSIS package needs to write these contents into a temporary location, so what I do is that I pass the result into a System.Object variable. I then use that variable in a Script Task.

Problem is whenever I execute my script task and DirectCast or CType the variable to a string (which is what the contents of the file is), I get a "Conversion from type '_ComObject' to type 'String' is not valid."

Any ideas on how to go about this problem?

View 1 Replies View Related

Trouble Joining Multiple Tables

Dec 14, 2004

Hi,
This seems like a basic problem but I can't figure out how to resolve it.

I have a query :

SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2


The output of the above query:

WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702


I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:

0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000



So as a result I am getting 9000 where wbs2 = '0141'

I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?

Thank You.

:)

View 2 Replies View Related

Can I Split A Long .sql File Into Multiple Files?

May 9, 2008

 I have one really long .sql file I'm working on.  It's actually a data conversion type script.  It's gotten really cumbersome to work on as long as it is.  I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks 

View 3 Replies View Related

Using PIVOT With Multiple Aggregates

Jan 16, 2008

I'd like to merge the 2 statements shown below into one. I'm wondering if there is a method of using PIVOT to get the SUM and COUNT aggregates in one statement. The only option I can get working is to use these as sub-queries but I'm hoping there is a better approach.

An answer would be great as would a better on-line resource than the BOL "Using PIVOT and UNPIVOT" topic.

Any guidance much appreciated.


/********

Get account type totals

********/

SELECT PVT.ACCOUNT_MANAGER_OID,

ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS',

ISNULL(PVT.[OTHER], 0) AS 'OTHERS'

FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,

1 AS 'REVIEW_IND',

CASE WHEN A.TYPE = ( 'Customer' )

THEN TYPE

ELSE 'OTHER'

END AS TYPE

FROM ACCOUNTS A LEFT OUTER JOIN

( SELECT ACCOUNT_OID,

1 AS [REVIEW_IND]

FROM dbo.ACCOUNT_HISTORY

WHERE TABLE_NAME = 'ACCOUNTS' AND

FIELD_NAME = 'REVIEW DATE'

) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID

WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL

) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT

ORDER BY PVT.ACCOUNT_MANAGER_OID





/**********

Get accounts review totals

***********/

SELECT PVT.ACCOUNT_MANAGER_OID,

ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS_REVIEWED',

ISNULL(PVT.[OTHER], 0) AS 'OTHERS_REVIEWED'

FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,

1 AS 'REVIEW_IND',

CASE WHEN A.TYPE = 'Customer' THEN TYPE

ELSE 'OTHER'

END AS TYPE

FROM ACCOUNTS A LEFT OUTER JOIN

( SELECT ACCOUNT_OID,

1 AS [REVIEW_IND]

FROM dbo.ACCOUNT_HISTORY

WHERE TABLE_NAME = 'ACCOUNTS' AND

FIELD_NAME = 'REVIEW DATE'

) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID

WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL

) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT

ORDER BY PVT.ACCOUNT_MANAGER_OID

View 5 Replies View Related

Pivot Multiple Columns

Sep 19, 2007

I have a table the records the results of three different tests that are graded on a scale of 1-7. The table looks something like this.
PersonId TestA TestB TestC

1 4 5 4
2 6 2 4
3 5 5 6
4 1 5 1

I would like to have a SQL statement that would pivot all this data into something like this

Test 1 2 3 4 5 6 7
A 1 0 0 1 1 1 0
B 0 1 0 0 3 0 0
C 1 0 0 2 0 1 0

Where the value for each number is a count of the number of people with that result.

The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together. Is there a way to do this in a single statement?

(If this has already been covered I apologize, but I could not find the solution.)


View 3 Replies View Related

Multiple Columns As The Pivot Key

Aug 27, 2007

Hello,

Here is a sample of the data that I am trying to pivot;

rec_id sequence field_name value
1 1 cat_nbr Granrier
1 1 cat_page pg 21
1 2 cat_nbr H&S
1 2 cat_page pg234
2 1 cat_nbr Ford
2 1 cat_page pg5

I need to pivot on rec_id and sequence to get an output like this:

rec_id sequence cat_nbr cat_page
1 1 Granrier pg21
1 2 H&S pg234
2 1 Ford pg5

All I seem to be able to get thoug is this:
rec_id sequence cat_nbr cat_page
1 1 Granrier
1 1 pg21
1 2 H&S
1 2 pg234
2 1 Ford pg5



It seems to me that the pivot transform can only pivot around one key value column. What am I missing?

Thanks.

View 4 Replies View Related

Pivot Multiple Values

Jun 28, 2006

Is there a way to pivot multiplie values in one 'run'.... In the order of ...

PIVOT ( SUM(DSH_TICKETS) FOR CPRF_NBR IN ([1], [2], [3], [4], [5])

SUM(HALL_CAPACITY) FOR CPRF_NBR IN ([1], [2], [3], [4], [5]) ) PVT

I know that there would be a problem with the headers, but that i could solve by using a second dummy for cprf_nbr and increase it with 10 (ex.)

Until knwo i did the jobg with a case statement, but it would be much nicer with a PIVOT.

View 3 Replies View Related

Join And Pivot Multiple Tables?

Jul 24, 2012

I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.

The DocType and Docs tables are relational. DocType.ID = Docs.tID
The Users and Docs tables are relational. Users.ID = Docs.uID

Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.

What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.

For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.

View 2 Replies View Related

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

Transact SQL :: Pivot On Multiple Results

Nov 9, 2015

I have a table similar to below:

itemID | part
1         | A
1         | B
2         | A
2         | A
2         | A
3         | C

I need the table to look like the following:

itemID | part1 | part2 | part 3
1         | A        | B       | null
2         | A        | A       | A
3         | C        | null    | null

There will _never_ be more than three parts to an item, and it does not matter what order they are in.

I cannot get pivot to work for me.

View 2 Replies View Related

PIVOT Multiple Columns In Test Table

Apr 24, 2014

-- Here's a test table where I'm trying to workout how to Pivot more than one column.

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0
BEGIN
DROP TABLE #Test_Pivot_Example
END

[Code] ....

Once I have worked this out then I need to dynamic populate the IN ([1] etc with the val;ue sin field [SIZE])

but one step at a time trying to workout pivot on more than one column.

View 4 Replies View Related

Dynamic Pivot Table With Multiple Tables

May 19, 2015

How to pass dynamic values in xml path query?

WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI  
INNER JOIN TERMINALGROUP TG  ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL  ON TG.GROUP_ID = TL.GROUP_ID

[Code] ....

I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.

View 7 Replies View Related

Power Pivot :: Multiple Relationship Between Same Tables

Sep 8, 2015

I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.

Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:

->Employee Table: Employee_name, Previous_Manager, Current_Manager
->Manager Table: Manager_Name

Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables:

-> Link 1: Previous_Manager ---- Manager_Name
-> Link 2: Current_Manager ---- Manager_Name

Right now, one of the links is inactive...

Is there a way in PowerPivot to create 2 active links like that ?

I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7

View 5 Replies View Related

SQL Server 2012 :: Pivot For Multiple Metrics By Year

Mar 25, 2015

Is there a way to show multiple metrics in 1 SQL pivot operator. Basically, I have the Table1 and want the desired results is the Table2 format.

Table1

ACCOUNTS YEARREVENUEMARGIN
ACCOUNT1 2012100 50
ACCOUNT1 2013104 52
ACCOUNT1 2014108 54

[code]....

View 3 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

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

Reporting Services :: Pivot A Table Of Data With Multiple Columns?

Nov 20, 2015

Running SQL Server 2005, trying to develop an SSRS report to basically pivot a table of data with multiple columns.

Here's the basic source table:

Day    Cases   Referrals    Vends
1         291          0             0
2         293          1             0
3         293          1             1

And I want to display it as:

Day             1       2       3
Cases         291   293   293
Referrals       0        1      1
Vends           0        0      1

I thought I could use a matrix for this but I can't seem to get it worked out. Is this even possible?

The Day number is meant to represent the day of the month and the user would input a start and ending date parameter.

View 8 Replies View Related

Power Pivot :: Modeling And Reporting With Multiple Date Dimensions

Jun 17, 2015

Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:

- Order created date
- Client due date
- Order first payment date (an order can have multiple payments)
- Order fully paid date
- Date assigned to vendor
- Vendor return date
- Date delivered to client

On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.

- Date vendor recruited
- Date vendor reviewed

At any given point the manager may want a report based on any of these dates. For example;

- Product type by order creation date (fiscal year / month)
- Product type by first payment date  (fiscal year / month)
- Product type by client due date (fiscal year / month)

and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier? 

View 6 Replies View Related

Power Pivot :: Measure Count Rows - If Condition - Multiple Tables

Nov 7, 2014

I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:

=CALCULATE(COUNTROWS(incident),
        apparatus[Incident Response Time] >-1 ||
        apparatus[Incident Response Time] <320,
        uv_901APP_TYPE[Description]="Engine",
        uv_901INCIDENT[Top_Category]="Fire"

[code]....

View 13 Replies View Related

FTP Task For Multiple Files

Dec 18, 2007

Hi i have a FTP task on my SSIS package where i want to select 3 files from a directory, this directory already contains other files but i only want 3 of them how do i only pull down the 3 files from the FTP site i can't seem to find a option on the FTP task to select what files i want from the direcroty.

View 3 Replies View Related

Run A Task Multiple Times

Oct 20, 2006

I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.

Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.

I would like to do this:

Task1 ------Success-----> Set Vars -----Success--> Log
|
Task2 ------Success-----> Set Vars -----Success-| (do the Log task again)
|
Task3 -------Success-----> Set Vars -----Success-| (do the Log task again)
|
etc

This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.

Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.

Any ideas?

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

Multiple SuccessValues For ExecuteProcess Task?

Jun 25, 2006

I have an execute process task to run Red-Gate's SqlDataCompare synchronization.  The normal exit code is 0 indicating successful synchronization.  However, if the tables are already identical and require no synchronization the process exit code is 63 and the task fails.  I do not want the task to fail if the tables are identical, but it seems I can only specify a single value in the task's SuccessValue property. I tried separating values with a comma, e.g. 0,63.  Any suggestions?

View 3 Replies View Related

Task With Multiple Inputs Not Executed

Apr 26, 2007

I have modified my workflow to take conditional branch. The workflow terminates after the branched tasks finish without continueing to the next task no matter how I set the flow out condition. I found I have this problem when a task take more than one input. Does SSIS has a seeting for limiting the inputs to be taken?



Thanks in advance for the help!

View 3 Replies View Related







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