|
Oracle CRM On Demand Report Services API Developer's Guide > Using the ReportExecute Method >
Using Filters in the ReportExecute Method
You can apply filters to the report generation using the optional Action parameter. The Action parameter supports a single action, which is Filter. You can apply up to six filters to a report. The format of a filter is: &Action=Filter&P0=1&P1=<op>&P2=<FilterColumn1Name>&P3=<FilterColumn1Value>
The components of the Filter action are:
- &Action=Filter. Specifies the optional Action parameter is being used and the selected action is Filter.
- &P0=1. Specifies how many filters to apply. The maximum number of filters is six. Each filter uses three parameters: operator, column, and value. The first filter parameters are specified in P1-P3, subsequent filters are specified in sets, namely P4-P6, P7-P9, P10-12, P13-P15, and P16-P18.
For example, to filter a report where SalesRegion = 7 and SalesForecast is greater than 10,000, the filters would look like this:
&Action=Filter&P0=2&P1=eq&P2=Sales.Region&P3=7&P4=gt&P5=Sales.Forecast&P6=10000
- &P1=<op>. Specifies the operator used in the filter, such as eq (equals), lt (less than), or bwith (begins with). For a complete listing of all operators, see Table 5.
- &P2=<FilterColumn1Name>. Specifies the column to be filtered. The column must exist in the report. To specify a table and a column, separate the table and columns names with a period (.) as follows: ttt.ccc.
If the table name or column name contains special characters (such as spaces), use hexadecimal encoding (for example %20 in place of a space) and enclose the table name or column name with double quotes (%22). For example to specify the Dollar Sales column in the Measures table:
Measures.%22Dollar%20Sales%22
NOTE: Use original column names and not display names. Also, make sure that the columns you specify in <FilterColumnName> are set up in the report as Is Prompted filters or as specific default filters.
- &P3=<FilterColumn1Value>. Specifies the value to use in the filter. If the operator requires multiple values, indicate the number of values and separate the values using a plus sign (+). For example, the bet (Between) operator needs two values. To filter on SalesRegion between 3 and 7, use:
&P1=bet&P2=Sales.Region&P3=2+3+7
If any of the values are special characters then they need to be enclosed in double quotes (%22), for example, if you are searching for the cities of Boston, Los Angeles, and Sacramento, the space in Los Angeles must be URL encoded as %20 and the term Los Angeles must be surrounded with %22:
&Action=Filter&P0=1&P1=cany&P2=City&P3=3+Boston+%22Los%20Angeles%22+Sacramento
Table 5 contains the list of operators you can use in filters.
Table 5. Filter Operators
|
|
eq |
Equal to or in |
neq |
Not equal to or not in |
lt |
Less than |
gt |
Greater than |
ge |
Greater than or equal to |
le |
Less than or equal to. |
bwith |
Begins with |
ewith |
Ends with |
cany |
Contains any. The value parameter can have multiple values, each separated with a plus sign (+). For example, to find Sales Regions 1,3, and 5:
&P1=cany&P2=Sales.Region&P3=3+1+3+5 |
call |
Contains all. The value parameter can have multiple values, each separated with a plus sign (+). |
like |
like. The value parameter must contain a value and end with the wildcard %25. For example, to find account names that are like Acme:
&P1=like&P2=Account.Name&P3=Acme%25 |
top |
top n items. For example, to find the top 6 sales revenues:
&P1=top&P2=Sales.Revenue&P3=6 |
bottom |
bottom n items. For example, to find the bottom 4 sales revenues:
&P1=bottom&P2=Sales.Revenue&P3=4 |
bet |
Between. The value parameter must have two values, separated by plus signs (+). For example, to filter on Sales Region between 3 and 7, use:
&P1=bet&P2=Sales.Region&P3=2+3+7 |
null |
Is null (the value parameter must be 0) |
nnull |
Is not null (the value parameter must be 0) |
|
