SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export report to excel and excel should create formula fields for the sum fields of the report


export report to excel and excel should create formula fields for the sum fields of the report

Author
Message
Rivkah Pil
Rivkah Pil
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4401 Visits: 18732
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
Carlton Leach
Carlton Leach
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1304
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..
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29897 Visits: 19009
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?
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2301 Visits: 1403
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.
SQLWannabe
SQLWannabe
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 571
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.
yshen8
yshen8
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 82
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.
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2301 Visits: 1403
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?
Peter_Lambrechts
Peter_Lambrechts
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 13
Hi,

I could use some help. BigGrin 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
Carlton Leach
Carlton Leach
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1304
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..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search