×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

group flip on parameter - date order goes wrong

group flip on parameter - date order goes wrong

group flip on parameter - date order goes wrong

(OP)
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?




RE: group flip on parameter - date order goes wrong

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

RE: group flip on parameter - date order goes wrong

(OP)
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)

RE: group flip on parameter - date order goes wrong

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

RE: group flip on parameter - date order goes wrong

(OP)
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

RE: group flip on parameter - date order goes wrong

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

RE: group flip on parameter - date order goes wrong

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

RE: group flip on parameter - date order goes wrong

(OP)
Thanks I have this working fine now

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close