Need to sort my records by a calculated formula

[Former Member]

Post Author: n8than1

CA Forum: Formula

Hello,

I have a formula that calulates the last order date or last inventory transaction date and returns which ever date is the earliest. I'm then subtracting todays date from that date to give me the number of days since any activity. How can I sort my report by this formula? It's a number field and I have 2 groups , Location (group 1) and part number (group 2). Normally I'd use the record sort tool, but my formula field is not showing up as a sortable field.

Thanks

[Former Member]

Post Author: pandabear

CA Forum: Formula

N8,

Keep it simple and let the query do the work. In my db2 world it looks like this:

SELECT SSH400.ACTV_NBR, {fn CURDATE()} - date(Max(SSH400.LAST_UPDT_DTE))

FROM SYSINT.SSH400

Group by SSH400.actv_nbr

This returns "ACTV_NUM" and the "calculated by the big bad (fast) server" number(of days) that

you're looking for. Sort or group any way you want and go to lunch early

Hope this helps,

The Panda

[Former Member]

Post Author: Charliy

CA Forum: Formula

If you're using some type of summary operation, or a NEXT or PREVIOUS, then that formula will not be available for sorting or grouping. My guess is that you had to do a summary to get the latest date in the file.

Solution: Make a main "driver" report that gets that summary information, then make your real report a subreport, with the summary data passed as parameters.

[Former Member]

Post Author: n8than1

CA Forum: Formula

Panda,

Thanks for responding! Your close!

Normally they do show up in the "Grouping" and "Sorting", but for some reason this formula is not and I don't know why.

I am using an if, then ,else to calulate the number of days. Could this be the problem?

How can I reverse the grouping?

[Former Member]

Post Author: pandabear

CA Forum: Formula

Hey N8,

I guess I need more info, 'cause I tried it and it sorted ok (kinda....).

I made 2 groups and then inserted my formula in the details below the 2nd group:

CurrentDate - Maximum ({MVH499.LAST_UPDT_DTE}, ({MVH499.FEE_ID}) )

Naturally, only one date shows up in this structure, but it did show up in the "Grouping" and "Sorting" options.

You could reverse the grouping, making the formula the top group if you wanted.

Is this close, or did I (probably) miss what you're looking for ?

The Panda