View's Calculated Fields

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

Comments (4)

photo
1

Hi Tal and Dor,


I was able to accomplish this with this query:


  1. SELECT a.ViewName, b.ColumnName
  2. FROM 72yellowfin20170420.reportfieldtemplate as b, 72yellowfin20170420.reportview as a
  3. where b.ViewId= a.ViewId
  4. and a.ViewId = 60593
  5. 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

photo
1

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!

photo
1

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

photo
photo
1

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