1. Oracle CRM On Demand Report Services API Developer’s Guide
  2. What’s New in This Release

3Using the ReportExecute Method

Using the ReportExecute Method

This chapter describes how to access the ReportExecute method. It includes the following topics:

Overview of the ReportExecute Method

The ReportExecute method accesses existing reports within Oracle CRM On Demand and creates portable files from them. A portable file is a file, such as a PDF file that can be shared and displayed outside of Oracle CRM On Demand.

To access existing reports, you need a scripting language, such as JavaScript or Perl, or a command line download tool such as Wget or cURL. The examples in this guide use the Wget tool, but the concepts are the same for all scripting languages and download tools.

The download tool calls the ReportExecute method and specifies the report (path and name), optional report filters, and the delivery format for the report. The Oracle CRM On Demand Report Services API can deliver reports in any of the following formats: PDF, XLSX, TXT, CSV, and MHTML. The download tool then transfers the file generated by the ReportExecute method and stores it in the same location from where the report execution service call is made or in a location specified with the output file.

The ReportExecute method uses the data visibility controls in Oracle CRM On Demand. The user account you use to sign on to Oracle CRM On Demand through the ReportExecute method by way of the download tool, determines the data you have visibility into. If the reports you are downloading are to be shared with other users, then you must consider what data they are permitted to see and make sure that you do not access the ReportExecute method with an account that has access to restricted records.

Sequence of Operations for Using the ReportExecute Method

The ReportExecute method is not a stand-alone command. First, you must make sure you have a download tool as described in About the Download Tool. Then use the following sequence:

  1. Sign on to Oracle CRM On Demand and create a cookie file (see Creating a Session Cookie File).

  2. Call the ReportExecute method (see Calling the ReportExecute Method).

  3. Close the Oracle CRM On Demand session (see Signing Off from Oracle CRM On Demand).

Calling the ReportExecute Method

When you have the Web session ID stored in a cookie file, you can then use the download tool to make the call to the ReportExecute method and get the report in the format you specify. When using Wget, the format for the call is as follows: 

wget --content-on-error --load-cookie "cookiefile1.txt" --output-document "pathname.pdf" 
"https://secure-servername/OnDemand/user/ReportService?Method=ReportExecute&Catalog=V3&
Path=ReportPathandName&Format=pdf&Action=Filter&P0=1&P1=op&P2=FilterColumn1Name&
P3=FilterColumn1Value&Refresh=Y" --max-redirect=100

Repeat the call to the method for each report you want to generate. Substitute your specific information for the parameters shown in the following table.

Table Wget Command Parameters for Calling the ReportExecute Method

Parameter Value in Example Description

--load-cookie

"cookiefile1.txt"

The name of the file into which Wget stored the Web session ID. The quotation marks around the file name are mandatory in Wget.

--output-document

"pathname.pdf"

The path and file name of the report file to be stored. Make sure that the file extension used matches the format specified in the Format parameter. For example, if you are generating a PDF file, then make sure the file name ends with .pdf. The quotation marks around the path name are mandatory in Wget.

servername

servername

The name or address of the server hosting Oracle CRM On Demand.

Use one set of double quotes to enclose the portion of the URL containing the following parameters:

  • servername

  • Method

  • Catalog (optional)

  • Path

  • Format

  • Action (optional)

  • Refresh

Method

ReportExecute

This is the call to the ReportService API. The method name is ReportExecute.

Catalog

V3

Optional, not case-sensitive. Specifies which catalog to use. The acceptable values are:

  • V3. If V3 is specified, then the ReportExecute method executes a report available in the Company Wide Shared Folder of the V3 catalog. This is the default value.

    To execute a report in the Company Wide Shared Folder of the V3 catalog, the following privilege is required: Access V3 Analytics.

  • V3m. If V3m is specified, then the ReportExecute method executes a report available in the Migrated Company Wide Shared Folder of the V3 catalog.

    To execute a report in the Migrated Company Wide Shared Folder of the V3 catalog, the following privilege is required: Access Migrated Company Wide Shared Folder.

