January 30, 2008 at 12:27 pm
When I export a report to excel and my report has sum fields, I would like excel to create the fields into formula fields that I could manipulate the data in excel and the values will ripple thru to the sum fields.
Is this possible?
Thanks in advance
January 30, 2008 at 4:06 pm
I don't believe this is possible. It seems like when RS exports the data, it does data only... and sometimes does some interesting things with column spanning cells too. It may be possible to do something clever where the "value" in the reporting services report is the expression you want Excel to evaluate when it's exported, but my guess is that RS would escape the function and you still wouldn't have what you wanted (besides that, the RS report would look funny then). For our reports, I usually export them to Excel, clean them (a little), then put the functions I want in. Once I had it the way I wanted, I used the new Excel file as a template with the formulas at the top and just cut-n-paste the data out of the new exports into the clean sheet. (yes, I should automate it, but it doesn't need to be done very often)
I know this doesn't help, but at least you know someone looked at your post (better than zero replies?)
Chad
February 21, 2008 at 2:18 pm
Yes Chad I believe you are correct, I have bumped into this and found that you cannot export formulas except in text to excel reports eg:
your RS expression would look like ="(C1 + B4)*52" when exported to excel you can push F2 and it will calculate tha value.
so it is kind of crude/half *** but it is an excel formula.
JK..
February 21, 2008 at 4:02 pm
If you're looking for an auto-updating EXCEL solution - skip SSRS, and tie a data query directly into your Excel spreadsheet. You can then build your totals, and simply tell it to "refresh the data", and poof - new up to date totals.
Some caveats involved - but should get you a lot closer that what you have going on now.
That's (starting in Excel), Data, Import data, New Database query. Follow wizard.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 7:51 am
I agree with Matt. Further, if you let Excel do the import from external data, you can set the query properties to preserve expressions in adjacent columns.
February 25, 2008 at 9:01 am
I also like Matt's solution. I do this all the time. The only problem with that is that I use an ODBC connection/dsn and you have to set that up on the user's pc. However, user's generally get excited that all they have to do is click "Refresh Data" and a new dataset is generated.
April 1, 2008 at 12:45 pm
I have created a render extension that will populate exported Excel file with formulas. There is no need to use template files like some third party venders have done. Please contact me if you need my assistance.
April 1, 2008 at 12:57 pm
Re ODBC connection set ups -- I have been stumbling over the phrase "dsn-less connections" from Google searches, and suspect they might be the key to not having to define the connections for each user. But I have not tried (and probably do not understand) the approach. Anyone have any better insights?
June 24, 2008 at 12:59 am
Hi,
I could use some help. 😀 I want to automatically create some SUMS (subtotals) and totals of columns (with varying number of rows,depending on which users has ran the report) that users can fill in with numbers in MS-excel (after the report was exported).
Best regards,
Peter
June 24, 2008 at 1:46 am
Hi Peter,
you could wrap the detail rows a group and calculate in a group footer?
i.e. have your header rows in the group header i.e. order number, order price etc (in this example group on customer id) then in your detail would contain a list of order numbers and prices, then your group footer could contain the totals.
clear as crystal? or clear as mud?
reply if you would like me to elaborate.
cheers,
Frank..
June 24, 2008 at 9:32 am
I found out (by accident one day) that SSRS will export formulas when the formula references other report textboxes using the ReportItems function(). Here is a smiple example. For instance...
enter the formula =field1.value into a textbox1 and name that textbox txtField1.
enter the formula =field2.value into a textbox2 and name that textbox txtField2.
enter the formula =reportItems!txtField1.value / reportItems!txtField2.value into textbox3
When you export this to an excel file, the formula (in textbox3) will appear in excel as a formula.
smv929
June 24, 2008 at 2:33 pm
Hi Frank,
First thank you for replying, but I don't know if you are talking about the same. I have three lists in my report for three different product types. Each customer is displayed on a new page. The first column (A) of each list displays the amount (a field, populated by the database), the second column (B) displays a dummy column (reportitem, that can be filled in by the user in MS-Excel once the report has been exported), the third column (a reportitem, that SUMS column A and column B) So far so good, it works when the user fills in a value in column B in MS-excel, column C is automatically updated. Now what I want to achieve is to also have automatically a SUM of column C. (and these for the 3 different lists, but also a general total of these subtotals).
I gave a simplified example just to clarify of what I try to achieve because the real report is setup more complicated.
Best regards,
Peter
July 19, 2008 at 10:44 pm
:cool:Hi!
Please advice how to create expression, is it work for matrix report?
July 27, 2008 at 4:02 pm
hi there, yep sure.
Right click the element you want to create the formula in i.e. table cell, text box watever then select expression.
This what youwere after?
November 13, 2008 at 8:48 am
Hi - I am very interested in the render extension that you created. I need to populate an exported Excel file with formulas.
Thanks so much!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply