SQL Statement - need to modify?

[Montana751]

Crystal XI

In Crystal 8.5, I was able to manually modify my SQL WHERE clause, my typically modification was;

WHERE blah blah blah //generated by Crystal Select Expert
//then I manually type in the following via Show SQL Statement:

AND AccountBalance.DateX = (SELECT Max(DateX) FROM AccountBalance AB2 WHERE AB2.AccountId = Account.Id)

Since I can no longer manually add to the SQL statement, how can I add "AND AccountBalance.DateX = (SELECT Max(DateX) FROM AccountBalance AB2 WHERE AB2.AccountId = Account.Id)" to my reports Where clause?

I tried using a SQL expression, but have no clue what I am doing. Any help would be appreciated. thanks

 

[synapsevampire]

A SQL Expression works, but you're missing one of the big advantages to Crystal 9 and above called Command objects.

Go to the Database->Show SQL Query and copy it out.

Now create another report and for the data source select Add Command. You can now paste in your query and modify any part of it.

I would suggest that you create reusable objects (Views or Stored Procedures on the database) rather than either of these approaches though.

-k

 

[Montana751]

I can't use it as a Datasource, my reports can be run off of a SQL and/or Oracle Database. My reports are run out of an Application my company wrote in C++ and we use Crystal as the reporting tool. When reports are run out of our application, our source code passes values to the Where clause at run time (this still functions as designed.

So maybe I asked the quesiton incorrectly, how about this;

how do I (or can I) modify the Crystal SQL statement in XI with an additional Select statement added to the WHERE clause?

I could create a View to hold this information, but I was trying to do it the way I did in 8.5.