August 18, 2005 at 3:54 pm
Curious if anyone might have some suggestions.
I currently have a Crystal report installed at client installs. The Crystal Report pulls data from SQL Server stored procedures. One client wants only some of the data in Xcel.
I thought it would be very nice if I could simply reuse the relevant stored procedures and pull them into Xcel.
I find when I use Data/Import External Data and connect to my SQL Server database, I see tables and views not sprocs.
It would seem I should be able to resuse all the work I've already done someone. Does anyone have any suggestions for how I might do this?
TIA, Kim
August 18, 2005 at 9:27 pm
Kim - you could run the sproc from QA and under tools - options - results tab - set the default results target to results to file - when you run the sproc you can save to file with .xls extension.
**ASCII stupid question, get a stupid ANSI !!!**
August 19, 2005 at 7:30 am
Thanks for your response.
I was hoping for more dynamic automated approach. I wouldn't be able to push that solution out to a client.
August 19, 2005 at 7:36 am
If i am not wrong, crystal report supports excel format (not 100% sure)
Another way is to create another sp, open excel as linked server, call ur existing SP for crystal report and insert the data into the sheet in the linked server (excel)
August 19, 2005 at 7:38 am
If the Crystal report is already pulling the data that you want, why not use the export function within Crystal to create the Excel file?
Depending on the formatting of the report, everything might not fall exactly into the cells as you would like it. Either create a new report designed to export into Excel, or put the Excel-friendly layout into a new section and use a parameter to hide the section that you don't want.
August 19, 2005 at 7:46 am
Thanks for your suggestions. I'll give 'em a try today.
August 19, 2005 at 8:47 am
When you open the query analyzer for Excel the Choose Data Source window has typically a check for use the query wizard to create queries, uncheck this, and select your data base. The MS Query tool will appear, with an add tables window, close this window. Click the SQL tool bar button. The SQL statement to enter is...
{Call S_Proc(?,?)}
where S_Proc is the stored procedure, and the ? are any input parameters.
This then can be set to automatically refresh, like any of the external data queries in excel. We use this tactic here frequently.
Hope this helps.
August 19, 2005 at 9:03 am
Thanks David! That is exactly what I was looking for.
Already got it pulling the data 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply