Help on aggregate currentMeasure and a calculated expression

[cbyrne]

Hi Guys - I have the below data items/conditions in report studio list report plus i have another data item which is also below. The problem is that when i try and put a running total onto this column i get a totally wrong count into thousands.


Data Item - Sub Weights
CASE [OBJ_CATEGORY] WHEN 'TFT' THEN
'10.00' WHEN 'CRT' THEN '10.00' WHEN 'WORKSTATION' THEN '10.00' WHEN 'FLOPPY DISC' THEN '10.00' WHEN '3G CARD' THEN '25' WHEN 'A3 LAMINATOR' THEN '4' WHEN 'AC ADAPTOR' THEN '200' ELSE '' END


Data Item - Weights2
CASE [Final Destination] WHEN 'Recycling' THEN [Sub Weights] ELSE '' END


Data Item - Total 1
running-total([Running Total of Sub Weights])

Data Item - Running Total of Sub Weights
running-count([Weights 2])

In the column i have Weights2 and it is this i am trying to total the values. Hope you can help and that i have explained this properly.

Many Thanks

Chis

[Lynn]

In your expression for sub-weights you have the numbers enclosed in single quotes which means they are character data type. Maybe treating them as numerics would be worth a try.

You'd just remove the single quotes from around the numbers in the "THEN" clauses of Sub Weights and Also change the ELSE clause of Weights2 to be zero (I think).

[cbyrne]

Thanks Lynn i will give it a try and let you know.



Chris

[cbyrne]

Hi Lynn - Excellent this worked a treat.  ;D


Many Thanks


Chris

[cbyrne]

Hi Lynn - I have an overall total of equipment coming in on a work order and a total which went to scrap/recycle which is the total of weights2 column, have you got any ideas how i would get the percentage of what went to scrap/recycle?

Below are the two data items in my report.

Data Item - Total R-Pallets Received
REPCUSTOMATTRIB([OBJ_CODE],'*', 'RMP068','OBJ','*')

Data Item - Total Weights to Recycle
total([Weights 2])

Many Thanks in Advance

Chris

[Lynn]

Hey Chris,
Glad you got the first issue worked out. I don't know what that function REPCUSTOMATTRIB is, but assuming Total R-Pallets Received and Total Weights to Recycle are the figures you need then can you just divide [Total Weights to Recycle] / [Total R-Pallets Received] and then format as a % in the layout?

Depending on what the rest of the query looks like you may need to define the scope of your total function with the "for" clause and/or set the aggregation properties of the data item. For example,

total([Weights 2] for report)

would give the total of the Weights 2 data item across all rows retreived by the query. If you needed a total by some category you might use

total([Weights 2] for [Some Category])

Hope this helps!
Lynn

[cbyrne]

Hi Lynn -Thanks again.  I seem to be struggling with this one i have created the below Data Item called Percentage to Recycle but now i get an error when i run, please see below.


QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0446 Oracle returned an error message.

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

UDA-SQL-0450 An unexpected error encountered during the "prepare" operation.


Data Item - Percentage to Recycle
[Total(Weights 2)]/[Total R-Pallets Weight]



Data Item - Total(Weights2)
[Weights 2]

Data Item - Total R-Pallets Weight
REPCUSTOMATTRIB([EVT_PARENT2],'*', 'RMP068','EVNT','R5') - This is a custom field in our data base.


Hope you can help Lynn, thithe last piece of the jigsaw.


Kind Regards

Chris

[Lynn]

sounds like something is still character datatype? ya gotta get 'em all to be numeric to do math on them. If you changed the weights2 item to be numeric like we talked about previously then is your custom field returning character datatype? You could cast it to a number of solve the problem that way perhaps.

There is a cast function in cognos but you can also use the Oracle function (TO_NUMBER). I prefer database functions.