SQL Server query to Crystal Reports

Question

I am trying to move reports that currently run in SQL Server to Crystal Reports.

Essentially the statement I want to reproduce is:

SELECT DATEPART(DD,DATE), COUNT(*) 
WHERE FOO = 'BAR' 
GROUP BY DATEPART(DD,DATE)

Count the occurrence of records that match a criteria, grouped by date.

I have used the Selection Expert to generate a equivalence relation (to evaluate the records) and would like to use the datepart function in a group by statement. I have gotten the GROUP BY selection expert to group by date - but it is the full timestamp (SS:HH:DD:MM) not by specific day i.e. March 1 2010.

I am sure there is a way to achieve what I want but have yet to find a tutorial explaining this scenario.

Any help you could lend would be appreciated

Answer 1

Thanks, but using a Crystal Reports is a requirement of the project - is there a way to accomplish this with the provided tool?

Answer 2

As with any other SDK/Language, there are many ways to do this. Here is the first one that I can think of:

1. Get the raw data into Crystal. (Sounds like you already did this)

2. In Crystal, make a new formula, call it "GroupByDate". In the formula editor, enter:
   datepart("yyyy-mm-dd",{mytable.mydatefield})

3. Go into the Group Expert. Group your report by GroupByDate.

4. Make a new formula, call it "AddMe". In the formula editor, enter:
   iif({mytable.foo="bar",1,0)

5. Drag & drop your AddMe formula into the details section. Right-click on it to Insert->Summary. Set your summary location as the group footer.

6. Preview your report and you should see the total counts in every group footer. To simplify the appearance of the report, you can also suppress the display of the detail and grouper header sections.

Again, there are many ways to do this. You can also get creative with a Running Total function. The Crystal Formula Editor has very useful help files. Use the Functions pane to select a function, press F1, and you'll get criteria, examples, etc.