Format help

[priyanthan]

I have a report that looks like the following

Terminal Change ACTIONFLG CHGED
TMK MMK
990000 2/25/2000 1 TMK
990000 6/30/2008 11
990000 2/25/2001 1 TMK
990000 3/5/2010 11


i need to show as


Terminal Change ACTIONFLG CHGED
TMK
990000 2/25/2001 1 TMK
990000 3/5/2010 11

i.e.
i need to show the latest date.
i used the following formula, but it always shows the latest date out of the 4 dates.

If {TAB.CHGED} = 'TMK' Then
If {TAB.ACTIONFLG}= 1 Then
MAXIMUM({TAB.ACTIONTS})

working with CR 8.5 and DB2.

Can anyone please help me with it.

 

[fisheromacse]

If you only need the maximum value for each of the codes (MMK/TMK) for each terminal(?), i would suggest using SQL Expressions.

//{%TMK}
(
Select max("ACTIONTS")
from TAB
where ({TAB.CHGED} = 'TMK'
)

and

//{%MMK}
(
Select max("ACTIONTS")
from TAB
where ({TAB.CHGED} = 'MMK'
)

 

[priyanthan]

Thanks fisheromacse,

Can you please tell me how to use SQL expressions as i haven't done this before.

 

[fisheromacse]

in xi, on the right side of the screen in the Field Explorer, right click on SQL expression Fields and choose new and type/copy/paste in the formulas. You may need to add additions to the 'where' clause in order to increase the specificity of your data returned.


I didn't notice when i 1st replied, but you are using 8.5 and i have no experience with that version so i hope you have the SQL expression option.

I am sure someone here will be able to add clarification to this thread.

 

[priyanthan]

there is SQL expression in 8.5. as you said i added the SQL that you mentioned. but it is picking the latest date of the TMK and atest date of the MMK for each TMK and MMK.

Am i missing anything here.

 

[fisheromacse]

a few more thoughts....

you may need to add to the WHERE clause...like this:

//{%TMK}
(
Select max("ACTIONTS")
from TAB
where ({TAB.CHGED} = 'TMK'
AND {TAB.ACTIONFLG}= '1'
)

//{%MMK}
(
Select max("ACTIONTS")
from TAB
where ({TAB.CHGED} = 'MMK'
AND {TAB.ACTIONFLG}= '11'
)


you can use the sql expression in formulas such as:

//{@checkTMK}
IF {TAB.ACTIONTS}={%TMK} then {TAB.ACTIONTS} else ""

//{@checkMMK}
IF {TAB.ACTIONTS}={%MMK} then {TAB.ACTIONTS} else ""

 

[priyanthan]

thanks for your thoughts, I guess now i could work on this now

 

[priyanthan]

Hello fisheromacse

I tried your suggestion but i always getting the maximum date value in the table.

this is what i'm using,
//%TMK
(
select max(actionts) from KEY K, posh.terminal t where k.id = t.ppadserno and ACTIONFLG = 1
)

Can you please help me with this.

Thanks.

 

[fisheromacse]

possilby the expression needs refined......

//%TMK
(
select max(actionts) from KEY K, posh.terminal t where k.id = t.ppadserno and ACTIONFLG = 1 and {TAB.CHGED} = 'TMK'
)


i thought you wanted to max date for each TMK and MMK.

Perhaps i missed something.

 

[priyanthan]

I need the max date for each TMK and MMk, but i'm getting the max date from the table to all the TMK and MMK.

i tried the one you just posted, but i'm getting the max date that is in the table based on the "where" clause.

Any clue...

Thanks,

 

[lbass]

If you want the max date based on a group on terminal, then you need to add a clause into the SQL expression, assuming it is in the "posh" table:

and
t.terminal = posh.terminal

Note that SQL expressions get data directly from tables, so you might also have to build in criteria used in your main reports selection criteria.

Another approach would have been to use formulas like this:

If not isnull({TAB.CHGED}) and
{TAB.CHGED} = 'TMK' and
{TAB.ACTIONFLG} = 1 Then
{TAB.ACTIONTS}

Then insert a maximum on this at the group level.

-LB

 

[fisheromacse]

the SQL expression will return the one max date that meets the where clause criteria you enter.

What is the grouping in your report?
If you group on ID# (if there is such a unique field) and place the SQL expression in the group header, what happens?

You will need a SQL expression for TMK and another for MMK.

Your most recent post contains tables/fields that were not in your original post.
When posting, please provide complete information about your data, your report, your current results an the desired results upfront. It may save much time later on for everyone involved.