Building On Screen Report Generator

[incendio]

Hi guys,

My app has a lot of reports ( displaying data in a form of table). Each report has its own Sub.
This Sub responsible to collect data and do some processing.
The codes are long and messy, imagine, codes for more than 20 reports, also time consuming to create those sub.

With the help of AI, I was able to replaced data processing with a single query.
This is a huge advantage, I can build a report generator that will create any kind of reports based on data.

Here are 2 tables structures needed for a report generator

CREATE TABLE M_REPORT
(
  ID INTEGER NOT NULL,
  REPORT_NM TEXT NOT NULL,
  DSCP TEXT,
  QRY TEXT, '---> sql query
  PARAMS text, '---> sql params
  PRIMARY KEY (ID)
);

CREATE TABLE M_RPT_COL (
    ID INTEGER,
    ID_RPT INTEGER NOT NULL,
    COL_TITLE TEXT,
    COL_TYP INT, '---> to format display value according to column typ
    COL_WIDTH int,
    FLD_NM Text, '---> Field name in sql so you can retrive its value by db.getString(FLD_NM)
    FTNT Text 'Foot note for table, to store grand total, etc
);

Here are some example codes in report generator

Sub ShowReport(RptName as string)
   Dim RptId as int = getRptId(RptNm)
   Dim Sql as int = getSql(RptId)
   Dim SglParams() as string = getSqlParams(RptId)
   Dim ColHeader() as string = getColHeader(RptId)
   Dim ColWidth() as int = getColWidth(RptId)
   Dim FootNote() as string = getFootNote(RptId)
  
   'Rest of code, call your table, can be B4XTable if don't need total, in my case UltimateListView
End Sub

Sub getSqlParams(RptId as int) As String() 'here you need to supply variable values for your report
    Dim Params As String = SQL.ExecQuerySingleResult2("select PARAMS from M_REPORT where ID = ?",Array As String(RptId))
    
    Dim parts() As String = Regex.Split(",", Params)
    Dim SqlArgs(parts.Length) As String
    For i = 0 To parts.Length - 1
        SqlArgs(i) = RptParams.Get(parts(i))
    Next
    Return SqlArgs
End Sub

Codes are more cleans and more important is, time to build each report is much more faster.

 

[LucaMs]

getRptId(RptNm)

That and others are functions, not properties.
I assume this is AI-generated code, using "Java notation", but in B4X you should use the uppercase GetRptId.