locked
Order Product Entity in Advanced Find RRS feed

  • Question

  • Hi,

    can someone explain to me why the Order Product Entity (salesorderdetail) is not showing up in the Advanced Find ?

    The original issue is the following:
    Even when I give the table FilteredSalesOrderDetail the alias 'CRMAF_' to make it pre-filterable on a custom report it won't show up on the Default Filter screen of the report. That is probably linked to the fact that it is not showing up on the normal Advanced Find screen either.

    Is there a way to change that?

    Reason being:

    When I run the report, CRM adds automatically a WHERE clause to the CRMAF_FilteredSalesOrderDetail which filters against the attribute modifiedonutc:

    ...
    FROM (select product.* from FilteredProduct as product) as CRMAF_FilteredProduct INNER JOIN ( select salesorderdetail.* from
    FilteredSalesOrderDetail as salesorderdetail where ( salesorderdetail.modifiedonutc >= dbo.fn_BeginOfLastXDay(GetUTCDate(),  30 ) and
    salesorderdetail.modifiedonutc <= GetUTCDate() )) as CRMAF_FilteredSalesOrderDetail ON
    ...

    That restricts my report to the last 30 days and I can't change it at the moment.

    Any ideas how I could get around that?

    Thanks,
    ybk

    Thursday, May 29, 2008 12:34 PM

Answers

  • Order Products, Quote Products, Opportunity Products and similar entities are very locked down.  I think the main reason they are locked down is because of the amount of calculation code built around them.  Totals up to the header record, discounts, price list pricing, etc.  You are correct in that since you can't see the entities in Advanced Find they are not going to work through the CRMAF_ (prefilter).  The prefilter (and default filter) is tightly linked to Advanced Find and since these entiteis have been kept from it using the CRM reporting API won't get you too far.

     

    Depending on your need and where you are at in your usage of the system, you could move to a custom Order and OrderDetail; you could create reports that use SQL authentication to query the CRM tables instead of going through the FilteredViews, then just lock down the report by only allowing the owner see it, or a spcific team; otherwise push the data into a custom table and build your report off that.

     

    Depending on what you need to accomplish I'm sure a way through this problem can be found.

     

    Fronk

    Saturday, May 31, 2008 5:36 AM

All replies

  • When I try to perform an Advanced Find on Orders or Invoices, I have an Order Product and an Invoice Product entity, respectively.

     

    Can you post your report rdl?  And what are you trying to do?  Is the SQL snippet from the report server after you loaded it into CRM or is it from a data source you built in the report?  When you run the report, do you see the filter?  If so, have you edited the default filter for the report?

    Friday, May 30, 2008 8:54 PM
  • Order Products, Quote Products, Opportunity Products and similar entities are very locked down.  I think the main reason they are locked down is because of the amount of calculation code built around them.  Totals up to the header record, discounts, price list pricing, etc.  You are correct in that since you can't see the entities in Advanced Find they are not going to work through the CRMAF_ (prefilter).  The prefilter (and default filter) is tightly linked to Advanced Find and since these entiteis have been kept from it using the CRM reporting API won't get you too far.

     

    Depending on your need and where you are at in your usage of the system, you could move to a custom Order and OrderDetail; you could create reports that use SQL authentication to query the CRM tables instead of going through the FilteredViews, then just lock down the report by only allowing the owner see it, or a spcific team; otherwise push the data into a custom table and build your report off that.

     

    Depending on what you need to accomplish I'm sure a way through this problem can be found.

     

    Fronk

    Saturday, May 31, 2008 5:36 AM