Path

ReportPathandName

The folder path to the report in the company shared folder, including the report name. For example:

  • Region1Sales is a report in the shared folder named Region1Sales.

  • Sales/Opportunities is a report named Opportunities in the Sales folder within the company shared folder.

ReportPathandName must be URL Encoded.

Format

pdf

The report can be delivered in any of the following formats: PDF, XLSX , TXT, CSV, and MHTML. This value is not case-sensitive.

Action

&Action=Filter&P0=1 &P1=op&P2=Filter Column1Name&P3=Fil terColumn1Value

Optional. This parameter supports application of column filters during the report generation. The values shown in the sample are specific to the Filter action. For more information on using filters in reports, see Using Filters in the ReportExecute Method.

Refresh

Y

Must be N (for No) or Y (for Yes).

If N, (recommended) then the ReportExecute method reuses the results of previously executed reports, which provides the fastest query performance for Analytical (Historical) reports.

If Y, then the ReportExecute method refreshes the report values, which provides the most current data.

--max-redirect

100

This is the number of attempts or redirects before the request is cancelled by Wget. The default value is 20, a value of 100 is sufficiently large enough for most reports.

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 Filter Operators.

  • &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

    Filter Operators

    The following table contains the list of operators you can use in filters.

    Table Filter Operators

    Operator Description

    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).

      Guidelines for Using the ReportExecute Method Efficiently

      The following suggestions can help you use the ReportExecute method more efficiently:

      • Create a folder for storing the download tool, the session cookie files, and the downloaded reports, which means you do not have to specify path names.

      • In the morning, execute all the Analytical (Historical) reports with the Refresh = Y parameter to calculate all the values for those reports. All remaining calls to those reports can use the Refresh = N parameter for faster processing.

      • Create a text file that contains copies of the common commands you use on a regular basis. You can then copy the commands from the text file and paste them on to the command line.

      • In the text file, create one section for all your sign on and session cookie commands and one section for the API calls.

      • Put a text title before each command so you can quickly identify the commands.

      The following is a sample text file of commands:

      ReportExecute method Commands


=== Sign On/Session Cookie Commands ===


Full Visibility Sign On:

wget  --content-on-error  --keep-session-cookies --save-cookies "cookiefile1.txt" --post-data 
"j_username=repfullvis&j_password=pass1&langCode=ENU" https://secure-servername/OnDemand/authenticate


Supervisor Sign On:

wget  --content-on-error  --keep-session-cookies --save-cookies "cookiefile1.txt" --post-data 
"j_username=repsuper&j_password=pass2&langCode=ENU" https://secure-servername/OnDemand/authenticate


Sales Rep Sign On:

wget  --content-on-error  --keep-session-cookies --save-cookies "cookiefile1.txt" --post-data 
"j_username=repsales&j_password=pass3&langCode=ENU" https://secure-servername/OnDemand/authenticate


=== Report Calls ===


Sales Summary, PDF format:

wget --content-on-error --load-cookie "cookiefile1.txt" --output-document "SalesSummary.pdf" 
"https://secure-servername/OnDemand/user/ReportService?Method=ReportExecute&Catalog=V3&Path=
ReportPathandName&Format=pdf&Refresh=Y" --max-redirect=100


Sales Summary, Text format with two filters:

wget --content-on-error --load-cookie "cookiefile1.txt" --output-document "SalesSummary.txt" 
"https://secure-servername/OnDemand/user/ReportService?Method=ReportExecute&Catalog=V3&
Path=ReportPathandName&Format=txt&Action=Filter&P0=2&P1=eq&P2=Sales.Region&P3=7&P4=gt&P5=
Sales.Forecast&P6=10000&Refresh=Y" --max-redirect=100


Output to an Excel spreadsheet:

