SSIS not producing information from my COMPUTE BY clause when exporting to Excel
-
When I run a script that uses COMPUTE BY in SSMS I get the desired results. When I try and create an SSIS package to export out to Excel, it completely ignores the COMPUTE BY clause. I need to do a subtotal by a certain group but I don't need to sum any of the columns.
I've been reading about GROUP BY (ROLLUP) but I can't get that to work. It wants me to add every single column name into the GROUP BY clause which of course ruins how I want the data to look when it's exported out to Excel.
Here's a sample of how I'd like the data to look like once it gets to Excel...
This is where I'd want a subtotal on the post_amt on every change in admit_svc_code. Like I said this works just fine if I use COMPUTE BY within SSMS but that line is ignored when I try and export it out to Excel. I know how to make this work if I export the raw data into Excel but I'd like the data to already look like that from the SSIS export.
-
Per Books Online "The COMPUTE and COMPUTE BY clauses are provided for backward compatibility." so use those in new development at your own risk. They do not exist in SQL Server 2012.
If you still want to use COMPUTE BY, know that it returns a second resultset. You can capture both if you execute your query to Fill a DataSet within a Script Task. You can then iterate over the DataSet to write your Excel file, but all of this would be custom .NET coding and you wouldn't really be using much of the built in SSIS functionality at that point.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
J M-314995 (3/5/2013)
When I run a script that uses COMPUTE BY in SSMS I get the desired results. When I try and create an SSIS package to export out to Excel, it completely ignores the COMPUTE BY clause. I need to do a subtotal by a certain group but I don't need to sum any of the columns.I've been reading about GROUP BY (ROLLUP) but I can't get that to work. It wants me to add every single column name into the GROUP BY clause which of course ruins how I want the data to look when it's exported out to Excel.
Here's a sample of how I'd like the data to look like once it gets to Excel...
This is where I'd want a subtotal on the post_amt on every change in admit_svc_code. Like I said this works just fine if I use COMPUTE BY within SSMS but that line is ignored when I try and export it out to Excel. I know how to make this work if I export the raw data into Excel but I'd like the data to already look like that from the SSIS export.
How about using SSRS to export data into the excel file? SSRS supports sub-total on which columns you want.
-
I'm not really a .Net programmer so I can't go that route. I do understand short and simple C# and VB scripts but I know more python and ruby than I do .Net languages.
I quit using SSRS a few years ago when the "double hop" issue was introduced on our system when our software vendor integrated the main set of apps we use with active directory. Looks like I'll have to try and re-tackle this issue to get my subtotals. I know this should be on a separate thread but I've been hesitant to setup an SPN because I haven't had the time to look into the security risks in using SPN's (if there are any).
Thank you for the responses though. =)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply