How to Add a Filter to a Data Store in Oracle BICC
Oracle BI Cloud Connector (BICC) empowers organisations to control and optimise their cloud data extractions by enabling precise filtering at the Data Store level. This feature is critical for extracting just the data you need in order to minimise both export size and downstream processing time.
Adding a Filter: Why and How?
When you add a filter to a BICC Data Store, you instruct Oracle Fusion to only extract rows matching the specified conditions. Filters can be based on dates, status codes, language settings, or any other view object column exposed by the Data Store configuration. Filters are written in a simple SQL-like syntax, referencing columns as __DATASTORE__.ColumnName.
Practical Steps
In BICC, go to your custom offering.
Select the Data Store you want to filter.
Edit Data Store Details and locate the “Query Filter” field.
Enter your filter condition using the proper syntax.
Validate and save the filter.
Examples of Data Store Filters
Filter based on ID
__DATASTORE__.ViewApplicationId = 0Filter based on Characters
__DATASTORE__.ActivityStatusCode = ‘NOT_STARTED’
Filter based on Language Based System Variable
__DATASTORE__.ProjectTypeTranslationPEOLanguage = VALUEOF(USER_LANGUAGE_CODE)
Filter based on Date Range
__DATASTORE__.CostDistributionLinesEOLastUpdateDate >= TIMESTAMP ‘2019-01-15 07:45:00’
Filter based on CURRENT_DATE
__DATASTORE__.CostDistributionLinesEOLastUpdateDate > CURRENT_DATE
Filter based on a Calculated Date i.e. 90 Days
(TIMESTAMPDIFF(SQL_TSI_DAY, __DATASTORE__.CostDistributionLinesEOLastUpdateDate, CURRENT_DATE) <= 90)
Filter between Two Timestamps
__DATASTORE__.TransactionsEOCreationDate >= CAST(‘2009-08-13 21:52:19.000’ as timestamp)
AND __DATASTORE__.TransactionsEOCreationDate <= CAST(‘2019-10-13 21:52:19.000’ as timestamp)
Best Practices
Use dynamic system variables like
VALUEOF(USER_LANGUAGE_CODE)to personalise extracts.Date and status filters help limit extracts to recent or in-progress records, saving time and storage.
Validate your syntax and test the filtered extracts before moving to production.
Summary
Adding filters to Data Stores in BICC is a powerful feature that gives you control and agility in Oracle Fusion Cloud Applications data management. With a little SQL know-how and the correct column references, you’ll be extracting only the data that matters – quickly, securely, and efficiently.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
