Exporting SSRS report to Excel 2003

Question

Exporting SSRS report to Excel 2003

paulcfry

SSC Rookie

Points: 42
More actions
June 16, 2009 at 2:52 am

#206023

I have an SSRS report which uses a single input variable and produces exactly the results I require when the reports are run via the Report Manager....
However, I have created a scheduled job to export multiple copies of the report in Excel format to a file share using a different input variable each time.
If I run the job manually then it runs successfully with no problems. If I allow the scheduled job to run, the data in the output Excel files gets merged. All of the Excel files contain the data related to the input variable as I would expect, but some also contain the data from the other tasks.
I'm finding it difficult to explain this one as I really have no idea why this is happening, but I would be very greatful for any advice.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

atfalatitkb Old Hand Points: 381 More actions · Answer 1

Hello Paul,

my first guess would be that for each subscription that executes your report with different input variables (parameters) you would want to have a different file name. What I do is include the variable (parameter) name in the file name. I currently have 2 subscriptions for 2 reports that run on a weekly schedule and are rendered to excel. The parameters represent the # of days past a certain date. one parameter's value is 30, the other one is 180. So the first subscription's report title is "30 days past that certain date". It has 30 as it's report parameter value. the second subscription's report title is "180 days past that certain date". It has 180 as it's report parameter value. When the weekly schedule executes these 2 subscriptions, it creates excel files named "30 days past that certain date.xls" and "180 days past that certain date.xls" in the file share that you have designated.

Since this is the first time I have posted a reply to this forum I hope I have steered you in the right direction.

Best Regards,

Ed

paulcfry SSC Rookie Points: 42 More actions · Answer 2

Hi Ed,

Many thanks for your response. My subscriptions are already set up with different output names, and my naming convention is exactly as you have suggested (they both have the same filename but with _@parameter at the end of the name to differentiate between them). The separate files get created every day at 8am in the schedule however for some reason the output data gets merged into one of the files so that it contains the data from both reports in one Excel file. The other output file is correct.

If I run the job manually this does not happen and if I run the report directly in SSRS Report Manager the results are correct. It must be something to do with the scheduled job, but I can't put my finger on it!

Paul.

atfalatitkb Old Hand Points: 381 More actions · Answer 3

Paul, I am guessing that the _@parameter is not being interpreted correctly. try hard-coding the exact value of the parameter at the end of the file name (or at the beginning, whatever is appropriate). attached is an example of one I have done. Hopefully the image will be attached because this is my first attempt at attaching an image like this.