SQL Expressions are for small bits that can be put into an existing Select statement.  For example, I have a stored function that formats user names based on a key field in the database so I can put this in a SQL Expression:

I read this thread today and went off and created a couple of commands in my database... all the time not thinking about the result that would give me when I went to test the report against a different database of the same schema.  It doesn't have the commands. 

Oh well... back to the drawing board.


Edited by kwsanders - 25 Jul 2007 at 7:07pm

When you say to create the commands in my report, are you referring to using SQL Expressions?  If so, then I guess I am doing something wrong that I need to try and figure out.

I am using Crystal 10 Advanced Full (10.0.0.533).  I need to create a report that is based on a single table.  The report has three columns.  Information for the current month, the previous month, and a summarization of year-to-date.

The first two columns are easy.  The third column is where I am getting tripped up and it is probably to do with my record selection formula.  I am not getting proper numbers for the roll-up on the year-to-date values.

One thing that I forgot to mention is that I need to determine a value based on a particular parameter.  I didn't think you can use parameters in SQL Expressions.


Edited by kwsanders - 26 Jul 2007 at 7:19am

I have to retype this based on memory... just lost everything I had typed.  

Anyway... I thought that SQL commands on the Database Expert screen are on the database and do not migrate with the report.  Is that not right?  At least it is not what I am seeing here.  I have to be able to dynamically change the datasource on the report at runtime.

As for my final column, it is a summarized view of the year-to-date data.   I need to be able to pull in a list of records based on a range of numbers.  From that dataset, I needed to calculate some summary totals across several values.

The big problem that I am having trouble understanding with Crystal Reports is how the records get loaded and calculated when the report is executed.  I had setup a formula to pick off a date value from a record if its ID field had the same value as the first value in my parameter range.  When the report runs, however, I see an empty field.  If I browse the data on that formula field, I see two values... a blank line and the second is the date that I expected.

I had setup the formula as follows:

dateTimeVar myDate;
IF {MYTABLE.ID} = {?TEST_ID} THEN
   myDate := {MYTABLE.SOME_DATE};

Any help someone could give me would be great.  I ordered a book from Buy.com recently for Crystal 10, but they sent me the book for Crystal 9.  I got an RMA, but I am waiting for them to send the correct book.

I need to go out and purchase Brian's book.  I had seen it a while back, but I did not pick it up at that time.

Add this line at the end of your formula: