Problem with expression in datafield

Posted by: patrick86 on 4 August 2017, 3:01 pm EST

  • Posted 4 August 2017, 3:01 pm EST

    Hello,

    i'm trying to divide value 1 by value 2, if one of the two values is 0 it wil generate an error cause division by zero isnt allowed.
    How can i resolve this?
    My current expression:
    =((RealDuration > 0 ) ? ((RealDuration / Budget) + 1) : 1 )

    But now i get:
    Error 2108 : An error occurred while executing control script [ Extended Info:Section : Detail , Event : Format , Line number: 0  Description:Invalid character]
  • Replied 4 August 2017, 3:01 pm EST



    The Datafield property doesn't support conditional
    statements . You need to have the conditional expression in the FetchData()
    event. Try this snippet in FetchData













    Private Sub ActiveReport_FetchData(EOF As Boolean)


          If Fields(“RealDuration”).Value  > 0  Then
             Fields(“RealDuration”).Value
    = _
                Fields(“RealDuration”).Value / Fields(“Budget”).Value + 1 
         
    Else
             Fields(“RealDuration”).Value
    = 1
        End If



    End Sub

  • Replied 4 August 2017, 3:01 pm EST

     Ali - DD wrote:

    The Datafield property doesn't support conditional
    statements . You need to have the conditional expression in the FetchData()
    event. Try this snippet in FetchData













    Private Sub ActiveReport_FetchData(EOF As Boolean)


          If Fields(“RealDuration”).Value  > 0  Then
             Fields(“RealDuration”).Value
    = _
                Fields(“RealDuration”).Value / Fields(“Budget”).Value + 1 
         
    Else
             Fields(“RealDuration”).Value
    = 1
        End If



    End Sub



    I dont want to change the value from RealDuration or Budget, cause these values are at another place in the report. Is it possible to add fields? So the result can be placed in a new field.
  • Replied 4 August 2017, 3:01 pm EST

    You can add a field to the Fields collection in the DataInitialize event:

    Me.Fields.Add("NewField")

    and in the above you can set it's value instead of the RealDuration:

    Private Sub ActiveReport_FetchData(EOF As Boolean)


          If Fields(“RealDuration”).Value  > 0  Then
             Fields(“NewField”).Value
    = _
                Fields(“RealDuration”).Value / Fields(“Budget”).Value + 1 
         
    Else
             Fields(“NewField”).Value
    = 1
        End If



    End Sub

    Now you can set the DataField of a textbox on the report to this new field.
  • Replied 4 August 2017, 3:01 pm EST

     Ali - DD wrote:
    You can add a field to the Fields collection in the DataInitialize event:

    Me.Fields.Add("NewField")

    and in the above you can set it's value instead of the RealDuration:

    Private Sub ActiveReport_FetchData(EOF As Boolean)


          If Fields(“RealDuration”).Value  > 0  Then
             Fields(“NewField”).Value
    = _
                Fields(“RealDuration”).Value / Fields(“Budget”).Value + 1 
         
    Else
             Fields(“NewField”).Value
    = 1
        End If



    End Sub

    Now you can set the DataField of a textbox on the report to this new field.


    Error 5000 : Error in processing report.
     [ Extended Info:SubReport for control MI_Productiviteit failed. Failure due to :Source: Report , Event: OnDataInitialize , Line number: 58  Description:Object doesn't support this property or method: 'Me.Fields']

    My recordset is rs, and it rs.fields.add also generates this error
  • Replied 4 August 2017, 3:01 pm EST

    Are you using a standalone RPX report that uses scripting? Can you compress and post the DSR/DSX files of these reports or the RPX files if you are using scripting.
  • Replied 4 August 2017, 3:01 pm EST

     Ali - DD wrote:
    Are you using a standalone RPX report that uses scripting? Can you compress and post the DSR/DSX files of these reports or the RPX files if you are using scripting.


    That will be hard, cause the files contains customer names, and passwords for databases ... but i can paste my subs/functions....it's a report with subreports, so it are multiple rpx files
    I think this is what you requested/need:

    Sub OnDataInitialize
    dim src
    dim dom
    dim ctlDetail
    dim dtmDate

        if vartype( rpt.UserData ) = vbString then
            Set dom = createObject( "MSXML2.DomDocument" )
            call dom.LoadXML( rpt.UserData )
            sqlUser = dom.selectSingleNode( "//report/database/@user" ).text
            sqlPassword = dom.selectSingleNode( "//report/database/@password" ).text
            sqlServer = dom.selectSingleNode( "//report/database/@server" ).text
            sqlDatabase = dom.selectSingleNode( "//report/database/@database" ).text

            set nd = dom.selectSingleNode("//report/params")
            set nd2 = nd.selectSingleNode("param[@name='DATE']")
            if not nd2 is nothing then
                dtmDate = cdate(nd2.getAttribute("value"))
            end if
        else
            sqlUser = "CENSORED"
            sqlPassword = "CENSORED"
            sqlServer = "CENSORED"
            sqlDatabase = "CENSORED"

            dtmDate = cdate("2005-04-21")
        end if
        m_dtmDate = dtmDate

        sqlConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & sqlUser
        sqlConnection = sqlConnection & ";Password=" & sqlPassword
        sqlConnection = sqlConnection & ";Initial Catalog=" & sqlDatabase
        sqlConnection = sqlConnection & ";Data Source=" & sqlServer
      
        m_strCon = sqlConnection

        rpt.ScriptDebuggerEnabled = true
            rpt.Sections( "Detail" ).Controls( "DataCtl" ).ConnectionString = sqlConnection

        set cn = createObject("ADODB.Connection")
        call cn.Open(sqlConnection)

        set cm = createObject("ADODB.Command")
        'cm.CommandTimeOut = "550"
        cm.CommandText = "A2PCustomerMI"
        cm.CommandType = 4

        set pm = cm.CreateParameter("dtmDate", 133, 1, , dtmDate)
        call cm.Parameters.Append(pm)

        set pm = cm.CreateParameter("lngRSID", 3, 1, , 2)
        call cm.Parameters.Append(pm)

        set cm.ActiveConnection = cn
        set rs = cm.Execute()
        Me.Fields.Add("RealDurationBudget") ' ERROR HERE!
        if not isEmpty(rs) then
            set rpt.Sections("Detail").Controls("DataCtl").RecordSet = rs
        end if
    End Sub


    seems very basic to me ....

    also tried:
    rpt.Fields.Add("NewField")

    Generates an error, Type mismatch: 'Fields'
  • Replied 4 August 2017, 3:01 pm EST

    Patrick,
    I don't know why you are getting this error but are you sure that that line is source of the error and not somewhere else in the code? What do you have in FetchData? As you can see from the attached sample adding a field shouldn't throw this error.
    If you can't post the report, please you can send it to me at activereports.support AT datadynamics.com and refer to this thread.

    2007/01/Sample-2.zip
  • Replied 4 August 2017, 3:01 pm EST

     Ali - DD wrote:
    Patrick,
    I don't know why you are getting this error but are you sure that that line is source of the error and not somewhere else in the code? What do you have in FetchData? As you can see from the attached sample adding a field shouldn't throw this error.
    If you can't post the report, please you can send it to me at activereports.support AT datadynamics.com and refer to this thread.


    Thnx a lot for your help and sample, the problem seems to be solved. It's a very complex report with 4 subreports and graphs about management and i'm just a software engineer, but the bugs are resolved now. The problem was at FetchData, my recordset was declared within the OnDataInit sub, so it wasnt accessable by OnFetchData....
    And some other minor bugs but i solved it.... thnx :)
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels