February 10, 2016 at 6:49 am
Good Morning All.
I am using custom code to get data from one of the textboxes (textbox182) in one of my tables. The code calculates closing balance by adding numbers I ask to and all works fine.
I then use the value of this textbox to determine the value of an indicator i have next to it by referring to the textbox value ReportItems!textbox182.value.
I need to format the value of this textbox in $M. The formatting for some strange reason is not holding. I wrote an expression for the formatting
=SWITCH(
SUM(Fields!OPEX_VAL.Value) >=1000000 , "'$' #,0,,.00"+"M",
SUM(Fields!OPEX_VAL.Value) >=1000, "'$' #,0,.00"+"K",
SUM(Fields!OPEX_VAL.Value)>=0,"'$' 0.00"
)
associated with this textbox and it does not work.
If i were to format the expression of the textbox itself
=SWITCH(
ISNOTHING(SUM(Fields!OPEX_VAL.Value)), "N/A", TRUE,
format(code.GetQuarterTotal(), "'$' #,0,,.00"+"M") & code.resetQuarterTotal()
)
This would work fine however the indicator will not be able to use it because its not a real number (output shows as $50.02M as an example).
Can anyone tell me how to get around this issue of formatting a textbox value determined by custom code without formatting the code value itself
February 10, 2016 at 1:51 pm
Can you explain more about the problem, because that expression should work just fine.
I have tested it to make sure and it does apply the formatting.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 10, 2016 at 1:55 pm
So in a nutshell, I am unable to apply textbox formatting using the expression option in the format properties when the value of the textbox is coming from custom code.
It turns out that in my expression, if i were to remove the code.resetQuartertotal piece, the formatting works fine as an expression. With the reset option appended to the code.QuarterTotals piece, it doesnt work.
February 10, 2016 at 2:50 pm
If I have a textbox called Sum_M with this expression for it's value:
=SUM(code.GetValue(Fields!Sum_M.Value))
And this expression for the Format:
= SWITCH(
ReportItems!Sum_M.Value >= 1000000 , "$ #,##0,,.00 M",
ReportItems!Sum_M.Value >= 100000, "$ #,##0,.00 K",
1=1, "$ #,##0.00"
)
Then the formatting works just as you want.
Is this how you are doing it?
Can you explain what is the difference between code.resetQuarterValue and code.QuarterValue?
Can you upload a sample report?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 11, 2016 at 6:45 am
Hey Mr Magoo
Thanks for getting back. Yes - in answer to your question, that formatting holds fine.
Heres my report custom code
Dim QuarterTotal As Integer
Function AddQuarterVals (ByVal FirstQuarterVal As Integer) As Integer
QuarterTotal = QuarterTotal + FirstQuarterVal
Return FirstQuarterVal
End Function
Function GetQuarterTotal() As Integer
Return QuarterTotal
End Function
Function resetQuarterTotal()
QuarterTotal = 0
End Function
The reset option just clears the value out of the variable so as not to inflate the total on multiple renders (thats for another topic).
Anyhoo.
In the textbox value, I have
=SWITCH(
ISNOTHING(SUM(Fields!OPEX_VAL.Value)), 0, TRUE,
cint(code.GetQuarterTotal())
)
This works with the format expression. If I append the reset total piece (&code.resetQuarterTotal), formatting will not work.
February 11, 2016 at 6:55 am
It's still a bit hard to tell what exactly you are doing.
Do you mean you are setting the Value expression for the textbox to this?
=SWITCH(
ISNOTHING(SUM(Fields!OPEX_VAL.Value)), 0, TRUE,
cint(code.GetQuarterTotal())
) & code.resetQuarterValue()
If not, what exactly are you doing?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 11, 2016 at 7:02 am
Yes - thats what im doing.. The value expression of the textbox is set to code...
The format expression for the textbox is my problem.
February 11, 2016 at 7:24 am
Well, your reset function doesn't return anything, so the expression is maybe corrupt or at best being implicitly convert to a string?
You could try adding return 0 to your reset function.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply