group flip on parameter - date order goes wrong

[mart10]

I am using CR 16 sp2

I want to create a parameter report based on 2 selections within a parameter called " @Analyse By ", currency or date by which the report will flip the order of the grouping, its a neat way of having one report and showing in different ways

So I have Group_Lvl1
if {?Analyse By} = 'Currency' then
{Currencies.SwiftCode}
else
{@Date_TransactionDate}


and Group_Lvl2
if {?Analyse By} = 'Currency' then
{@Date_TransactionDate}

else
{Currencies.SwiftCode}


WHERE {@Date_TransactionDate} is ToText(Date(1979,12,31) + {TransactionHeader.ActualDateNumber})

I had to put ToText on the above formula because my 2 group formula insisted on being in String format


The report flip fine BUT the trouble is it does not now sort in date order - it used to when the formula was in date format and not string

So how do I get around this?

 

[IanWaterman]

Create a separate formulae each for display and sort/group.

as this is used for sort/group {@Date_TransactionDate} make stringdate format for this formula yyyymmdd and this will sort correctly.

Ian

 

[mart10]

Sorry either I didnt understand or did something wrong but its made no difference at all. The group has to be a string and then it sorts by that group which gives incorrect sort. Or are you saying I can overide the string group with this other totext group (display)

 

[lbass]

Change the formula to:

ToText((Date(1979,12,31) + {TransactionHeader.ActualDateNumber}),"yyyyMMdd")

Note that I added parens--not sure if they are necessary. Didn't test.

-LB

 

[mart10]

No not working I get same result and I amgetting confused now on what has been set up Please can you describe in more detai what I am attemting to code up

thanks

 

[IanWaterman]

Did you modify the formula as suggested by LB

ToText((Date(1979,12,31) + {TransactionHeader.ActualDateNumber}),"yyyyMMdd")

Please show out put for this formula.
You should be using this formula to group your data. Remove any other date groupings
Use your original formula to display date on report tableau.

Ian

 

[lbass]

If you are showing your actual formulas in this thread, note that your second formula references “Currency” when it should say “Date”. If this doesn’t fix the issue, then please show your actual formulas again and the output as Ian suggested.

-LB

 

[mart10]

Thanks I have this working fine now