Report Wizard - Joining 2 tables

(0) Share

Report

Posted on by rockycj

150

I need to join 2 tables, the Lead table and the Opportunity table for a Report. The data in the report will also show both the Lead data and the Opportunity data. Is this possible in the Report Wizard?

My Primary record type is "Leads" and the Related record type is "Opportunities (Originating Lead).

I am new to CRM reporting, so I'm looking for professional advice on how to do this within CRM.

Normally I would need to do a UNION statement in SQL and create a view and then point the report to the View. Not sure how to do this in the Report Wizard for CRM.

Thank you for your time!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Mamatha Swamy 5,426 on at

Like (0)

Report

Yes, using report wizard you can select fields (columns) from both leads and opportunities. For the query itself, CRM performs a JOIN (lead.leadid=opportunity.originatingleadid) not a UNION.

When you are adding report columns using the wizard, you will be able to select 'record type' as lead or opportunity and then add fields from those entities.

Was this reply helpful? Yes No
Suggested answer

Aileen Gusni 44,524 on at

Like (0)

Report

Hi Rocky,

In Report Wizard you can query to 2 entities that are related entities, but if you choose Lead and Opportunity, it will be shown in 1 Table format, cannot have 2 tables format and it is not Union, it is Join which the Primary Record type is the primary entity.

Want to know what is your report objective, Union or Join.

Was this reply helpful? Yes No
rockycj 150 on at

Like (0)

Report

I understand it's a join per Lead and Opportunity. But I need these fields in One report:

From Leads: Market, Topic, Owner, Company Name, and Est. Contract Value.

From Opportunity: Market, Project Title, Bus. Sev/Sales Rep, Potential Customer, Est Contract Value.

Which are basically the same fields in both Leads and Opportunity page. The reason why I need this is because we created an

Option in the Status Reason in Opportunity page called "Demoted to Leads". So I need to do a report for ALL LEADS.

My primary report would be from the Leads table. But then I need the Opportunity table, to pick up the "Demoted to Leads".

Would like ONE report to pull all, instead of 2 reports.

Does this make sense? Can you help with this in Report Wizard? Because when I select the fields, I can only get the

fields from the Leads table.

Thank you,

CC

Was this reply helpful? Yes No
Suggested answer

Aileen Gusni 44,524 on at

Like (0)

Report

Hi Rocky,

Yes, you are correct.

Does this make sense? Can you help with this in Report Wizard? Because when I select the fields, I can only get the

fields from the Leads table.

--> Yes, it make sense, and it is possible, have you selected the Field from Opportunity record type?

It can show both tables fields

Thank you.

Was this reply helpful? Yes No