Parameter to choose day, week, month or year for cross tab 2

[javedi]

Hello

I have created a cross tab report that shows dates in the rows. Although I am able to group on day, week, month etc. within the cross tab expert, I would like to create a paramater to allow the user to choose how they want the data to be grouped.

Does anyone have any idea's how this can be achieved?

Many thanks,
Javedi

 

[fisheromacse]

I think that you would need to do multiple crosstabs then have all of the crosstabs suppressed except for the one desired based upon the user parameter choice.

 

[lbass]

You can create a formula like this to use as your row:

select {?Interval}
case "Day" : {table.date}
case "Week" : {table.date}-dayofweek({table.date})+1
case "Month" : {table.date}-day({table.date})+1
case "Quarterly" : dateserial(year({table.date}),(datepart("q",{table.date})*3)-2,1)
case "Yearly" : date(year({table.date}),1,1)

-LB

 

[javedi]

Hi LB

Thanks for the formula.

An errror is returned from the formula below, highlighting the "Day" and showing "A date range is required here."

select {?Period}
case "Day" : {Enquiry.ReceivedDate}
case "Week" : {Enquiry.ReceivedDate}-dayofweek({Enquiry.ReceivedDate})+1
case "Month" : {Enquiry.ReceivedDate}-day({Enquiry.ReceivedDate})+1
case "Quarterly" : dateserial(year({Enquiry.ReceivedDate}),(datepart("q",{Enquiry.ReceivedDate})*3)-2,1)
case "Yearly" : date(year({Enquiry.ReceivedDate}),1,1)

Any ideas why?

Thanks
Javedi

 

[lbass]

{?Period} should be set up as a string parameter and do not allow range values--you should have it set up for discrete values only.

-LB

 

[javedi]

Thanks LB

This worked well.

Javedi