Error "Input string was not in a correct format" when opening Department Summary

Products

• Asset Management Solution
• IT Management Suite

Issue/Introduction

When going to Resource Management\Resources\Organizational Types\Department\Group Management\ and selecting Department Summary, the following error is displayed:

An exception has occurred while generating Department's assigned users rollup costs. Input string was not in a correct format.

The following error is in a.log:

Log File Name: C:\Program Files\Altiris\Notification Server\Logs\a.log
Priority: 1
Date: 04/10/2008 14:42:54
Tick Count: 625973531
Host Name: Localhost

Process: w3wp.exe (2136)
Thread ID: 9552
Module: AltirisNativeHelper.dll
Source: Altiris.AssetContractCommon.Summary.DepartmentAssignedUsersSummaryCtrl.getUserCountAssetCost
Description: An exception has occurred while generating Department's assigned users rollup costs. Input string was not in a correct format.

Environment

ITMS 8.x

Asset Management Solution 8.x

Cause

The summary (where the problem is generated) is invoking stored procedure 'spDepartmentAssetRolledUpCosts'. One of the columns the stored procedure returns is "assetDirectCost" which joins the tables #tmp, vAssetDepartmentOwner, Inv_Cost_Items

The table 'Inv_Cost_Items' is missing data that computer's or users associated with the department do not contain an amount and therefore it returns a NULL value. The NULL value is what seems to be generating the error. It is expecting a legitimate numerical value.

The routine within spDepartmentAssetRolledUpCosts that calculates this value is found in lines 112-122 (as follows):

-- update assetDirectCost
   if(@checkCost = 1 )
     begin
      update #tmp set assetDirectCost = t.cost from
   (select sum(CI.Amount*vAD.[Ownership Percentage]/100)as cost, #tmp.Guid as Guid
       from vAssetDepartmentOwner vAD
       join #tmp on #tmp.Guid = vAD._DepartmentGuid
       join Inv_Cost_Items CI on CI._ResourceGuid = vAD._AssetGuid
      group by #tmp.Guid) as t
      where t.Guid = #tmp.Guid
     end

Resolution

The problem/error is usually a temporary problem because the required inventory items to calculate and return a valid number should eventually be submitted by the associated computers.
                             
The problem can be eliminated regardless of available inventory if the code in the stored procedure is modified as follows to check for a NULL value, and if it is returned, to return 0. Otherwise it will return the calculated sum.

-- update assetDirectCost
   if(@checkCost = 1 )
    begin
     update #tmp set assetDirectCost = t.cost from
     (select
      CASE when sum(CI.Amount*vAD.[Ownership Percentage]/100) IS NULL
       THEN 0
       ELSE sum(CI.Amount*vAD.[Ownership Percentage]/100)
      end as cost
     ,#tmp.Guid as Guid
     from vAssetDepartmentOwner vAD
      join #tmp on #tmp.Guid = vAD._DepartmentGuid
      join Inv_Cost_Items CI on CI._ResourceGuid = vAD._AssetGuid
     group by #tmp.Guid) as t
     where t.Guid = #tmp.Guid
    end

Attached to the KB is an altered stored procedure that can be run against the Altiris database using the query analyzer.