How to create report with record that has lookup field and subgrid?

(0) Share

Report

Posted on by USA80

Would like to get a report some how that will show me all opportunities with Food = Pizza, and see all the Restaurant Types for these opportunities within a report.
The Restaurant Types are in a subgrid on the Opportunity form. I can't seem to pull an advanced find query to make this happen.

The Food field on the Opportunity form is a lookup to the Food entity

There is a N:N relationship between Opportunity and Restaurant Types.

Any help is appreciated, thank you.

All responses (6)

Answers (1)

Suggested answer

USA80 2 on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

The field was not searchable, so I had to have searchable on the field = Yes, then I could see it.
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

Hi USA80,

It’s so strange, all fields of opportunity will show in the dropdown list by default.

1.Are you sure Food and opportunity is 1:N relationship and lookup field has created.

2.Make sure 'Record type' is opportunity.

3.Try to use 'Food' as record type value and select 'name' field of food entity as column.

4.Create new custom entity and build 1:N relationship with opportunity, then check it's lookup field can appear in the list or not.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.
USA80 2 on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

I can't see it from the layout area of the wizard. When I click on Add column, the lookup field doesn't appear, so I can't select it. It is only this lookup field that I can't see in the drop down of fields.
Community Member Microsoft Employee on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

Hi USA80,

Can you provide screenshot about it? Which step you can’t select ‘Food’ lookup field?

Only ‘Food’ lookup field can’t be selected? Other Lookup fields can be select or not?

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.
USA80 2 on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

I can't see the "Food" field to select at all. This field is on the Opportunity record, and is a lookup to Food Entity. Why can't I see/select this field?
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: How to create report with record that has lookup field and subgrid?

Hi USA80,

You can use Report Wizard to create a report which contains Food lookup field and sub-grid (N:N relationship).

Here are steps:

1.Go Settings > Customizations > Customize the system > Entities > Opportunity > N:N relationship to find the relationship between opportunity and Restaurant Types.

In Other Entity section, Display Option was set as "Do not Display" by default, you need change it, the N:N relationship can be available in Report Wizard while creating report.

2.Reports entity are not show in the sitemap, you can find it in advanced Find(Or you can add it to sitemap directly with app designer).

3.Create ‘Report Wizard’ button in new report page, then select entities.

4.Set opportunity filter criteria as your need---show all opportunities with Food = Pizza, and add fields you need from these two entities

5.Run report after setting all.

6.Test result:

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.