Report for associated line items

[EvaUR]

Hi reader,

I'm trying to create a specifc report with HubSpot. We use our HubSpot to track all online and offline orders made with our retails. Which means that our Deals have line-items that associate with a product in our collection, like a refrigerator or a washing machine.

I'm trying to build a report that will give me an insight in which products are often ordered together. In this case I want to select all Deals that contain line item 'washing machine' and I would like to see an overview of all the line items in those deals. 

I know I can build custom reports with line items but I have not been able to gain the insight I need. When I filter on line items with washing machines I then only see those line items, and not the other line items in that same deal.

I hope someone can point me in the right direction

[danmoyle]

Hi @EvaUR - and thanks for the tag @kvlschaefer. 

I think of a custom report, pivot table in this scenario. I'd start with Deals as primary source, and line items as the additional data source.

I don't think I'd add the filter for "Product: Name" contains any of "washing machine." I think that would only show you those products. What I'd try to do is see if the pivot table could show me washing machine in the column headers, and what other product names are in the Deals under the rows. 

Since I don't use our HubSpot in this way, I can't show a good screenshot, but that's how I'm thinking of it nd how I'd "play" with the reporting. Hope that helps inspire and answer. 

[EvaUR]

Hey @danmoyle @kvlschaefer,

Thank you for responding! I've been trying to use the pivot table to make the report I need but I'm finding it really hard to gain the insights I needed.

Our deals don't really reference the products that have been ordered, that's what the line items are for. Besides that we have over 25.000 active products in our inventory so the report very quickly doesn't show any date because the table is too large to display. Filtering seems to be neccesary.

@danmoyle is right though in saying that when I use a filter for washing machine I only see those line items, and not the other line items in the deal. I've included some screenshots of what I'm seeing with this report, and also a screenshot of the report I originally tried to make.

Hopefully you'll have some more ideas on how to get the insights we need 🙂 Thanks again!

[danmoyle · Accepted solution]

@EvaUR I got this idea from my colleague Molly. 

So first create a list to filter the deals that have the line item(s) in question: 

Then use that list as the filter on your report (That way you are pulling in all deals, not just the line items):

Hopefully that helps with the filtering! 

[EvaUR]

This was a fantastic help! It doesn't give me all the insights I need, but it's a great starting point. Thank you for your time and assistance!

[danmoyle]

Hmmm... I'm going to check in with some colleagues who play in reporting and see if I can find some additional ideas, @EvaUR. Thanks for the additional context! 

[kvlschaefer]

Hi @EvaUR, 

Thanks for reaching out to the Community!  

Can you please share a screenshot of your report setup including the filters and a deal example you would like to include in the report? Having some visuals would be helpful for us to offer advice. 

I also wanted to invite our subject matter experts to see if they have any suggestions.

Hi @Shadab_Khan, @danmoyle, @Jnix284  do you have any thoughts on this? 

Thank you! 

Best,

Kristen