I’ve been ‘lucky’ enough to spend some time generating reports using Crystal Reports XI. One of the things I wanted to do was customise the SQL used to generate the report. Initially, it wasn’t obvious how to do this and Google couldn’t find any hints.
Happily, I found out how to customise the SQL used in Crystal Reports!
Open your report and open the Database Expert.
- Select “Current Connection”
- Click “Add Command”
- This will open a dialog – paste an SQL command into the left pane of the dialog.
- Click OK on the ‘command’ dialog.
- The source table for the report will now be the command, and the fields for the report will be based on the select query of the command.
It also appears that any parameters added to a report with an SQL command can be passed to the command – this can be done from the same dialog that the SQL command was added. However, I didn’t try this – I was happy enough to find out about the custom SQL! (Trust me, when you’re working on Crystal Reports, little victories are really important!) I’d recommend making sure that you’re query is right before you paste it into your report – debugging an SQL query is much easier than debugging a Crystal Report!
Mike Pyne's Blog 
Wow, I had no idea… I have been using Crystal for over 3 years on a daily basis and did not know about the feature. I have been writing VIEWS in SQL and often used these views only once for a certain report and never reused it.
Thanks