Urgent Help! Date Ranges, Count and Nulls

[sayden]

Hi, I have a problem

I am creating a report based on several tables. I have two parameters with which The user selects the month and year, and it is supposed to calculate how many people came in each day for that month. What I am looking for is a formula for X (Y, Z and E are variations of X) that will do the count of people who came in each day. I also need a column that displays each day of the month as a number (1 -31) to be entered. And calculate the totals per row, and the total per column.

the structure is below.

-------------------------------------------------
Day A B C D Total
1 X Y Z E [ X + Y + Z]
2
3
4
5
.
.
.
31
Total [sum of X] Grand Total[Sum Of above]
______________________________________________
X = count of people


I am currently using the following formula

(A.date) = @Date

//@Date
Date(tonumber({?Year}),tonumber({?Month}), {sc_days.Day})

where Month is selected by the user from a combobox
and Year is entered by the user into a textbox
and sc_days is a column in a table with the numbers 1 - 31.

I had managed to get it to show the count for the days there were people but I somehow changed it and can't get it to add up. I get the following results for my test results
@date Count
15/11/2000 1
11/11/2000 1
15/11/2000 1

Which I need to become
...
10/11/2000 0
11/11/2000 1
12/11/2000 0
13/11/2000 0
14/11/2000 0
15/11/2000 2
...
I am using Crystal Reports for VB.Net and a MySQL database. Can someone help me please! I need to get this done by this Friday, and I'm at my wits end!

 

[solmskid]

If you group by a date field CR will let you define each day, you could insert a summary count for each day. I'm
not sure how that would fit in with your other groupings,
but you should be able to improvise....

 

[sayden]

I am back! Thanks solmskid. I took your hint and I've been working at it and so far I have got it to work. Unfortunately it hasn't been as straight forward as I would have liked.

the structure has changed. I no longer need one of the Total fields. So...
----------------------------------------------
Day A B C D
1 X Y Z E
2
3
4
5
.
.
.
31
Total [sum of X]
______________________________________________

I may have done this the round about way (ab)using formulas(and so if there are improvements I would appreciate them) but it works -

I couldn't use the tip about the month, because the month I needed to group by used my days column from my table and I got an error for months with days less than 31. I used a Select formula instead:
------------------------------------------
eventdate, transID and Day are from the database

//@Select
//ensures the selection criteria
Year(eventdate) in (?Year) and
Month(eventdate) in (?Month)

I then grouped: first by Day: which gave me days 1 - 31 (why it didn't work before I have no idea!)

and then by transID: when I discovered my running total was counting the same records multiple times, this solved the problem
----------------------------------------
I used a running total for the formula X I was after

#RCTotal
Summary - by transID; summary type count

Evaluate: -
Formula: transID <> previous ID and
Day(eventdate) = day

Reset:- on Day Group

I inserted the running total into the footer section of the Day group.

It works fine - now I need to work on the totals at the bottom.

I was posting this to find a way to do the totals (I had started trying the tedious manual running totals) but I just found a nicer way.

I am using another Running total.

Summary - by transID; summary type count

Evaluate: -

Formula: transID <> previous ID and
Day(eventdate) = day

Reset:- Never

It seems to work but if you can see some problems please point thme out for me.

Thanks