If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to perform calculation on "Layout Calculation" columns

Started by Dhomer60, 07 Jul 2020 01:07:29 PM

Previous topic - Next topic

Dhomer60

Hello,

I have bit of an issue and I cannot figure out the answer.

I have a layout calculation that gives the average cost based on several other criteria. The names/values have been changed:

IF ([SQL1].[OBJ_CLASS] = 'HP' AND [SQL1].[EVT_PPM] = 'MPM') THEN ('$15,000')
ELSE IF
([SQL1].[OBJ_CLASS] = 'HP' AND [SQL1].[EVT_PPM] = 'MAPM') THEN ('$25,000')
ELSE IF
([SQL1].[OBJ_CLASS] = 'SF' AND [SQL1].[EVT_PPM] = 'MPM') THEN ('$40,000')
ELSE IF
([SQL1].[OBJ_CLASS] = 'SF' AND [SQL1].[EVT_PPM] = 'MAPM') THEN ('$45,000')
ELSE
NULL

I want to add up the total cost of the event and, if that event was done in-house, I want to subtract the total cost from the average cost.

IF ([In House?] = 'YES') THEN (([Issued Part Raw Calc] + [Booked Labor Cost] +[Direct Purchase Cost Raw Calc] - [Average Cost])) ELSE NULL

However, I cannot subtract the total cost from the average cost because I continually get the following error:

An error occurred while performing operation 'sqlOpenResult' status='-9'. UDA-SQL-0107 A general exception has occurred during the operation "open result".ORA-01722: invalid number

I know that the issue is with the data type on the Average Cost field but I cant figure it out. Ive have changed the data format and tried to use Cast and To_Number but continue to the same errors.

EDIT: I have figured out the issue. It was user error on my part.