Happy Business Starts Here

Community Manager

How to get ALL the products per active subscription

See Reply



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn

4 REPLIES 4
Community Manager

Re: How to get ALL the products per active subscription

Scenario:
We need a report that lists the products each active subscription has.

 

Solution:
We'll need to create the following Data Source Export.

 

- Navigate to Data Exports in the left-side menu
- Select the Rate Plan data source from the top picklist
- Select the following fields (or more if you'd like to see more, I was trying to grab the essentials only): 1., RatePlan.Name 2., Product.Name 3., Subscription.Name, Subscription.Status, Subscription.Version
- Select the filter Subscription.Status = Active

 

I have created the above Data Source Export and here's a partial result for a multi-product subscription:

 

results.JPG

 

 

If you'd like to use a SOAP call for the above instead of the UI, here's the query for the same:

<ns1:create>
<ns1:zObjects xsi:type="ns2:Export">
<ns2:Format>csv</ns2:Format>
<ns2:Name>export</ns2:Name>
<ns2:Query>Select RatePlan.Name, Product.Name, Subscription.Name, Subscription.Status, Subscription.Version from RatePlan where Subscription.Status='Active'</ns2:Query>
</ns1:zObjects>
</ns1:create>



Subscribe to Zuora System Updates at Zuora Trust
Follow Zuora Global Support on Twitter and LinkedIn

Zuora Staff

Re: How to get ALL the products per active subscription

A customer recently asked for a variation of this report, specifically all the rate plans and charges belonging to subscriptions active at any time in a past time period - this was an audit request, the auditors were asking for all the active subscriptions in 2016 but the request was made in Q2 2017!

 

Again the rate plan charge data source comes to the rescue, the trick with this report was to include subscriptions that were active at any time during 2016. And so you want every subscription that has a status of active or cancelled and which has a subscription start date before Jan 1 2017 (meaning the subscription started in 2016 or earlier) and also has a subscription end date after Jan 1 2016 (meaning the subscription was still going at the start of 2016 - a little known Zuora quirk is that the 'end date' is usually the day AFTER the actual end date, it's the day of the start of the next renewal, for example is I start a 12 month subscription on Jan 1 2015 and look at the subscription end date, Zuora will report that as being Jan 1 2016 and not Dec 31 2015 like you'd expect, so subscriptions that end on Jan 1 2016 ACTUALLY ended the day before, Dec 31 2016 - obviously someone thought this was a good idea, me, I'm not sure, anyway....).

 

There's another twist if you have evergreen subscriptions, you need to also look for subscriptions who's subscription end date is blank, so the filter will have to be the subscription end date is after Jan 1 2016 OR nothing. When you cancel an evergreen subscription it'll finally pick up a subscription end date, that'll be the day of the cancellation but if it's still live the end date will be empty (null).

 

Using the rate plan charge data source in the reporting tool you'll want something like this:

 

Active subscriptions in 2016

 

Consider making the date filters in this report interactive (check the 'Ask User Later' boxes) and you'll be able to run the report for any period, for example, 'last month'. 

Newly Enrolled

Re: How to get ALL the products per active subscription

I created the export, but also included the Account details so I could limit results by account. However, that returned duplicate productIDs for a single active SubscriptionID.   Is there a way to limit the list of products to only show what list of products an account will get on their next shipment? 

Zuora Staff

Re: How to get ALL the products per active subscription

Can you share the specifics of your export query?

 

You should only get duplicate product ids if the same product occurs on the same subscription multiple times. You should also be able to report on what products active on any subscripton at any time in the future but in your use case when is the next shipment? Where is that captured or is it the same date for all subscriptions?