COGNOiSe.com - The IBM Cognos Community

Hello All

We have a requirement to create a package which is user friendly where users can enter date to select most recent row based on users requirement.

Here is how data in our table ( People soft Job table) ..sample data

Emplid          EffectiveDatetime    Effective Sequence
***************************************
0011            2012-01-01  10AM           0
0011            2012-01-01  11AM           1
0011            2012-02-01  11AM           0
0011            2013-01-01  11AM           0
0011            2014-01-01  11AM           0

0012            2012-01-01  10AM           0
0012            2012-01-01  11AM           1

0012            2012-02-01  11AM           0
0012            2012-03-01  11AM           0
0012            2013-01-01  11AM           0
0012            2014-01-01  11AM           0

Currently if we need a maximum row based on user date selection we have to use Max( effectivedatetime) other calculation
in report studio .in order to give report to end users.

Which we want to give feature a self service in Query Studio ..where they can just enter date FM should able to do those calculations
based on date from user.

with above example if user enter date as 2013-01-01 ..we do effective <=2013-01-01 , do max..do present these rows in report

0011            2013-01-01  11AM           0
0012            2013-01-01  11AM           0


I'm using a query subject ..in which I'm using SQL

SELECT B.EMPLID, B.EFFDT ,B.EFFSEQ
FROM PS_JOB B

WHERE (
         B.EFFDT = (
SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.EMPL_RCD = B_ED.EMPL_RCD
          AND B_ED.EFFDT <= #prompt ('asofdate','date') #)
    AND B.EFFSEQ =
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
        WHERE B.EMPLID = B_ES.EMPLID
          AND B.EMPL_RCD = B_ES.EMPL_RCD
          AND B.EFFDT = B_ES.EFFDT))

Due to bug in FM 10.2 (when I validate this ..it asks for a prompt value even after entering value it's askingagain) not able to validate and check if this will work or not .

Will this work or if any 1 does this kind of work in FM in your place . Please do let me know ASAP

Rgds
J

try

prompt('asofdate','date','2013-01-01')

the help information says, giving a default value will make the prompt optional. hopefully the bug during validation will go away and you will be able to check

Thx..If I use the same code as u mentioned ..it's looking for some , etc ..didn't work out.