CODEWITHSUNDEEP
sqlsql-server-2008dynamics-crm-2011reporting-services
Yacine Zine
Yacine Zine

Reputation: 619

Report doesn't show data in VS2008 on Server 2008 R2?

I have to build a custom report that takes the orderlines for each order with the client's name,address and username on the report.

When I make a simple query like:

SELECT FilteredSalesOrder.*, FilteredAccount.* 
FROM FilteredSalesOrder INNER JOIN FilteredAccount 
ON FilteredSalesOrder.customerid = FilteredAccount.accountid

I retrieve data but only for a single line. When there are more, the preview shows nothing. which is not that big of a problem because I only need it for 1 order at a time.

So for my OrderReport I need all the OrderLines for each order. The report is printed out per Order not for multiple orders at the same time. It will look something like this: enter image description here

I just joined 5 tables from which I need data in my Order Report.

SELECT FilteredSalesOrder.*, FilteredAccount.*, FilteredSalesOrderDetail.*, FilteredSystemUser.*, FilteredProduct.*
FROM ((((FilteredSalesOrder INNER JOIN FilteredAccount 
ON FilteredSalesOrder.customerid = FilteredAccount.accountid) INNER JOIN FilteredSalesOrderDetail 
ON FilteredSalesOrderDetail.salesorderid = FilteredSalesOrder.salesorderid) INNER JOIN FilteredProduct
ON FilteredProduct.productid = FilteredSalesOrderDetail.productid) INNER JOIN FilteredSystemUser
ON FilteredSystemUser.systemuserid = FilteredSalesOrder.ownerid)

So to summarize:

Upvotes: 0

Views: 202

Answers (1)

Yacine Zine
Yacine Zine

Reputation: 619

  • Is my SQL query correct?

It returned an error because the query returned more than 665 Columns and apparently SQL can't handle that.

  • When I upload a simple report it gives me the error rsInvalidDataSourceReference. I know what this issue means, but what do I need to do to solve it.

This was solved by adjusting the connection string when you open the report in notepad++ you can alter it to match your production environment.

  • How do I make sure that in the report, the orderline table keeps on growing as needed to include all the orderlines?

That is something that the report handles itself when you make the report using the report wizard of Visual Studio 2008 on the SQL Server. If you drag and drop fields from your dataset. The report will only take the first record it finds.

Upvotes: 1

Related Questions