I'm looking to perform a calculation between two numbers but will display it as a Ratio ( 3:5 ).
Example:
[n1] / [n2] = n1 : n2 (in lowest form)
150 / 250 = 150 : 250 = 3:5
Thanks in advance!
One way would be to create a data item that casts each number to a character and then concatenates the two with a a text colon in between.
Try:
cast([field1] as varchar(10)) + cast([field2] as varchar([10]))
That still won't get me the Greatest Common Denominator in order to output in lowest form.
Here's the equivalent to what I'm trying to do in Java Script: http://stackoverflow.com/questions/4652468/is-there-a-javascript-function-that-reduces-a-fraction
Sorry, didn't read your post fully. I don't think there is an easy way in Cognos. Not sure if it will help, but I wrote a recursive query in SQL Server to find it:
with q1 as
(select 150.0 as X, 250.0 as Y, 150.0/250.0 as CALC),
q2 as
( select cast(X as decimal(18,6)) as X,
cast(Y as decimal(18,6)) as Y,
cast((X)%Q1.CALC as decimal(18,6)) as remainder
from q1
union all
select cast(q2.x-1.0 as decimal(18,6)) as X,
cast((q2.X-1)/Q1.CALC as decimal(18,6)) as Y,
cast((q2.X-1)%Q1.CALC as decimal(18,6)) as remainder
from q1,q2
where q2.x > 1)
select cast(cast(min(X) as integer) as varchar(10)) + ':' + CAST(cast(min(Y) as integer) as varchar(10))
from q2 where remainder = 0
OPTION (MAXRECURSION 1000)