select @TotalAssetCount = ISNULL(COUNT(*),0)
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
select @TotalAssetCount = ISNULL(@TotalAssetCount,0) + ISNULL(COUNT(*) ,0)
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- Get all locations and their immediate parent, if they do not have one, they are their own parent
insert into @Location (LocationDimKey,ParentLocationDimKey)
select a.ProvanceLocationDimKey,isnull(b.LocationIsContainedByLocation_ProvanceLocationDimKey,a.ProvanceLocationDimKey)
from ProvanceLocationDimvw a
left join ProvanceLocationIsContainedByLocationFactvw b on a.ProvanceLocationDimKey = b.ProvanceLocationDimKey
and b.DeletedDate is null
where A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- If they have a parent update the ParentLocationDimKey untill there are no more parents
while @@ROWCOUNT <> 0
begin
update @Location
set ParentLocationDimKey = b.LocationIsContainedByLocation_ProvanceLocationDimKey
from @Location a
join ProvanceLocationIsContainedByLocationFactvw b on a.ParentLocationDimKey = b.ProvanceLocationDimKey
and a.ParentLocationDimKey <> b.LocationIsContainedByLocation_ProvanceLocationDimKey
and b.DeletedDate is null
end
/*
select * from @location
select ProvanceLocationDimKey,DisplayName from ProvanceLocationDimvw
*/
-- select a.DisplayName,d.*,e.DisplayName
select count(*) as AssetCount, isnull(e.DisplayName,'Unknown') DisplayName,@TotalAssetCount TotalAssetCount, isnull(e.ProvanceLocationDimKey,-1) LocationDimKey
from ProvanceHardwareAssetStandardDimvw a
join (select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
union all
select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete) b on a.BaseManagedEntityId = b.BaseManagedEntityId
left join ProvanceHardwareAssetStandardHasLocationFactvw c on a.ProvanceHardwareAssetStandardDimKey = c.ProvanceHardwareAssetStandardDimKey AND C.DeletedDate IS NULL
left join @Location d on c.HardwareAssetStandardHasLocation_ProvanceLocationDimKey = d.LocationDimKey
left join ProvanceLocationDimvw e on d.ParentLocationDimKey = e.ProvanceLocationDimKey
group by e.DisplayName,e.ProvanceLocationDimKey</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AssetCount">
<DataField>AssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TotalAssetCount">
<DataField>TotalAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="LocationDimKey">
<DataField>LocationDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="AssetByCostCenter">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query AssetsByCostCenter HardwareAssetDashboard
set nocount on
declare @ComputerAssetLifeCycleDisposed varchar(255),
@ComputerAssetLifeCycleLifeCycleReturned varchar(255),
@GeneralAssetLifeCycleDisposed varchar(255),
@GeneralAssetLifeCycleReturned varchar(255),
@ObjectStatusPendingDelete varchar(255),
@TotalAssetCount int
select @TotalAssetCount = ISNULL(COUNT(*) ,0)
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
select @TotalAssetCount = ISNULL(@TotalAssetCount,0) + ISNULL(COUNT(*),0)
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- Get all CostCenters and their immediate parent, if they do not have one, they are their own parent
insert into @CostCenter (CostCenterDimKey,ParentCostCenterDimKey)
select a.ProvanceCostCenterDimKey,isnull(b.CostCenterIsContainedByCostCenter_ProvanceCostCenterDimKey,a.ProvanceCostCenterDimKey)
from ProvanceCostCenterDimvw a
left join ProvanceCostCenterIsContainedByCostCenterFactvw b on a.ProvanceCostCenterDimKey = b.ProvanceCostCenterDimKey
and b.DeletedDate is null
where A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- If they have a parent update the ParentCostCenterDimKey untill there are no more parents
while @@ROWCOUNT <> 0
begin
update @CostCenter
set ParentCostCenterDimKey = b.CostCenterIsContainedByCostCenter_ProvanceCostCenterDimKey
from @CostCenter a
join ProvanceCostCenterIsContainedByCostCenterFactvw b on a.ParentCostCenterDimKey = b.ProvanceCostCenterDimKey
and a.ParentCostCenterDimKey <> b.CostCenterIsContainedByCostCenter_ProvanceCostCenterDimKey
and b.DeletedDate is null
end
/*
select * from @CostCenter
select ProvanceCostCenterDimKey,DisplayName from ProvanceCostCenterDimvw
*/
-- select a.DisplayName,d.*,e.DisplayName
select count(*) as AssetCount, isnull(e.DisplayName,'Unknown') DisplayName,@TotalAssetCount TotalAssetCount, isnull(e.ProvanceCostCenterDimKey,-1) CostCenterDimKey
from ProvanceHardwareAssetStandardDimvw a
join (select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
union all
select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete) b on a.BaseManagedEntityId = b.BaseManagedEntityId
left join ProvanceHardwareAssetStandardHasCostCenterFactvw c on a.ProvanceHardwareAssetStandardDimKey = c.ProvanceHardwareAssetStandardDimKey AND C.DeletedDate IS NULL
left join @CostCenter d on c.HardwareAssetStandardHasCostCenter_ProvanceCostCenterDimKey = d.CostCenterDimKey
left join ProvanceCostCenterDimvw e on d.ParentCostCenterDimKey = e.ProvanceCostCenterDimKey
group by e.DisplayName,e.ProvanceCostCenterDimKey</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AssetCount">
<DataField>AssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TotalAssetCount">
<DataField>TotalAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CostCenterDimKey">
<DataField>CostCenterDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="AssetsByOrganization">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query AssetsByOrganization HardwareAssetDashboard
declare @ComputerAssetLifeCycleDisposed varchar(255),
@ComputerAssetLifeCycleLifeCycleReturned varchar(255),
@GeneralAssetLifeCycleDisposed varchar(255),
@GeneralAssetLifeCycleReturned varchar(255),
@ObjectStatusPendingDelete varchar(255),
@TotalAssetCount int
select @TotalAssetCount = ISNULL(COUNT(*) ,0)
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
select @TotalAssetCount =ISNULL( @TotalAssetCount,0) + ISNULL(COUNT(*) ,0)
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- Get all Organizations and their immediate parent, if they do not have one, they are their own parent
insert into @Organization (OrganizationDimKey,ParentOrganizationDimKey)
select a.ProvanceOrganizationDimKey,isnull(b.OrganizationIsContainedByOrganization_ProvanceOrganizationDimKey,a.ProvanceOrganizationDimKey)
from ProvanceOrganizationDimvw a
left join ProvanceOrganizationIsContainedByOrganizationFactvw b on a.ProvanceOrganizationDimKey = b.ProvanceOrganizationDimKey
and b.DeletedDate is null
where A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
-- If they have a parent update the ParentOrganizationDimKey untill there are no more parents
while @@ROWCOUNT <> 0
begin
update @Organization
set ParentOrganizationDimKey = b.OrganizationIsContainedByOrganization_ProvanceOrganizationDimKey
from @Organization a
join ProvanceOrganizationIsContainedByOrganizationFactvw b on a.ParentOrganizationDimKey = b.ProvanceOrganizationDimKey
and a.ParentOrganizationDimKey <> b.OrganizationIsContainedByOrganization_ProvanceOrganizationDimKey
and b.DeletedDate is null
end
/*
select * from @Organization
select ProvanceOrganizationDimKey,DisplayName from ProvanceOrganizationDimvw
*/
-- select a.DisplayName,d.*,e.DisplayName
select count(*) as AssetCount, isnull(e.DisplayName,'Unknown') DisplayName,@TotalAssetCount TotalAssetCount, isnull(e.ProvanceOrganizationDimKey,-1) OrganizationDimKey
from ProvanceHardwareAssetStandardDimvw a
join (select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposed,@GeneralAssetLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
union all
select a.EntityDimKey,a.BaseManagedEntityId
from ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposed,@ComputerAssetLifeCycleLifeCycleReturned)
AND A.IsDeleted = 0
and ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete) b on a.BaseManagedEntityId = b.BaseManagedEntityId
left join ProvanceHardwareAssetStandardHasOrganizationFactvw c on a.ProvanceHardwareAssetStandardDimKey = c.ProvanceHardwareAssetStandardDimKey AND C.DeletedDate IS NULL
left join @Organization d on c.HardwareAssetStandardHasOrganization_ProvanceOrganizationDimKey = d.OrganizationDimKey
left join ProvanceOrganizationDimvw e on d.ParentOrganizationDimKey = e.ProvanceOrganizationDimKey
group by e.DisplayName,e.ProvanceOrganizationDimKey</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AssetCount">
<DataField>AssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TotalAssetCount">
<DataField>TotalAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrganizationDimKey">
<DataField>OrganizationDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="InvestmentByManufacturer">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query InvestmentbyManufacturer HardwareAssetDashboard
DECLARE @ObjectStatusPendingDelete VARCHAR(255)
SELECT @ObjectStatusPendingDelete = 'System.ConfigItem.ObjectStatusEnum.PendingDelete'
;with Investments as (
SELECT A.Cost Cost,
A.CostCurrency CostCurrency,
E.DisplayName DisplayName,
DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0) ReceivedDate,
E.ProvanceCompanyDimKey ProvanceCompanyDimKey
FROM ProvanceHardwareAssetStandardDimvw A
JOIN ProvanceHardwareAssetStandardIsHardwareCatalogSerializedFactvw B ON A.ProvanceHardwareAssetStandardDimKey = B.ProvanceHardwareAssetStandardDimKey AND B.DeletedDate IS NULL
JOIN ProvanceHardwareCatalogSerializedDim C ON B.HardwareAssetStandardIsHardwareCatalogSerialized_ProvanceHardwareCatalogSerializedDimKey = C.ProvanceHardwareCatalogSerializedDimKey AND C.IsDeleted = 0 AND ISNULL(C.ObjectStatus,'') <> @ObjectStatusPendingDelete
JOIN ProvanceHardwareCatalogSerializedHasManufacturerFactvw D ON C.ProvanceHardwareCatalogSerializedDimKey = D.ProvanceHardwareCatalogSerializedDimKey AND D.DeletedDate IS NULL
JOIN ProvanceCompanyDimvw E ON D.HardwareCatalogSerializedHasManufacturer_ProvanceCompanyDimKey = E.ProvanceCompanyDimKey AND E.IsDeleted = 0 AND ISNULL(E.ObjectStatus,'') <> @ObjectStatusPendingDelete
WHERE DATEDIFF(mm,A.ReceivedDate,GetDate()) < 13)
-- End of Investments
select SUM(A.Cost) Cost,
MIN(A.CostCurrency) CostIsoCurrencyCode,
MIN(A.DisplayName) DisplayName,
DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0) ReceivedDate,
A.ProvanceCompanyDimKey CompanyDimKey
FROM Investments A
JOIN (SELECT TOP 10 b.ProvanceCompanyDimKey
FROM Investments B
GROUP BY b.ProvanceCompanyDimKey
ORDER BY SUM(B.Cost)) C ON A.ProvanceCompanyDimKey = C.ProvanceCompanyDimKey
GROUP BY A.ProvanceCompanyDimKey, DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0)</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Cost">
<DataField>Cost</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostIsoCurrencyCode">
<DataField>CostIsoCurrencyCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ReceivedDate">
<DataField>ReceivedDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="CompanyDimKey">
<DataField>CompanyDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="InvestmentBySupplier">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query InvestmentbySupplier HardwareAssetDashboard
DECLARE @ObjectStatusPendingDelete VARCHAR(255)
SELECT @ObjectStatusPendingDelete = 'System.ConfigItem.ObjectStatusEnum.PendingDelete'
;with Investments as (
SELECT A.Cost Cost,
A.CostCurrency CostCurrency,
C.DisplayName DisplayName,
DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0) ReceivedDate,
C.ProvanceCompanyDimKey ProvanceCompanyDimKey
FROM ProvanceHardwareAssetStandardDimvw A
JOIN ProvanceHardwareAssetStandardHasSupplierFactvw B ON A.ProvanceHardwareAssetStandardDimKey = B.ProvanceHardwareAssetStandardDimKey AND B.DeletedDate IS NULL
JOIN ProvanceCompanyDimvw C ON b.HardwareAssetStandardHasSupplier_ProvanceCompanyDimKey = C.ProvanceCompanyDimKey AND C.IsDeleted = 0 AND ISNULL(C.ObjectStatus,'') <> @ObjectStatusPendingDelete
WHERE DATEDIFF(mm,A.ReceivedDate,GetDate()) < 13)
-- End of Investments
select SUM(A.Cost) Cost,
MIN(A.CostCurrency) CostIsoCurrencyCode,
MIN(A.DisplayName) DisplayName,
DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0) ReceivedDate,
A.ProvanceCompanyDimKey CompanyDimKey
FROM Investments A
JOIN (SELECT TOP 10 b.ProvanceCompanyDimKey
FROM Investments B
GROUP BY b.ProvanceCompanyDimKey
ORDER BY SUM(B.Cost)) C ON A.ProvanceCompanyDimKey = C.ProvanceCompanyDimKey
GROUP BY A.ProvanceCompanyDimKey, DATEADD(MONTH,DATEDIFF(MONTH,0,A.ReceivedDate),0)</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Cost">
<DataField>Cost</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostIsoCurrencyCode">
<DataField>CostIsoCurrencyCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ReceivedDate">
<DataField>ReceivedDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="CompanyDimKey">
<DataField>CompanyDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="AgeOfAsset">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query AgeOfAssetHardwareAssetDashboard
DECLARE @ObjectStatusPendingDelete VARCHAR(255),
@ComputerAssetLifeCycleDisposedId VARCHAR(255),
@ComputerAssetLifeCycleReturnedId VARCHAR(255),
@GeneralAssetLifeCycleDisposedId VARCHAR(255),
@GeneralAssetLifeCycleReturnedId VARCHAR(255),
@HardwareAssetOwnershipTypeNonOwnedId VARCHAR(255),
@HardwareAssetOwnershipTypeLeasedId VARCHAR(255)
SELECT COUNT(1) AS AssetCount, C.AgeGroup, MAX(C.TotalAssetCount) TotalAssetCount
FROM (SELECT COUNT(*) OVER () as TotalAssetCount,
B.Cost,
CASE WHEN B.ReceivedDate is null THEN 'Unknown'
WHEN (DATEDIFF(yy,B.ReceivedDate,GetDate())) BETWEEN 0 AND 1 THEN 'IsLessTwoYrs'
WHEN (DATEDIFF(yy,B.ReceivedDate,GetDate())) BETWEEN 2 AND 5 THEN 'IsBetweenTwoAndFiveYrs'
WHEN (DATEDIFF(yy,B.ReceivedDate,GetDate()))> 5 THEN 'IsGreaterFiveYrs'
END AgeGroup
FROM (SELECT a.Cost,a.ReceivedDate
FROM ProvanceComputerAssetDimvw A
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposedId,@ComputerAssetLifeCycleReturnedId)
AND ISNULL(A.OwnershipType,'') NOT IN (@HardwareAssetOwnershipTypeNonOwnedId,@HardwareAssetOwnershipTypeLeasedId)
AND A.IsDeleted = 0
AND ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
UNION ALL
SELECT a.Cost,a.ReceivedDate
FROM ProvanceGeneralAssetDimvw A
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposedId,@GeneralAssetLifeCycleReturnedId)
AND ISNULL(A.OwnershipType,'') NOT IN (@HardwareAssetOwnershipTypeNonOwnedId,@HardwareAssetOwnershipTypeLeasedId)
AND A.IsDeleted = 0
AND ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
) B ) C
GROUP BY C.AgeGroup</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AssetCount">
<DataField>AssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgeGroup">
<DataField>AgeGroup</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TotalAssetCount">
<DataField>TotalAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="AssetStatus">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@UserLanguage">
<Value>=Code.GetReportLocLanguageCode()</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query AssetStatus HardwareAssetDashboard
-- DECLARE @UserLanguage VARCHAR(255) SELECT @UserLanguage = 'ENU'
select c.LifeCycleValue HardwareAssetLifecycleStatusValue,
MAX(ProvanceComputerAssetLifeCycleId) HardwareAssetLifecycleStatusId,
count(*) AssetCount,
sum(LeasedYn) LeaseAssetCount,
max(c.TotalAssetCount) TotalAssetCount,
count(*) - sum(LeasedYn) OwnedAssetCount
From ( select COUNT(*) OVER () TotalAssetCount,
LifeCycleValue,ProvanceComputerAssetLifeCycleId,
CASE WHEN OwnershipType = @HardwareAssetOwnershipTypeLeasedId THEN 1 ELSE 0 END LeasedYn
from (SELECT B.ProvanceComputerAssetLifeCycleValue LifeCycleValue,A.OwnershipType,B.ProvanceComputerAssetLifeCycleId
FROM ProvanceComputerAssetDimvw A
LEFT JOIN (SELECT A.ProvanceComputerAssetLifeCycleId,
ISNULL(B.DisplayName, A.ProvanceComputerAssetLifeCycleValue) AS ProvanceComputerAssetLifeCycleValue
FROM ProvanceComputerAssetLifeCyclevw a
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId AND B.LanguageCode = @UserLanguage) B ON A.LifeCycle_ProvanceComputerAssetLifeCycleId = B.ProvanceComputerAssetLifeCycleId
WHERE A.LifeCycle NOT IN (@ComputerAssetLifeCycleDisposedId,@ComputerAssetLifeCycleReturnedId)
AND ISNULL(A.OwnershipType,'') NOT IN (@HardwareAssetOwnershipTypeNonOwnedId)
AND A.IsDeleted = 0
AND ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete
UNION ALL
SELECT b.ProvanceGeneralAssetLifeCycleValue, A.OwnershipType,B.ProvanceGeneralAssetLifeCycleId
FROM ProvanceGeneralAssetDimvw A
LEFT JOIN (SELECT A.ProvanceGeneralAssetLifeCycleId,
ISNULL(B.DisplayName, A.ProvanceGeneralAssetLifeCycleValue) AS ProvanceGeneralAssetLifeCycleValue
FROM ProvanceGeneralAssetLifeCyclevw a
LEFT OUTER JOIN DisplayStringDim B ON A.EnumTypeId = B.BaseManagedEntityId AND B.LanguageCode = @UserLanguage) B ON A.LifeCycle_ProvanceGeneralAssetLifeCycleId = B.ProvanceGeneralAssetLifeCycleId
WHERE A.LifeCycle NOT IN (@GeneralAssetLifeCycleDisposedId,@GeneralAssetLifeCycleReturnedId)
AND ISNULL(A.OwnershipType,'') NOT IN (@HardwareAssetOwnershipTypeNonOwnedId)
AND A.IsDeleted = 0
AND ISNULL(A.ObjectStatus,'') <> @ObjectStatusPendingDelete ) b
SELECT 1 As GroupingProperty,
Z.ContractType,
Sum(Case when datediff(d,GETDATE(),Z.ExpiryDate) <= @ThresholdContractExpired THEN 1 ELSE 0 END) AS Expired,
Sum(Case WHen datediff(d,GETDATE(),Z.ExpiryDate) > @ThresholdContractExpired
and datediff(d,GETDATE(),Z.ExpiryDate) <= @ThresholdContractCloseToExpired THEN 1 Else 0 END) AS CloseToExpired,
Sum(Case When datediff(d,GETDATE(),Z.ExpiryDate) > @ThresholdContractCloseToExpired THEN 1 Else 0 END) AS Good
FROM
(
SELECT 'Lease' AS ContractType, A.ExpiryDate
FROM ProvanceLeaseAgreementDimvw A
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete
UNION ALL
SELECT 'Warranty' AS ContractType, B.ExpiryDate
FROM ProvanceWarrantyAgreementDimvw A
JOIN ProvanceHardwareAssetStandardHasWarrantyAgreementFactvw B ON A.ProvanceWarrantyAgreementDimKey = B.HardwareAssetStandardHasWarrantyAgreement_ProvanceWarrantyAgreementDimKey
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete
UNION ALL
SELECT 'Support' AS ContractType, A.ExpiryDate
FROM ProvanceHardwareMaintenanceAgreementDimvw A
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete
UNION ALL
SELECT 'Disposal' AS ContractType, a.ExpiryDate
FROM ProvanceDisposalAgreementDimvw A
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete
UNION ALL
SELECT 'UserDefined' AS ContractType, A.ExpiryDate
FROM ProvanceUserDefinedAgreementDimvw A
WHERE A.IsDeleted = 0
AND ISNULL(a.ObjectStatus,'') <> @objectStatusPendingDelete) Z
GROUP BY Z.ContractType</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="ContractType">
<DataField>ContractType</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="GroupingProperty">
<DataField>GroupingProperty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Expired">
<DataField>Expired</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CloseToExpired">
<DataField>CloseToExpired</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Good">
<DataField>Good</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ContractAdminSettings">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>--Query ContractAdminSettings HardwareAssetDashboard
-- The full outer join will ensure we have a value even if there is no row in ProvanceAssetAdminSettings.
SELECT DISTINCT TOP 1 ISNULL(B.AgreementExpiredThreshold,1) ExpiredThresholdDays, ISNULL(B.AgreementExpiryWarningThreshold,365) CloseToExpiredThresholdDays
FROM (select 1 C1) a
full outer join ProvanceAssetAdminSettingsDim B on B.IsDeleted = 0
--AND SourceId = @SourceId</CommandText>
</Query>
<Fields>
<Field Name="ExpiredThresholdDays">
<DataField>ExpiredThresholdDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CloseToExpiredThresholdDays">
<DataField>CloseToExpiredThresholdDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="LT_Report">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>LT_Report</DataSetName>
<ValueField>XML_F52E2B61_18A1_11d1_B105_00805F49916B</ValueField>
</DataSetReference>
</DefaultValue>
<Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="ThresholdContractExpired">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>ContractAdminSettings</DataSetName>
<ValueField>ExpiredThresholdDays</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Threshold Contract Expired</Prompt>
</ReportParameter>
<ReportParameter Name="ThresholdContractCloseToExpired">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>ContractAdminSettings</DataSetName>
<ValueField>CloseToExpiredThresholdDays</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Threshold Contract Close To Expired</Prompt>
</ReportParameter>
</ReportParameters>
<Code><Script>
Const LocTableStringQuery As String = "R/S[@N='{0}']/text()"
Const TimeZoneParameterName As String = "TimeZone"
Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
Const IsRelativeTimeSupported As Boolean = False
Const TimeTypeParameterName As String = "TimeType"
Const TimeWeekMapParameterName As String = "TimeWeekMap"
Dim LocTables As System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)
Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
Dim ReportStartDate As DateTime
Dim ReportEndDate As DateTime
Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
Dim ReportCulture As System.Globalization.CultureInfo
Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor
Dim TargetList As String
Protected Overrides Sub OnInit()
LocTables = new System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)()
ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneInformation.Current
ReportStartDate = DateTime.MinValue
ReportEndDate = DateTime.MinValue
ReportTime = Nothing
ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
TargetList =Nothing
End Sub
'Public Function GetCallingManagementGroupId() As String
' Return 'Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.M'anagementGroupId
'End Function
Public Function GetReportLocLanguageCode() As String
Return ReportCulture.ThreeLetterWindowsLanguageName
End Function
Public Function GetReportLCID() As Integer
Return ReportCulture.LCID
End Function
Public Function GetLocTable(Name As String) As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
Dim LocTable As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
If Not LocTables.TryGetValue(Name, LocTable) Then
LocTable = New Microsoft.EnterpriseManagement.Reporting.XmlStringTable(LocTableStringQuery, Report.Parameters(Name).Value)
LocTables.Add(Name, LocTable)
End If
Return LocTable
End Function
Public Function GetReportTimeZone() As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
If IsNothing(ReportTimeZone) Then ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation.FromValueString(Report.Parameters(TimeZoneParameterName).Value)
Return ReportTimeZone
End Function
Public Function ToDbDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToUniversalTime(DateValue)
End Function
Public Function ToReportDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToLocalTime(DateValue)
End Function
Public Function GetReportStartDate() As DateTime
If (ReportStartDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
Else
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value)
End if
End If
return ReportStartDate
End Function
Public Function GetReportEndDate() As DateTime
If (ReportEndDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
If IsBusinessHours(GetReportTimeFilter()) Then ReportEndDate = ReportCulture.Calendar.AddDays(ReportEndDate, 1)
Else
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value)
End if
End If
return ReportEndDate
End Function
Public Function GetReportTimeFilter() As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
If IsNothing(ReportTime) Then ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Report.Parameters(TimeTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
return ReportTime
End Function
Public Function IsBusinessHours(Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As Boolean
return (Not IsNothing(Value)) And (Value.TimeType = Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Business)
End Function
Public Function FormatDateTime(Format As String, Value As DateTime) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatNumber(Format As String, Value As Decimal) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatString(Format As String, ParamArray Values() as Object) As String
return String.Format(ReportCulture, Format, Values)
End Function
Public Function NullFormatString(Format As String, Value as String) As String
return IIF(String.IsNullOrEmpty(Value), String.Empty, String.Format(ReportCulture, Format, Value))
End Function
Public Function FormatBusinessHours(Format As String, Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As String
Dim result As String
If IsBusinessHours(Value) Then
Dim firstDay As DayOfWeek
Dim days As System.Collections.Generic.List(Of String)
firstDay = ReportCulture.DateTimeFormat.FirstDayOfWeek
days = new System.Collections.Generic.List(Of String)()
For loopDay As DayOfWeek = DayOfWeek.Sunday To DayOfWeek.Saturday
Dim day As DayOfWeek
day = CType((CInt(loopDay) + CInt(firstDay)) Mod 7, DayOfWeek)
If value.WeekMap.Contains(day) Then days.Add(ReportCulture.DateTimeFormat.GetAbbreviatedDayName(day))
Next loopDay
result = FormatString(Format, DateTime.Today.Add(Value.StartTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), DateTime.Today.Add(Value.EndTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), String.Join(",", days.ToArray()))
Else
result = String.Empty
End if
return result
End Function
Public Function BuildXmlValueList(ByVal ValueList() As Object) As String
Return Microsoft.EnterpriseManagement.Reporting.MultiValueParameter.ToXml("Data", "Value", ValueList)
End Function
REM -------------------------------------------------
Dim ReportFormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
Const FormatListParameterName As String = "DataFormat"
REM -------------------------------------------------
Public Interface IDataFormatter
Function FormatData(ByVal Value As String) As String
End Interface
Public Class RankDataFormatter
Implements IDataFormatter
Private Series As System.Collections.Generic.SortedDictionary(Of Decimal, String)
Private Culture As System.Globalization.CultureInfo
Public Sub New(ByVal Config As System.Xml.XmlNode, ByVal ReportCulture As System.Globalization.CultureInfo)
Culture = ReportCulture
Series = New System.Collections.Generic.SortedDictionary(Of Decimal, String)()
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
Series.Add(Decimal.Parse(SeriesXml.Attributes("Rank").Value), SeriesXml.Attributes("Format").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String = CDec(Value).ToString("G", Culture)
For Each FormatItem As System.Collections.Generic.KeyValuePair(Of Decimal, String) In Series
Dim RunningValue As Decimal = Math.Round(Value / FormatItem.Key)
If RunningValue > 0 Then
Result = String.Format(Culture, FormatItem.Value, RunningValue.ToString("G", Culture))
Else
Exit For
End If
Next
Return Result
End Function
End Class
Public Class LookupDataFormatter
Implements IDataFormatter
Private MappingTable As System.Collections.Generic.IDictionary(Of String, String)
Public Sub New(ByVal Config As System.Xml.XmlNode)
MappingTable = New System.Collections.Generic.Dictionary(Of String, String)(StringComparer.OrdinalIgnoreCase)
For Each SeriesXml As System.Xml.XmlNode In Config.ChildNodes
MappingTable.Add(Decimal.Parse(SeriesXml.Attributes("Value").Value), SeriesXml.Attributes("Label").Value)
Next
End Sub
Public Function FormatData(ByVal Value As String) As String Implements IDataFormatter.FormatData
Dim Result As String
If MappingTable.ContainsKey(Value) Then
Result = MappingTable(Value)
Else
Result = Value
End If
Return Result
End Function
End Class
Public Function GetReportFormatList() As System.Collections.Generic.Dictionary(Of String, IDataFormatter)
If IsNothing(ReportFormatList) Then
Dim Xml As System.Xml.XmlDocument = New System.Xml.XmlDocument()
Xml.LoadXml(Report.Parameters(FormatListParameterName).Value)
ReportFormatList = New System.Collections.Generic.Dictionary(Of String, IDataFormatter)(StringComparer.OrdinalIgnoreCase)
For Each Node As System.Xml.XmlNode In Xml.DocumentElement.ChildNodes
Dim Formatter As IDataFormatter = Nothing
Select Case Node.Attributes("Type").Value.ToUpper()
Case "RANK"
Formatter = New RankDataFormatter(Node, ReportCulture)
Case "LOOKUP"
Formatter = New LookupDataFormatter(Node)
End Select
If Not IsNothing(Formatter) Then
ReportFormatList.Add(Node.Attributes("Name").Value, Formatter)
End If
Next
End If
Return ReportFormatList
End Function
Public Function FormatData(ByVal FormatName As String, ByVal DataType As String, ByVal Value As String)
Dim FormatList As System.Collections.Generic.Dictionary(Of String, IDataFormatter) = GetReportFormatList()
If Not String.IsNullOrEmpty(FormatName) And Not IsNothing(FormatList) Then
If FormatList.ContainsKey(FormatName) Then
Return FormatList(FormatName).FormatData(Value)
End If
End If
If Not String.IsNullOrEmpty(DataType) Then
If DataType = "DateTime" Then
Return FormatDateTime("g", CDate(Value))
ElseIf DataType.StartsWith("UInt") Then
Return FormatNumber("G", Value)
End If
End If
Return Value
End Function
REM ----------------------------------------
Public Dim CurrentObjectList As String
Public Function InitList(ByRef List As String) As String
List = String.Empty
Return List
End Function
Public Function AddListItem(ByRef List As String, Item as String) As String
List = List + Item
Return List
End Function
Public Function GetObjectList(ByVal OptionsXml As String) As String()
Dim Xml As System.Xml.XmlDocument
Xml = New System.Xml.XmlDocument()
Xml.LoadXml(OptionsXml)
Dim Result As System.Collections.Generic.List(Of String)
Result = New System.Collections.Generic.List(Of String)
For Each ObjectNode As System.Xml.XmlNode In Xml.SelectNodes("/Value/Object")
If Not Result.Contains(ObjectNode.InnerText) Then
Result.Add(ObjectNode.InnerText)
End If
Next
Return Result.ToArray()
End Function
</Script></Code>
<CodeModules>
<CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</CodeModule>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>4ccfd6e4-92f8-4ee4-92f3-e474fef0feb6</rd:ReportID>
</Report>
Home