New Topic

Community Topic Question Administration

Nathan Schroeder

NA Business Team

Assigned

Category Administration

Product Version 7.2

View's Calculated Fields

Tal Mickel shared this question ago

Answered

Hi,

I want to query the repository in order to retrieve all the view's calculated fields (not necessarily being used in reports).

i tried the following query with no luck:

SELECT *

FROM dbo.ReportFieldTemplate

WHERE ViewId = 3470127

AND FieldTemplateId NOT IN ( SELECT FieldTemplateId

FROM dbo.ReportField )

AND RefRltshpTypeCode = 'CALCFIELDSIMPLE'

Thanks!

The same question

Replies (2)

Nathan Schroeder ●

Hi Tal and Dor,

I was able to accomplish this with this query:

SELECT a.ViewName, b.ColumnName
FROM 72yellowfin20170420.reportfieldtemplate as b, 72yellowfin20170420.reportview as a
where b.ViewId= a.ViewId
and a.ViewId = 60593
and b.RefRltshpTypeCode IN ('CALCFIELD', 'SQL', 'CUSTOMFUNCTION');

I also realized that in the query I sent you yesterday (what calc fields are not in use), you will want to include the IN clause of this field so that pre-defined calc fields and freehand SQL calc fields are included as well.. I will update that ticket appropriately.

Please let me know if this works for you.

Regards,

Nathan

Reply URL

Tal Mickel ●

Hi Nathan,

The query also retrieves calculated fields not from the view level but also from the report level.

what i am asking is what part of the query filters only calculated fields that was created in the view and not calculated field that was created in the report?

Thanks!

URL

Nathan Schroeder ●

Hi Tal,

'CACLFIELDSIMPLE' applies to calculated fields created at the report level while 'CALCFIELD' applies to calculated fields created at the view level. To further refine this to calculated fields created at the view level you can also add HierarchyLevel = 1

Regards,

Nathan

URL

Nathan Schroeder ●

Hi Tal,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

Reply URL