Reports are shown as views, which are tables that cannot be modified. There are four types of report views:

• Custom reports defined in your Adobe Analytics instance.
• The UniversalReport view, which includes every available dimension and metric. Use it to create dynamic custom Adobe Analytics reports by selecting a subgroup of metrics and dimension columns.
• Custom static reports generated by the CreateReport Stored Procedure.
• Predefined report views, which are standard reports that replicate the output of Adobe Analytics report templates. The available predefined report views include: KeyMetrics, LastTouchChannel, LastTouchChannelDetail, Orders, PageOccurrences, Pages, PageViews, Products, Revenue, SiteSections, TrackingCode, Units, Visitors, Visits.

Unlike traditional database views, however, it is not very helpful to select all metrics and dimensions in a given table. Date is the default dimension for every report, so the query:

SELECT * FROM MyReport

becomes:

SELECT Date, {all the metrics here} FROM MyReport

But if the query has criteria, then the default dimensions are the dimensions used in the criteria. For example:

SELECT * FROM MyReport WHERE Country = 'England'

becomes:

SELECT Country, {all the metrics here} FROM MyReport WHERE Country = 'England'

Additionally, the Adobe Analytics API limits the number of dimensions you can request data from per REST API call to one. This means that the only way for the provider to generate reports with multiple dimensions is to divide dimensions into parts.

Let's take a query example:

SELECT Country, City, Visits FROM MyReport

• First, the provider requests all the values of the first dimension, Country. In this example they are England and Germany.
  
• Second, the provider tries to divide England into parts using the dimension City, and it gets London and Liverpool.
  
• Finally, it divides Germany into parts using City and gets: Berlin and Frankfurt.
  

If we have a third dimension, the provider divides every city into parts with the third dimension (which makes one request per city).

Now, imagine a four dimensional report where dimensions have many values. Typically, the generation of that report would require a lot of API calls. However, the Adobe Analytics API allows us to provide some filters that can significantly shorten the execution time of the query.

The Adobe Analytics API uses the following logical operators: 'AND', 'OR', and 'NOT', and also uses the 'MATCH', 'CONTAINS', 'BEGINS-WITH' and 'ENDS-WITH' operators, which means that criteria such as the ones below are handled server-side:

... WHERE Dimension = 'Value'
... WHERE Dimension LIKE '%value%'
... WHERE Dimension1 LIKE '%value' AND Dimension2 = 'Value2'
... WHERE Dimension LIKE 'value%'
... WHERE Dimension1 = 'Value1' AND Dimension2 = 'Value2'
... WHERE Dimension = 'Value1' OR Dimension = 'Value2' OR Dimension = 'Value3'
... WHERE Dimension IN ('Value1', 'Value2', 'Value3', 'Value4')
... WHERE (Dimension1 = 'value1' OR Dimension1 = 'Value2') AND (Dimension2 = 'value3' OR Dimension2 = 'value4')

CData strongly recommended that you use as many filters as possible; otherwise queries with many dimensions can take a lot of time.

Defining Custom Reports

To create a report view on a query, you can use the CreateReport stored procedure. This stored procedure provides an easy way to generate new view definitions with a custom combination of dimensions and metrics. Calling it creates a new schema file that you can query like any other view.

The stored procedure takes a view name, a comma-separated list of metric names, a comma-separated list of metric Ids, a comma-separated list of dimension names, a comma-separated list of dimension Ids, and an output folder as inputs.

Set the Location connection property to the folder containing the new script files in order to access them (you can set Location instead of the output folder).

Get the metric and dimension Ids by querying the Metrics and Dimensions views, as shown below:

SELECT Id, Name FROM Dimensions
SELECT Id, Name FROM Metrics

For example, to use a new schema along with the default schemas, set the Location property to the db subfolder in the installation folder and make the following call:

EXEC CreateReport Dimensions = 'Country, City', Metrics = 'PageViews, Visits', TableName = 'MyCustomReport'