November 19, 2010 at 9:18 am
Hi,
Does anyone know of a good sproc that can formulate query results into an excel business report?
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
November 19, 2010 at 9:50 am
A proc? No. SSIS can do that easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 9:54 am
GSquared (11/19/2010)
A proc? No. SSIS can do that easily.
Can you reference an SSIS article that outlines how to accomplish this?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
November 19, 2010 at 9:56 am
You're trying to get SSIS to output an Excel file, right?
Just about any documentation on SSIS will tell you how to do this. Just Bing/Google/whatever "ssis export excel" and you'll find plenty of data on it.
Or is there something more here than just that simplicity?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 10:18 am
GSquared (11/19/2010)
You're trying to get SSIS to output an Excel file, right?Just about any documentation on SSIS will tell you how to do this. Just Bing/Google/whatever "ssis export excel" and you'll find plenty of data on it.
Or is there something more here than just that simplicity?
Yes, something more than just simplicity. I am not talking about using an Excel Destination to write data from SQL. Yes that is doable with SSIS or using linked server from SQL to EXcel.
Rather I want more than this which is something that gets a business report created...the data and also nice formatting, defined ranges, sums, calculated fields, totals, subtotals, and posibly graphs. For financial reporting, something that dynamically does a pivot table and so on in order to allow a degree of data mining by the user.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
November 19, 2010 at 10:51 am
That's WAY outside the realm of what a stored procedure can do.
Have you taken a look at SSRS? It can do some pretty amazing reports, with the features you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 12:02 pm
If you or your users already have the nice report, pivot table, vlookups or offset(match()) structure, formatting and so forth generated, I have had success providing business users, within Excel, with Data>Import External Data>New Database Query, and letting the user click the red exclamation point in the external data toolbar when they want the local data refreshed. In one case, I set the query to return all of a view on SQL with a rolling three month to date, so there was no need to embed criteria in the Excel view.
The neat thing I found with the target named range is on query refresh, Excel automatically redefines the named range to fit resulting rows. So a pre-existing vlookup or pivot table refers to the data by the query named range will always include all rows.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply