Retrieve Product Details in CRM 2011

I am working on a PowerPivot report which retrieves Order Details (line items) from Orders in CRM using the query below:

SELECT
[dbo].[SalesOrderDetail].[ProductIdName]
,[dbo].[SalesOrderDetail].[SalesOrderId]
,[dbo].[SalesOrderDetail].[ExtendedAmount]
,[dbo].[SalesOrderDetail].[CreatedOn]
,[dbo].[SalesOrderDetail].[ExchangeRate]
,[dbo].[SalesOrderDetail].[BaseAmount_Base]
,[dbo].[SalesOrderDetail].[ExtendedAmount_Base]
,[dbo].[SalesOrderDetail].[Tax_Base]
,[dbo].[SalesOrderDetail].[ManualDiscountAmount_Base]

FROM [dbo].[SalesOrderDetail]

WHERE NOT (([ProductIdName] IS NULL OR ([ProductIdName]='')))


This basically gives you what you would see in the Order Product Associated View in CRM but for all the Orders.

I need what shows up in the All Order Products View, for all the Orders.

I thought of modifying the above query as follows:

Remove the WHERE clause. This is great. It retrieves all the Order Products.

The only problem is, I don't really want them all. I want everything excluding freight charges.

(In our system, we are using the Services, rather than Existing Products for the following:

* Charges made to a G/L Accounts

 We have two of these, one for misc services and one for freight. These are  mapped through the Dynamics Connector so that on the Sales Order in NAV, the line Item will be a Charge/Item for the appropriate G/L Account.

* Comments (also mapped through the Connector)

So, I thought, ok, I will use the following as the WHERE clause:

WHERE [ProductDescription] != 'Delivery'

I don't really want Comments, but that's ok. The Write-in Product  (ProductDescripton field) for the freight charges is called 'Delivery'.

However, this change doesn't give me ANY of the Order Product Associated View products. It gives me ONLY the Write-in Products.

I tried the query using SQL Server Management Studio on our SQL Server and same result.


Obvioulsly, it is possible to retrieve all the Order Products on an Order -- CRM does it!

How can I do it?

Thanks!
MaglinFurnitureAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Feridun KadirPrincipal ConsultantCommented:
I think you are asking for all order products where the product is an existing product and also write in products but not ones called Delivery.

Try using this where clause:

where
 (
isproductoverriddenname='Existing' or
(isproductoverriddenname='Write In' and productdescription !='Delivery')
)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MaglinFurnitureAuthor Commented:
This did not work as is.  I  get these error messages:

Invalid column name 'isproductoverriddenname'.


Apparently you can't append 'name' to these fields, as you can with some of the others. I had to fire up SQL Server Management Studio and find out what the numeric equivalents were for 'Existing' and 'Write-In'. and that worked:

where
  (
 isproductoverridden='0' or
(isproductoverridden='1' and productdescription !='Delivery')
 )


Thanks!
Feridun KadirPrincipal ConsultantCommented:
The fields are definitely there if you user the filtered views, at least they are in CRM 2011.
MaglinFurnitureAuthor Commented:
I'm not using Filtered, but works either way. Thanks very much for your assistance!
Feridun KadirPrincipal ConsultantCommented:
You are welcome.  The filtered views honour security.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.