I have a column witha case statement in it...in one condition I have to concatinate spaces and amount...for example:' ' and 50....now I want to display that amount field in decimals...like 50.00 and still able to concatinate with spaces...what should I do please help
Database is oracle
Quote from: rr.bi1212 on 08 Dec 2016 12:14:50 AM
I have a column witha case statement in it...in one condition I have to concatinate spaces and amount...for example:' ' and 50....now I want to display that amount field in decimals...like 50.00 and still able to concatinate with spaces...what should I do please help
Is this just for display purposes in a list, or do you need to use the concatenated value for something specific? If it's just for display, you can leave the amount as a number format with two decimal places. You can unlock the structure of the report, drag a text item inside the list column (before the amount item) and enter your spaces. Don't forget to re-lock the structure of the report when you're done.
Cheers!
MF.
I have to concatenate with spaces as the spaces varies for each record.
Quote from: rr.bi1212 on 08 Dec 2016 09:56:54 AM
I have to concatenate with spaces as the spaces varies for each record.
Can you explain why? What is the requirement you're trying to solve? It's possible there might be a simpler way - we won't know unless you explain what the requirement is, though.
Cheers!
MF.
The amount field should display till ten characters example:9999999.99...if the amount is less than 10 characters the column should display with leading spaces and then concatenate with the amount.
For example if the amount is 50.00 then it should display " 50.00"....I achieved the rest of the things...but unable to display decimal part of this logic.
is it possible to just play around with the data format?
maximum No. of digits, Pattern, decimal number, padding...
Can you simply right justify the field?
lpad(
cast(
round(
[value]
,0
)
,varchar(10)
)
||
'.'
||
substr (
cast (
[value] * 100
,varchar(10)
)
,char_length (
cast (
[value] * 100
,varchar(10)
)
) -1
)
,10
,' '
)
only works with oracle, as I don't know a cognos equivalent of lpad.