?
Solved

Writing an Excel Formula in Reporting Services 2003?

Posted on 2006-07-03
8
Medium Priority
?
2,371 Views
Last Modified: 2007-12-19
Has anyone found a way to pass an Excel Formula using Reporting Services?  I have a report that is being used to generate a spreadsheet.  I need to have the generated spreadsheet contain a foumula in 2 of the resulting cells.  It's okay for the report to show the text of the formula.  Once the spreadsheet is generated, the user types values into one column of the spreadsheet (an empty cell that I include in the RS report).  Then I want the formula to automatically calculate.  

I haven't been successful in trying to get reporting Services to pass the formula.  All I want is something like this formula result in the spreadsheet:

= W1-X1+1

Then user types info into X1 and the formula would calculate.  

I'm not sure if it's possible to pass an excel formula to have it render correctly.  

Any thoughts?

Bryant
0
Comment
Question by:Bryav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 14

Accepted Solution

by:
simon_kirk earned 1600 total points
ID: 17036256
Try setting an expression in the RS table field to something like:

=ReportItems!Textbox1.Value  - ReportItems!TextBox2.Value + 1

This should convert into an excel formula providing that the dataset is in a table and that all references to ReportItems are within the same scope.
0
 
LVL 1

Author Comment

by:Bryav
ID: 17042244
I tried this and it's now bringing in a formula, but not quite working properly.  I get a message when opening the spreadsheet about a broken link and asked to fix it or ignore.

Here's the RS formulas I have and the resulting excel formulas:

RS formula #1:
= ReportItems!textbox26.Value * ReportItems!UE.Value

Excel Results #1 for 1st 3 rows:
= _79 * _81
= _106 * _108
= _133 * _135 ...

RS Formula #2:
= ReportItems!URI.Value - ReportItems!CutInUnitNumber.Value + 1

Excel Results #2 for 1st 3 rows:
= _80 - _62 + 1
= _107 - _89 + 1
= _134 - _116 + 1 ...

Any ideas on tweaking this?  I'd say this definately progress!
0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 17042438
Not sure what you are after.  Are the formulas/results not what you are expecting, or is it just about the broken link?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:Bryav
ID: 17042710
I looked at the spreadsheet in more detail.  At first I didn't know what the _80, _62, etc. were.  I can see that RS created individual names for each cell and the resulting formulas are using those named cell references.

The strange thing is that the formula returns #NAME? as a result in the excel formula.  When I type "= _80" in a blank cell it highlights the named cell and appears that it will work (the names seem to be valid and accurate), but when I hit enter it displays the #NAME? error.

I also went in to the [Edit] [Links] menu to check out the links.  It's trying to link to a 'Details.xls' file that could not be found.

It appears the formula is working properly and is converting to excel as designed, but when the named cells are used they result in a #NAME? error.  Then there's this Details.xls link that is being created by RS as well.

Hope that clarifies things a bit more. I'm thinking there must be some more settings somewhere in RS or a config file that controls this behavior???

Let me know if you have any more ideas.
0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 17042844
I've quickly tested a table in a report,  and had no problems exporting to Excel and the formulas working using RS2000 SP2 and Excel 2002 SP2.

=ReportItems!Textbox1.Value  - ReportItems!TextBox2.Value

Try creating a simple table report with one formula, and then export this to Excel.  This will hopefully show whether it's the report or Excel that's causing the problem.
0
 
LVL 1

Author Comment

by:Bryav
ID: 17043679
Okay, I just tested 2 version of Excel 2003 SP1 and SP2.  Both behaved the same way.

I'm thinking this is an RS issue.  I'm not sure what version we're on, but I don't think we've ever applied the service packs.  That's probably our issue.

Thanks so much for your help Simon!

I'll close the question and give you the points.  I think we'll have to apply the service packs to our Reporting Services install for this to work.
0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 17043798
No probs.  Here's the link to SP2:

http://www.microsoft.com/downloads/details.aspx?FamilyID=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en

Repost another question if your still getting the problem after SP2.
0
 
LVL 1

Author Comment

by:Bryav
ID: 17043983
I sure will.  Thanks.  By the way I just checked and we're on the base install, not even SP1.

We'll upgrade to SP2 and I'm sure things will work better.  Thanks again for your help.

Bryant
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question