How can I see the reports in which a specific view field is used?

Vefa Gulecyuz shared this question 49 days ago
Awaiting Reply

Hey there, I want to remove a field from a view and I do not want this to create any problems in my reports because this field is used in some of the reports.

I already check one by one all the reports from that specific view and removed them. But the field is still grayed out in the view meaning that it is somehow used in a report/subquery/filter etc.

Is there a way to see where a specific field is used in the reports/subqueries/filters etc?

That can also be via repository database.


Application Version:8.0.4
Build:20191216
Java Version:1.8.0_252
Operating System:Linux 4.19.112+ (amd64)

Comments (11)

photo
1

Hi Vefa,

Thanks for reaching out to support with your question. I've found a query that may help in this case-

Find Reports by Column Name

SELECT ReportName FROM reportheader as h

WHERE ReportId in (

SELECT ReportId FROM .reportfield as f

INNER JOIN reportfieldtemplate as t

WHERE t.FieldTemplateId=f.FieldTemplateId

AND t.ColumnName="DEMOGRAPHIC");


Does this help to find where the field is being used?

Thanks,

Eric

photo
1

Hey Eric thanks for the query. This query works but it only shows the reports if a field is used as a column. If a field is used as a filter, it does not show the report.

And according to this query result, I am still unable to find what I am looking for. All the reports are correct, using the field I want.

I wrote another query that shows all the reports/subqueries from a specific view:

SELECT ReportName,* FROM reportheader as h

WHERE ReportId in (
SELECT ReportId FROM reportfield as f
INNER JOIN reportfieldtemplate as t on t.FieldTemplateId=f.FieldTemplateId 
) 
and reportstatuscode in ('OPEN','DRAFT') and viewid = 219533
Again, this query does not show anything new either.

The field that I want to remove does not exist in these reports, yet I am unable to remove that field from the view.

There must be a bug or maybe the field is still blocked by archived or deleted reports etc.

photo
1

Hi Vefa,

Thanks for the update,sorry the query didn't return the results you were looking for here. I assume there's a couple of table joins needed to link the filter field id with a report, so I'll do some research and see if I can get that query for you.

Thanks,

Eric

photo
1

Hi Vefa,


If you know the name of the column from the Database, you should be able to run a query similar to below to get the Reports/ Subqueries that use the column that are still open.


select * from reportHeader where reportId in 
(select reportId from ReportFieldTemplate rft join ReportField rf using(fieldtemplateid) where rft.ColumnName = '<column name>')
and reportstatuscode = 'OPEN'
Let me know how you go with this.


Kind regards,

Simon

photo
1

Hi Vefa,


I just wanted to check in to see how you are travelling with this. Please let me know if you have any further questions, otherwise, I will shortly go ahead and mark this as answered.


Kind regards,

Simon

photo
1

Hey Simon, thanks for the reply. Sorry I was busy with other stuff.

As far as I see this query also brings reports that have a specific column in the report itself. (i.e if this field is in the select statement) but it does not show the reports where this field can be used as a filter.

I need a query that shows the reports/subqueries that use my target field in any part of the report/subquery query itself (it can be in select or where statement). Is this possible?

photo
1

Hi Vefa,


It isn't the prettiest query, but I think the below should do the trick. Let me know if this gets the results that you are after.


select * from reportheader where reportid in (
select reportid from reportfilter where fieldtemplateid in (
select fieldtemplateid from ReportFieldTemplate rft join ReportField rf using(fieldtemplateid) where rft.ColumnName = '<column_name>'))
and reportstatuscode = 'OPEN'
union
select * from reportHeader where reportId in (
select reportId from ReportFieldTemplate rft join ReportField rf using(fieldtemplateid) where rft.ColumnName = '<column_name>')
and reportstatuscode = 'OPEN';

Kind regards,

Simon

photo
1

Hi Simon, thanks for the reply. This is exactly what I needed. However, even though I checked everything one by one, I could not find it in any reports/subqueries, yet that field is still in use somehow.

I think we can close this ticket. The problem must be somewhere else.

Thanks a lot!

photo
1

Hi Vefa,


What is your issue exactly, so I can understand if there might be another way to help you? For example, the field 'ColumnName' is what is read in from the Data Source, where I believe 'ShortDescription' is the name of the field defined in Yellowfin. This may help you narrow down the issue that you are having.


Otherwise, if you are experiencing an error you could replicate the issue and then send through the appserver/logs file for further inspection.


Kind regards,

Simon

photo
1

Hi Simon, the original problem is still the same as it is described in the original post.

Basically, I want to remove a field from a view. But that field is used in some reports, so it is greyed out when I edit the view. I can not remove it. I checked all the reports/subqueries one by one from that view, none of them is seemingly using that field. But it is still greyed out, I can not remove it.

I suspected that YF is somehow not showing correctly in the edit view, so I wanted to see everything related to that field in the database. This is why I have requested above queries.

As a last resort, I will import/export the same view without that field. But I want to check everything possible before doing this.

photo
1

Hi Vefa,


Thanks for clarifying that for me. Would you be able to take a screenshot of what you mean by the field is greyed out? Are you referring to the View Model page?


The method you have just described should work, although I would make a back up before trying this just in case there are issues. There may be something else that you can do at the View level, like removing the table that that contains the field and then re-adding it, although, I would err on the side of caution trying this as well, as it may affect many different fields in many different reports, where you may have to manually re-add them. Again, making a back up before trying anything should save you a lot of time.


Kind regards,

Simon