Lets assume you need to report on products that are added or deleted on a daily basis. We can put that data into an Oracle table and use it as a filter to refine our output.
First lets create an SQL file Entries.sql with some sample data as shown below:
Drop Table SAMP_ENTRIES;
CREATE TABLE "SAMP_ENTRIES"
( "ID" NUMBER,
"STATUS" VARCHAR2(30)
) ;
Insert into SAMP_ENTRIES (ID,STATUS) values (1,'Bluetooth Adaptor');
Insert into SAMP_ENTRIES (ID,STATUS) values (2,'Game Station');
Insert into SAMP_ENTRIES (ID,STATUS) values (3,'MaxiFun 2000');
Insert into SAMP_ENTRIES (ID,STATUS) values (4,'PocketFun ES');
Insert into SAMP_ENTRIES (ID,STATUS) values (5,'Touch-Screen T5');
Insert into SAMP_ENTRIES (ID,STATUS) values (6,'LCD HD Television');
- connect to the database as BISAMPLE/BISAMPLE user and password and execute the sql script as shown below:
- Open the RPD file in the "BI Administration Tool".
- Right click the "Connection Pool" in the "Physical" Layer and select "Import Metadata"
- Click "Next" button to reach to the screen below.
- Select the "SAMP_ENTRIES" table and click on the "Import Selected" button then click on the "Finish" button.
- Double Click on the "SAMP_ENTRIES" table in the "Physical" pane and under the "Keys" tab enter "ID" for the "Key Name" and select the "ID" under Columns.
- Click "OK" button.
- Drag the "SAMP_ENTRIES" table from the "Physical" layer to the "Business Model and Mapping" layer under the "Sample Sales" Business Model.
- Double Click on the "SAMP_ENTRIES" logical table and select the "Lookup table" checkbox and click the "OK" button.
- Drag the "SAMP_ENTRIES" logical table from the "Business Model and Mapping" pane to the "Presentation" pane and rename it to "Filter Vals".
- Select File -> Save and click yes when asked for the global consistency check.
- Now reload your RPD file by going to the URL: http://localhost:7001/em
- Navigate to the "Farm_bifoundation_domain" -> "Business Intelligence" -> coreapplication.
- Select the "Deployment" tab then the "Repository" tab.
- Select the "Lock and Edit Configuration".
- Select the "Browse" button and choose the RPD file that you just edited and saved.
- Then restart your server under the "Overview" tab.
- Now to create your report with the filter from the newly created SAMP_ENTRIES table.
- Goto the URL: http://locahost:9704/analytics
- Select the "New" -> "Analysis" -> "Sample Sales"
- Under the "Filter Vals" folder drag the "Status" column to the "Selected Columns" area.
- Save the report as "FilterVals"
- Now select the "New" -> "Analysis" -> "Sample Sales"
- Drag the "Product" and "Revenue" columns to the "Selected Columns" area.
- Select the "Product" options and filter and enter the below values as show in the screenshot.
- Click the "Browse" button and select the "FilterVals" report we saved above.
- Click the "OK" button.
- Now click the Results tab to show the results from the just the entries in the "SAMP_ENTRIES" table.
Summary:
As seen above we have use a filter first by importing the table "SAMP_ENTRIES" to our RPD file, then creating a "FilterVals" table to use as a filter to our final report.
We can automate the process of editin the "SAMP_ENTRIES" table by creating and executing our Entries.sql file when ever our data changes in real time.