Category
Report Builder
Product Version
9.0
SQL for setting all filters to Cache values from Reference code
Completed
Hi Team,
Are you able to provide an SQL that i can run on Yellowfin DB which will set all filters to use Cached values. I have over 300 reports that need to be set to cached values and clicking through one by one is pretty time consuming. Also, would like to set a schedule refresh for all reportrs through an
SQL.
The same question
Hello Dylan,
Thank you for reaching out to Yellowfin Support
While I look into this, could you please share the info.jsp. To access this page, just append info.jsp to your YF url.E.g. http://localhost:8080/info.jsp
Regards,
Yamini Naidu
Hello Dylan,
Thank you for reaching out to Yellowfin Support
While I look into this, could you please share the info.jsp. To access this page, just append info.jsp to your YF url.E.g. http://localhost:8080/info.jsp
Regards,
Yamini Naidu
Hello Dylan,
Thank you for providing the info.jsp. Please give me some time to check into this; once I have my findings, I'll get back to you.
Regards,
Yamini Naidu
Hello Dylan,
Thank you for providing the info.jsp. Please give me some time to check into this; once I have my findings, I'll get back to you.
Regards,
Yamini Naidu
Hello Dylan,
Hope you are doing well
I just wanted to let you know that I've created a task for the dev team to review this issue further. Once I hear from the development team about any updates, I'll let you know.
Regards,
Yamini Naidu
Hello Dylan,
Hope you are doing well
I just wanted to let you know that I've created a task for the dev team to review this issue further. Once I hear from the development team about any updates, I'll let you know.
Regards,
Yamini Naidu
Hello Dylan,
Good Morning!
Just wanted to let you know that the development team has responded to your inquiry with the following statement:
I am sharing some information about filter values list selection here and here.
There is "cached filter" and "cached filter on demand", which would have different functionality depending on these formatting settings:
The report writer can set filter value lists to be cached, meaning the system does not have to go back to the source database to retrieve a list of values each time the user runs the report.
However, to cache the filter values, Yellowfin makes a number of entries in several different tables("REPORTFILTER", "CACHEDFILTER", "CACHEDFILTERVALUES"), which have complex relations between them - this happens when the user presses the "Refresh now" from the picture above, as initially there would not be any cached values. These values depend on the field content at the moment. SQL queries that should insert these entries require preliminary analysis of the source database and advance database knowledge. Also, such a queries should be constructed individually for every report. I understand that the client has over 300 reports and I am sorry to say it, but it is going to be faster and easier to use the user interface.
Regarding schedule refresh for all reports - there is no such functionality in Yellowfin and this also could not be achieved with a single SQL query. Data for report refresh is stored in the "TASKSCHEDULE" table. To schedule a report refresh - a new entry needs to be inserted in the table. There are 21 columns in the table and I will try to explain the data that needs to be inserted - please see taskscheduleDDL.txt
for column data types:
iporg - organization ID - 1 is for the default org;
schedulesubjectcode - subject type of the schedule - this needs to be 'MIREPORT';
scheduleunitcode - type of unit ID - this needs to be 'REPORTID';
scheduleunitid - this is for the report-ID - the report which we want to be refreshed. could be extracted in various ways;
scheduleison - boolean switch to turn the schedule refresh on/off - this needs to be 'true';
localtimezonecode - local time zone code - I believe this should be 'AUSTRALIA/SYDNEY' for the client;
frequencytypecode - type code of frequency - this could be 'WEEKLY', 'DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY' 'ENDOFMONTH'(for the last day of the month)... ;
frequencyunit - depends of the frequencytypecode - if we choose weekly type code and want a refresh every Monday it needs to have value 1 ;
scheduledlocalruntime - time of the report - 0 for midnight;
scheduleuuid - auto genereted ID of the scheduled task. It looks like this '5ced5d68-f877-4a8e-96b6-db2a62eb6ab8';
The rest of the column values could be omitted or will be auto-generated after the first refresh. If the client states what their database type is and what type of schedule they are interested in(is it daily, monthly, weekly.. and what time of the day) - I will gladly provide a query with the exact parameters.
An example query for this schedule:
looks like this:
"102482" is the ID of the report I am scheduling refresh for and the client will have to replace it for every report they want to add a refresh schedule and add an unique "scheduleuuid".
Please let me know if you have any additional questions.
Regards,
Yamini Naidu
Hello Dylan,
Good Morning!
Just wanted to let you know that the development team has responded to your inquiry with the following statement:
I am sharing some information about filter values list selection here and here.
There is "cached filter" and "cached filter on demand", which would have different functionality depending on these formatting settings:
The report writer can set filter value lists to be cached, meaning the system does not have to go back to the source database to retrieve a list of values each time the user runs the report.
However, to cache the filter values, Yellowfin makes a number of entries in several different tables("REPORTFILTER", "CACHEDFILTER", "CACHEDFILTERVALUES"), which have complex relations between them - this happens when the user presses the "Refresh now" from the picture above, as initially there would not be any cached values. These values depend on the field content at the moment. SQL queries that should insert these entries require preliminary analysis of the source database and advance database knowledge. Also, such a queries should be constructed individually for every report. I understand that the client has over 300 reports and I am sorry to say it, but it is going to be faster and easier to use the user interface.
Regarding schedule refresh for all reports - there is no such functionality in Yellowfin and this also could not be achieved with a single SQL query. Data for report refresh is stored in the "TASKSCHEDULE" table. To schedule a report refresh - a new entry needs to be inserted in the table. There are 21 columns in the table and I will try to explain the data that needs to be inserted - please see taskscheduleDDL.txt
for column data types:
iporg - organization ID - 1 is for the default org;
schedulesubjectcode - subject type of the schedule - this needs to be 'MIREPORT';
scheduleunitcode - type of unit ID - this needs to be 'REPORTID';
scheduleunitid - this is for the report-ID - the report which we want to be refreshed. could be extracted in various ways;
scheduleison - boolean switch to turn the schedule refresh on/off - this needs to be 'true';
localtimezonecode - local time zone code - I believe this should be 'AUSTRALIA/SYDNEY' for the client;
frequencytypecode - type code of frequency - this could be 'WEEKLY', 'DAILY', 'MONTHLY', 'QUARTERLY', 'ANNUALLY' 'ENDOFMONTH'(for the last day of the month)... ;
frequencyunit - depends of the frequencytypecode - if we choose weekly type code and want a refresh every Monday it needs to have value 1 ;
scheduledlocalruntime - time of the report - 0 for midnight;
scheduleuuid - auto genereted ID of the scheduled task. It looks like this '5ced5d68-f877-4a8e-96b6-db2a62eb6ab8';
The rest of the column values could be omitted or will be auto-generated after the first refresh. If the client states what their database type is and what type of schedule they are interested in(is it daily, monthly, weekly.. and what time of the day) - I will gladly provide a query with the exact parameters.
An example query for this schedule:
looks like this:
"102482" is the ID of the report I am scheduling refresh for and the client will have to replace it for every report they want to add a refresh schedule and add an unique "scheduleuuid".
Please let me know if you have any additional questions.
Regards,
Yamini Naidu
Hello Dylan,
Hope you are doing well.
I just want to touch base to see if you had chance to read through my response. If you can let me know that would be great.
Thank you!
Regards,
Yamini Naidu
Hello Dylan,
Hope you are doing well.
I just want to touch base to see if you had chance to read through my response. If you can let me know that would be great.
Thank you!
Regards,
Yamini Naidu
Replies have been locked on this page!