Connect Oracle Smart View#

The Oracle Smart View connector connects Reportworq to Oracle EPM Cloud (Oracle Fusion Cloud EPM Planning). It reads a Smart View grid that you author in a source workbook, re-queries Oracle EPM Cloud for current numbers, and writes the refreshed values back into the grid. The refreshed report then flows through the normal Reportworq distribution channels, the same as any other data source.

This page covers the whole path for a first-time user: connect to your EPM Cloud instance, author a workbook Reportworq can refresh, use the Oracle Smart View Dimension parameter, and understand the limits.

What "Smart View" means in Reportworq#

Oracle Smart View for Office is Oracle's Excel add-in for building ad hoc grids against EPM Cloud. When you refresh a grid in that add-in, it stores the grid's definition inside the workbook as hidden worksheet properties: the point of view (POV), the dimensions on rows and columns, the cube, and the connection URL.

Reportworq's Oracle Smart View connector reads that stored grid definition. At run time it does not open the Smart View add-in and does not need it installed on the Reportworq server. Instead, it parses the grid definition out of the workbook, sends the equivalent query to the Oracle EPM Cloud REST API, and distributes the refreshed values into the grid's cells. In short: you use Smart View once, in Excel, to lay out the grid; Reportworq then refreshes that grid on a schedule without Excel or the add-in.

Because the connector works on the Excel worksheet plane this way, it is a report-refresh source, not a relational database. It has no SQL or table query surface. See Notes and limits.

Before you begin#

• You are an administrator, working in Settings > Integrations > DATASOURCES. See Connect a data source for the general add-and-test flow.
• You have the service URL of your Oracle EPM Cloud environment (the pod host, for example myenv-myident.epm.us-phoenix-1.ocs.oraclecloud.com). Use the host as it appears in the browser address bar when you are signed in to EPM Cloud, without the https:// prefix.
• You have a username and password for an Oracle EPM Cloud account that can read the application and cubes you want to report on. Oracle usually expects the username to begin with your identity domain (your Oracle tenant name), followed by a dot and the user name, like acme.jane.doe rather than just jane.doe. The exact form can vary between Oracle environments, so if you are not sure, your Oracle administrator can give you the correct username.
• The account has at least read access to the target application and its plan types (cubes). No write access is needed; the connector reads data only.
• Best practice: connect with a dedicated Reportworq service account, not an individual person's login. A shared service account keeps the connection working when people change roles or leave, and scopes access to what Reportworq needs.

Connect to Oracle EPM Cloud#

1. Go to Settings > Integrations and select Add integration.
2. Find Oracle Smart View in the catalog and select Add. This creates the integration and opens its editor.
3. Select Enable datasource connection. A disabled datasource cannot be selected or refreshed.
4. In Datasource Name, enter a clear, unique name. Models, parameters, and reports refer to the datasource by this name, so choose it carefully. See the warning on renaming in Connect a data source.
5. In Host, enter your EPM Cloud service host. Enter the bare hostname only. Reportworq adds https:// for you, so do not include a scheme, a path, or a trailing slash. Entering https:// here produces an invalid address and the connection fails.
6. In Username, enter the account's username. This usually begins with your identity domain (tenant), like acme.jane.doe. If you are not sure of the exact form, see Before you begin.
7. In Password, enter the account password.
8. Select Save. Save stays disabled until you have changed something.

How Reportworq authenticates#

The connector authenticates to the Oracle EPM Cloud REST API with HTTP Basic authentication: the username and password you enter, sent on each request over HTTPS. There is no OAuth, SSO, token, or API-key option. The credentials are stored encrypted in the Reportworq content store. If your EPM Cloud environment requires federated sign-in or blocks Basic authentication, provision an EPM Cloud account that can authenticate with a username and password for the REST API. See Notes and limits.

Test the connection#

Select Test connection. Reportworq contacts the EPM Cloud REST API with the credentials you entered.

