Crysal Reports Dynamic SQL

[ultim8]

Hi,
I am trying to dynamically modify the columns that I display on a crystal report from VB.NET.

I can easily modify the report parameters, the report selection criteria, but not the columns that i return from the database.

Anybody have any suggestions??

Thanks
Steve

[ultim8]

I also want to be able to change by which column the report is sorted, and the sort direction (asc or desc)

Basically, i want to be able to build the report either from a dataset, a recordset, or a SQL statement.

Cheers.
Steve

[Athley]

check out the example down below. Its reports are connected to created stored procedure, where you can modify the SQL statements as you like.

Link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvssamp/html/vbcs_UseCrystalReports.asp)

/Leyan

[ultim8]

Leyan, Thanks.

Unfortunately, I have this example already - it does not show how to customize the sql statement that the report uses - only how to modify the report design, and how to populate parameters that have already been created.

I am wanting to modify the report SQL query so that I can alter the selected columns and the sorting characteristics.

Any further suggestions would be appreciated.

Steve

[Athley]

Maybe I'm not understanding your question fully, but.....

In the "Windows Form Designer generated code" Region of that examples Main form, they create the stored procedures they link to the reports from an SQL statement, wich you can change. As I see it this is not necessary as you might create your own stored procedure to access.

This is an example of what the do in that Region:
cmSQL.CommandText = "CREATE PROCEDURE dbo.GetAllCustomerOrders " & _
"AS " & _
"SELECT CUST.CompanyName, " & _
"ORD.OrderID, " & _
"ORD.OrderDate, " & _
"ORD.ShippedDate, " & _
"PROD.ProductName, " & _
"ORD_D.UnitPrice, " & _
"ORD_D.Quantity " & _
"FROM Customers CUST " & _
"INNER JOIN Orders ORD " & _
"ON CUST.CustomerID = ORD.CustomerID " & _
"INNER JOIN [Order Details] ORD_D " & _
"ON ORD.OrderID = ORD_D.OrderID " & _
"INNER JOIN Products PROD " & _
"ON ORD_D.ProductID = PROD.ProductID " & _
"ORDER BY ORD.OrderDate " & _
"Return"

/Leyan

[Athley]

As I changed the row....

"ORDER BY ORD.OrderDate " & _

to

"ORDER BY ORD.OrderDate Desc" & _

....it changed the sort order of the reportto descending. Is not that what you wanted to do?

/Leyan

[ultim8]

Athley,
thanks again for your reply (I appreciate your persitance)

The point you are missing (unless i am being very silly) and the problem I am having, is that I cannot change the columns displayed on a report.

Even if I base the report on a view, then modify the view (for instance, add an extra column) I cannot change Crystal's SQL statement to display that extra column - likewise if I wish to hide a column from the report.


Yes, you are right, I can base a report on a view, then change sort orders/selection criteria... but not the report columns!

Any further ideas/help :confused:
Steve