IMPLEMENTING CRM FOR BEST REPORTING PRACTICES

(0) Share

Report

Posted on by CRM Learner 88

So in reviewing CRM, I believe the best way to set it up is so that entities can be reported through the use of views/dashboards/charts.... I believe we should try to rely less on SSRS reports as possible. This being said, what would be the best approach in creating products/opportunities/accounts/contacts/leads so that it is enabled for Advanced Finds?

I know this varies depending on the business and the requirements obviously.. but I'm just looking for some best practices so that once we set it up we can take advantage of the native reporting in CRM as much as possible and not be reliant on SSRS.

I appreciate everyone's help on here.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Mahendar Pal 45,095 on at

Like (0)

Report

Hello,

Business entities are enabled for Advance find by design, but it is visible to user or not that depends on his security role, so what type of permission and up to what level he can access records.

Please refer: msdn.microsoft.com/.../gg334717.aspx for understanding security roles

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There are some things that an Advanced Find just cannot do that you will still need reporting for. Basic functions such as sums or order values, concatenating fields and other basic features that your users will want are just not available.

Was this reply helpful? Yes No
CRM Learner 88 on at

Like (0)

Report

I believe CRM 2015 has calculated fields now, correct? Also charts can be used to Sum data. What other limitations are there of Advanced Find?

Was this reply helpful? Yes No
CRM Learner 88 on at

Like (0)

Report

Can't calculated fields fill the gap for sums or order values, concatenating fields?

From what I've been able to gather about CRM, setting the system up in a way where you can query reports with the advanced find is the most intuitive and easiest way to go.

I guess what I'm looking for though is advice on entity/field set up out of the box to make it most configurable to Advanced Finds.

For example I understand there is a native flaw when reporting on products.

Was this reply helpful? Yes No
Suggested answer

ThomasN 3,190 on at

Like (0)

Report

Hi Learner 8,

Thanks for reaching out. I know exactly what your looking for and is something I have implemented before. The catch for advanced finds, views, and charts that the reporting helps resolve is what I call the third layer. For exampled if A is related to B and B is related to C, you can see A data with B details, but you cannot see how A and C are related through B.

So products is a good example. Without any changes to lookup opportunity and products you use opportunity product lines, but if you want to find out just opportunities by products, that would not work since you have an opportunity for every row of the opp product lines. Or if there is a "product type" field on the product you cannot get to that from the Opportunity record you have to go through the opp product line record, etc.

Essentially if you want to have something easily reportable place a relationship directly on the record. We solved this through workflows that updated a Product Type field on the Opportunity record when new opp product lines were created.

I hope this helps. Let me know if I can clarify more.

-Tom

Was this reply helpful? Yes No
CRM Learner 88 on at

Like (0)

Report

Hi Tom thank you for the reply. I haven't been on here in a bit so sorry for the late follow up.

So let me write out your example and see if I can understand. Here is how I am envisioning that 3 layer relationship.

Accounts>Opportunities>Products

I would want to be able to see how many opportunities a particular product would have as well as the total amount of sales for it. - From what I understand the challenge here is excluding other products if there were several on an opportunity.

If you give me an example I think that would help.

Was this reply helpful? Yes No
Suggested answer

ThomasN 3,190 on at

Like (0)

Report

Hi Learner,

Thanks for connecting back. The limitation is around the fields on an entity, and what you want to see in each row. Advanced Find will not allow duplicate rows, so you have to start with what you want each unique row to represent.

Remember you add a relationship to opportunities using the "Opportunity Product" record. You would do an advanced find for Opportunity Products. The question that asks "How Many" is best answered by a Chart, unless you want to manually count each row.

Example - On the Product is a field called "Product Type".

Business Need: Group Opportunities by Product Type.

OOB does not work because you get a list of Opportunity products which there are multiple per opportunity.

If you go to add LOOK FOR opportunity you cannot not add any field from Product record because there is no direct relationship.

Solution: Create Product Type global option set, and add it to Product, Opportunity and Opportunity Product.

Now LOOK FOR opportunity and you can add the field Product Type because it is on the Opportunity record. You can also create a calculated field on the Opportunity Products to count how many of each product type because the field is on the Opportunity Product record. You have to have a direct relationship.

Cannot make a jump over a relationship. Advanced shows columns from only directly related records no columns from records related to a record that is related to the record you are looking for.

I am sorry if this is confusing. This takes trying different things to see this in action. Hope this helps.

-Tom

Was this reply helpful? Yes No
CRM Learner 88 on at

Like (0)

Report

Tom first of all I appreciate you having the patience to help me understand this. I think it might be semantics of some of the words in your explanation that still has me a little confused but it is starting to make more sense. Let me address your points in my terms and tell me if I'm correct or not...

"Remember you add a relationship to opportunities using the "Opportunity Product" record. You would do an advanced find for Opportunity Products. The question that asks "How Many" is best answered by a Chart, unless you want to manually count each row.

Example - On the Product is a field called "Product Type".

Business Need: Group Opportunities by Product Type.

OOB does not work because you get a list of Opportunity products which there are multiple per opportunity."

1.) So first of all, even if I don't want to use a chart, wouldn't the total amount of records be indicated at the bottom of the view?

2.) So the business need here would be to get a report of how many opportunities are in each product type, correct? You are saying the OOB wouldn't work because the opportunities could have multiple products on them each of which would be a different product type. Is this correct?

" Now LOOK FOR opportunity and you can add the field Product Type because it is on the Opportunity record. You can also create a calculated field on the Opportunity Products to count how many of each product type because the field is on the Opportunity Product record. You have to have a direct relationship.

Cannot make a jump over a relationship. Advanced shows columns from only directly related records no columns from records related to a record that is related to the record you are looking for."

1.) I think the calculated field would be the only option (or at least that I understand) that works here.. But I'm not understanding where it gets placed exactly... Since you're saying 'Product Type' would be on the Product record itself but still can't give us accurate reporting for the example above, how would putting a calculated field on the same record (that has the same relationship to opportunity) make this now reportable?

Again I'm sorry I'm slow but I'm definitely learning a lot here, thanks so much again.

Was this reply helpful? Yes No
ThomasN 3,190 on at

Like (0)

Report

Hi Learner, no problem, this is not the best way to explain all this. Sorry I am not doing better.

1. The total count of records is visible at the bottom.

The need I was discussing was a count of unique opportunities that have that particular product type. The view would show there are 50 Opportunity Products with this type, however, that may only break down into 10 opportunities.

2. Correct.

1, The calculated field would be created on the Opportunity record, and can count Opportunity Products that have a s specific product type. That is why this works best for the particular need. May not work for all, but in this scenario it works.

Now the next step would be to go make some mistakes, in Sandbox of course. That is how I figured these pitfalls out. Of course keep asking if you run into any issues we always have time for you. Have a great day!

Was this reply helpful? Yes No