cross-tab report problem

[tpoettker]

I am a newbie to Crystal and can't seem to figure out the cross tab report. I need a report that counts the number of events by dept and then event type by month.

My report needs to look something like this:

Jan Feb Mar Total
Dept A
Event Type 1 9 11 1 21
Event Type 2 2 5 9 16
Dept A Totals 11 16 10 37

Dept B
Event Type 1 5 9 2 16


I think I need the cross tab report but can't get it to work. My rows are Dept and Type. I think my columns need to be formula for range of dates (20030101 to 20030201, etc) but when I preview the sample, I get two columns for Jan (True|False). My formula is ToNumber(event_date) >= 20030101 and ToNumber(event_date) <= 20030131. And the summarized fields are counts of my formulas. Can anyone help?

 

[lbass]

For your column, choose your date field, and then select it and choose &quot;group options&quot; and a column will be printed for each month.

-LB

 

[tpoettker]

I changed my column to the event_date (removed the formulas) and selected group option. When I selected the &quot;grouped by&quot; event_date (instead of customized Group Name Field), I get a column for every date (20030101, 20030102, 20030103...20030131). How do I get a total for all of Jan?

 

[lbass]

Sorry, I wasn't paying attention to the fact that your &quot;date&quot; was actually a string. You should try creating a formula like:

if {table.event_date} in &quot;20030101&quot; to &quot;20030131&quot; then &quot;Jan&quot; else
if {table.event_date} in &quot;20030201&quot; to &quot;20030229&quot; then &quot;Feb&quot; else
if {table.event_date} in &quot;20030101&quot; to &quot;20030131&quot; then &quot;Mar&quot; else //etc.

This should be available for your column in the crosstab. As long as your field is always contains 8 digits, this should work without needing to convert the field to a number.

-LB

 

[dgillz]

MonthName(month(NumbertoDate({YourField}))) will return the name of the months.

It will also do this for every &quot;date&quot; in the database, including other years, so you may want to exclude those records from your report in a record selection formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[tpoettker]

I have event_date for my column. I clicked on Group Option/Use a formula as group name and entered the formula as your described.

This changed the column head to &quot;Jan&quot;, &quot;Feb&quot;, etc. But I get a column for each day (01/01/, 01/02/, 01/03, ...01/31). It just says &quot;Jan&quot; &quot;Jan&quot; &quot;Jan&quot;. How do I get one column for a Jan total?

this is probably very easy but driving me crazy.

 

[Reebo99]

To convert your field to date type use :

Date(Picture({event_date},&quot;xxxx/xx/xx&quot

Now, follow the first instructions by lbass, using this formula as your field accross the top.....

Reebo
UK

&quot;Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.&quot;
- Albert Einstein (1879-1955)

 

[tpoettker]

thank you, thank you, thank you.

Converting to date opened new group options that were not there before.

 

[lbass]

I don't know whose formula you are now using, but I meant for you to use my second formula as the column--this will give you the correct data and column name.

You could also convert your field to a date as suggested by the others and then use the group options to set it to &quot;on change of month.&quot;

-LB