Can a FNMS report be executed from SQL Management Studio?

[lsmith]

We know we can create a FNMS Custom Report from a SQL stored procedure, however, what we want to do is "in some way" execute a report created from the FNMS-Report option in SQL.

The FNMS report is created with multiple filters, and they are updated frequently. The report is saved with the same name every time the filter is updated or when columns are added or removed.

Has FNMS a function or stored procedure that receives the report number (or name) to execute the report?
Has somebody done something like this previously? Any suggestions?
Thank you

[ChrisG · Accepted solution]

A SQL script like the following can be executed against a FlexNet Manager Suite On-premises compliance database to retrieve the SQL query used to run a report, and then execute it to load the data into a temporary table for subsequent manipulation:

DECLARE @SearchSQL NVARCHAR(MAX)

SELECT @SearchSQL = SearchSQL
FROM dbo.ComplianceSavedSearch
WHERE ComplianceSavedSearchID = 12345 -- Replace with appropriate condition(s) to identify the report to be executed

IF @SearchSQL IS NULL
BEGIN
	RAISERROR('ERROR: Report has a NULL query - a user must refresh the report in the web UI to generate the query before this script can be run', 16, 1)
	RETURN
END

IF @SearchSQL NOT LIKE '%SELECT  TOP(@RowLimit) results.*%'
BEGIN
	-- If this condition occurs, the structure of the report query
	-- being produced by FNMS may have changed since this script
	-- was developed. In that case this script will need to be
	-- changed to cope with the latest report query structure.
	RAISERROR('ERROR: Report query is not structured as expected', 16, 1)
	RETURN
END

-- Instead of returning the report results as a result set,
-- change the query to populate a temporary table so that
-- data can be subsequently manipulated.
SET @SearchSQL = REPLACE(@SearchSQL, 'SELECT  TOP(@RowLimit) results.*', 'SELECT results.* INTO #ReportData')

EXECUTE sp_executesql @SearchSQL, N'@SearchText NVARCHAR(1), @RowLimit INT', @SearchText = '', @RowLimit = -1

SELECT * FROM #ReportData
-- Or do whatever else you want to with the data here

Note that the SQL query that is used to generate the report data is not generated until the report has been run at least once in the web UI. The script contains a check to verify that has been done.

[lsmith]

Thank you Chris, I did a test and it worked!!

[DerekMac]

Great Chris, thanks!
As mentioned, would have to create temp table #ReportData, or do what I did,  avoid the temp table ('SELECT results.*')
Also had to change ComplianceSavedSearch to ComplianceSavedSearchID.

The only issue I have now is extra data in the headings of the JOINed fields.
Example heading: R2_bfd577cc81eb08f663b69a18109eada8_ApplicationToInstallation_ApplicationName
... when all I want is ApplicationName.

[adrian_ritz1]

Hello,

@ChrisG, sorry to jump in, I tried also your script and for me is not working, I receive the following error:

Msg 208, Level 16, State 0, Line 30
Invalid object name '#ReportData'. 

Is like temporary table is not created, when I try and disable the replace and run the scrip is working, so it's a little bit stage, do you have some hints in this case?

Thank you for your support and I wish you a nice day.