Generating custom reports using Excel spreadsheet

OpenLM system provides many built-in reports such as:
Group and Project usage
Usage level reports (report and chart)
License denial reports (supported for FLEXnet FLEXlm license manager)
Active users
And more…

Managers always require more reports and OpenLM provides an easy way to generate custom reports by using OpenLM EasyAdmin ability to export information in a CSV format and to process the information using a spreadsheet software.

The process starts by creating the query that will define which information will be exported out of OpenLM system. EasyAdmin provides a unique and easy to use tool that allows the user to define the exact information needed. The user can filter the query by: server name, vendor name, features, users, groups, projects, time and even working days.

After defining the query the information will be displayed in EasyAdmin, using the export button located below the grid the information can be exported to a CSV file that can be easily imported into the spreadsheet software.

The user can also save the filter created for future use by using the “Funnel” icon.

The reports generated this way are available for any of the supported license managers like: FLEXnet, FLEXlm, IBM LUM, Sentinel RMS, SafeNet HASP, RLM and more.

Generating a Unique Daily Users Report

The unique daily user report allow companies to know who is consuming the network licenses every day. In order to create this example we have used Autocad network licenses (from Autodesk).

The usage information is exported in CSV format from the EasyAdmin->License Activity TAB.

The action is demonstrated in OpenLM Version 1.7 and Microsoft Excel Version 2010.

1. Using  ‘Easy Admin’ interface: Click Start -> Reports -> License Activity.
The filter on the left can be used in order to create the query for the report. After creating the query and clicking the “Apply” button, click the “Export” button (The small icon with green arrow at the bottom of the grid).  Save the generated CSV file on the disk.

2. Open the CSV file using Microsoft Excel.

3. Make a copy of the “Start Time” column in order to remove the hour component from the field.

4. Select the new column, click on “Data-> Text To Columns”  on the menu of Microsoft Excel, a window will pop:

1. Select “Delimited”’ and click “Next”.

2. Choose the ‘Space’ box and click next

3. Select ‘Text’ and click Finish

5. Three new columns has been created as a result of the process we did (columns which detail the date, the time & the daily time zone – AM/PM ).

We only need the column with the date.

Delete the other two columns and change the name of the column (The dates column)  to ‘Date Usage’

 

6. Select the column ‘Date Usage’ and right click the mouse in order to select the ‘Format Cell’ option from the menu. A window will open –  select ‘Date’ from the ‘Category’ list and click ‘OK’

 

7. Select all columns (CTRL+A) , click on ‘Insert’ ( in  Microsoft Excel menu ) and  on PivotTable.

Select the ‘New Worksheet’ on the ‘Create PivotTable’ window that will pop up and click ‘OK’.

A new worksheet will be created.

 

8. On the right side of the screen you will see all your columns( under ‘PivotTable Field List’ )

Drag first the ‘Date Usage’ field  with the mouse to the ’Row Labels’ box and then drag the ‘User Name’ field  to the ‘Row Labels’ box.

 

The example report shows the unique users that consumed licenses for each day. In the example report some days lists one user, for example 03/04/2011. On the 14/03/2011 we had two unique users.

Generating a Group Usage Report for Projects

The following report lists the total number of license usage hours each group investing for each project. Such a report can be useful for billing purposes or in order to support managerial decisions.

OpenLM provides two built in reports: Project usage report and Group usage report. this custom reports integrates the two reports together.

1. Perform steps 1-7 (listed above in the previous report).

2. On the right side of the screen you will see all your columns’ names ( under ‘PivotTable Field List’ ). Drag the ‘Project’ field to the ’Row Labels’ box and then drag the  ‘Group’ field  to the same box as shown in the following image.

3. Drag the ‘Count Of Usage Time’ field into the ‘Values’ box. The result will be generated automatically:

We can see that the GISteam group used 31 hours of Autocad network licensed software for the “City Plan” project and the “GISSpecial” only used 1 hour for the “Environment” project.

 

Generating a Feature (licensed software) Usage Report for Projects

The following report lists the products (licensed software, features in FLEXlm) used on each project. Such a report can be useful for billing purposes or in order to support managerial decisions.

1.Perform steps 1-7 (listed above).

2. On the right side of the screen you will see all your columns’ names ( under the ‘PivotTable Field List’ ),drag  the ‘project’ field  to the ’Row Labels’ box and then drag the ‘Product’ field to the same box as shown in the following image.

In this example we have used information from an ESRI ArcGIS license manager. In the report we can see the list of features used for the “City Plan” project. We can se the use of base licenses such as ArcView and ArcInfo or extensions such as 3DAnalyst network license.

Generating a Feature Usage Chart

The following chart shows the total number of users that used specific license (feature).

1. Perform steps 1-6 (listed above).

2. Select all columns (CTRL+A) , click on ‘Insert’ (Microsoft Excel menu) and choose the ‘PivotChart’ option from the ‘PivotTable’ menu as shown in the following image.

3. Select ‘New Worksheet’ on the ‘Create PivotTable with PivotChart ’ window that will pop and click ‘OK’. A new work sheet will be created.

4.  On the right side of the screen you will see all your columns (under the ‘PivotTable Field List’). Drag the ‘Product’ field to the ‘Legend Fields’ box and then drag the ‘User Name’ field to the ‘Values’ box.
This  automatically will generate a chart of the number of users that used each license, As shown in the following image.

Summary

This article explains how to generate custom reports and charts by using OpenLM export functionality and a spreadsheet software. OpenLM system allows the user to generate a query and to export the information to a CSV file that can be further processed by the spreadsheet software.
The article demonstrated the creation of sevelral reports such as: Unique Daily Users Report, Group Usage Report for Projects, Feature (licensed software) Usage Report for Projects and Feature Usage Chart.