Report builder in SQL

[MrPotato]

Is there any way to access, execute and collect, through the SQL queries, resutls from reports created in webui - Report builder (custom or built in ones alike)?

I failed to find a table responsible for this. I'm running on-prem 2019 R2, db used is MS SQL.

[jjensen]

Hello @MrPotato,

The ComplianceSavedSearch table in the Compliance DB may be of interest to you - particularly the SearchSQL column.

https://docs.flexera.com/FlexNetManagerSuite2020R1/EN/Schema/index.html#SysRef/schema/ComplianceSchema/Compliance.Logic.CoreTables/reference/ComplianceSavedSearch.html

HTH,

Joseph

[MrPotato]

Hi @jjensen, appreciate your quick reply. I understand there should be a procedure or a function where I can use data from this table to execute said reports. Is there any further reading material on this?

[msutharh]

Hi @jjensen I get query details from SearchSQL but not able to execute the same. Can you please guide me how to execute and extract the report from backend?

[JohnSorensenDK]

@msutharh 

The SearchSQL column contains the SQL code, so if you declare and set the required variables you should be able to execute it from SQL Server Management Studio's query editor. The query editor will highlight the missing variables that need to be declared.

If you share its content in the forum other community members may be able to provide specific guidance.

Thanks,

[msutharh]

@JohnSorensenDK 

I have attached SQL query. I am also able to execute the query and get output in result window. but it was not executing while creating store procedure. It shows blank records. can someone help me on this to create store procedure for auto download this report on particular schedule?

[bheadley]

Hi @msutharh!

The data you are querying is scoped to specific operator context. You can use the stored procedure 'SetSessionContext' to set to context under which the query is run. The stored procedure takes 2 parameters with the first being an OperatorID and the second being a TenantID. Find the TenantID in the 'Tenant' table and find the OperatorID by querying the 'ComplianceOperator' table looking for the OperatorLogin of the operator you need to use for the SQL.

The use of the table functions like 'TablePurchaseOrderDetailCurrentUser',  'TableComplianceUserCurrentUser' and 'TableAssetCurrentUser' near the top of the SQL script give away the use of operator scoping.

So, put something like:

EXEC SetSessionContext 123, 1

at the very top of the script once you have the necessary information.

HTH!

Bill

[ChrisG]

This is an old thread, but for people finding it in the future, check out the following thread which also contains an answer to this:

Can a FNMS report be executed from SQL Management Studio?