Dynamic grouping

[sikkilamkiran]

hi,
I have a requirement where i have a static parameter which having the following columns
Control ID - Number
Control Name - char
Receive date - date
Data Source -char

I would like to use the value selected by user as a group in my report.

I tried to create a formula where
if {?Group By} = 'Control ID' then
{Workflow_BE.Control ID}
ELse if {?Group By} = 'Control Name' then
{Workflow_BE.Control Name}
ELse if {?Group By} = 'Received Date' then
{Workflow_BE.User Task Received Date}
ELse if {?Group By} = 'Data Source' then
{Workflow_BE.Target Data Source Name}
else
{Workflow_BE.Control Rating Name}

its giving me error saying
A DATE_TIME IS REQUIRED HERE. Probably due to different dataatypes in the same formula.

Please help its urgent.

Thanks in Advance.
Kiran

 

[paulmarr]

Kiran,

You answered your own question - they must be the same data types.

Use ToText({Workflow_BE.User Task Received Date},"dd/MM/yyyy"
) for the Received Date output and all will work fine.

Cheers
paulmarr

 

[MisterMo]

That will however cause the sorting to be alphabetical for every group dates included.

if you want more flexibility you could create a formula for each field so you can keep their original format and you can sort each one differently.

eg.
@ByControlId
if {?Group By} = 'Control ID' then
{Workflow_BE.Control ID}

@ByDate
if {?Group By} = 'Received Date' then
{Workflow_BE.User Task Received Date}

and so on

then create a group for each formula and suppress them all

depending on the value of your parameter only one of them will be triggered.





-Mo