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
Here are some example codes in report generator
Codes are more cleans and more important is, time to build each report is much more faster.
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
B4X:
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
B4X:
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
