June 8, 2009 at 2:23 pm
We are converting EXCEL reports to SSRS.
One Excel file has like 10 Sheets with lots of code behind.
It's hard to maintain.
We are planing to migrate each sheet to a separate RS report
and then we would like somehow to package them together
in one Excel file and store in a specific location.
Can this "Merge & Store" process be implemented maybe with some SSRS code?
The alternative is a scheduled batch script that runs VBScript every 15 min, finds 10 different
Excel files, merges them and stores the merged file to a specific location.
But it would be nice to do everything in SSRS...
June 9, 2009 at 5:38 pm
Is it possible to put everything from your 10 worksheets into a single SSRS report? If you were able to craft them into a series of separate tables in the report, and set each table with a page break before, the report would output to a single Excel file with 10 worksheets.
June 10, 2009 at 6:39 am
Nate, Thank you for a quick response.
I thought about this option.
But then in your output Excel your Sheets
would be named "Sheet1", "Sheet2",etc.
There is no control over naming. Users will not like it.
June 10, 2009 at 6:47 am
Since you say each tab of the Excel spreadsheet it hard to maintain, I would recommend using Sub-reports. Hopefully you are using SSRS 2008, because it handles sub-reports nicely:
(1) Create a separate SSRS report for each tab in the Excel spreadsheet.
(2) Create a main report that will contain all of the sub-reports. Create parameters, if needed, then set up each sub-report, making sure to properly connect the parameters.
NOTE: SSRS 2008 has a bug where you may get an error when trying to use more than one sub-report. To get around this, add a List control onto your report, then add the sub-report INSIDE the list box. It worked nicely for me. Also, since you want each sub-report on a separate tab in Excel, you'll need to right-click on each List box and click on the box thats says Add Page Before.
Here is a link to the Microsoft article on the work-around for the sub-report problem: http://support.microsoft.com/kb/967749/
(3) Here is a critical part: DEPLOY ALL SUB-REPORTS, and also tha main report! you can hide the sub-reports in Report Manager if desired, or you can leave them seen in case someone would like to run just one of the tabs separately.
Hope this helps!
June 10, 2009 at 6:52 am
unknown_sql,
We're on SSRS 2005.
Do you think we still can try this approach?
June 10, 2009 at 7:03 am
Yes, it still should work in 2005.
June 10, 2009 at 7:46 am
I tried it with Sub Reports
and it's the same problem.
Regardless how you name your subreports, tables
you still end up with "Sheet2", Sheet2".
But I would like to control those Sheet names.
June 10, 2009 at 11:02 am
OK.
After some research it looks like
it only can be implemented if you Export to XML and then create XSLT file
and specify it in
Report Properties/
Data Output/
Data Transform:/
See this article that explains the steps:
This is nice to know but actually it's not going to work for us.
It's too much maintenance (Design XSLT,test,possible errors while transforming XML)
We need a somplier solution.
I will look at OfficeWriter software by SoftArtisans.
June 11, 2009 at 4:43 am
I think that you still can use Excel.
What is your source? If it is a lot of code to maintain may be you shall do more
in the source SQL server? to prepair the report.
I have developed Excelreports with many sheets which in fact do not demand
any maintenance VBA coding at all.
Maybe I can help you off line.
/Gosta
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply