CrossTab custom group option

[mam668]

I have a Version 11 Cross-Tab report that uses a date to group by. The user wants to be able to group by week, month, quarter, and year. All these options are available in Cross-Tab group options. Instead of creating a separate report for each group option, is it possible to just use one report to do this and let the user pass in the grouping option through a parameter?

Example:

If the User entered “month”, the report would look like:


2/2005 3/2005 4/2005 5/2005 6/2005 7/2005
Rec1 8 5 4 10 3 6
Rec2 4 2 1 0 9 10


If the User entered “quarter”, the report would look like:


1/2005 4/2005 7/2005
Rec1 13 17 6
Rec2 6 10 10

Thanks

 

[lbass]

Create a parameter {?Period} with options: Week, Month, Quarter, Year. Then create a formula like the following:

if {?Period} = "Week" then dateserial(year({table.date}), month({table.date}),
day({table.date})-dayofweek({table.date})+1) else
if {?Period} = "Month" then dateserial(year({table.date}), month({table.date}), 01) else
if {?Period} = "Quarter" then dateserial(year({table.date}),(datepart("q",{table.date})*3)-2,01) else
if {?Period} = "Year" then dateserial(year({table.date}), 01,01)

Use this as your column field instead of {table.date}. You can then format the column label while in preview mode to display just the month and year. Or you could leave it as a starting date and add another higher order column field based on the formula:

if {?Period} = "Week" then "Week Starting" else
if {?Period} = "Month" then "Month Starting" else
if {?Period} = "Quarter" then "Quarter Starting" else
if {?Period} = "Year" then "Year Starting"

-LB

 

[mam668]

Thank you, this is exactly what I needed!