Excel formulas from an SSRS report

Hi,
I have a sql report that runs on a SQL 2005 reporting server. The business requirement is that the excel formulas be downloaded when the user downloads the report in excel format. It is ok for the users to see the formula and I do not mind writing an ad-hoc report only for excel downloads. I got around the first hurdle by reading this post -
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21907152.html

The problem that I am having is writing a formula using an aggregate function that sums up all the values in a column (the last row in the report should be Totals of its corresponding columns).
So if there are 10 rows, column 1 of Row 11 should be  = sum(c1:c10)

All help is appreciated
atarafderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Just another thought, Can you use a SSIS job to email the the spreadsheet. That way you can write the data into an existing workbook, with the data on a back sheet, and the summary and formula and all that on the front sheet.

Cheers
  David
0
atarafderAuthor Commented:
dtodd, this is not an option unfortunately. We are currently migrating fro CR to SSRS.
The reports are a part of a large web application
0
David ToddSenior DBACommented:
Hi,

Sorry, don't know. The crystal to excel exports I've seen tend to be values rather than formula.

If the intent is for something in excel that your users can then use and manipulate, I'd start with Excel rather than starting with crystal or other reporting solution.

That is, how to get hte data into excel - create a form and put some VBA code in a module that takes some input from the form and returns the result set as a workbook in excel, or use Microsoft Query.

As you indicated, these suggestions might not fly, but dollars to donuts you will be able to craft a nicer and possibly more user friendly solution than by using a reporting tool.

Cheers
  David
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

atarafderAuthor Commented:
I am willing to increase the points to 1000 :). This question is of urgent nature. Thanks.
0
atarafderAuthor Commented:
I guess this cannot be done -
http://www.themssforum.com/SVCS/create-Excel/
0
David ToddSenior DBACommented:
Hi,

"I guess this cannot be done - ..."

Looks that way.

I guess there are really two options - 1) go without the formulars 2) use an excel solution or DTS solution with the formulars.

It depends on how badly your users want those formulars, vs how eagerly or not they will embrace a new delivery mechanism.

On one of the simpler ones, why don't you impliment a couple of variations and demo the variations explaining the advantages and disadvantages and let your users decide. The object is to show what is achievable and what isn't, and at least in this case eliminate the impossible demands.

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atarafderAuthor Commented:
David,

Thanks for your inputs. The problem is that the application is already in use and the users have access to reports using excel formulas that have been built using a datagrid and by changing the respone mime type to excel.

This effort is to convert all of the reports to one reporting standard using SSRS.

Thanks,
AJ
0
chrismcCommented:
Hi atarafder,

I'm pretty sure there is no way to do this.

It used to be possible in the earlier version but was buggy. Unfortunately when I raised the bug with MS, they just turned the feature off and I don't think they have ever put it back in.
Formulae against ReportItems objects with objects in the same section (i.e. same row) will work, but not when totalling down a column.

Sorry!
Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.