Creating a Report from the Report Builder is not returning correct number of Resources

Article:TECH152895  |  Created: 2011-02-08  |  Updated: 2011-02-08  |  Article URL http://www.symantec.com/docs/TECH152895
Article Type
Technical Solution


Issue



Created a Report through the report build and when you add a resource type like computer it shows 100 computers but after adding associations to show now it is only showing 84 computers now.


Environment



SMP 7.0 sp5

Asset Management Solution 7.0 mr2

CMDB solution 7.0 sp1 mr2


Cause



When adding associations to be displayed the way the sql is created it will only show the resources with those associations.  It will not show those that do not have those associations set.

Here is an example of how the report is made:

 

 

@g2_AssetUserOwners uniqueidentifier SET @g2_AssetUserOwners = 'ed35a8d1-bf60-4771-9dde-092c146c485a'

[vri1_Computer]

.[Guid] AS [_ItemGuid],

[vri1_Computer]

.[Name]

FROM

[vRM_Computer_Item]

 

 

 

AS [vri1_Computer]LEFT OUTER JOIN ([ResourceAssociation] AS [ra3_Asset User Owners]LEFT OUTER JOIN [vRM_User_Item] AS [vri4_User]ON ([ra3_Asset User Owners].[ChildResourceGuid] = [vri4_User].[Guid]))

 

ON ([vri1_Computer].[Guid] = [ra3_Asset User Owners].[ParentResourceGuid])

WHERE

 

(

 

(

 

([ra3_Asset User Owners].[ResourceAssociationTypeGuid] = @g2_AssetUserOwners)

 

OR

 

([ra3_Asset User Owners].[ResourceAssociationTypeGuid] IS NULL)

 

)

 

)

 

 

 

 

 

DECLARE

SELECT


Solution



To change the report so you see all resources and the data associated to those resources you have to change the filtering of the report from the where clause to the join.

Here is an example of what it should look like after the modifications:

 

 

@g2_AssetUserOwners uniqueidentifier SET @g2_AssetUserOwners = 'ed35a8d1-bf60-4771-9dde-092c146c485a'

[vri1_Computer]

.[Guid] AS [_ItemGuid],

[vri1_Computer]

.[Name]

FROM

[vRM_Computer_Item]

 

 

AS [vri1_Computer]LEFT OUTER JOIN [ResourceAssociation] AS [ra3_Asset User Owners]ON ([vri1_Computer].[Guid] = [ra3_Asset User Owners].[ParentResourceGuid])

 

AND (([ra3_Asset User Owners].[ResourceAssociationTypeGuid] = @g2_AssetUserOwners)

 

OR

 

([ra3_Asset User Owners].[ResourceAssociationTypeGuid] IS NULL))

 

 

LEFT OUTER JOIN [vRM_User_Item] AS [vri4_User]ON ([ra3_Asset User Owners].[ChildResourceGuid] = [vri4_User].[Guid])

 

 

 

 

DECLARE

SELECT




Article URL http://www.symantec.com/docs/TECH152895


Terms of use for this information are found in Legal Notices