How to format a string as currency

[TheBrenda]

Re: How to format a string as currency

I have a column (balance) that shows '$**.**' for employee balances and the correct balance for non-employees. To do this I had to cast the balance to a varchar. So the column is a varchar. And it handled the decimal, but the commas, $ (which is an easy add) and now come to think of it, no idea what it idoes for negative numbers.

But my issue at the moment is the commas. Any have any ideas how i can take a string like 1234567.89 and make it 1,234,567.89?

[Lynn]

Could you just leave it as a decimal in the query but use an advanced conditional style in your layout to display $**.** when it is an employee balance? Seems easier to me that way.

[TheBrenda]

I will look at doing that. I am trying to put together the simplest and shortest number of steps for my clients to use whenever they want to hide any numeric values based on some condition. Trying to keep the steps simple and easily reproducible. If I can come up with an expression, then they can just copy it from the instructions and replace the field name. (There is an added complication that you cannot total the string field directly, but that is another thread). So I will look at the conditional formatting, but would also like to see if someone has any ideas on how to format a string with commas to look like currency in the expression?

[bdbits]

Please take Lynn's post to heart. You are going about this the wrong way. You almost never need to cast to varchars and build your own formatting with expressions. Right-click the (numeric) column on the report, and from the popup menu pick Style > Data Format. Explore your options by selecting from the "Format type" dropdown.

[TheBrenda]

I do not see in Format, or Conditional Style Basic or Advance, how to format a numeric field to conditionally print a string: '$***,**.**'

[Lynn]

There isn't any pre-set thing to do that, but all the elements you need are there.

For example, let's say you have a relational data source and your report is a list. Your list has a column for type (Employee or Non-Employee) and a column for Balance. If you click on your Balance column you can make an advanced conditional style with an expression like [Type]='Employee' and then set the visible property on the advanced tab to "No". This will hide the amount. Then you can unlock the report and drag in a text object that you put $**.** into. Then click on that text item and do another advanced conditional style with the same expression as the first, except style the "all other values" to be the one where the visible property is set to No.

This will hide the actual amount for employees and instead show the text string.

[TheBrenda]

In my ignorant language, here is what I did.

My report has two columns in a list: balance, employee. I unlocked the report and put a conditional style on the Text Item Balance. The expression was [employee] =1 to not be visible.

Then added a text item into the balance column and called it mask. balance column now has two text items: mask and balance. added a conditional style on the mask. the express was [employee] <> 1 to not be visible.

Is this what you were saying? I could not understand your instruction exactly.

Now I would like to get the mask to be right justified. I have set that for the column and th mask text item in the conditional style - but still it is in the middle of the column.

[Lynn]

You may not have done it exactly as I described but there are many ways to do the same thing. Sounds like you are close to what you want.

You might need to use the "box type" property set to none instead of the "visible" property set to no. I should have said that to begin with since not visible means the thing is still there taking up space but you just can't see it. This would prevent your mask text item from aligning properly as you discovered. The box type of none means it won't take up any space. Try that and see if it does what you want. Not sure the impact on any totals so you should test that to see what you are facing.

[bdbits]

TheBrenda, sorry if I came across harshly. I do not think you are ignorant, there are many Cognos things I do not know about either. I think Lynn has a good handle on this, so I will stay silent now.  :-X

[TheBrenda]

Setting box type = none worked like a charm. And the totals are still correct. Much better solution than my slapped together CAST to varchar.

bdbits - no worries. You were trying to help and I was not offended.