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

Hi Deepak,

Thank you for acknowledging. Looking forward for your response.

Thanks & Regards,
- Mahesh

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:

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

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

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;