Yellowfin metadata SQL query to find all the filters present in dashboards and it's tabs

Mahesh Karpe shared this question 3 months ago
Completed

Hello Team,


I would like to know if there is a way to find out what are the list of filters used in yellowfin dashboards and it's tabs. Can you provide an SQL query to find this out or guide how we can find this?

Replies (5)

photo
1

Hi Mahesh,

Thanks for reaching out to support with your query.

Kindly allow us sometime to investigate if there is way to find out the dashboard filters via sql query. I will get back to you with an update.

Warm regards,

Deepak

photo
1

Hi Deepak,

Thank you for acknowledging. Looking forward for your response.

Thanks & Regards,
- Mahesh

photo
1

Thanks Mahesh.

Usually the tables that we might want to have a look are ReportGroup, ReportGroupEntity, ReportGroupFilter, ReportFilter, to find out the filterid/filteruui and then use ReportInstance and ReportInstanceFilter tables to find the values applied. For example:

b75bfeebffe2826ce75a044aa1dca28b


But there seems to a missing relation to find out the instanceid (may be its done through code). I am not really sure if we can achieve this via sql.

Thanks,

Deepak


photo
1

Hi Mahesh,

I have tried to further analyse this particular scenario and saw that the values for the dashboard filter are directly being inserted into table via a method called insertReportInstanceFilter() (from code) and hence we do not have a relationship between reportinstancefilter and ReportGroupFilter.

So, unfortunately, I believe we cannot query the db to get the values applied on dashboards. But we still can rely on the latest reportinstances to check the filters applied (as shown in the above screenshot)

Please let me know if there is anything that I can assit with.

Warm regards,

Deepak

photo
1

Hi Deepak,

Apologies for not being clear in describing the requirement. I am looking for only the Filter names present in dashboards and don't need the data values present in these filters as of now. For example in your screenshot provided in trail response. The "Demographic" filter name is the name which I am looking for. So the expected output should look like "Dashboard Name", "Tab/Page Name", "Folder Name" and "Sub-Folder Name" (in which the dashboard resides), "Filter Name on the tab or main page of the dashboard".

I have debugged and arrived at a query (currently not much efficient due to use of distinct clause). However, there are few issues in it.
1. Extra filters are visible maybe due to incorrect joins (as unable to find the primary report linked to the dashboard)
2. In some cases, the master filter which is linked to the dashboard filter does not show in this query.

Below is the query I have tried from my end. It would be really helpful if you could guide me with the steps or suggest changes to below query -

select distinct rg1.shortdescription as "Dashboard Name",
CASE WHEN RG2.ShortDescription = 'New Tab' THEN 'Main Page' else RG2.ShortDescription END as "Page/Tab",
rg1.categorycode as "Folder",rg1.subcategorycode as "SubFolder",
coalesce(rgfo.formatvalue,rgf.filtername,rfo.description) as "Filter Name" from 
  ReportGroup RG1
  LEFT JOIN ReportGroupEntity RGE1
 ON RG1.GroupId = Case when RGE1.entitytypecode='SUBTAB' then RGE1.GroupId end
  LEFT join ReportGroup RG2 on Case when RGE1.entitytypecode='SUBTAB' then RGE1.entityid end=RG2.GroupId --tabbed
left join ReportGroupEntity RGE2
  on Case when RGE1.entitytypecode='SUBTAB' then RGE2.GroupID end= RG2.GroupId
left join widgetcanvas wc on RG2.Groupid = wc.EntityId
left join widgetitem wi on wc.canvasuuid=wi.canvasuuid
left join widgetformat wf on wi.widgetuuid=wf.widgetuuid 
and lower(wf.propertyname)='publishuuid'


left join ReportHeader on   ReportHeader.reportid  --when RGE2.entitytypecode='SUBTAB' then RGE1.GroupID end
 = RGE2.entityid or wf.propertyvalue=ReportHeader.publishuuid
inner join reportformat rfo on ReportHeader.reportid=rfo.reportid
inner join reportfilter rpf on rfo.reportid=rpf.reportid and rfo.entityid=rpf.filterid


left join reportgroupformat rgfo on RGE1.entityid=Case when rgfo.componenttypecode='FILTER' and rgfo.formatkey='DESCRIPTION' then rgfo.entityid end
and rgfo.groupid=RGE1.groupid
left join reportgroupfilter rgf on RG1.groupid=CASE when rgf.filtername is not null then rgf.reportgroupid end
and rpf.filteruuid=rgf.reportfilteruuid 

where RG1.ipowner in (1)
and rfo.formattypecode = 'DESCRIPTION'
and RG1.statuscode='OPEN' and rfo.entitycode ='FILTER' and ReportHeader.reportstatuscode='OPEN'
AND Nullif(coalesce(rgfo.formatvalue,rgf.filtername,rfo.description),'') is not null;

photo
photo
1

Hi Mahesh,

Unfortunately, the current request seems to be falling out of scope for support as this request is specific to your environment/business requirement. Unfortunately, we don't have any documentation on the db tables/schemas. At this stage, I believe it's better to reach out to specialist services to see if they can assist with this request.

Please let us know if you would like us to get in touch with your CSM to discuss further.

Warm regards,

Deepak

photo
1

Hi Mahesh,

Hope you're having a good week.

Just wanted to touch base on this request and see if there is anything else that we can assist with.

Thanks,

Deepak

photo
1

Hi Mahesh,

Hope you had a great weekend.

Just wanted to check-in and see if there is anything you are needing from me to help get this resolved?

Kind regards,

Deepak Chaganti

photo
1

Hi Mahesh,


I hope things are going well with you.


Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.


Kind regards,

Deepak Chaganti

Leave a Comment
 
Attach a file