COGNOiSe.com - The IBM Cognos Community

Hello all,
this is my first topic and I'm newbie. I use cognos 10.2.
After I get the excel report, run the macro VBA to have the final report.
I would like do some cycle IF and FOR in Cognos directly instead to use the macro but I have some difficulty.
The table is somethink like this:

Part              Code                  Description           check
AAAA         BGQ              descr1               descr2
AAAA         BGS                   descr2   
BBBB              BFN                   descr3   

How I can translate cycle below in cognos?

For i = 2 To NRows

        If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
           
           Cells(i, 4).Value = Cells(i + 1, 3).Value
        End If
    Next i

Thanks in advance

Quote from: Effepi on 01 Sep 2019 10:19:57 AM
Hello all,
this is my first topic and I'm newbie. I use cognos 10.2.
After I get the excel report, run the macro VBA to have the final report.
I would like do some cycle IF and FOR in Cognos directly instead to use the macro but I have some difficulty.
The table is somethink like this:

Part              Code                  Description           check
AAAA         BGQ              descr1               descr2
AAAA         BGS                   descr2   
BBBB              BFN                   descr3   

How I can translate cycle below in cognos?

For i = 2 To NRows

        If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
           
           Cells(i, 4).Value = Cells(i + 1, 3).Value
        End If
    Next i

Thanks in advance

Hi,

If I am understanding correctly, the requirement here is to repeat the value in the check column for all rows with the same value in the Part column?

You could try changing the check column in your list to be a calculated item:

maximum([check] for [Part])

Does this give you what you need?

MF.

Hi MF,

the part could have 2 descriptions so if part1 = part2 then check1 = descr2

My final goal is having a new column with "descr1 & descr2" (with concatenate)

Thanks for your support
FP

Quote from: Effepi on 04 Sep 2019 06:47:02 AM
Hi MF,

the part could have 2 descriptions so if part1 = part2 then check1 = descr2

My final goal is having a new column with "descr1 & descr2" (with concatenate)

Thanks for your support
FP

Hi,

I'm sorry - I'm not quite clear on what your requirement is here. Can you provide a clear explanation of what you need to do?

MF.

Probably not what you want to hear, but looking at what you've got makes me think that a stored procedure in your database is the way to go. Cognos doesn't really handle row by row iterations.

OK, It's as I thought.
It could be resolved converting rows in colunms as the descriptions are just 10.
I will check

Thanks to all.

you are trying to compare the value of a row with the value in the next row?

the suggestion of BigChris is good. having said that, this is just at the top of my head and i am not sure if this will even work so take at your own risk...

1. add a row column...
2. create two identical queries Q1, Q2

select ..., Q2.descr as check1
from Q1 inner join Q2 on Q1.row = Q2.row+1 and Q1.Part = Q2.Part

It's difficult to know if any of the responses are actually related to your requirements.  Can you please describe your requirements?