LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted = 0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('WA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is null) or (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is null) or (A.EffectiveDate <= Convert(DateTime, Convert(varchar(50),@EffectiveDateTo,102) + ' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (F.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="Lease">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query Lease
SELECT *
FROM (
SELECT C.AgreementNumber AgreementCode,
F.supplier,
A.Id,
A.DisplayName,
A.EffectiveDate,
A.ExpirationDate,
E.RelatedAssetCount,
d.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM LeaseContractDimvw A
INNER JOIN LeaseAgreementContractFactvw B ON A.LeaseContractDimKey = B.LeaseAgreementContainsLeaseContract_LeaseContractDimKey
AND B.DeletedDate IS NULL
INNER JOIN LeaseAgreementDimvw C ON B.LeaseAgreementDimKey = C.LeaseAgreementDimKey
AND C.IsDeleted = 0
AND ISNULL(C.ObjectStatus,'')<> @objectStatusPendingDelete
AND C.Status = @entityStatusActive
LEFT OUTER JOIN AgreementDimvw D ON c.BaseManagedEntityId = D.BaseManagedEntityId
LEFT OUTER JOIN
(SELECT a.HardwareAssetHasLease_LeaseContractDimKey LeaseContractDimKey,
COUNT(b.HardwareAssetDimKey) RelatedAssetCount
FROM HardwareAssetLeaseContractFactvw a
INNER JOIN HardwareAssetDimvw b ON a.HardwareAssetDimKey = b.HardwareAssetDimKey
AND b.IsDeleted = 0
AND ISNULL(b.ObjectStatus,'')<> @objectStatusPendingDelete
WHERE a.DeletedDate is null
GROUP BY a.HardwareAssetHasLease_LeaseContractDimKey
) E ON A.LeaseContractDimKey = E.LeaseContractDimKey
LEFT OUTER JOIN
(SELECT d.DisplayName supplier,
c.LeaseAgreementDimKey,
d.CompanyDimKey
FROM AgreementDimvw a
INNER JOIN AgreementCompanyFactvw b ON a.AgreementDimKey = b.AgreementDimKey
AND b.DeletedDate is null
INNER JOIN LeaseAgreementDimvw c ON a.BaseManagedEntityId = c.BaseManagedEntityId
AND c.IsDeleted = 0
AND ISNULL(c.ObjectStatus,'') <> @objectStatusPendingDelete
INNER JOIN CompanyDimvw d ON b.AgreementSuppliedByCompany_CompanyDimKey = d.CompanyDimKey
AND d.IsDeleted = 0
AND ISNULL(d.ObjectStatus,'') <> @objectStatusPendingDelete
WHERE a.IsDeleted =0
AND (a.ObjectStatus IS NULL OR a.ObjectStatus <> @objectStatusPendingDelete)
) F ON C.LeaseAgreementDimKey = F.LeaseAgreementDimKey
LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('LE' IN (@ContractType)) OR ('AL' IN (@ContractType)))
AND ((@ExpiredDateBegin IS NULL) OR (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo IS NULL) OR (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin is NULL) OR (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo is NULL) OR (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (F.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ContractType">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query ContractType
create table #ContractType (label varchar(255) null, value varchar(255) null)
;WITH vContractTypes as(
SELECT ISNULL(B.DisplayName,B.SubElementName) AS label,
B.SubElementName, A.ManagementPackId,B.DisplayStringDimKey
FROM ManagementPackDim A
Left JOIN DisplayStringDim B ON A.ManagementPackId = B.ManagementPackId
AND B.LanguageCode = @LanguageCode
AND B.ElementName = 'Provance.ITAssetManagement.Report.EndofContracts2'
WHERE A.MPName = 'Provance.ServiceManager.Itam.Software.Reports'
AND B.SubElementName like '%')
--Use this section to rebuild the default values below if there are changes
--SELECT DISTINCT 'INSERT INTO #ContractType (label,value) select ''[' + OptionSet.LABEL + ']'',''' + OptionSet.VALUE + '''
--where not exists (select label from #ContractType where value = ''' + VALUE + ''')'
INSERT INTO #ContractType (label,value)
SELECT DISTINCT OptionSet.Label, OptionSet.Value
FROM
(
SELECT C.label, 'AL' AS value FROM vContractTypes C WHERE C.SubElementName = 'R' union all -- @SubElementName union all
SELECT C.label, 'WA' AS value FROM vContractTypes C WHERE C.SubElementName = 'G' union all -- @WarrantySubElementName union all
SELECT C.label, 'LE' AS value FROM vContractTypes C WHERE C.SubElementName = 'D' union all -- @LeaseSubElementName union all
SELECT C.label, 'DI' AS value FROM vContractTypes C WHERE C.SubElementName = 'C' union all -- @DisposalSubElementName union all
SELECT C.label, 'SV' AS value FROM vContractTypes C WHERE C.SubElementName = 'H' union all -- @SupportSubElementName union all
SELECT C.label, 'SM' AS value FROM vContractTypes C WHERE C.SubElementName = 'F' union all -- @MaintenanceSubElementName union all
SELECT C.label, 'SA' AS value FROM vContractTypes C WHERE C.SubElementName = 'E' union all -- @LicenseAgreementSubElementName union all
SELECT C.label, 'UD' AS value FROM vContractTypes C WHERE C.SubElementName = 'W' -- User Defined
) OptionSet
--INSERT INTO #ContractType (label,value)
--select isnull(@LanguageCode,'null'),'LanguageCode'
--where not exists (select label from #ContractType where value = 'AL')
-- Default values
-- We have seen an invalid language code being passed. These default values will allow the report to continue.
INSERT INTO #ContractType (label,value) select '[All]', 'AL' where not exists (select label from #ContractType where value = 'AL')
INSERT INTO #ContractType (label,value) select '[Warranty Agreements]', 'WA' where not exists (select label from #ContractType where value = 'WA')
INSERT INTO #ContractType (label,value) select '[Lease Schedules & Agreements ]', 'LE' where not exists (select label from #ContractType where value = 'LE')
INSERT INTO #ContractType (label,value) select '[Disposal Agreements]', 'DI' where not exists (select label from #ContractType where value = 'DI')
INSERT INTO #ContractType (label,value) select '[Hardware Maintenance & Support Agreements]', 'SV' where not exists (select label from #ContractType where value = 'SV')
INSERT INTO #ContractType (label,value) select '[Software Maintenance & Support Agreements]', 'SM' where not exists (select label from #ContractType where value = 'SM')
INSERT INTO #ContractType (label,value) select '[Software License Agreements]', 'SA' where not exists (select label from #ContractType where value = 'SA')
INSERT INTO #ContractType (label,value) select '[User Defined Agreements]', 'UD' where not exists (select label from #ContractType where value = 'UD')
select * from #ContractType a
order by a.label
drop table #ContractType</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="label">
<DataField>label</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="value">
<DataField>value</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="LT_Report">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ManagementPackSystemName">
<Value>Provance.ServiceManager.Itam.Software.Reports</Value>
</QueryParameter>
<QueryParameter Name="@ReportSystemName">
<Value>Provance.ITAssetManagement.Report.EndofContracts2</Value>
</QueryParameter>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
<QueryParameter Name="@Scope">
<Value>%</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>ReportDisplayStringGet</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="XML_F52E2B61_18A1_11d1_B105_00805F49916B">
<DataField>XML_F52E2B61-18A1-11d1-B105-00805F49916B</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SupportContract">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SupportContract
SELECT *
FROM (
SELECT C.AgreementNumber AgreementCode,
F.supplier,
A.Id,
A.DisplayName,
A.EffectiveDate,
A.ExpirationDate,
E.RelatedAssetCount,
d.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM SupportContractDimvw A
INNER JOIN SupportAgreementContractFactvw B ON A.SupportContractDimKey = B.SupportAgreementContainsSupportContract_SupportContractDimKey
AND B.DeletedDate IS NULL
INNER JOIN SupportAgreementDimvw C ON B.SupportAgreementDimKey = C.SupportAgreementDimKey
AND C.IsDeleted = 0
AND ISNULL(C.ObjectStatus,'')<> @objectStatusPendingDelete
AND C.Status = @entityStatusActive
LEFT OUTER JOIN AgreementDimvw D ON c.BaseManagedEntityId = D.BaseManagedEntityId
LEFT OUTER JOIN
(SELECT a.HardwareAssetHasSupportContract_SupportContractDimKey SupportContractDimKey,
COUNT(b.HardwareAssetDimKey) RelatedAssetCount
FROM HardwareAssetSupportContractFactvw a
INNER JOIN HardwareAssetDimvw b ON a.HardwareAssetDimKey = b.HardwareAssetDimKey
AND b.IsDeleted =0
AND ISNULL(b.ObjectStatus,'')<> @objectStatusPendingDelete
WHERE a.DeletedDate IS NULL
GROUP BY a.HardwareAssetHasSupportContract_SupportContractDimKey
) E ON A.SupportContractDimKey = E.SupportContractDimKey
LEFT OUTER JOIN
(SELECT d.DisplayName supplier,
c.SupportAgreementDimKey,
d.CompanyDimKey
FROM AgreementDimvw a
INNER JOIN AgreementCompanyFactvw b ON a.AgreementDimKey = b.AgreementDimKey
AND b.DeletedDate IS NULL
INNER JOIN SupportAgreementDimvw c ON a.BaseManagedEntityId =c.BaseManagedEntityId
AND c.IsDeleted = 0
AND isnull(c.ObjectStatus,'') <> @objectStatusPendingDelete
AND c.Status = @entityStatusActive
INNER JOIN CompanyDimvw d ON b.AgreementSuppliedByCompany_CompanyDimKey =d.CompanyDimKey
AND d.IsDeleted = 0
AND isnull(d.ObjectStatus,'') <> @objectStatusPendingDelete
WHERE a.IsDeleted =0
AND (a.ObjectStatus IS NULL OR a.ObjectStatus <> @objectStatusPendingDelete)
) F on C.SupportAgreementDimKey = f.SupportAgreementDimKey
LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND (A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND (('SV' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin is null) or (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo is null) or (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin IS NULL) or (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo IS NULL) or (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (F.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="RelatedAssetCount">
<DataField>RelatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="DisposalContract">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query DisposalContract
SELECT *
FROM ( SELECT C.AgreementNumber AgreementCode,
F.supplier,
A.Id,
A.DisplayName,
A.EffectiveDate,
A.ExpirationDate,
E.relatedAssetCount,
D.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM DisposalContractDimvw A
INNER JOIN DisposalAgreementContractFactvw B ON A.DisposalContractDimKey = B.DisposalAgreementContainsDisposalContract_DisposalContractDimKey
AND B.DeletedDate IS NULL
INNER JOIN DisposalAgreementDimvw C ON B.DisposalAgreementDimKey = C.DisposalAgreementDimKey
AND C.IsDeleted = 0
AND(C.ObjectStatus IS NULL OR C.ObjectStatus <> @objectStatusPendingDelete)
AND C.Status = @entityStatusActive
LEFT OUTER JOIN AgreementDimvw D ON C.BaseManagedEntityId = D.BaseManagedEntityId
LEFT OUTER JOIN
(SELECT COUNT(b.HardwareAssetDimKey) relatedAssetCount,
d.HardwareAssetDisposalHasDisposalContract_DisposalAgreementDimKey DisposalAgreementDimKey
FROM HardwareAssetDispoalAssetFactvw a
INNER JOIN HardwareAssetDimvw b ON a.HardwareAssetDimKey = b.HardwareAssetDimKey
AND b.IsDeleted = 0
AND(b.ObjectStatus IS NULL OR b.ObjectStatus <> @objectStatusPendingDelete)
INNER JOIN DispoalAssetContractFactvw d ON a.HardwareAssetHasHardwareAssetDisposal_HWDisposalDimKey = d.HWDisposalDimKey
AND d.DeletedDate is null
WHERE a.DeletedDate is null
GROUP BY d.HardwareAssetDisposalHasDisposalContract_DisposalAgreementDimKey
) E ON C.DisposalAgreementDimKey = E.DisposalAgreementDimKey
LEFT OUTER JOIN
(SELECT d.DisplayName supplier,
c.DisposalAgreementDimKey,
d.CompanyDimKey
FROM AgreementDimvw a
INNER JOIN AgreementCompanyFactvw b ON a.AgreementDimKey = b.AgreementDimKey
AND b.DeletedDate IS NULL
INNER JOIN DisposalAgreementDimvw c ON a.BaseManagedEntityId = c.BaseManagedEntityId
AND c.IsDeleted = 0
AND(c.ObjectStatus IS NULL OR c.ObjectStatus <> @objectStatusPendingDelete)
AND C.Status = @entityStatusActive
INNER JOIN CompanyDimvw d ON b.AgreementSuppliedByCompany_CompanyDimKey = d.CompanyDimKey
AND d.IsDeleted = 0
AND (d.ObjectStatus IS NULL OR d.ObjectStatus <> @objectStatusPendingDelete)
WHERE a.IsDeleted =0
AND (a.ObjectStatus IS NULL OR a.ObjectStatus <> @objectStatusPendingDelete)
) F ON C.DisposalAgreementDimKey = F.DisposalAgreementDimKey
LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND(A.ObjectStatus IS NULL OR A.ObjectStatus <> @objectStatusPendingDelete)
AND(('DI' in (@ContractType)) OR ('AL' in (@ContractType)))
AND((@ExpiredDateBegin is null) or (A.ExpirationDate >= @ExpiredDateBegin))
AND((@ExpiredDateTo is null) or (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND((@EffectiveDateBegin IS NULL) OR (A.EffectiveDate >= @EffectiveDateBegin))
AND((@EffectiveDateTo IS NULL) OR (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (F.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="supplier">
<DataField>supplier</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="relatedAssetCount">
<DataField>relatedAssetCount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SWMaintance">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SWMaintance
SELECT *
FROM (
SELECT A.AgreementNumber Code,
A.DisplayName,
A.AgreementNumber,
A.EffectiveDate,
A.ExpirationDate,
B.Publisher,
d.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM SWMaintAgtDimvw A
LEFT OUTER JOIN
(SELECT a.SWMaintAgtDimKey,
b.DisplayName Publisher,
b.CompanyDimKey
FROM SWMaintAgtPublisherFactvw a
INNER JOIN CompanyDimvw b ON a.SoftwareMaintenanceAgreementHasPublisher_CompanyDimKey = b.CompanyDimKey
AND b.IsDeleted = 0
AND isnull(b.ObjectStatus,'') <> @objectStatusPendingDelete
WHERE a.DeletedDate IS NULL
) B on A.SWMaintAgtDimKey = B.SWMaintAgtDimKey
LEFT OUTER JOIN AgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND isnull(A.ObjectStatus,'') <> @objectStatusPendingDelete
AND A.Status = @entityStatusActive
AND (('SM' IN (@ContractType)) OR ('AL' IN (@ContractType)))
AND ((@ExpiredDateBegin IS NULL) OR (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo IS NULL) OR (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin IS NULL) OR (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo IS NULL) OR (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (B.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Code">
<DataField>Code</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Publisher">
<DataField>Publisher</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="SWLicenseAgreement">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
</QueryParameters>
<CommandText>--Query SWLicenseAgreement
SELECT *
FROM (
SELECT A.AgreementNumber Code,
A.AgreementNumber,
A.EffectiveDate,
A.ExpirationDate,
C.PublisherName,
B.LicensingProgramValue,
d.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM SWLicenseAgtDimvw A
LEFT JOIN LicensingProgramvw B ON A.Program_LicensingProgramId = B.LicensingProgramId
LEFT JOIN
(SELECT a.DisplayName PublisherName,
b.SWLicenseAgtDimKey,
a.CompanyDimKey
FROM CompanyDimvw a
INNER JOIN SWLicenseAgtPublisherFactvw b ON a.CompanyDimKey = b.SoftwareLicenseAgreementHasPublisher_CompanyDimKey
AND b.DeletedDate IS NULL
WHERE a.IsDeleted =0
AND isnull(a.ObjectStatus,'') <> @objectStatusPendingDelete
) C ON A.SWLicenseAgtDimKey = C.SWLicenseAgtDimKey
LEFT OUTER JOIN AgreementDimvw D ON A.BaseManagedEntityId = D.BaseManagedEntityId
LEFT JOIN AgreementOrganizationFactvw G ON D.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON D.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON D.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON D.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND isnull(A.ObjectStatus,'') <> @objectStatusPendingDelete
AND A.Status = @entityStatusActive
AND (('SA' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin IS NULL) OR (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo IS NULL) OR (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin IS NULL) OR (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo IS NULL) OR (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (C.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Code">
<DataField>Code</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="PublisherName">
<DataField>PublisherName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="LicensingProgramValue">
<DataField>LicensingProgramValue</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="Dummy">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>select 1</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="ID">
<DataField />
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ContractAdminSettings">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<CommandText>SELECT DISTINCT TOP 1 ExpiredThresholdDays, CloseToExpiredThresholdDays
FROM ContractAdminSettingsDim
WHERE IsDeleted = 0
--AND SourceId = @SourceId</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</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>
<DataSet Name="ParameterDisplayName">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>declare @sSupplierDisplayName NVARCHAR(4000),
@sLocationDisplayName NVARCHAR(4000),
@sCostCenterDisplayName NVARCHAR(4000),
@sOrganizationDisplayName NVARCHAR(4000),
@sContractManagerDisplayName NVARCHAR(4000)
SELECT @sSupplierDisplayName = substring(A.DisplayName,1,4000)
FROM CompanyDimvw A
WHERE A.CompanyDimKey = @Supplier
AND A.IsDeleted = 0
SELECT @sLocationDisplayName = substring(A.DisplayName,1,4000)
FROM LocationDimvw A
WHERE A.LocationDimKey = @LocationId
AND A.IsDeleted = 0
SELECT @sCostCenterDisplayName = substring(A.DisplayName,1,4000)
FROM CostCenterDimvw A
WHERE A.CostCenterDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sOrganizationDisplayName = substring(A.DisplayName,1,4000)
FROM OrganizationDimvw A
WHERE A.OrganizationDimKey = @CostCenterId
AND A.IsDeleted = 0
SELECT @sContractManagerDisplayName = substring(A.DisplayName,1,4000)
FROM SystemUserDimVw A
WHERE A.SystemUserDimKey = @ContractManagerId
AND A.IsDeleted = 0
SELECT @sSupplierDisplayName SupplierDisplayName,
@sLocationDisplayName LocationDisplayName,
@sCostCenterDisplayName CostCenterDisplayName,
@sOrganizationDisplayName OrganizationDisplayName,
@sContractManagerDisplayName ContractManagerDisplayName</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SupplierDisplayName">
<DataField>SupplierDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="LocationDisplayName">
<DataField>LocationDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostCenterDisplayName">
<DataField>CostCenterDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrganizationDisplayName">
<DataField>OrganizationDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContractManagerDisplayName">
<DataField>ContractManagerDisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="UserDefined">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@ThresholdContractExpired">
<Value>=Parameters!ThresholdContractExpired.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ThresholdContractCloseToExpired">
<Value>=Parameters!ThresholdContractCloseToExpired.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@LocationId">
<Value>=Parameters!LocationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@objectStatusPendingDelete">
<Value>System.ConfigItem.ObjectStatusEnum.PendingDelete</Value>
</QueryParameter>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
<QueryParameter Name="@LocationDimKeyList">
<Value>=Parameters!LocationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ContractManagerId">
<Value>=Parameters!ContractManagerId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@entityStatusActive">
<Value>EntityStatus.Active</Value>
</QueryParameter>
<QueryParameter Name="@ContractType">
<Value>=Parameters!ContractType.Value</Value>
</QueryParameter>
<QueryParameter Name="@OrganizationDimKeyList">
<Value>=Parameters!OrganizationDimKeyList.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@OrganizationId">
<Value>=Parameters!OrganizationId.Value</Value>
<rd:UserDefined>true</rd:UserDefined>
</QueryParameter>
<QueryParameter Name="@ExpiredDateBegin">
<Value>=Parameters!ExpiredDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@ExpiredDateTo">
<Value>=Parameters!ExpiredDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateBegin">
<Value>=Parameters!EffectiveDateBegin.Value</Value>
</QueryParameter>
<QueryParameter Name="@EffectiveDateTo">
<Value>=Parameters!EffectiveDateTo.Value</Value>
</QueryParameter>
<QueryParameter Name="@Supplier">
<Value>=Parameters!Supplier.Value</Value>
</QueryParameter>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
</QueryParameter>
<QueryParameter Name="@CostCenterDimKeyList">
<Value>=Parameters!CostCenterDimKeyList.Value</Value>
</QueryParameter>
<QueryParameter Name="@Threshold">
<Value>=Parameters!Threshold.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query UserDefined
SELECT *
FROM (
SELECT A.AgreementNumber AgreementCode,
A.AgreementNumber,
A.DisplayName,
A.EffectiveDate,
A.ExpirationDate,
C.SupplierName,
D.AgreementType,
E.AgreementDimKey,
case when a.ExpirationDate is null then 'UN'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractExpired then 'EX'
when datediff(d,GETDATE(),A.ExpirationDate) <= @ThresholdContractCloseToExpired then 'CL'
else 'GO'
end Threshold,
datediff(d,GETDATE(),A.ExpirationDate) ExpiredDays
FROM UserDefinedAgreementDim A
LEFT OUTER JOIN UserDefinedAgreementTypevw B ON A.Type_UserDefinedAgreementTypeId = B.UserDefinedAgreementTypeId
LEFT OUTER JOIN
(SELECT c.DisplayName SupplierName,
b.EntityDimKey,
C.CompanyDimKey
FROM AgreementCompanyFactvw a
JOIN AgreementDimvw b on a.AgreementDimKey = b.AgreementDimKey AND B.IsDeleted =0
JOIN CompanyDimvw c ON a.AgreementSuppliedByCompany_CompanyDimKey = c.CompanyDimKey AND ISNULL(c.ObjectStatus,'') <> @objectStatusPendingDelete
WHERE a.DeletedDate IS NULL
) C ON A.EntityDimKey = C.EntityDimKey
LEFT OUTER JOIN (SELECT a.UserDefinedAgreementTypeId,
ISNULL(b.DisplayName, a.UserDefinedAgreementTypeValue) AgreementType
FROM UserDefinedAgreementTypevw a
LEFT JOIN DisplayStringDimvw b ON a.EnumTypeId = b.BaseManagedEntityId
AND b.LanguageCode = @LanguageCode
) D on A.Type_UserDefinedAgreementTypeId = D.UserDefinedAgreementTypeId
LEFT OUTER JOIN AgreementDimvw E ON A.BaseManagedEntityId = E.BaseManagedEntityId
LEFT JOIN AgreementOrganizationFactvw G ON E.AgreementDimKey = G.AgreementDimKey AND G.DeletedDate IS NULL
LEFT JOIN AgreementCostCenterFactvw H ON E.AgreementDimKey = H.AgreementDimKey AND H.DeletedDate IS NULL
LEFT JOIN AgreementLocationFactvw I ON E.AgreementDimKey = I.AgreementDimKey AND I.DeletedDate IS NULL
LEFT JOIN AgreementSystemUserFactvw J ON E.AgreementDimKey = J.AgreementDimKey AND J.DeletedDate IS NULL
WHERE A.IsDeleted =0
AND ISNULL(A.ObjectStatus,'') <> @objectStatusPendingDelete
AND A.Status = @entityStatusActive
AND (('UD' in (@ContractType)) OR ('AL' in (@ContractType)))
AND ((@ExpiredDateBegin IS NULL) OR (A.ExpirationDate >= @ExpiredDateBegin))
AND ((@ExpiredDateTo IS NULL) OR (A.ExpirationDate <= Convert(DateTime,Convert(varchar(50),@ExpiredDateTo,102)+' 23:59:59',120)))
AND ((@EffectiveDateBegin IS NULL) OR (A.EffectiveDate >= @EffectiveDateBegin))
AND ((@EffectiveDateTo IS NULL) OR (A.EffectiveDate <= Convert(DateTime,Convert(varchar(50),@EffectiveDateTo,102)+' 23:59:59',120)))
AND ((@Supplier is null) OR (@Supplier < 1) OR (C.CompanyDimKey = @Supplier))
AND ((@OrganizationId is null) OR (@OrganizationId < 1) OR (G.AgreementHasOrganization_OrganizationDimKey in (@OrganizationDimKeyList)))
AND ((@CostCenterId is null) OR (@CostCenterId < 1) OR (H.AgreementHasCostCenter_CostCenterDimKey in (@CostCenterDimKeyList)))
AND ((@LocationId is null) OR (@LocationId < 1) OR (I.AgreementHasLocation_LocationDimKey in (@LocationDimKeyList)))
AND ((@ContractManagerId is null) OR (@ContractManagerId < 1) OR (J.AgreementOwnedBySystemUser_SystemUserDimKey = @ContractManagerId))
) Z
WHERE ((Z.Threshold IN (@Threshold)) OR ('AL' IN (@Threshold)))
ORDER BY Z.ExpirationDate</CommandText>
</Query>
<Fields>
<Field Name="AgreementCode">
<DataField>AgreementCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementNumber">
<DataField>AgreementNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EffectiveDate">
<DataField>EffectiveDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="DisplayName">
<DataField>DisplayName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpirationDate">
<DataField>ExpirationDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="SupplierName">
<DataField>SupplierName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementType">
<DataField>AgreementType</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="AgreementDimKey">
<DataField>AgreementDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Threshold">
<DataField>Threshold</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ExpiredDays">
<DataField>ExpiredDays</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="Threshold">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@LanguageCode">
<Value>=Code.GetReportLocLanguageCode()</Value>
</QueryParameter>
</QueryParameters>
<CommandText>--Query Threshold
create table #ThresholdType (label varchar(255) null, value varchar(255) null)
;WITH vThresholdTypes as(
SELECT ISNULL(B.DisplayName,B.SubElementName) AS label,
B.SubElementName, A.ManagementPackId,B.DisplayStringDimKey
FROM ManagementPackDim A
Left JOIN DisplayStringDim B ON A.ManagementPackId = B.ManagementPackId
AND B.LanguageCode = @LanguageCode
AND B.ElementName = 'Provance.ITAssetManagement.Report.EndOfContracts2'
WHERE A.MPName = 'Provance.ServiceManager.Itam.Software.Reports'
AND B.SubElementName like '%' )
--Use this section to rebuild the default values below if there are changes
--SELECT DISTINCT 'INSERT INTO #ThresholdType (label,value) select ''[' + OptionSet.LABEL + ']'',''' + OptionSet.VALUE + '''
--where not exists (select label from #ThresholdType where value = ''' + VALUE + ''')'
INSERT INTO #ThresholdType (label,value)
SELECT DISTINCT OptionSet.Label, OptionSet.Value
FROM
(
SELECT C.label, 'AL' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'R' union all
SELECT C.label, 'EX' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'X' union all
SELECT C.label, 'CL' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'Y' union all
SELECT C.label, 'GO' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'Z' union all
SELECT C.label, 'UN' AS value FROM vThresholdTypes C WHERE C.SubElementName = 'ZA'
) OptionSet
--INSERT INTO #ThresholdType (label,value)
--select @LanguageCode,'languagecode'
--where not exists (select label from #ThresholdType where value = 'AL')
-- Default values
-- We have seen an invalid language code being passed. These default values will allow the report to continue.
INSERT INTO #ThresholdType (label,value) select '[All]','AL' where not exists (select label from #ThresholdType where value = 'AL')
INSERT INTO #ThresholdType (label,value) select '[Close]','CL' where not exists (select label from #ThresholdType where value = 'CL')
INSERT INTO #ThresholdType (label,value) select '[Expired]','EX' where not exists (select label from #ThresholdType where value = 'EX')
INSERT INTO #ThresholdType (label,value) select '[Good]','GO' where not exists (select label from #ThresholdType where value = 'GO')
INSERT INTO #ThresholdType (label,value) select '[Unknown]','UN' where not exists (select label from #ThresholdType where value = 'UN')
IF @LocationId IS NOT NULL and @LocationId > 0
BEGIN
INSERT INTO #LocationDimKey
VALUES(@LocationId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO #LocationDimKey
SELECT A.LocationContainsChildLocation_LocationDimKey
FROM LocationChildLocationFactvw A
WHERE A.DeletedDate IS NULL
AND A.LocationDimKey IN (SELECT LocationDimKey FROM #LocationDimKey)
AND A.LocationContainsChildLocation_LocationDimKey NOT IN (SELECT LocationDimKey FROM #LocationDimKey)
SELECT @iRowcount = @@ROWCOUNT
END
END
select LocationDimKey from #LocationDimKey
union
select 0
order by LocationDimKey</CommandText>
</Query>
<Fields>
<Field Name="LocationDimKey">
<DataField>LocationDimKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="CostCenterDimKeyQuery">
<Query>
<DataSourceName>DWDataMart</DataSourceName>
<QueryParameters>
<QueryParameter Name="@CostCenterId">
<Value>=Parameters!CostCenterId.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>CREATE TABLE #CostCenterDimKey (CostCenterDimKey int)
DECLARE @iRowcount int
IF @CostCenterId IS NOT NULL and @CostCenterId > 0
BEGIN
INSERT INTO #CostCenterDimKey
VALUES(@CostCenterId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO #CostCenterDimKey
SELECT A.CostCenterContainsChildCostCenter_CostCenterDimKey
FROM CostCenterChildCostCenterFactvw A
WHERE A.DeletedDate IS NULL
AND A.CostCenterDimKey IN (SELECT CostCenterDimKey FROM #CostCenterDimKey)
AND A.CostCenterContainsChildCostCenter_CostCenterDimKey NOT IN (SELECT CostCenterDimKey FROM #CostCenterDimKey)
SELECT @iRowcount = @@ROWCOUNT
END
END
select CostCenterDimKey from #CostCenterDimKey
union all
select 0
order by CostCenterDimKey
IF @OrganizationId IS NOT NULL and @OrganizationId > 0
BEGIN
INSERT INTO #OrganizationDimKey
VALUES(@OrganizationId)
SELECT @iRowcount = @@ROWCOUNT
WHILE @iRowcount > 0
BEGIN
INSERT INTO #OrganizationDimKey
SELECT A.OrganizationContainsChildOrganization_OrganizationDimKey
FROM OrganizationChildOrganizationFactvw A
WHERE A.DeletedDate IS NULL
AND A.OrganizationDimKey IN (SELECT OrganizationDimKey FROM #OrganizationDimKey)
AND A.OrganizationContainsChildOrganization_OrganizationDimKey NOT IN (SELECT OrganizationDimKey FROM #OrganizationDimKey)
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>
<Language>en-US</Language>
<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>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>9bf3e609-5344-43d6-b1aa-066d834bca0b</rd:ReportID>
</Report>
Home