Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

export report to excel and excel should create formula fields for the sum fields of the report Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 19, 2009 10:01 AM
Points: 2, Visits: 23
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
Post #449610
Posted Wednesday, January 30, 2008 4:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,607, Visits: 17,927
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
Post #449738
Posted Thursday, February 21, 2008 2:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 868, Visits: 1,131
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..
Post #458811
Posted Thursday, February 21, 2008 4:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 7,139, Visits: 15,190
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?
Post #458848
Posted Friday, February 22, 2008 7:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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.
Post #459112
Posted Monday, February 25, 2008 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:36 AM
Points: 118, Visits: 567
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.
Post #459741
Posted Tuesday, April 1, 2008 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 18, 2013 9:01 AM
Points: 50, Visits: 81
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.
Post #477989
Posted Tuesday, April 1, 2008 12:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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?
Post #478002
Posted Tuesday, June 24, 2008 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2008 12:37 PM
Points: 2, Visits: 13
Hi,

I could use some help. :D 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
Post #522359
Posted Tuesday, June 24, 2008 1:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 868, Visits: 1,131
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..
Post #522368
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse