Account balances at given date

2005-12-16
2013-05-02
  • Graeme Pyle
    Graeme Pyle
    2005-12-16

    Hi all,

    I'm trying to get a report with all account balances at a given date in Compiere, but can't figure out how.

    This is what is referred to in my country as a Trial Balance (obviously different to the Compiere TB).

    Can anyone give me a few pointers on where to look? I've tried the "Account Viewer", but I can't get opening balances with that. I've tried playing with the Compiere TB, but I can't get rid of the detail.

    In my country, a TB (list of all account balances at a given date) is very important to accountants - surely Compiere caters for that? Is there anything I'm missing perhaps?

    Thanks,
    Graeme

     
    • Kathy Pink
      Kathy Pink
      2005-12-16

      did you look at the statement of accounts or try a financial report?

       
    • Graeme Pyle
      Graeme Pyle
      2005-12-19

      Ah yes, I forgot that you can have Dr and Cr only columns in the "Report Column Sets" - problem solved.

      Our implementation is going well - looking forward to partnership and other implementations in the new year.

      Regards,
      Graeme

       
    • albert
      albert
      2005-12-20

      Now Compiere’s finReport Can’t specific the date you want, only use the fix date is end of period
      ----------------------------------------
      FineReport.java
      ----------------------------------------
      String sql = "SELECT p.C_Period_ID, p.Name, p.StartDate, p.EndDate, MIN(p1.StartDate) FROM C_Period p "
                  + " INNER JOIN C_Year y ON (p.C_Year_ID=y.C_Year_ID),"
                  + " C_Period p1 "
                  + "WHERE y.C_Calendar_ID=?"
                  + " AND p.PeriodType='S' "
                  + " AND p1.C_Year_ID=y.C_Year_ID AND p1.PeriodType='S' "
                  + "GROUP BY p.C_Period_ID, p.Name, p.StartDate, p.EndDate "
                  + "ORDER BY p.StartDate";
      PreparedStatement pstmt = null;
      try
      {
      pstmt = DB.prepareStatement(sql, null);
      pstmt.setInt(1, m_report.getC_Calendar_ID());
      ResultSet rs = pstmt.executeQuery();
      while (rs.next())
      {
      FinReportPeriod frp = new FinReportPeriod (rs.getInt(1), rs.getString(2),rs.getTimestamp(3), rs.getTimestamp(4), rs.getTimestamp(5));
      ------------------------------------------------
      FinReportPeriod.java
      ------------------------------------------------
      public String getPeriodWhere ()
      {
      StringBuffer sql = new StringBuffer ("BETWEEN ");
      sql.append(DB.TO_DATE(m_StartDate))
      .append(" AND ")
      .append(DB.TO_DATE(m_EndDate));
      return sql.toString();
      }    //    getPeriodWhere

       
    • albert
      albert
      2005-12-26

      Compiere provides a comprehensive solution for small-to-medium sized enterprises (SME) in distribution and services ......we evolution Compiere from small-to-medium to large-to-huge sized system, define account item not base-document only we use target-document, generate po/so add-on by user-id to prevent conflict for same time same function has a lot of concurrent user.
      Thanks Kathy Pink, Jorg Janke give us a path to be a huge system.
      Taiwan Support Team Ph.D. Albert  0958-222-948 Skype DrAlbert