Avatar of x30n30
x30n30
 asked on

Excel formula in Reporting services 2003

I am trying to build a report in Reporting Services 2005, which could be exported to Excel.
what i need is that the report would be able to export some formulas from reporting services as well.

the problem starts when i try to sum something :

this formula i have created in reporting services
=(ReportItems!X.Value+ReportItems!Y.Value-ReportItems!Z.Value*255

after exporting looks something like this =_X&_Y-_Z*255

but it should be =_X+_Y-_Z*255

i tried to convert the fields before adding them to formula like =CDbl(ReportItems!X.Value)
but it doesn't work when i'm trying to include a result of one formula in calculation of another result...

any suggestions?
Microsoft SQL ServerSSRS

Avatar of undefined
Last Comment
Bodestone

8/22/2022 - Mon
Bodestone

You probably want ="=" + ReportItems!X.Value + "+" + ReportItems!Y.Value + "-" + ReportItems!Z.Value + "*255"
x30n30

ASKER
this will gibe an output as a string, what i need is a numeric formula

the excel output is similar to this....

 ="=" &_X&"&" & _Y& "-" & _Z& "*255"
Bodestone

Yeah, sorry, I see what you mean now.

Hmmm, it seems to be replacing the plus with an ampersand, everything else looks OK right?

I shall ponder and ruminate since nothing springs to mind immediately.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
x30n30

ASKER
yes everything else works fine,

reason for this problem is that in Reporting Services the plus sign has two meanings - summarizing figures and merging two strings. & has only one meaning - merging 2 strings.

and for some reason excel wants to use the wrong meaning of (+) :D

hope someone will post a workaround for this
ASKER CERTIFIED SOLUTION
Bodestone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
x30n30

ASKER
Wery simple solution for a though problem :D
x30n30

ASKER
somehow didn't thought it in such way, thanks! :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bodestone

Aye, I couldn't find a way round the conversion of + to & so I thought, just do away with the +.
Far from elegant but if it works until a proper solution is found....