Daily, MTD & YTD Cross-Tab 1

[tcgoth]

CR 8.0
SQL

I reviewed previous posts and thought this process would be straightforward. I'm obviously missing something important.

I want to do a cross tab that displays Daily, MTD and YTD activity based on a datetime field (rpt_customer.cust_rec_date)

I want the output to look like the following:

Daily MTD YTD
Client 1
Client 2
etc.

I'm using the following formulas:

Daily

if cDate({rpt_customer.cust_rec_date})=CurrentDate then "1" Else "0"

MTD

If date({rpt_customer.cust_rec_date}) in MonthToDate then "1" Else "0"

YTD

if date({rpt_customer.cust_rec_date}) in YearToDate then "1" Else "0"

I then inserted the fields into the Cross Tab as summary fields. All summary fields are showing up with the same totals so obviously I dont have this correct. Help!

Thanks!

 

[kenhamady]

Maybe you are using COUNT as the summary operation when you should be using SUM. Zeros 'count' when you do counting.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[tcgoth]

Ken,

Thanks - right on. My other problem was that I had the numbers formatted as test so "Sum" wasn't even an option.

One additional question. How in the heck do I set this up in Cross Tab to get it to format properly.

I just want:
Daily MTD YTD
Client Name 23 46 112

If I put the Daily, MTD and YTD in the Summary field I obviously get the numbers stacked on each other. If I add my formula fields to the Column section I end up with some grotesque 12 column thing. Do I need to write daily, MTD and YTD into one formula and then add that formula to the column section? If so then how do I get the appropriate sum totals?

Thanks for your help!

 

[lbass]

Your formulas should be written:

...then 1 else 0

With 8.0, you don't have the option of horizontal summaries. I think the only way you can do this is with a manual crosstab. Insert a group on client, and then add your formulas to the detail section. Then right click on each and insert sums. Drag the group name into the group footer and then suppress the detail section.

-LB

 

[tcgoth]

Thanks lbass - works like a charm!