Crystal - Dynamic Group by Month Week Year based on a parameter response

0
votes

I have a report that is currently grouped by "day" for a date range selected. But other people want to run the same report for different time period, ie Day, Week, Month, Year etc.

I want to offer this choice via a parameter DAY, WEEK, MONTH etc, but i dont know the formula to write in the group section....

if {@Group By Period Formula} = "DAY" 
then (formula for daily) else
if {@Group By Period Formula} = "WEEK" 
then (formula for weekly) else
if {@Group By Period Formula} = "MONTH" 
then (formula for monthly) else

I dont know the formualsfor daily, weekly, monthly

1
crystal-reportsreport
can you explain week part? how do you need it in report? – Siva
What is your date format in database – Siva

1 Answers

0
votes
  1. Create a parameter GroupBy with three values "Weekly","Monthly","Daily".

  2. Create a formula Group1Formula with the following code - Change the table and column name to the date field you are using

    if {?groupby} = "Monthly" then date(year({Command.date}),month({Command.date}),1) else if {?groupby} = "Weekly" then dateadd("ww",datepart("ww",{Command.date})-1, date(year({Command.date}),1,1)- dayofweek(date(year({Command.date}),1,1)+0)) else if {?groupby} = "Daily" then {Command.date}

    -- This formula evaluates the parameter, and if the parameter is "monthly", the formula outputs the month and year from the date field. If the parameter is weekly, it outputs the start of the week that the day is in. i.e. 1 1 2016 is the week of 12 26 2015 -- NOTE if your day of the week is different than this, additional adjustments may be needed. If the parameter is "daily", the formula outputs the date field without any modification

  3. Click Report, Group Expert, Add the formula "Group1Formula" as a group. Click Options and select For Each Day The formula will handle the grouping - we don't want the group expert to do anything.

`[enter image description hereenter image description here enter image description here