Find Detail on Tables Used in View
Answered
I am in need to optimize a view that... let's just say it "lost it's way." It is now in need of optimizing. Is there a way I can find what tables/columns are being used in what reports? I do not know if in modifying the view I will break any existing reports. I know how to see what reports are being used, just not what tables. I am competent in DB use as well....
Thanks,
Zack
The same question
Hi Zack,
Thanks for reaching out. There's not really an easy way to obtain a list of columns/tables being used by a report, though it is technically possible. Generally, field definitions are stored in two places: ReportFieldTemplate and ReportField. The reason it's difficult to return a list by report is because most field definitions are stored at the View level - specifically unchanged View fields that are used at the report level. For example, if you build a report with 5 fields, and 4 of them are drag and dropped from the View without being altered at all at the report level, these four fields will remain linked to the ViewId in the ReportFieldTemplate table. Now for that 5th field, if it's changed - say you make it so there's a Default Value at the report level, a new entry will be created in the ReportField table and will be linked to FieldTemplateId so you can see what field this "new" field is based off of in the View.
For example, I have a Column called Profit Margin in report 60737:
I can see the field it's based on in the View level:
And can see what ViewId the field is linked to.
Let's say field 4 you then change the formatting of, for example:
This is what was being used initially, stored in ReportFieldTemplate table, but if alteringn format, there will now there be another entry linked to the report in the ReportField table such as what you see in my screenshot above this one... ultimately, it's going to be a mix of fields directly from View, unchanged, stored in the ReportFieldTemplate table as well as the ReportField table. As you can imagine that makes returning a list of fields used in a report rather complicated, if even at all possible, which it might not be.
That said, there technically is another way to return tables. Also a little messy, and not always available, but should give you what you're looking for if it is. In the ReportInstance table, stored under the SQLText column, you'll see reports' full SQL queries, which of course contain all the tables and columns being used. To use same report example as above:
Technically, you could write a query to parse out the table names and this may be your best bet to determine which tables/columns are used in a particular report.
Hopefully this explanation makes sense and this option is suitable. Please let me know how goes and whether you have any additional questions.
Regards,
Mike
Hi Zack,
Thanks for reaching out. There's not really an easy way to obtain a list of columns/tables being used by a report, though it is technically possible. Generally, field definitions are stored in two places: ReportFieldTemplate and ReportField. The reason it's difficult to return a list by report is because most field definitions are stored at the View level - specifically unchanged View fields that are used at the report level. For example, if you build a report with 5 fields, and 4 of them are drag and dropped from the View without being altered at all at the report level, these four fields will remain linked to the ViewId in the ReportFieldTemplate table. Now for that 5th field, if it's changed - say you make it so there's a Default Value at the report level, a new entry will be created in the ReportField table and will be linked to FieldTemplateId so you can see what field this "new" field is based off of in the View.
For example, I have a Column called Profit Margin in report 60737:
I can see the field it's based on in the View level:
And can see what ViewId the field is linked to.
Let's say field 4 you then change the formatting of, for example:
This is what was being used initially, stored in ReportFieldTemplate table, but if alteringn format, there will now there be another entry linked to the report in the ReportField table such as what you see in my screenshot above this one... ultimately, it's going to be a mix of fields directly from View, unchanged, stored in the ReportFieldTemplate table as well as the ReportField table. As you can imagine that makes returning a list of fields used in a report rather complicated, if even at all possible, which it might not be.
That said, there technically is another way to return tables. Also a little messy, and not always available, but should give you what you're looking for if it is. In the ReportInstance table, stored under the SQLText column, you'll see reports' full SQL queries, which of course contain all the tables and columns being used. To use same report example as above:
Technically, you could write a query to parse out the table names and this may be your best bet to determine which tables/columns are used in a particular report.
Hopefully this explanation makes sense and this option is suitable. Please let me know how goes and whether you have any additional questions.
Regards,
Mike
Hi Zack,
I just wanted to check in and see how things are going with this. Did this help at all?
Regards,
Mike
Hi Zack,
I just wanted to check in and see how things are going with this. Did this help at all?
Regards,
Mike
Mike,
Yes. Not ideal, but it sufficed!
Thanks,
Zack
Mike,
Yes. Not ideal, but it sufficed!
Thanks,
Zack
Hi Zack,
Indeed not ideal, but glad to hear this seemed to at least eventually get you to what you were looking for. This considered, I'll go ahead and close this case out, but please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Hi Zack,
Indeed not ideal, but glad to hear this seemed to at least eventually get you to what you were looking for. This considered, I'll go ahead and close this case out, but please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Replies have been locked on this page!