5 Replies Latest reply on Dec 1, 2015 6:07 AM by mr.voyager

    Virtual List Problem

    mr.voyager

      Hello, Dear All,

       

      I’m trying to crate a cross tab report ( 300 Rows X 15 Columns ) with Virtual List Technique which is related to just one table.

      I’m getting client names in first column without problem.

      But, second column data is not matching with first column (ClientNames).

       

      Also, i wonder what happened to when ExecuteSQL didn’t find anything in the query. Because Most of the column2 data are must be blank.

       

      Please help me to work it out.

       

      Recep

       

       

       

      Here is my report script;

       

       

      Set Variable ( $ClientList; Value:

       

      Let([

      query = “SELECT DISTINCT MusteriIsmi FROM vl_UPLITEMS WHERE PlanTarihi = ? and \"_idOgun\" = ?";

      result = ExecuteSQL( query ; "" ; "" ; UPL::PlanTarihi; UPL::_idOgun)

      ];

      result

      )

       

      If [ not IsEmpty ( $ClientList ) ]

        Set Variable [ $ItemCount; Value: ValueCount ( $ClientList ) ]

       

        Loop

       

        Exit Loop If [ Let ( $i = $i + 1; $i > $ItemCount ) ]

       

        #COLUMN1

        Set Variable [ $Client; Value: GetValue ( $ClientList; $i ) ]

        Set Variable [ $$COLUMN1; If( IsEmpty( $$COLUMN1 ) ; $Client ; $$COLUMN1 & ¶ & $Client ) ]

       

        #COLUMN2

        Set Variable [ $Portion; Value: Let([

        query = “SELECT Porsiyon FROM vl_UPLITEMS WHERE PlanTarihi = ? and \"_idOgun\" = ? and \"_idYMK\" = ? and MusteriIsmi = ?";

        result = ExecuteSQL( query ; "" ; "" ; UPL::PlanTarihi; UPL::_idOgun; 3148; $Client)

        ];

        result

        ) ]

        Set Variable [ $$COLUMN2; If( IsEmpty( $$COLUMN2 ) ; $Portion ; $$COLUMN2 & ¶ & $Portion ) ]

       

        End Loop

       

        Refresh Window

       

      End If