• Charts with "Average per document" shows incorrect values

    By Olli Kämäräinen 1 decade ago

    Report runs fine but there is one problem when data is written to Excel.

    Average values are written as text, so number 43,320984093 turns somehow to 43320984093.

    I made correction to code this way:

    Chart-SubLibrary –> Sub WriteDataToExcel

    Around line 29 i added following code:

    %REM

    OPK 5.11.2009 ADDED NUMBER CONVERSION FOR AVERAGE CHARTS<br/>

    %END REM

    If graphProfile.strDisplayResultAs = "AVERAGE" Then

    If IsNumeric(displayArray(intRow, intColumn) ) Then<br/>
    xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = CDbl( displayArray(intRow, intColumn) )<br/>
Else<br/>
    xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = displayArray(intRow, intColumn) <br/>
End If<br/>

    Else

    xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = displayArray(intRow, intColumn)       <br/>

    End If



    Same config didn't show values in flash-chart.

    This is formula for average value:

    (@Date(EndDate)-@Date(StartDate))/ 86400

    • Correction

      By Olli Kämäräinen 1 decade ago

      I already had to change code little bit because some of the averages are "true" when tested for "IsDate".

      So it's better to move If statement little bit higher:



      For intColumn = 0 To intColumnCount

      If graphProfile.strDisplayResultAs = "AVERAGE" Then

                  If IsNumeric(displayArray(intRow, intColumn) ) Then<br/>
                xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = <br/>

      CDbl( displayArray(intRow, intColumn) )

                  Else<br/>
                xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = <br/>

      displayArray(intRow, intColumn) 

                  End If<br/>
        Else<br/>
            If Isdate( displayArray(intRow, intColumn) ) Then<br/>
                xlsheet.Range(col(intColumn)+ Cstr(intRow + intStartDataRow)).value = <br/>

      Cdat( displayArray(intRow, intColumn) )

                      <br/>
            Else<br/>
                xlsheet.Range(col(intColumn)+ CStr(intRow + intStartDataRow)).value = <br/>

      displayArray(intRow, intColumn) 

                  End If<br/>
        End If<br/>



      Next

      • Love your work

        By David Jeyachandran 1 decade ago

        Thanks Olli!



        I will add this code into the next release! Really appreciate your work.



        Kind Regards

        David