how to report on fields used in a view

Janine Delicato shared this question 14 months ago
Answered

Can I create a report from the yellowfin database that will show which table and column name are associated with a field in a view?5586cf9a246f06abb5875b00931b406a

Comments (11)

photo
1

Hi Janine,

Thanks for getting in touch. Something like this should provide what you need.

select

a.ViewDescription as "View",

c.viewname as "Table",

b.columnName as "Raw Column",

b.shortdescription as "Column Name in View"

from ReportView a

inner join ReportFieldTemplate b on (a.viewId = b.viewId)

left outer join ReportView c on (b.SubViewId = c.viewId);

and if you want to filter by the view then just add a where is clause on the end for example.

where a.ViewDescription = 'Ski Team';

3b1ca8863e0a6873731fe3f5870006eb

or if you want to filter by c.ViewName it would allow you to filter by the database table that the column came from.

fe5dbab3170b11e036638a78e023bc49


or filter as required :)


Please give this a go and let me know how it went.


Regards,

Paul

photo
1

Perfect! Just what I wanted.


Rgds, Janine

photo
1

Hi Paul


Would it be possible for you to assist with a couple of other reprots?


- identify which fields from a view are used in a report (we sometimes want to remove fields but can't because they are being used).


- identify which reports are in a dashbaord but show the overall dashboard name not just the tab name.


Hope you can assist!


Rgds, Janine

photo
1

Hi Janine,

You can use this for - identify which fields from a view are used in a report (we sometimes want to remove fields but can't because they are being used).

select

a.ViewDescription as "View",

c.viewname as "Table",

b.columnName as "Raw Column",

b.shortdescription as "Column Name in View",

e.ReportName as "Report Name"

from ReportView a

inner join ReportFieldTemplate b on (a.viewId = b.viewId)

left outer join ReportView c on (b.SubViewId = c.viewId)

left outer join ReportField d on (b.FieldTemplateId = d.FieldTemplateId)

left outer join ReportHeader e on (d.ReportId = e.ReportId)

where a.ViewDescription like 'Ski Team';

For the second one I need to work on a little but bouncing between tickets at the moment and I might not get back to you till next week as I will be off for a couple of days, but if you want to try, the following may help. Perhaps have a go else let me know if you need some code.


- identify which reports are in a dashbaord but show the overall dashboard name not just the tab name.

ReportGroup contains the main Dashboard.

There is usually a second ReportGroup record for the sub tabs.. This can be linked to the parent dashboard by ParentGroupId field (to GroupId).

ReportGroupEntity has what reports are on a tab. (GroupId to EntityId where EntityTypeCode is REPORT)


ReportHeader has the ReportName (Join to ReportGroupEntity on ReportId)

Thanks for your patience,

Paul

photo
1

Hi Janine,

Did you manage to get the second part of your question working. If not then this should work.

select

a.shortdescription as "Dashboard",

e.shortdescription as "SUb TAB",

--a.groupid as "ReportGroup Group ID",

--b.groupid as "ReportGroupEntity Group ID",

--b.entityid as "Entity ID",

c.reportname as "Report Name"

--b.*

From ReportGroup a

inner join ReportGroupEntity d on(a.groupid = d.groupid and d.entitytypecode='SUBTAB')

inner join ReportGroup e on(d.entityid = e.groupid)

inner join ReportGroupEntity b on(e.groupid = b.groupid and b.entitytypecode='REPORT')

inner join reportheader c on(c.reportid = b.entityid) -- tried inner also

order by a.shortdescription;

Let me know how this goes.

Thanks,

Paul

photo
1

Hi Janine,

Please remove the -- lines :)

photo
1

Hi Janine,

Just touching base with this ticket and seeing if everything is now okay or whether you need further help on this?

Regards,

Paul

photo
1

Hi Paul,

Thanks for your assistance with this. I have now been able to create the reports I needed.


Janine

photo
1

Hi Janine,

Thats is excellent news. I will close this off for now, but please reach out if further assistance is required.

Regards,

Paul

photo
1

Hi Paul

One of the other areas we are having issues with is deleted some reports, typically ones created by people no longer using the system.

Either they cannot be deleted (i.e. only option is to Open with no Delete in menu)

92da40040d19c5774a3c6ca67ee3ea4aeef88426523ad018daf21e60822d1104

Or you cannot see a report in a folder but if you try to delete the folder it won’t allow it as it thinks there is a report there:

ba32829fd76f5dc33e685136cb7fa9b9

Some guidance on how to remove these would be most welcome!

Kind regards,

Janine Delicato

Technical Business Intelligence Analyst

Defence Health| Level 4, 380 St Kilda Road, Melbourne VIC 3004

Phone/Fax 03 8679 1105 Mobile 0422 864655 | defencehealth.com.au

966209d16f17061e800088e4d1e5295a

From: Support Queue [mailto:support@Yellowfin.bi]

Sent: Thursday, 18 October 2018 9:12 PM

To: Janine Delicato <janine.delicato@defencehealth.com.au>

Subject: New Comment in "how to report on fields used in a view"

photo
1

Hi Janine,

Just letting you know that I have created a different ticket for this problem so it can be looked at independently. You should have received the ticket via email however if not the ticket is https://community.yellowfinbi.com/ticket/11128

This just helps with tracking tickets internally and prevent problems getting muddled up within each other.

Cheers

Paul