Bdavey
Occasional contributor
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Jan 08, 2020
01:01 PM
I have been creating custom SQL queries for our own reporting tools and I am having a hard time pulling the total purchase costs on a per license basis.
My question is what IDs and or Tables are being used in FNMS to link these tables (foreign keys for these tables) does anyone have experience here?
1 Solution
Accepted Solutions
mfranz
Shining star
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Jan 08, 2020
01:57 PM
Hi,
This should get you started.
SELECT
sl.Name LicenseName
,SUM(pod.TotalPrice) LicenseValue
FROM SoftwareLicense sl
LEFT JOIN EntitlementTransaction et
ON sl.SoftwareLicenseID = et.SoftwareLicenseID
LEFT JOIN PurchaseOrderDetail pod
ON et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
GROUP BY sl.Name
There are a few more things to consider.
- I am not sure if entitlements not linked via "Unprocessed Purchases" show up in the "EntitlementTransaction" table at all
- Multiple entitlements from a purchase can be linked to different licenses, so you'll have to get the actual "Adjustment" per transaction and multiply it by the "UnitPrice" from the purchase oder line (PurchaseOrderDetail)
Best regards,
Markward
Softline Group is Europe's leading independent expert in Software Asset Management.
2 Replies
mfranz
Shining star
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Jan 08, 2020
01:57 PM
Hi,
This should get you started.
SELECT
sl.Name LicenseName
,SUM(pod.TotalPrice) LicenseValue
FROM SoftwareLicense sl
LEFT JOIN EntitlementTransaction et
ON sl.SoftwareLicenseID = et.SoftwareLicenseID
LEFT JOIN PurchaseOrderDetail pod
ON et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
GROUP BY sl.Name
There are a few more things to consider.
- I am not sure if entitlements not linked via "Unprocessed Purchases" show up in the "EntitlementTransaction" table at all
- Multiple entitlements from a purchase can be linked to different licenses, so you'll have to get the actual "Adjustment" per transaction and multiply it by the "UnitPrice" from the purchase oder line (PurchaseOrderDetail)
Best regards,
Markward
Softline Group is Europe's leading independent expert in Software Asset Management.
Bdavey
Occasional contributor
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Jan 08, 2020
02:14 PM
Re: Foreign Key for License and Purchases
This is what I was looking for, I can customize and incorporate this. Thanks!