→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cwatsonii
Engaged Sweeper II
In version 4.x, I used the following queries to create monthly reports (which were auto emailed to managers) of the compliance reports that were built into the webpage.

Software Compliance
Select Top 1000000 web40licenseview.License, web40licenseview.[number in use] As [In use], web40licenseview.Nrlicenses As Purchased, web40licenseview.Priceperlicense As Price, web40licenseview.Missing As Missing, tblsublicenses.softwareName As Sublicense, web40usedsoftware.[number in use] As [In use sublicense] From web40licenseview Inner Join tblLicenses On web40licenseview.LicenseidID = tblLicenses.LicenseidID Left Outer Join (web40usedsoftware Inner Join tblsublicenses On web40usedsoftware.softwareName = tblsublicenses.softwareName And IsNull(web40usedsoftware.softwareVersion, '') Like IsNull(tblsublicenses.softwareVersion, '')) On tblLicenses.LicenseidID = tblsublicenses.LicenseidID Order By web40licenseview.License, tblsublicenses.softwareName


OS Compliance
Select web40OStotal.OS, web40OStotal.[Number in use],
IsNull(tblOsLicenses.Nrlicenses, 0) As Purchased,
IsNull(tblOsLicenses.Priceperlicense, 0) As PriceperLicense, Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) Else Null End As Missing, Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) * IsNull(tblOsLicenses.Priceperlicense, 0) Else Null End As Total
From web40OStotal Left Outer Join
tblOsLicenses On web40OStotal.OS = tblOsLicenses.OS
Order By Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) * IsNull(tblOsLicenses.Priceperlicense, 0) Else Null End Desc



In version 5, it seems the tables have changed quite a bit. I've started creating a new report for the new version, but now I'm stuck. Here's what I have for the Software Compliance.

Select Distinct Top 1000000 tblLicenses.softwareName As Name,
tblSublicensesOrders.Nrlicenses As Purchased
From tblLicenses
Left Join tblsublicenses On tblLicenses.LicenseidID =
tblsublicenses.LicenseidID
Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID


Could anyone assist please? I'm going to continue to keep working on it.

BTW, the new version is way cool! Thanks for all the awesome additions and changes!
1 ACCEPTED SOLUTION
cwatsonii
Engaged Sweeper II
Here are the simple reports:

Operating System
Select Distinct Top 1000000 tblOsLicenses.OS As Name,
upgrade_web40OStotal.[Number in use] As [In Use],
tblOssubLicensesOrders.Nrlicenses As Purchased,
upgrade_web40OStotal.[Number in use] - tblOssubLicensesOrders.Nrlicenses As
Missing
From tblOssubLicenses
Inner Join tblOsLicenses On tblOsLicenses.OSLicenseidID =
tblOssubLicenses.OSLicenseidID
Inner Join tblOssubLicensesOrders On tblOsLicenses.OSLicenseidID =
tblOssubLicensesOrders.OSLicenseidID
Inner Join upgrade_web40OStotal On tblOsLicenses.OS = upgrade_web40OStotal.OS


Application/Software
Select Distinct Top 1000000 tblLicenses.softwareName As Name,
tblSublicensesOrders.Nrlicenses As Purchased,
upgrade_web40usedsoftware.[number in use] As [In Use],
upgrade_web40usedsoftware.[number in use] - tblSublicensesOrders.Nrlicenses As
Missing
From tblLicenses
Left Join tblsublicenses On tblLicenses.LicenseidID =
tblsublicenses.LicenseidID
Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Left Join upgrade_web40usedsoftware On tblLicenses.softwareName =
upgrade_web40usedsoftware.softwareName

View solution in original post

1 REPLY 1
cwatsonii
Engaged Sweeper II
Here are the simple reports:

Operating System
Select Distinct Top 1000000 tblOsLicenses.OS As Name,
upgrade_web40OStotal.[Number in use] As [In Use],
tblOssubLicensesOrders.Nrlicenses As Purchased,
upgrade_web40OStotal.[Number in use] - tblOssubLicensesOrders.Nrlicenses As
Missing
From tblOssubLicenses
Inner Join tblOsLicenses On tblOsLicenses.OSLicenseidID =
tblOssubLicenses.OSLicenseidID
Inner Join tblOssubLicensesOrders On tblOsLicenses.OSLicenseidID =
tblOssubLicensesOrders.OSLicenseidID
Inner Join upgrade_web40OStotal On tblOsLicenses.OS = upgrade_web40OStotal.OS


Application/Software
Select Distinct Top 1000000 tblLicenses.softwareName As Name,
tblSublicensesOrders.Nrlicenses As Purchased,
upgrade_web40usedsoftware.[number in use] As [In Use],
upgrade_web40usedsoftware.[number in use] - tblSublicensesOrders.Nrlicenses As
Missing
From tblLicenses
Left Join tblsublicenses On tblLicenses.LicenseidID =
tblsublicenses.LicenseidID
Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Left Join upgrade_web40usedsoftware On tblLicenses.softwareName =
upgrade_web40usedsoftware.softwareName