conditionally format number with decimals

Is there a way to format a number column with two decimals most of the time and if a parameter (showCents) is selected as No (do not show the cents) then format the number without the decimal places? I can round and trunc in SQL, but the format Number in BIRT seems to always show the decimal places.

Find more posts tagged with

Comments

mwilliams

Hi cmrdjr,

One possible solution is to put two text boxes in the table cell that the decimal/no decimal number will be.

In one put:
<VALUE-OF format="$#,###.00">row["COLUMN_NAME"]</VALUE-OF>

In the other put:
<VALUE-OF format="$#,###">row["COLUMN_NAME"]</VALUE-OF>

Then in the OnRender script method for each one put:
if (params["PARAM_NAME"].value == "no"){
//if (params["PARAM_NAME"].value == "yes"){
this.text = "";
}

The "no" line in one and the "yes" line in the other, however you want it set up.

Again, this is just one solution. There are probably other ways that it can be done with scripting.

Hope this helps.

cmrdjr

I'll give this a shot and let you know. I have plenty of scripting in this report now, so a little more won't hurt anything.

cmrdjr

Just tried this and it works using a dynamic text box. However I need to also be able to add a hyperlink to this data element. There is no hyperlink property for a text box. I then thought of trying to put the logic in the onRender or onCreate or onPrepare Script for the data element. The problem I am now having is I don't know how to assign the format to the item. When I type "this." and the box for acceptable items includes a getStyle, but no setStyle and there is no set DecimalFormat.

Any ideas?

bhanley

Instead of using dynamic text, drag the data element into the cell twice. Set the visibility on both to be hidden by default. Then in the scripting layer, evaluate your parameter and determine which one you want to actually show at run time.

Now that the correct data element is displaying, select each one, look at the bottom of the IDE to the properties pane. Select "Format Number". Set the formatting on each one accordingly.

Lastly, set the Hyperlink up on each data element. You should be good to go.

You may see a binding error when you try to add the same data element twice tot he same table. Just give one of them a unique name (you will be prompted to do so). You will still be drawing on the same data source, this just allows the engine to treat each element as an independent entity (which you want to have happen).

Good Luck!

bhanley

I just had an even better idea.

Add the two instances of the data element and hide them both as described above. But when you hide them, use the "conditional visibility" feature in BIRT. After you click the "Hide Element" checkbox, you will be able to enter an expression into the Detail area. Simply use the expression editor to evaluate your show/hide logic.

This will handle all the run-time work for you. No additional onRender scripting required!