Audit views - reportfield table

Hi Brendan,

Sorry for delay.

I will get back to as soon as possible.

Kind Regards,

Mahesh

Hi Brendan,

In the Reportfieldtemplate table, the ShortDescription is the column which matches with the ColumnHeading in the ReportField table.

That is the clue, not sure if this helps you in anyway, if this is not helpful, please send me the SQL query, I will try to get a solution.

Let me know if you have any questions.

Kind Regards,

Mahesh

Hi Mahesh, thanks for having a look. I was thinking it'd be good to get the column (short description) as it is applied to the field at the report level, particularly if it had been changed from the view/field template short description. I'll keep digging and see what I can find.

Thanks,

Brendan

Hi again! So I haven't solved that particular challenge (and working within the limits for now) but have also discovered that the SQLText field is populated for only some records in the reportinstance table (seemingly archived and not open), and it is always blank in the reportheader table. Do you know of any reason why it would only be recording for some? Image of results and SQL are attached.

Thanks,

Brendan

Hi Brendan,

The SQLtext field is empty because those reports are no longer in use. For some if the SQL is too long to fit in that field then it is stored in different table in encrypted format which we cannot see.

Please let me know if you have any questions.

Kind Regards,

Mahesh

Hi Mahesh, that's strange as it is the reports that are no longer in use that I can get the SQL text from the reportinstance table but not the current ones (that I ran this morning), and the SQL wouldn't have changed significantly between the current and archived versions.

Hi Brendan,

What I found is the Reports which are Archived in the ReportInstance table are marked as deleted in the ReportHeader table.

I am just wondering what are you trying to find out or want to extract from the database, like I mean your requirements and its use.

If it's feasible and quick I can find answers for our developers.

Please let me know your questions.

Kind Regards,

Mahesh

Thanks Mahesh, I guess my aim is two-fold: one is to be able to be able to return a report's SQL as a whole, and the other is to be able to search it for field names in order to identify reports that might refer to a field within a calculated field or join (I've been able to identify reports that use a field as a column or a filter using other specific tables). This will help us report on all instances where a view field is used within reports.

Hi Brendan,

Thanks for your patience.

It is hard to get a hold of a developer and discuss as they are busy.

However, the only idea to get all the fields used in the reports is by taking the SQL from each and every report from Yellowfin and we cannot get all the fields directly from the configuration database as they are encoded.

Please let me know if you have any questions.

Kind Regards,

Mahesh

Hi Brendan,

Hope you are doing well.

Can you please let me know if you have any questions?

Kind Regards,

Mahesh

Hi Mahesh, thanks for following up - I guess no questions at the moment as the aim was to be able to return/search SQL across reports rather than access each one individually.

Kind regards,

Brendan

Hi Brendan,

Thanks for your reply.

What we can do is create an idea ticket in this scenario.

Team will do assess and let us know if it possible to do in a single or few SQL queries.

I will create an idea ticket, you will receive an email and you can add on more information.

Kind Regards,

Mahesh

Hi Brendan,

As I have created an Idea ticket, I will close this ticket now.

Kind Regards,

Mahesh