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+Repo rtItems!Y. Value-Repo rtItems!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?
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+Repo
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?
You probably want ="=" + ReportItems!X.Value + "+" + ReportItems!Y.Value + "-" + ReportItems!Z.Value + "*255"
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"
the excel output is similar to this....
="=" &_X&"&" & _Y& "-" & _Z& "*255"
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wery simple solution for a though problem :D
ASKER
somehow didn't thought it in such way, thanks! :)
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....
Far from elegant but if it works until a proper solution is found....