how to report on fields used in a view

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';

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

or filter as required :)

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

Regards,

Paul

Perfect! Just what I wanted.

Rgds, Janine

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

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

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

Hi Janine,

Please remove the -- lines :)

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

Hi Paul,

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

Janine

Hi Janine,

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

Regards,

Paul

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)

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:

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

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"

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