| Author |
Topic |
|
JeniQ
Starting Member
29 Posts |
 Posted - 2009-06-02 : 09:56:52
|
| In a report that I am attempting to modify, there is currently a cell that sums two previous cells. For exampleTextbox1:Current Funding (Fields!CurFund.Value)Textbox2:New Funding (Fields!NewFund.Value)Textbox3:New Amount (Fields!CurFund.Value+Fields!NewFund.Value)The user request is for the value of Textbox3 to actually be a sum of Textbox1 and Textbox2. The reason for his request is that when he exports this report to Excel, he'd like the value of the third cell to automatically update if he changes the value of Cell 1 or Cell 2. So I thought the correct approach would be this:New Amount (ReportItems!textbox1.value+ReportItems!textbox2.value)However, when I preview this change, I get "#Error" as the result. Am I doing something obviously wrong? Thanks, in advance, for your help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-02 : 10:06:25
|
You will have to write the Excel formula using relative columns for the sum.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-02 : 10:26:43
|
So, I have to write Excel code and insert it in the report? quote:
Originally posted by Peso
You will have to write the Excel formula using relative columns for the sum.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-02 : 11:36:38
|
Basically, yes. If you want the formula to work in Excel.And there is still no guarantee the formula will work if the content is imported as text.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-02 : 13:08:30
|
quote:
Originally posted by JeniQ
In a report that I am attempting to modify, there is currently a cell that sums two previous cells. For exampleTextbox1:Current Funding (Fields!CurFund.Value)Textbox2:New Funding (Fields!NewFund.Value)Textbox3:New Amount (Fields!CurFund.Value+Fields!NewFund.Value)The user request is for the value of Textbox3 to actually be a sum of Textbox1 and Textbox2. The reason for his request is that when he exports this report to Excel, he'd like the value of the third cell to automatically update if he changes the value of Cell 1 or Cell 2. So I thought the correct approach would be this:New Amount (ReportItems!textbox1.value+ReportItems!textbox2.value)However, when I preview this change, I get "#Error" as the result. Am I doing something obviously wrong? Thanks, in advance, for your help.
are textbo1,2 & 3 used in same scope? ie.same table,matrix,..? |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-02 : 13:15:41
|
| Well, the three textboxes are in the same table. They are using EXPR to pull data from datasets populated by Stored Procedures. The data in textbox1 and textbox2 are both generated by the same SP.Did I answer your question correctly? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-02 : 13:32:29
|
| yup...then i think you should be able to use the posted expression provided you've numeric values coming from both textboxes. |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-02 : 13:41:24
|
| Well, that may be the problem. Textbox2 actually reads more like this:=IIF(Fields!NewFund.Value>0, FormatCurrency(Fields!NewFund.Value, 0), "Complete")I'd be happy to get the "#Error" message when it tries to add the value of Textbox1 to "Complete" if it would properly add the value of Textbox1 to the value of Textbox2 when Textbox2 was numeric. Is this impossible? |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-03 : 15:35:31
|
quote:
Originally posted by Peso
Basically, yes. If you want the formula to work in Excel.And there is still no guarantee the formula will work if the content is imported as text.
I'm trying simple code like this:=SUM(RC9:RC17)But it doesn't like the colon - it is a line terminator and not allowed. Any suggestions on how to enter a range of cells in SSRS that Excel can use? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-04 : 13:17:50
|
quote:
Originally posted by JeniQ
Well, that may be the problem. Textbox2 actually reads more like this:=IIF(Fields!NewFund.Value>0, FormatCurrency(Fields!NewFund.Value, 0), "Complete")I'd be happy to get the "#Error" message when it tries to add the value of Textbox1 to "Complete" if it would properly add the value of Textbox1 to the value of Textbox2 when Textbox2 was numeric. Is this impossible?
try putting default value as some numeric value and then using your current expression |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-04 : 13:28:08
|
quote:
Originally posted by visakh16try putting default value as some numeric value and then using your current expression
Visakh,I've actually skipped ahead a bit - see my last post. I simplified the report so that Textbox3 was just the total of Textbox1 + Textbox2. The result was that Textbox 3 properly calculated the sum of those two referenced items. Then I tried exporting to Excel and Textbox3 exported as a value, not a formula. So, now I am stuck on how to write the Excel code in the report. Thanks,Jeni |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-04 : 13:39:24
|
quote:
Originally posted by JeniQ
quote:
Originally posted by visakh16try putting default value as some numeric value and then using your current expression
Visakh,I've actually skipped ahead a bit - see my last post. I simplified the report so that Textbox3 was just the total of Textbox1 + Textbox2. The result was that Textbox 3 properly calculated the sum of those two referenced items. Then I tried exporting to Excel and Textbox3 exported as a value, not a formula. So, now I am stuck on how to write the Excel code in the report. Thanks,Jeni
it will export as a value. so was your attempt to generate excel code corresponding to this expression? |
|
|
|
JeniQ
Starting Member
29 Posts |
Posted - 2009-06-04 : 14:35:03
|
| Right! Rather than using this formula:=ReportItems!textbox1.value+ReportItems!textbox2.valueI tried using this:=SUM(RC9:RC17)I thought that even though SSRS couldn't interpret the value, that Excel could and would interpret it. However, I'm not even sure that will work. I don't think that my code is actually working in SQL, either. *Sigh* |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-14 : 04:10:02
|
quote:
Originally posted by JeniQ
Right! Rather than using this formula:=ReportItems!textbox1.value+ReportItems!textbox2.valueI tried using this:=SUM(RC9:RC17)I thought that even though SSRS couldn't interpret the value, that Excel could and would interpret it. However, I'm not even sure that will work. I don't think that my code is actually working in SQL, either. *Sigh*
nope...i dont think second expression will work. the first one however does |
|
|
|
|
Summing the value of two cells