Daily,WeekToDate,MonthToDate,YearToDate Report

[OKCMikeSoll]

I have a report that is count the number of tickets based off of {TICKET.OPEN_TIME}. I would like to use a cross-tab for this report. I have started a formula to split the count up by day, WTD, MTD, and YTD, the problem is that when it counts a ticket under MonthToDate, it does not count it for YearToDate like I need it. Here is the formula below:

if {TICKET.OPEN_TIME} in CurrentDate-1 then "Daily"
else if {TICKET.OPEN_TIME} in WeekToDateFromSun then "WTD"
else if {TICKET.OPEN_TIME} in MonthToDate then "MTD"
else if {TICKET.OPEN_TIME} in YearToDate then "YTD"

This may be a dumb question but is there someting like an "else/and" operator? Any assistance would be most appreciated!

Thanks,
Mike

 

[lbass]

I think you need to use a manual crosstab for this. Do you have a row field you want to group on? Let's say you want to look at ticket counts by event type. You would group on {table.eventtype} and then create a series of formulas for the details section:

//{@daily}:
if {table.date) = currentdate - 1 then 1 else 0

//{@weektodatefromsun}:
if {table.date} in weektodatefromsun then 1 else 0

//{@monthtodate}:
if {table.date} in monthtodate then 1 else 0

//{@yeartodate:
if {table.date} in yeartodate then 1 else 0

Then right click on each of these formulas and insert summaries (SUM, not count). Then suppress the details section and drag the group name into the group footer next to the summaries.

-LB

 

[OKCMikeSoll]

This worked perfectly!! Thank you very much for your assitance.

-Mike