CR 8.5 Formulas for Month Summarized Data

[SpringK]

I have a crosstab report based on SQL query. I have summarized days operating and days standby (rows) and months (columns). At the end of the report, I want to create a formula that computes the days utilized each month as follows

01/2005 02/2005
Days Operating 25 18
Days Standby 2 3

Total Days 27 21
Days Utilized 87% 75%

Where days utilized is the Total Days / Days in Month.

How do you this, and do you have to do this for each month, ie Jan has 31 days, Feb has 28 days, Mar has 31 days, etc. Since my crosstab report is being summarized by the column value on the month, I'm not sure how to accomplish this.

 

[cmv131]

Depending on how you have it setup to crosstab, you could create a formula with something along these lines to get the days in a month. It takes adds negative the number of days in the following month, to the same day in the followling month (thus giving 0 or whatever the last day of the current month is)

It may or may not need some sort of date parameter, but I am using a similar formula to do a rolling year of data to determine #days / month and it works great.

day(
   dateadd("D",-day
              (dateadd("M",1,Date)
              ), 
    day(dateadd("M",1,Date)
        )
    )