Search code examples
crystal-reportsreport

Filtering function in formula field

I am new to Crystal Reports (2016) and I´m working on an evaluation for our department now. From CR I access data in a SAP Info set table. This works fine so far.

My data has the following the format and is read line by line:

Source data

As you can see, that sometimes there is a Q-Number with Position 100 AND 105, sometimes just with Position 100. So sometimes there are duplicates of the Q-numbers. What I need is, that in case of an exisiting position 105 to just use this line. If there is no position 105, use the line with position 100. This should eleminate the duplicates and only leave the information I need.

After putting the new formula field in my report, it should show the data like this (column on the right):

Needed data

I tried that already with the "onlastrecord"-function, but without the needed result. It works to just use the line with position 105 in case of both positions. But Q-numbers with position 100 only are not displayed.

This is my code so far:

If onlastrecord=true and (tonumber({Z_QM_INFOSET_CR.VIQMFE-POSNR})=0100)

then {Z_QM_INFOSET_CR.QMEL-QMNUM} else

if (tonumber({Z_QM_INFOSET_CR.VIQMFE-POSNR})=0105)

then {Z_QM_INFOSET_CR.QMEL-QMNUM}

I hope my case is understandable..

Thanks a lot in advance!

Solution

  • I understand you need a formula that prints the value in these situations:

    1. show the q-number when position is 105
    2. show the q-number when position is 100 and there is no 105 for the same q-number

    So, i suggest an approach to check the next record and a contour condition for the last one.

    Try this formula:

    if {Z_QM_INFOSET_CR.VIQMFE-POSNR} = "0105" 
then {Z_QM_INFOSET_CR.QMEL-QMNUM}
else if onlastrecord and {Z_QM_INFOSET_CR.VIQMFE-POSNR} = "0100"
then {Z_QM_INFOSET_CR.QMEL-QMNUM}
else if {Z_QM_INFOSET_CR.VIQMFE-POSNR} = "0100"
and next({Z_QM_INFOSET_CR.QMEL-QMNUM}) <> {Z_QM_INFOSET_CR.QMEL-QMNUM}
then {Z_QM_INFOSET_CR.QMEL-QMNUM}
else ""