Which DB table can provide SQL query for certain report?

Hi Steven,

I've merged your two requests together as I they seem to be directly related.

You can look for information on the SQL of the Report in the ReportInstance table. Firstly, you will need to find the ReportId of the report that you are looking for the SQL for, by querying the ReportHeader table, where you can use the ReportId to query the ReportInstance table. The SQLText column should give you the SQL that was executed at the time of creating that particular instance of the Report.

select * from reportinstance
where reportId = <reportId>;

Let me know if this helps answer your questions.

Kind regards,

Simon

Hi Simon,

Thank you for the prompt response!

I think I can locate the SQL now in the backend.

May I know why for some report, there is no SQL tab of the information module? I attached one example.

which kind of report I cannot check the SQL from Yellowfin frontend?

Thanks and regards

Hi Steven,

I'm glad to hear that worked for you.

Normally, not being able to see SQL syntax for certain reports is due to Access Control or privacy settings set up in your environment. There's not enough information from what you've described to be able to say definitively, where it could also be a defect with the unsupported version of Yellowfin that you are using.

Kind regards,

Simon

Hi Simon,

Please confirm if my understanding is correct or not.

We can only get the SQL script of certain report after running it for at least once from frontend;

at backend, some reports were encrypted, that's why SQLtext shows null? If so, May I know how to encrypt the report SQL?

Thank you!

Hi Steven,

In the UI, if a Report is protected by some sort of Access Control or privacy settings, it may not be visible to the User looking at the Report, which is what may be happening in your case. I don't have enough information from just the screenshot you have sent, so I'm not sure if there would be another reason.

In the Database, you can access the SQL text in the ReportInstance table, which is a table of Report instances that were generated by the associated Report (found in the Report Header table). If there is no SQL in the SQL Text section, then the Report may not have run, it could have been saved with new Report Fields/ Filters etc. which would still mean that it is a new instance of the Report.

SQL is generally created by Yellowfin on the fly, so this would be the only place that this information would be held in the Database.

Let me know if this answers your question.

Kind regards,

Simon

Hi Simon,

I think I'm clear with this part now, much appreciate your patient explanation?

Please leave this topic as closed.

Regards