New Topic

Community Topic Question Administration

Nathan Schroeder

NA Business Team

Assigned

Category Administration

Product Version 7.2

Identify calculated fields that are not in use

Tal Mickel shared this question 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!

The same question

Best Answer

Nathan Schroeder ●

Hi Tal and Dor,

Please let me know if this query works for you:

SELECT a.ColumnName, a.ViewId, b.ViewName, b.ViewDescription, a.FieldTemplateId FROM 73yellowfin20170420.reportfieldtemplate as a, 73yellowfin20170420.reportview as b
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

Replies (1)

Nathan Schroeder ● ●

Hi Tal and Dor,

Please let me know if this query works for you:

SELECT a.ColumnName, a.ViewId, b.ViewName, b.ViewDescription, a.FieldTemplateId FROM 73yellowfin20170420.reportfieldtemplate as a, 73yellowfin20170420.reportview as b
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

Reply URL

Tal Mickel ●

Thanks!

URL

Nathan Schroeder ●

No Problem!

URL

Nathan Schroeder ●

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

URL

Tal Mickel ●

Hi Nathan,

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

Thanks

URL

Nathan Schroeder ●

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

URL