Retrieve Dashboard Filters with Default value from Repository

Tal Mickel shared this question 2 years ago
Answered

Hi,


I am trying to find a way to query from the repository all the dashboards that for one of their filters has a specific default value.


For example, i want to get a list of all the dashboards that for filter "Status Category" has the default value "Completed".


Thanks!

Best Answer
photo

Hi Tal,


wow, that turned out to be harder than I thought (because they are stored as name/value pairs) but I persevered because I think you have been very patient and understanding with that terrible null table condition bug (which we hope to finally fix when we get the logs from that other client who can reproduce the bug at will - he said this week). Here is the result of my efforts for you, I hope it's what you are looking for, please let me know:


  1. SELECT trim(rg.ShortDescription) as 'Dashboard Tab Name',
  2. CASE
  3. WHEN rgf.FormatKey = 'DESCRIPTION' THEN 'Filter Name'
  4. WHEN rgf.FormatKey = 'DEFAULTVALUE1' THEN 'Default Filter Value'
  5. ELSE rgf.FormatKey
  6. END as 'Description',
  7. trim(rgf.FormatValue) as 'Value'
  8. FROM ReportGroup rg
  9. INNER JOIN ReportGroupFormat rgf ON rg.GroupID = rgf.GroupID
  10. WHERE rg.StatusCode = 'OPEN'
  11. AND rgf.FormatKey IN ('DEFAULTVALUE1', 'DESCRIPTION')
  12. AND RGF.componentID IN
  13. (SELECT DISTINCT rgf.ComponentID
  14. FROM ReportGroupFormat rgf
  15. WHERE rgf.FormatValue = 'Completed')

Comments (9)

photo
1

Hi Tal,


If I have understood the requirement correctly, I think the following query should do it for you:


SELECT rg.ShortDescription

FROM reportGroup rg

INNER JOIN reportgroupformat rgf ON rg.groupID = rgf.groupID

WHERE rgf.FormatKey = 'DEFAULTVALUE1' and rgf.FormatValue = 'Completed'


See how that goes and please let me know.


regards,

David

photo
1

Thanks for the quick response.


How can i get the field name for which i have the default value (in the original example "Status Category")?


Thanks!

photo
1

Hi Tal,


wow, that turned out to be harder than I thought (because they are stored as name/value pairs) but I persevered because I think you have been very patient and understanding with that terrible null table condition bug (which we hope to finally fix when we get the logs from that other client who can reproduce the bug at will - he said this week). Here is the result of my efforts for you, I hope it's what you are looking for, please let me know:


  1. SELECT trim(rg.ShortDescription) as 'Dashboard Tab Name',
  2. CASE
  3. WHEN rgf.FormatKey = 'DESCRIPTION' THEN 'Filter Name'
  4. WHEN rgf.FormatKey = 'DEFAULTVALUE1' THEN 'Default Filter Value'
  5. ELSE rgf.FormatKey
  6. END as 'Description',
  7. trim(rgf.FormatValue) as 'Value'
  8. FROM ReportGroup rg
  9. INNER JOIN ReportGroupFormat rgf ON rg.GroupID = rgf.GroupID
  10. WHERE rg.StatusCode = 'OPEN'
  11. AND rgf.FormatKey IN ('DEFAULTVALUE1', 'DESCRIPTION')
  12. AND RGF.componentID IN
  13. (SELECT DISTINCT rgf.ComponentID
  14. FROM ReportGroupFormat rgf
  15. WHERE rgf.FormatValue = 'Completed')

photo
1

Thanks a lot for your help!

photo
1

You are welcome!

photo
1

Hi Dave,


I have a dashboard with on sub-tabs. The dashboard has a filter with default value 'Completed'.


For some reason when i run both queries with FormatValue = 'Completed' i get no results.


Can you think of a reason for this behavior?


Thanks!

photo
1

Hi Dave,


Ignore the last comment.


Thanks!

photo
photo
1

Hi Tal,


hmmm....not sure....I guess that maybe it's because I didn't test it on dashboard tabs with sub-tabs. I'll have to look further into this.....


I'll let you know what I find.


regards,

David

photo
1

Hi Tal,


I have been running that query over here, making sure I have a mixture of tabs with and without sub-tabs, all with filters with default values. but unfortunately I can't get the same result as you - I always get the correct result.

Your particular dashboard tab must have something different about it than mine, can you describe the components of the dashboard tab that isn't showing up in the query results, maybe a few screenshots would help too.


regards,

David

photo
1

Hi Tal,


I've attached a short video just to show how I tested the query on my Yellowfin.


regards,

David