Custom reports with dynamic links

[marius]

In FlexNet Management Suite it is possible to create custom reports (FNMS Repots section).

Such report can call underlying SQL stored procedure, or SQL query, defined in SearchSQL column of [FNMSCompliance].[dbo].[ComplianceSavedSearch_MT] table.

If report is created directly in the database, by creating record on [ComplianceSavedSearch_MT] table with defined SearchSQL value, returned results in WEB UI are shown as static list.

While reports created through WEB UI, might include clickable values, allowing to open Inventory device properties, Application properties, License properties and number of other views.

I am wondering if it is possible to make custom reports, with underlying SQL query, to include clickable values as an output on WEB UI. As example if SQL query return list of applications, is it possible to perform the match on application name or id, in order to add a link to the Application Properties window.

Thanks, Marius.

[ChrisG]

I don't know the answer to this, but have a suggestion of a direction to investigate:

Configure a custom report that includes a link similar to what you are trying to set up, then take a look in the ComplianceSavedSearch.SearchMapping value that gets saved for that report.

I wonder if you may find some details in the SearchMapping XML content which are the magic for FlexNet to know that the column should be a link to a record of a particular type and ID. If you can find something there then that may enable you to work out some magic to put into the SearchMapping value for the record you are manually creating in ComplianceSavedSearch to get a link appearing.

[mfranz]

The approach is absolutely correct. The easiest way is to create a report from the WebUI, containing as many objects, you'll later need in your custom report, as possible. Then get the SearchMapping from ComplianceSavedSearch and change it as needed (removing/adding your own columns). Then register your custom report using that mapping.

Some things to consider:

• If you combine SearchMapping data from multiple reports, make sure to correct <QueryDepth>, so that each object gets its own (e.g. Computer and User). Otherwise it may mix up IDs.
• You'll need to keep IDs (e.g. ComplianceComputerID) in the report and underlying SQL statement. They can be hidden, but are needed to reference the correct object.

[marius]

Thanks, I managed to apply SearchMapping to my custom report. As result I got some working links and groupings on my report. But it would be nice to have documentation on SearchMapping XML elements, this would help to understand implementation better.

[ChrisG]

@marius  - great to hear of your success here! If you have any further details of a SearchMapping that worked for you then it would be great to see a sample posted here.

Frankly, when getting to this level of tweaking you are starting to work with internal system details that are not intended to be directly accessed or used - hence you won't find any documentation for this. I'm not wanting to discourage you from being adventurous and enjoying the power of working directly at this level, but please understand that it is an approach that you won't find any formal support for.

[JanPie]

Hi Marius,
I was wondering about the same recently. I built SQL report with addition of some columns that are not available in reporting module and I'd like to make some values clickable.
May I ask how you were able to register new report with custom Searchmapping?

[erwinlindemann]

Gentlemen,

The XML stored in the [ComplianceSavedSearch].[SearchMapping] column indeed is the location that defines the layout of columns in your custom report, as well as additional properties like if a column will to work as a link.

Manually editing this XML based on samples taken from existing FNMS reports is not a structured approach though.

May I suggest a better solution? The concept for the suggested FNMS reporting library is to use two objects for configuring a report in FNMS:

1. A view that defines the report structure – columns and properties, like if a report column should be available from the ‘Column Chooser’ by default.
2. A stored procedure that actually retrieves the data.

After defining these two objects, instead of using the clunky standard FNMS procedure ‘ComplianceCustomViewRegister’, you call a procedure ‘SoSoComplianceCustomViewRegister’ from the library that does all configuration work in the background:

EXEC dbo.SoSoComplianceCustomViewRegister 
       @ReportNameUnique =   'FNMS Accounts with their latest activity date'
      ,@Description =        'All accounts with the date of their last activity.'
      ,@VersionNo =          '1.0'
      ,@ReportFolderName =   'Sample Training Reports'
      ,@ViewDefinitionName = '<YourCustomReportDefinitionView>'
      ,@StoredProcedure =    '<YourCustomReportStoredProcedure>' 

The attachment to this post contains the reporting library including the ‘SoSoComplianceCustomViewRegister’ procedure and all additional modules required.

The attachment also includes a sample report that uses the reporting library for retrieving all devices (inventories) that have either a Microsoft SQL Server database or an Oracle Database installed.

For documentation, an interactive training module with sample implementations is conveniently provided at the end of the SQL reporting library. If you have a working FNMS test environment, you can load the reporting library into the Microsoft 'SQL Server Management Studio', connect to the [FNMSCompliance] database and work through the examples, creating interesting reports on the fly.

And yes, the documentation also covers how to create reports with dynamic links. That is where this whole discussion started after all 😁.