Happy Business Starts Here

Highlighted
Student

help pulling reports for line items

Hi all,

 

Attempting to run a report that lists all customers who actively have a subscription to one charge item

Multiple attempts to massage the data have come up blank. I could really use an assist with this! 

2 REPLIES 2
Zuora Staff

Re: help pulling reports for line items

If you're using the new reporting tool there's a sample report you can use to get what you want, 'MRR by Account', it's in the 'Booking' section under 'Drivers'. That particular account will sum MRR, but you probably want a detail version of this report where you also expand the columns displayed.

 

The report filters in that report aren't intuitive, here are some notes that I hope help explain why the report works. 

 

The key is to use the 'Rate Plan Charge' data source (NOT the Product Rate Plan Charge data source), this will allow you to list the account and subscripton info in addition to stuff from the rate plan and rate plan charge. First you want to filter on subscription status being NEITHER Expired OR Draft. You also want to filter on the rate plan charge effective start date and the rate plan charge effective end date. Basically you want the start date to be equal to or before (less than) 'today' or the day you are interested in, and you want the effective end date to be AFTER (greater than) 'today' or the day you are interested in OR the effective end date is null.  

 

Why include 'Cancelled' subscriptions, if you have a future dated cancellation, you customer called to cancel and they have to wait to their next billing dy for that to take effect, in Zuora you've created a future dated canccellation amendment. In turn this means the subscripton status is now cancelled, but they're still active, the cancellation has yet to kick in, hence they're (correctly) included with these filters. But what about genuinely cancelled subscriptions? They get purged by the effective date filters. If a customer cancelled last year, the effective end date of all the charges in that subscription will be in the past. So this set of filters will capture future dated cancellations yet ignore cancelled subscriptions (and charges).

 

The reason for the effective end date filters only being 'greater than', and not 'greater than or equal to', is that Zuora records the effective date as the day AFTER the charge ends (cute Zuora quirk). So if you subscription term end is 12/31, the effective end date of every charge will be recorded as 1/1, and so 'greater than' is the appropriate filter. The reason for the effective end is null filter is that if you have active evergreen subscriptions, they have no effective end date!

 

So putting all this together in the right combination:

 

  (Subscription.Status not in 'Expired' OR 'Draft')

AND

 (EffectiveStartDate <= 'today')

AND

 (

    (EffectiveEndDate > 'today' OR EffectiveEndDate is null)

  )

 

If you don't have the new reporting tool, you can still get the info you want but you'll need two reports, one for termed subscriptions and one for evergreen. The old tool can't do logical ORs so one report will have the 'Effective End Date > today' and the other will have Effective End Date is null.

 

As I mentioned earlier, this isn't obvious and while I hope I've explained what's going on, I'm happy to explain further, just ask. 

Student

Re: help pulling reports for line items

I'm getting a lot of extra info and want to drill down to one line item (product: description)