User Selected Date Grouping 1

[CHDolbey]

Hello,

I was wondering if I could get some help with a problem I am having. Currently, I have a report that I have users select the start date and end date to get there report results from. The date field is also used as the group. They would also like to be able to pick how they can group the results. They would like there choices to be between Daily, Weekly, Bi-Weekly, Semi-Monthly, Monthly, Quarterly, Semi-Annually, and Annually.

I am wondering if there is a easy way to do this perhaps a way to create groups based off a parameter field they select at the start?

I'm not quite sure how I can accomplish this since it seems like I am unable to give user control of how to group the report.

 

[CR85user]

When you select change group for the group in question you ought be able to select: 'Use formula for group sort'.

Create a parameter for each date period.

Then simply reference a parameter for the formula after clicking the 'x2' button next to it.

'J

 

[CR85user]

Just to clarify - that is one parmeter with each date period defined within it. Not multiple parameters.

'J

 

[synapsevampire]

Ooops, was simple until semi-monthly, but it's all doable.

So a record selection might look like:

if {?MyDatePromptParamter} = "Weekly" then
{table.date} in lastfullweek
else
if {?MyDatePromptParamter} = "Bi-Weekly" then
{table.date} in minimum(lastfullweek)-7 to maximum(lastfullweek)
else
if {?MyDatePromptParamter} = "Use specified dates" then
{table.date} in {?MyParmDates}
...etc...

Working out dates for the periods required, then have a seperate date field with the display description only turned on.

So the user sees 2 prompts, one asking for the time period type, one choice being "Use specified dates", and in that instance must complete the date prompt for accurate reporting.

-k

 

[CHDolbey]

First thank you for the reply guys I see where your going with how to set this up now another question I have with the way you are showing Weekly and Bi-Weekly right there. Now that is just going to show the dates for the last week or 2 weeks correct?

What there asking for is say for example they want to check all the dates between January and May of this year for example. They also want it to be sorted on the date field Semi-Monthly. They want to be able to choose all this through parameters.

Will this solution above take care of this since they want to choose the dates to list and sort it based on the date grouping?

Sorry for the trouble!

 

[lbass]

You could replace the date group with a group on a formula like the following formula, where {?interval} is a string parameter with the choices you mention above. However, you would need to build in whatever your definition is of "semi-monthly" and "semi-annually".

select {?interval}
case "Annually" : date(year({table.date}),1,1)
case "Semi-annually" :
(
if {table.date} < date(year({table.date}),7,1) then
date(year({table.date}), 1,1) else
date(year({table.date}),7,1)
)
case "Quarterly" :
dateserial(year({table.date}), (datepart("q",{table.date})*3)-2,1)
case "Monthly" : {table.date}-day({table.date})+1
case "Semi-monthly" :
(
if day({table.date}) in 1 to 15 then
{table.date}-day({table.date})+1 else
{table.date}-day({table.date})+15
)
case "Bi-weekly" :
(
if remainder(datepart("ww",{table.date}),2) = 0 then
dateadd("ww", datepart("ww",{table.date})-1,
date(year({table.date}),1,1)-dayofweek(date(year({table.date}),1,1)))+1 else
dateadd("ww", datepart("ww",{table.date})-2,
date(year({table.date}),1,1)-dayofweek(date(year({table.date}),1,1)))+1
)
case "Weekly" : dateadd("ww", datepart("ww",{table.date})-1,
date(year({table.date}),1,1)-dayofweek(date(year({table.date}),1,1)))+1
case "Daily" : {table.date}

-LB

 

[CHDolbey]

Thank you guys for the help I think you helped me fine exactly what I needed!

Thanks!