Solved

Does anyone know which tables store the repeater's filter criteria for business analytics or business reporter reports?

  • 21 February 2020
  • 2 replies
  • 276 views

Userlevel 1
Badge +2

We have created many Business Analytics reports and need to modify hard-coded filter criteria in many of the reports.  Is there a table that stores the filter criteria for repeaters that have been defined in the report template?  I’ve tried searching the XML files that are generated by unzipping the XLST file.  But have not been able to find anything related to the repeaters.  

I also found the table that stores the report parameters (XLR_TEMPLATE_PARAMETER_TAB) ; those the user of the report is prompted for, but can’t locate where the row, column and cell repeaters are stored.  

icon

Best answer by CallumW 24 February 2020, 13:33

View original

2 replies

Userlevel 6
Badge +15

This looks like them…

 

Repeaters are dsod_grouping

Regards, Callum

Userlevel 1
Badge +2

This is great, thanks Callum.   

The information I was looking for is in the dsod_criteria_tab table. 

I then ran the following SQL to extract all the filter criteria, per repeater row. 

This will allow me to review all the filters for all BA/BR reports.  The result is similar to what you see in the Advance Filter Criteria section. 

SELECT template_id, set_id
, LISTAGG(c.dimension_item_id || c.fact_item_id || c.operator || c.bind_value, ' ')
WITHIN GROUP (ORDER BY function_no) AS CRITERIA_TEXT
FROM
( -- criteria row functions in order
select *
from dsod_criteria_tab c
where c.template_id like '0%'
order by 4,3,2,1
) c
GROUP BY template_id, set_id
order by 1,2
;

I tried a database UPDATE of the bind_value for one criteria.  Unfortunately, after the update is committed subsequent downloads of the BA/BR report don’t show the change to the bind_value.   It would be nice to be able to UPDATE the values.  But it appears that we need to make the changes in the BA/BR reports themselves. At least we can query to make sure that we don’t miss any. 

Reply