Identify calculated fields that are not in use

Tal Mickel shared this question 2 years ago
Answered

Hi,


I want to retrieve from the repository a list of all the reports that contains calculated field that are using a specific field in their calculation.


The issue is how can i identify those calculated fields in reports that are not using them in columns or in filters?


Thanks!

Best Answer
photo

Hi Tal and Dor,

Please let me know if this query works for you:


  1. SELECT a.ColumnName, a.ViewId, b.ViewName, b.ViewDescription, a.FieldTemplateId FROM 73yellowfin20170420.reportfieldtemplate as a, 73yellowfin20170420.reportview as b
  2. WHERE RefRltshpTypeCode IN ('CALCFIELD','SQL', 'CUSTOMFUNCTION') AND FieldTemplateId NOT IN (SELECT FieldTemplateId FROM 73yellowfin20170420.reportfield) AND a.ViewId = b.ViewId

Essentially I am selecting all unused calculated fields, and then joining the relevant view name to give it some context.

Regards,

Nathan

Comments (1)

photo
1

Hi Tal and Dor,

Please let me know if this query works for you:


  1. SELECT a.ColumnName, a.ViewId, b.ViewName, b.ViewDescription, a.FieldTemplateId FROM 73yellowfin20170420.reportfieldtemplate as a, 73yellowfin20170420.reportview as b
  2. WHERE RefRltshpTypeCode IN ('CALCFIELD','SQL', 'CUSTOMFUNCTION') AND FieldTemplateId NOT IN (SELECT FieldTemplateId FROM 73yellowfin20170420.reportfield) AND a.ViewId = b.ViewId

Essentially I am selecting all unused calculated fields, and then joining the relevant view name to give it some context.

Regards,

Nathan

photo
1

Thanks!

photo
1

No Problem!

photo
1

Edited the query to include freehand and pre-defined calculated fields.

photo
1

Hi Nathan,


I don't know about CALCFIELD, SQL, CUSTOMFUNCTION but the query only retrieve correct results if i add RefRltshpTypeCode = CALCFIELDSIMPLE.


Thanks

photo
1

Hi Tal,

I apologize I figured that you were interested in calculated fields created at the view level. As I mentioned in my previous response, CALCFIELD applies to calculated Fields created at the view level, while CALCFIELDSIMPLE applies to fields created at the report level. In the example below you can see the different between two identical calculations created in each location:

63e203a13f10a0047ab3adc628f443ff

You can also see that view level fields have a value in 'GroupCode'. Let me know if this makes sense.

Regards,

Nathan

photo