You then need seven formulas to calculate the date of the first row of the calendar. You could manage with one formula, but we believed that made the report more difficult to design and it could increase the risk of errors in writing the formulas. You do not need to put these on the page as they will be used elsewhere.

The formulas are called {@Sunday}, {@Monday} etc. One for each day of the week. The {@Sunday} Formula

looks like:

{@Start of Month} - DayofWeek({@Start of Month}) + 1

The logic here has been used widely in our examples in the past. Subtracting the DayOfWeek from a date takes you back to the previous Saturday. Add one to get the Sunday for the start of the calendar.

The Monday formula looks like:

{@Sunday} + 1

The remaining days of the week are calculated by adding one to the previous day.

Now, there is a small omission on the calendar report just presented. Did you notice? The problem is the  December 2006 group in the report. That month is 31 days long and begins on a Friday. A month that starts on a Saturday has a similar problem.

You need to include two more running total fields. The {#Sun6} running total is similar to the other {#Sun..}

running totals. It has a conditional evaluate of

{GLTrans.TRDate}= {@Sunday} + 35

The conditional display string is

ToText({@Sunday}+35,"dd-MMM-yyyy")

The other conditional formatting you need is to suppress the field if the fifth row of dates isn’t needed. The

conditional format for the suppress of that field is

month({@Sunday}+35) <> month({Table.DateField})

Some reports can be designed with a footer section and all the values calculated using conditional Running Totals. This report uses that technique. You need 35 running totals to calculate the values for each day. Yes, we know there is a maximum of 31 days in a month, but there are five rows of seven days, so a running total is needed for each of these.

The {#Sun 1} running total is displayed. Select a field you want to summarize or count. The conditional evaluation is

{Table.DateField}={@Sunday}

Similarly, use a specific day for the other running total fields for the first row.

The next row of running totals ( {#Sun 2}, {#Mon 2}, etc) continue the logic for the second week of the month.

The {#Sun 1} Conditional evaluation is

{Table.DateField}={@Sunday} + 7

All the other running totals for the second week add seven days to the day for that column. For the third week, add 14 days, and for the fourth and fifth weeks add 21 and 28 days respectively. Place these in five rows of seven columns each in the group footer. You have to use the footer as Running Total Fields only calculate for the first record when placed in a header section.

So what you will have now is five rows of seven numbers in your group footer. You know that each represents the data for a different day, but that isn’t obvious yet.

Select the first Running Total ({#Sun 1} Right click to format field.

We can change the number to a display a date by setting the “Display String” property. This is on the common tab. The formula here is:

ToText({@Sunday},"dd-MMM-yyyy")

When we look at the calendar on the wall for January 2007, we also see days for December and February on the same page as a picture of a Tennessee waterfall. The days for these other months are often shaded differently. We can apply the same technique in this report. On the {#Sun 1} running total field, set the font conditionally with:

if {@Sunday} < {@Start of Month} then silver else black

Do a similar thing for the next five days in the top row. Make sure you use the formula for the day for that column. The {#Sat 1} field will not need this as it will always be a date in the current month.

Do a similar set of conditional fonts on the last row of the report. The font for {#Sat 5} is

if {@Saturday}+28 > {@End of Month} then silver else black

You need to do similar things back to the Monday on the final row. The Sunday will always be in the calendar, but the remainder of the bottom row might not be.

Now you have a presentable calendar with days color coded to show how the month has performed. By using running totals you can display values for every day in the month, regardless of whether there are data for that day or not.

Data for a range

But what if your data are for a range? This would be great for project and call center reports. To do this, you need only a small change to the evaluation condition. If your table has a start date and end date, use this

{Table.StartDate}<={@Tuesday} and {Table.EndDate}>={@Tuesday}