Enterprise PDM Reporting (2 of 2)

In the last post, I described some of the things that you can do with SQL Reporting and Enterprise PDM.  In this post I wanted to give you an example that you could use in your own vault.

If you watch the video in the previous post you will see a project report and a license usage report.  The project report is vault specific, but the license report is the same for any vault.  So, in this post, I am sharing the reports and SQL queries for the license usage report.

The report looks like this in report builder:

This report is driven by two queries.  The first query is used for the chart and the first table.  It looks like this in SQL (just copy the data between the dashed lines into SQL or into report builder):

————————————————–

declare @licensetable Table(hostname text, ProgramType int)
Insert Into @licensetable(hostname, ProgramType)
Select Distinct hostname, ProgramType from ConisioMasterDB.dbo.LoggedInUsers

Select (Select Count(ProgramType) FROM @licensetable where ProgramType=0) as 'Logged In CAD Users',

(Select Count(ProgramType) FROM @licensetable where ProgramType=1) as 'Logged In Contributors',

(Select Count(ProgramType) FROM @licensetable where ProgramType=3) as 'Logged In Viewers'

——————————————-

The second table is built from the following SQL query (just copy the data between the dashed lines into SQL or into report builder):

——————————————-

select
distinct
liu.hostname,
liu.username,
gliu.DatabaseName,
case
  when gliu.ProgramType=0 then 'CAD'
  when gliu.ProgramType=1 then 'Contributor'
  when gliu.ProgramType=2 then 'Viewer'
  when gliu.ProgramType=3 then 'Web'
end as LicenseType
from
ConisioMasterDb.dbo.LoggedInUsers liu,
ConisioMasterDb.dbo.LoggedInUsers gliu,
master..sysprocesses sp
Where
liu.spid = gliu.spid and
liu.spid = sp.spid and
sp.program_name like '%EdmServ%'

——————————————–

To make it easy, I have also attached the report file so you can load it right into Report Builder. Download Licenseuse.  Once uploaded you will need to change the connection string information so that it points to your current SQL database.  You can change this data by right-clicking on the data source and changing the connection properties.  See the screenshot below:

This should get you started.  Hopefully this works well.  Any feedback, visit InFlow Technology at www.inflow-tech.com.