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
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 ComplianceSavedSearch = 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.
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
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 ComplianceSavedSearch = 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.
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
@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.
