update via sql dashboard name

Bill Perrello shared this question 1 year ago
Answered

We are beginning to institute some standards (naming conventions) for the many dashboards that we have in in existence. Across 3 different environments (DEV, QA, PROD), it would be quite a headache to rename them in one environment and then export them/then import them to each of the two new environments. Would would prefer to use SQL to update the metadata; and the SQL would be reusable across each DB. After spending a few minutes looking around, I am guessing the data we want to update is in the reportGroup table. Narrowing it down to only those records that are grouptypecode of 'SUBTAB' and statuscode seems to get me to close to want looking in the ShortDescription field, but appears to have duplicate rows and more rows than expected? Appears this list has dashboard subtabs also in it - how do i distinguish between dashboard and subtab? (we are 7.2 version build 20160524) . I would also like to know if the YF app servers will need to be restarted to pick up the changes; my memory is there is/can be some caching that wont allow these changes to be picked up without a restart.

Best Answer
photo

Hi Bill,


Ok firstly I need to state that the method of updating data directly from the Yellowfin DB is not something that is supported, and may have some undesired consequences.

So it's important to understand this is only a rough guide, and if you run into further issues because of this, you will need to revert all changes, so you need to have a backup prior to doing any of this, and anything arisen from this cannot be considered a product defect. Now with that out of the way......


Running the query below will return ANY active dashboard. irrespective if it's a dashboard with or without sub-tabs, as it shouldn't really matter.

select * from ReportGroup


where StatusCode='OPEN'

order by shortdescription


Just update the ShortDescription, and possibly LongDescription.

One thing to note is that SUBTABS do not have descriptions so you will have a NULL for this, leave it as NULL.


If you really wanted to find out which Dashboard those SubTabs live under, you will need to join the ReportGroup.GroupID=ReportGroupEntity.ReportGroupID

But this table is only for reference, you should not be changing anything here.


You will NOT need to restart the service, just refresh the dashboard page, or posisbly log out and back in.


Hope this gives you the results you were after, please let me know how it all goes.


Regards,

David

Comments (4)

photo
1

Hi Bill,


Ok firstly I need to state that the method of updating data directly from the Yellowfin DB is not something that is supported, and may have some undesired consequences.

So it's important to understand this is only a rough guide, and if you run into further issues because of this, you will need to revert all changes, so you need to have a backup prior to doing any of this, and anything arisen from this cannot be considered a product defect. Now with that out of the way......


Running the query below will return ANY active dashboard. irrespective if it's a dashboard with or without sub-tabs, as it shouldn't really matter.

select * from ReportGroup


where StatusCode='OPEN'

order by shortdescription


Just update the ShortDescription, and possibly LongDescription.

One thing to note is that SUBTABS do not have descriptions so you will have a NULL for this, leave it as NULL.


If you really wanted to find out which Dashboard those SubTabs live under, you will need to join the ReportGroup.GroupID=ReportGroupEntity.ReportGroupID

But this table is only for reference, you should not be changing anything here.


You will NOT need to restart the service, just refresh the dashboard page, or posisbly log out and back in.


Hope this gives you the results you were after, please let me know how it all goes.


Regards,

David

photo
1

Hi Bill,


Just checking in on this to see if the info provided gave you what you were after?


Regards,

David

photo
1

Hi David - Thank you very much. This worked well. Here specifically was the SQL we used to pup[date report names:


UPDATE reportheader SET reportname=REPLACE( REPLACE(reportname,' Reporting',''),' Report','') WHERE  reportstatuscode = 'OPEN' and UPPER(reportname) like '%REPORT%'and reportname not like '%reports'and reportname not like '%Yellowfin Report%' 


And here is the SQL we used to update Dashboards:


update ReportGroup set ShortDescription = REPLACE( REPLACE(ShortDescription,' (Dashboard)','') ,' Dashboard','') where  StatusCode = 'OPEN'and    ShortDescription like '%Dashboard%'

photo
1

No problems at all Bill. Please let us know if there was anything else you were after.