Hello,

I was asked to create a report which summarizes the sum of checks, the count of the customers paid the checks, and the count of the checks which are due within the selected month and year. Also, I was asked to make the summary divided into periods for example 5 days.

/wp-content/uploads/2014/10/screenshot_575559.png


In this case, the report should show the sum of checks, the count of the customers paid the checks, and the count of the checks which are due in November 2014. The summary is divided into periods of 5 days as the following:

/wp-content/uploads/2014/10/screenshot2_575560.png

If the period is 10 days, the report will be as the following:

/wp-content/uploads/2014/10/screenshot3_575576.png

I used the following formula as Cross-Tab Columns Formula:

Ceiling (day({Command.CheckDate})/{?days}) // This formula name is d which will be used in another formula later

Day Formula (d)     Day Formula (d)      Day Formula (d)
1 1 11 3 21 5
2 1 12 3 22 5
3 1 13 3 23 5
4 1 14 3 24 5
5 1 15 3 25 5
6 2 16 4 26 6
7 2 17 4 27 6
8 2 18 4 28 6
9 2 19 4 29 6
10 2 20 4 30 6
31 7

Here is the result:

/wp-content/uploads/2014/10/screenshot5_575620.png

To make the column header more meaningful, I added the following formula into the Cross-Tab Group Options > Group Name Formula as shown in the screenshot:

/wp-content/uploads/2014/10/screenshot4_575577.png

if ({@d}-1)*{?days}+1=Day(DateAdd(“d”,-1,DateAdd (“m”,1 , Date ({?Year},{?Month} ,1 )))) then

// The first day in the period = the last day in the month then show only that day for example 31

CSTR(({@d}-1)*{?days}+1,0)

Else if {@d}*{?days}>Day(DateAdd(“d”,-1,DateAdd (“m”,1 , Date ({?Year},{?Month} ,1 )))) then

// The last day in the period > The last day in the month then show the beginning of the period with ++ for example if the period is 7 days then the last period will be 29++

CSTR(({@d}-1)*{?days}+1,0)+”++”

Else

CSTR(({@d}-1)*{?days}+1,0)+”-“+Cstr(({@d})*{?days},0)

The final result as the following:

/wp-content/uploads/2014/10/screenshot2_575560.png

I hope this was helpful

Best regards,

Alaa

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply