Display total cases for each Monday, Tuesday, Wednesday, etc from a date range 1

[mvalley]

I have a report that list data based on a date range.
I have been asked to break down the report to show total cases done on Mondays, Tuesday, Wednesdays, etc.

The report is currently grouped by date, and the footer shows to total cases done on that date (count of date).

I am looking for guidance on how to do this, sounds like it should be simple enough to do, I am just too much of a novice to figure it out myself and would welcome any and all suggestions.
Thanks in advance, Mary

 

[MCuthill]

mvalley,

The DayOfWeek() & WeekdayName() functions should be able to be utilized for your problem. This function will return a numeric value for the day of the week a certain date is, Sunday = 1, Monday = 2, etc.

I *think* the following formula, added as a Group outside of your existing group should work.

{@DayOfWeek_Name}

[blue]WeekdayName[/blue]([blue]DayOfWeek[/blue]({Table.Field}))

where {Table.Field} is your Date field.

You could also just have this field as the only group on the report, but that is at your decretion. By having both, you could see the number of transactions on a Monday, then Drill-Down to see all the dates defined as a Monday and the total for each date. By having the above as the only group level, you would not be able to drill down.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[xxyyzz49]

One thing to try.
Create groups for each day of week with summary for each day. The total of all that will be your overall total.

Another approach depending on data source.
Modify your database query, assuming a database data source, to group by date and you can use a database date function to identify the day of the week.

Yet another approach.
Write a selection formula that counts the records by day of week using an array then display the results in the report footer.

Hopefully there is a grain of inspiration in there.

 

[mvalley]

Thanks for all the suggestions, however I like the last option
"Write a selection formula that counts the records by day of week using an array then display the results in the report footer." Can you explain further how I would do this? I am not familiar with arrays nor how to use them. Displaying the results in the report footer would work GREAT for my manager. Thanks again. Mary

 

[MCuthill]

mvalley,

If you like the idea of displaying all the totals in a footer, but are unfamiliar with Array's, another option is to simply create a Formula field for each day of the week, and sum them to the group or report footer. Ordering the Formula fields in the Week-order will give the appearance of an array, without having to deal with them.

The formula's would look like:
{@COUNTER_Sunday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 1 THEN 1 ELSE 0

{@COUNTER_Monday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 2 THEN 1 ELSE 0

{@COUNTER_Tuesday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 3 THEN 1 ELSE 0

{@COUNTER_Wednesday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 4 THEN 1 ELSE 0

{@COUNTER_Thursday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 5 THEN 1 ELSE 0

{@COUNTER_Friday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 6 THEN 1 ELSE 0

{@COUNTER_Saturday}

IF [blue]DayOfWeek[/blue]({Table.Field}) = 7 THEN 1 ELSE 0

Not saying it may not be a good idea to learn array's and how to use them, but wanted to offer an alternative if you did not wish to do so.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mvalley]

Mike, Thank you for the excellent instructions, easy to follow and provided just the results we wanted where we wanted them. My manager is VERY happy ;>)
Have a great day, I know I will!! Mary