wget --content-on-error --load-cookie "cookiefile1.txt" --output-document "SalesSummary.xlsx" 
"https://secure-servername/OnDemand/user/ReportService?Method=ReportExecute&Catalog=V3&
Path=ReportPathandName&Format=xlsx&Action=Filter&P0=2&P1=eq&P2=Sales.Region&P3=7&P4=gt&
P5=Sales.Forecast&P6=10000&Refresh=Y" --max-redirect=100

      Troubleshooting the ReportExecute Method

      The Oracle CRM On Demand Report Services API can encounter problems with the command line and parameters, and also with generating reports. The Oracle CRM On Demand Report Services API responds to error conditions by returning an error message. To resolve a problem, see the following table.

      Table ReportExecution Method Error Messages

      Problem Cause Solution

      Your ReportService request failed. VariableName method is incorrect or not supported. Please check for supported methods and try again.

      You specified an incorrect method name.

      Check for supported methods and try again.

      Your ReportService request failed (Required parameter VariableName missing in method VariableName). Please check your syntax and try again.

      One or more parameters are missing.

      Review the parameters used and revise the command.

      Your ReportService request failed (Incorrect delivery format VariableName provided; supported delivery formats are VariableName). Please check your delivery format and try again.

      You specified an incorrect delivery format.

      Review the delivery format used and specify a supported format.

      Your ReportService request failed (Incorrect Action parameter value provided; supported Action is 'Filter'). Please check your Action parameter value and try again.

      You specified the Action=Filter parameter incorrectly.

      Specify the Action=Filter parameter correctly.

      Your ReportService request failed (Incorrect P0 parameter value provided; supported values only within range 1-6). Please check your P0 parameter value and try again.

      You specified the P0 value (number of filters) outside the range of 1-6.

      Review and specify the P0 parameter correctly within the range of 1-6.

      Your ReportService request failed (Specified P0 parameter value and subsequent parameters not matching). Please provide proper parameters and try again.

      The number of filters specified in the P0 value does not match the subsequent filters provided.

      Make sure that the number of filters provided matches with the P0 parameter value.

      Your ReportService request failed (Operator is not in the specified list of operators). Please provide proper operator and try again.

      You specified an operator that is not in the specified list.

      Use only supported operators as shown in Filter Operators.

      Your ReportService request failed (Table column parameter is not existing or not specified properly). Please provide the table column parameter correctly and try again.

      You specified the table column incorrectly.

      Review and make sure the table column does exist and is specified properly, double-quotes (%22) are provided where needed and encoding is done for spaces (%20).

      Your ReportService request failed (Column value parameter is not specified properly). Please provide the column value or values correctly and try again.

      You specified the column value incorrectly.

      Review and make sure that the number of values match the operator, multiple values are separated by a plus (+) sign, and are enclosed within double quotes (%22) where needed.

      Your ReportService request failed (Incorrect refresh option VariableName provided; supported refresh options are 'Y' or 'N'). Please check your refresh option and try again.

      You specified an incorrect refresh option.

      Review the refresh option used and specify a supported option.

      Your ReportService Report Execution request failed (Internal error). Please ensure that you can run the report in CRM On Demand and try again.

      The ReportService encountered a problem that is not related to a parameter in the table in Calling the ReportExecute Method. Possible causes include lack of permission to the report folder, the report timed out, memory issues, or the report name was misspelled or does not exist.

      Using Oracle CRM On Demand and the same account, try to generate the report. If the report is generated, then check the file name to make sure the correct values are used, and that you have not exceeded service allotments.

      Your ReportService request failed (Catalog not enabled)

      Analytics V2 is disabled for your company instance.

      Contact your administrator.

      Your ReportService request failed (Incorrect Catalog value provided; supported Catalog values are ‘V3’ or ‘V3m’). Please check your Catalog value and try again

      The catalog you specified is not available.

      Review the value that you specified in the Catalog parameter and specify a supported value.

      Access Denied

      The user account that you used to sign in to Oracle CRM On Demand to call the ReportExecute method does not have the appropriate privilege to access the Company Wide Shared Folder or the Migrated Company Wide Shared Folder.

      Contact your administrator to get the appropriate privilege enabled for the user account.

      Error messages are captured in the output file specified for the generated content in the ReportExecute method.