• A reachable, correctly configured environment returns "Success."
• If it fails, re-check the host (bare hostname, no https://), the username format and password, and that the account can sign in to EPM Cloud. The connector retries a few times before reporting a failure, so a test against an unreachable host can take a few seconds.

You can also validate every configured integration at once with Test all connections in the Integrations header.

Author a workbook Reportworq can refresh#

Reportworq refreshes a Smart View grid, so the source workbook must contain one. Author it once in Excel with the Oracle Smart View add-in, then hand it to Reportworq as a report source.

1. In Excel, open Oracle Smart View for Office and connect to the same EPM Cloud application and plan type (cube) you connected Reportworq to.
2. Build an ad hoc grid: place your dimensions on rows, columns, and the POV, and choose the members you want. Every dimension in the cube must be represented somewhere on the grid: on rows, on columns, or in the POV. A dimension that is left off cannot be resolved and the refresh reports it as missing.
3. Refresh the grid in Smart View so that Oracle writes the current numbers and the grid definition into the worksheet.
4. Save the workbook. The saved workbook now carries the embedded grid definition Reportworq reads.
5. Add the workbook to Reportworq as a report source and use it in a job the same way as any other source report. See the report-source and job guides for the general flow.

When the job runs, Reportworq, for each worksheet that carries a Smart View grid:

• reads the grid's POV, row members, column members, and cube from the embedded definition,
• validates every member against the cube's metadata,
• queries the EPM Cloud REST API for the values at each row-and-column intersection,
• clears the old values and writes the current ones into the grid cells, and
• removes the hidden Smart View properties from the delivered copy, so the distributed workbook is a clean values workbook.

Cells that contain a formula are left untouched, so calculations you build around the grid are preserved.

Use the Oracle Smart View Dimension parameter#

The Oracle Smart View Dimension parameter lets a job override which member of a dimension the grid is refreshed for, without editing the workbook. It is the basis for multi-copy and burst runs, for example refreshing the same package once per entity or once per cost center and delivering a copy for each.

To add one:

1. In the job or parameter set, add a parameter of type Oracle Smart View Dimension.
2. In the Dimension picker, expand your application, then its cube, then select the dimension you want to drive.
3. In Items, select the members to run for. Selecting several members is what produces several output copies, one per member.
4. Select Test... to confirm the selection resolves against EPM Cloud.

At run time the parameter overrides the matching dimension's member in the grid's POV before Reportworq queries EPM Cloud. When the parameter is used to drive a multi-copy or burst job, Reportworq generates one refreshed report per selected member, and each copy is refreshed for that member.

Distribute the result#

The Oracle Smart View connector only refreshes data; it does not deliver anything. A refreshed workbook is an ordinary Reportworq report output, so it flows through all the normal distribution channels: email, network folders, SharePoint and OneDrive, and the other configured distributors, with bursting, scheduling, formats, and audit exactly as for any other source. Using Oracle EPM Cloud as the source changes where the numbers come from, not how the report is delivered. See the distribution and automation guides for those steps.

Notes and limits#

• Basic authentication only. The connector supports a username and password over HTTPS. There is no OAuth, SSO, token, or API-key option. An environment that blocks Basic authentication for the REST API cannot be reached until an account that allows it is provisioned.
• Not a relational or data-model source. Oracle Smart View refreshes a Smart View grid; it has no SQL or table query surface, and you cannot write SQL against it or build a Data Model on top of it. To reach a relational Oracle database, use the ODBC, OLE DB, or a CData connector instead. See the Connector catalog.
• A workbook needs an embedded Smart View grid. Reportworq refreshes a worksheet only if it carries the grid definition that the Smart View add-in writes on refresh. A worksheet without it is skipped. Author and refresh the grid in Smart View, then save, before handing the workbook to Reportworq.
• Every dimension must be represented. If the grid's rows, columns, and POV together do not cover every dimension in the cube, the refresh reports the missing dimensions and does not complete for that worksheet.
• Members must exist in the cube. A row, column, or POV member that is not valid for the selected cube is reported as an error for that worksheet.
• Host is a bare hostname. Enter the EPM Cloud host without https://, without a path, and without a trailing slash. Reportworq builds the HTTPS URL from it.

Troubleshooting#

• Test connection fails. Re-check the host is the bare EPM Cloud hostname (no https://), and that the username format and password are correct for that environment. Confirm the account can sign in to EPM Cloud directly, and that the REST API is reachable from the Reportworq server.
• The refresh reports missing dimensions. The grid does not place every dimension in the cube. Open the workbook in Smart View, add the missing dimension to the rows, columns, or POV, refresh, and save.
• A member is reported as not available in the cube. A row, column, or POV member (or a member chosen in an Oracle Smart View Dimension parameter) is not valid for the selected cube. Correct the grid or the parameter selection to use members that exist in that cube.
• Cells stay blank after a run. Confirm the worksheet still carries a Smart View grid (it must be authored and refreshed in Smart View, then saved), and that the account has read access to the application and cube. Cells that hold a formula are intentionally left untouched.

Going deeper. For the general add-and-test flow and how to manage a live datasource safely, see Connect a data source. For the full list of connectors and their traps, see the Connector catalog.