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!