how to re-add a decimal point to incoming data

[ry1633]

Not sure where to ask this -- if I should do it in Framework Manager or on the Report end (Report Studio or Workspace).

I have a field called PRODUCT_CODE that is an alphanumeric set that comes in from the database with no decimal point, but my users would like to display in the report with the decimal point after the 2nd-from-the-right.  Example

PRODUCT_CODE value:  B86200

Should display as:  B862.00

How can accomplish it and where should I do it in?  I assume probably in the model in Framework Manager.

[bus_pass_man]

People smarter than me can give you more flexible solutions and provide a through critique of the example that I have below, I imagine.  For example, I can't provide a good explanation how the substring which returns the last two characters actually works.   It does give you a pointer to one possible way to implement a solution to what you want. 

You want to be able to take strings of varying length and insert a character (in this case the decimal point) at a fixed point relative to the end of the string.

Use char_length to determine the string's length.   Use substring to parse out the two substrings.   Some vendors will have functions (like left and right in DB2) which could also help build up your string. 

I prefer the || concatenation operator over the + operator.  It doesn't have the possibility of being misunderstood.


substring
(
[Sales and marketing data].[SLS_ORDER_METHOD_DIM].[ORDER_METHOD_EN]
,
1
,
(
character_length (
[Sales and marketing data].[SLS_ORDER_METHOD_DIM].[ORDER_METHOD_EN]  )
-2)
)

||'.'||

substring
(
[Sales and marketing data].[SLS_ORDER_METHOD_DIM].[ORDER_METHOD_EN]
,
(   
character_length (
[Sales and marketing data].[SLS_ORDER_METHOD_DIM].[ORDER_METHOD_EN]  )
-1)
,
2
)

Result:

F.ax
Telepho.ne
Ma.il
E-ma.il
W.eb
Sales vis.it
Speci.al

[ry1633]

I've tried that in Framework Manager and also tried setting it to Decimal (separator,2) in Properties/Format.  Neither has worked so far.

[bdbits]

If the datatype of the item is actually character, then a decimal format will have no effect.

bus_pass_man is correct, and he took the trouble to run it against the Cognos sample data to show it is valid. The expression is such that it is easy to be a little off and not get the expected result. You might want to put the parts of the expression as data items, so you can validate they are evaluating as expected.

Is your data item perhaps padded with spaces? If so, you may need to add trim() to the expressions to strip the spaces. Also, if the actual values are a constant length, you can simplify the expression by putting constants instead of character_length() calculations.

One other thing - if this is ETLed data and they always want to see it this way, I would have the ETL changed. Much easier for you. :)