Skip to main content

Create a report from saved data queries

Zuora

Create a report from saved data queries

  1. Last updated
  2. Save as PDF

This article describes how to create and customize reports based on saved data queries in Zuora Reporting, including common use cases and limitations.

Zuora Data Query allows you to extract the data you need from your tenant, not only data from a single data source or object, but you can also get data to fit complex use cases, such as joined data across multiple objects, filtered data with combined filter conditions, or customized data from custom objects. When creating reports based on saved data queries, Zuora utilizes query results as the data source for reporting. Reports from saved data queries can be more flexible compared with reports from data sources.

For more information, see Data Query.

Prerequisites

Ensure you have data queries saved in your tenant. For more information about how to save data queries, see Save data queries.

Procedure

To create a report from a saved data query, complete the following steps:

  1. Select a saved data query for the report.
  2. Build and preview the report.
  3. Run and view the report.

Alternatively, you can create a report from data sources or use standard reports as a starting point. For more information, see Create a summary report and Standard reports. 

Select a saved data query for the report

To select a saved data query as the data source of a report, complete the following steps:

  1. In the left navigation menu, select Reporting > Reporting.
  2. Click the dropdown button dropdown button.png to the right of + Create New Report, and then click Create report from saved query.
    The “Saved data queries” page opens, presenting all saved data queries in your tenant.
  3. Click Select in the Action column to select a saved data query.
    Make sure the query results of this saved data query contains all the data you want to present in the report.

Zuora creates a copy of the saved data query after the report is created. This copy will not be affected by the changes to the original query, such as updates or deletions.

Note that you cannot edit the copied query once selected.

Build and preview the report

The steps for building and previewing reports from saved data queries are the same as reports based on data sources. For more information, see Build and preview the report.

Run and view the report

The steps for running and viewing reports from saved data queries are the same as reports based on data sources. For more information, see Run and view the report.

Note that you cannot run a saved report if its underlying saved data query does not adhere to the limits enforced by Zuora Data Query. For more information, see Notes and Limitations for Data Query.

Sample queries for common Reporting use cases

Here are the sample queries for some common use cases when creating reports from saved data queries.

Query data from custom objects

You can query data from custom objects you created in your tenant.

For example, the following query returns the Brand and Model values from the Vehicle custom object:

SELECT brand__c, model__c
FROM default__vehicle
WHERE year__c >= 2021

For more information, see Custom Objects.

Query data by unioning multiple business objects together

You can query data on the same custom fields across multiple Zuora business objects by using the UNION operator.

For example, the following query returns the AdditionalMessage custom field value from the InvoiceItem, CreditMemoItem and DebitMemoItem objects:

SELECT 'Invoice Item' as Type, AdditionalMessage__c
FROM invoiceitem
WHERE updateddate >= date('2023-05-01')
UNION ALL
SELECT 'Credit Memo Item' as Type, AdditionalMessage__c
FROM creditmemoitem
WHERE updateddate >= date('2023-05-01')
UNION ALL
SELECT 'Debit Memo Item' as Type, AdditionalMessage__c
FROM debitmemoitem
WHERE updateddate >= date('2023-05-01')

Query data with ad hoc foreign currency conversion

You can query data with foreign currency conversion information by using the fx_convert function.

For example, the following query returns the transaction amount (Invoice.Amount), which is converted from the transaction currency (Account.Currency) to your home currency (USD):

SELECT invoice.amount, fx_convert(invoice.amount, account.currency, 'USD', least(invoice.posteddate, invoice.invoicedate))
FROM invoice
INNER JOIN account ON invoice.accountid = account.id
WHERE invoice.updateddate >= date('2023-05-01')

For more information about foreign currency conversion, see Foreign currency conversion for Data Query.

Query data from Zuora Audit Trail

You can query the audit trail data in your tenant, including login history, setting changes, and business object changes.

For example, the following query returns the login history from May 2023 to June 2023:

SELECT *
FROM auditloginevent
WHERE year = 2023 AND month >= 5 AND month <= 6

For more information, see Audit Trail.

Query data requiring advanced calculations

You can utilize advanced SQL queries to efficiently retrieve data that aligns with your reporting requirements, including cohort analysis.

For example, the following query returns the start month, which is a commonly used metric in cohort analysis, for each account:

SELECT a.id,
       a.accountnumber,
       a.name,
       concat(cast(year(min(rpc.effectivestartdate)) as varchar),'-',lpad(cast(month(min(rpc.effectivestartdate)) as varchar),2,'0')) as account_start_month
FROM rateplancharge rpc
INNER JOIN rateplan rp ON rpc.rateplanid = rp.id
INNER JOIN subscription s ON rp.subscriptionid = s.id
INNER JOIN account a ON s.accountid = a.id
WHERE s.status in ('Active','Cancelled')
GROUP BY 1,2,3

Notes and limitations

  • The query results of saved queries cannot contain columns with the same name. You can use aliases to avoid this problem, as shown in the following sample queries:
    Supported Not supported 
    SELECT account.name AS AccountName, subscription.name AS SubscriptionName
FROM account, subscription
WHERE account.id = subscription.accountId

    SELECT account.name, subscription.name
FROM account, subscription
WHERE account.id = subscription.accountId
  • Aliases in saved data queries for Reporting cannot contain spaces or special characters.
    Supported Not supported 
    SELECT account.name AS AccountName, subscription.name AS SubscriptionName
FROM account, subscription
WHERE account.id = subscription.accountId

    SELECT account.name AS "Account Name", subscription.name AS "Subscription-Name"
FROM account, subscription
WHERE account.id = subscription.accountId
  • All limitations for reports from data sources apply. For more information, see Notes, Limits, and Limitations.
  • All limitations for Data Query apply. For more information, see Notes and Limitations.
  • The saved data query cannot contain data from the User object because this object is not supported by Reporting.