How to Create Automated Hospital RL Reports from SIMRS

At the beginning of every month, the medical records team of a hospital with 120 beds and around 18,000 outpatient visits usually spends 4–6 working days just to recap the RL (Hospital) report before the upload deadline to e-SIRS on the 10th. Visit data is pulled manually from SIMRS into Excel, ICD-10 diagnoses are copied one by one, and then BOR, LOS, and TOI figures are recalculated with a calculator. The result: reports are often late, figures are inconsistent between staff, and management only finds out about a surge in polyclinic visits after the monthly meeting. Yet all the raw materials already exist in the SIMRS database, they just have not been processed automatically. This article discusses how to build an automated RL report pipeline from SIMRS — from mapping tables, writing queries, validating, to producing an Excel file ready for upload, with code examples that can be run directly.

Why Manual RL Report Recapping Is Always a Bottleneck

The RL report is not a single file but a collection of forms with different operational definitions: RL 1.1 (hospital identity), RL 2.1 (bed availability), RL 3.1 (basic data), RL 4.1–4.3 (inpatient, outpatient, and emergency morbidity), and RL 5.1–5.3 (visits and inpatient indicators). A single staff member doing everything manually will almost certainly use different definitions on different forms, because each form requires a different calculation method.

A real example that often occurs: the number of outpatient visits is calculated with COUNT(DISTINCT no_rkm_medis). This method makes a follow-up patient who comes three times in a month count only once, so RL 5.1 is lower than it should be. Conversely, if all registration rows are summed without a filter, visits with canceled status and patients who moved polyclinics are also counted. The correct equivalent is to count valid visit transaction numbers, then count new patients separately based on the last 6 months of history.

Similar problems appear in inpatient indicators. BOR is calculated from care days divided by (beds × number of days in the month). SIMRS stores admission and discharge dates, but a monthly report that only uses the discharge date will be completely wrong for a patient admitted on January 28 and discharged on February 3 — the care days must be split across the respective months. An error this small makes manual recapping never truly stable from month to month.

Mapping RL Data Sources in the SIMRS Schema

In SIMRS Khanza — the most widely used by type C/D hospitals and clinics in DIY — core data is stored in MySQL/MariaDB in the tables reg_periksa (visits), pasien, poliklinik, diagnosa_pasien, prosedur_pasien, kamar_inap, kamar, bangsal, penjab, and petugas. Commercial SIMRS have different table names, but the logic is almost always the same: there is a visit transaction table, a diagnosis table with a primary diagnosis marker, and a room table for inpatient care.

Three columns must be understood before writing any query. status_lanjut distinguishes Ralan and Ranap; stts marks a visit as Sudah, Belum, or Batal — Batal rows must always be excluded. diagnosa_pasien.prioritas = 1 is the primary diagnosis, while priority 2 and above are secondary diagnoses that must not be used for RL 4.x. For payer segmentation (general, BPJS, insurance), take it from penjab.kd_pj.

One architectural rule that must not be violated: never run heavy recap queries directly against the production database during polyclinic hours. Create a read-only replica or a staging database populated by a daily ETL process at 01:00, then point all reporting to it. This avoids the reg_periksa table being locked when thousands of transactions come in during the morning.

Building an Automated Pipeline: SQL Views, Validation, Excel Export

A minimal pipeline consists of five stages: an SQL view per RL form, a validation script, an Excel generator according to the e-SIRS template, a snapshot archive, and an upload process. Create clearly named views such as vw_rl51 and vw_rl43, store their definitions in Git, and treat them like application code — not ad-hoc queries in a personal spreadsheet that only one person understands.

Validation is the part most often skipped. Four mandatory rules: total visits must equal the number of unique transaction numbers; total discharged patients equals live patients plus dead patients; no empty ICD-10 codes (fill with the default Z00.0 or mark for correction); and BOR must be in the range of 0–100 percent. If any one fails, the pipeline stops and sends a notification rather than producing wrong numbers.

The following example pulls RL 5.1 data from Khanza using Python 3.12, pandas 2.2, SQLAlchemy 2.0, and openpyxl 3.1:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://etl:rahasia@10.0.0.9/sik?charset=utf8mb4")
sql = """SELECT r.no_rawat, r.no_rkm_medis, r.kd_poli,
       COALESCE(d.kd_penyakit,'Z00.0') AS icd10
FROM reg_periksa r
LEFT JOIN diagnosa_pasien d ON d.no_rawat=r.no_rawat AND d.prioritas=1
WHERE r.status_lanjut='Ralan' AND r.stts='Sudah'
  AND r.tgl_registrasi BETWEEN :awal AND :akhir"""
df = pd.read_sql(sql, engine, params={"awal":"2025-01-01","akhir":"2025-01-31"})
df["pasien_baru"] = ~df["no_rkm_medis"].duplicated()
df.to_excel("RL51_2025-01.xlsx", index=False)

After the file is created, save it to a folder per period and record its SHA-256 hash as audit evidence. For uploading to e-SIRS, note that e-SIRS does not yet provide a public API; full automation with Playwright 1.44 is possible but fragile against interface changes. A safer approach is semi-automatic: the Excel file is validated automatically, and the operator only presses upload.

Best Practices

1. Separate each RL form into named SQL views and version them in Git so definition changes can be tracked and rolled back.
2. Document a one-page data dictionary: the meaning of status_lanjut, stts, prioritas, and kd_pj, plus the ICD-10 mapping used.
3. Create a dedicated ETL database user with only GRANT SELECT privileges, and store credentials in environment variables, not hardcoded in scripts.
4. Run a 3-month parallel run: compare automated versus manual results with a target difference of 0 for visits and a maximum of 0.5 percent for morbidity before shutting down the old process.
5. Schedule it via cron or a systemd timer (for example 0 2 2 * *), save snapshots, and send email or Telegram Bot API notifications if validation fails.

FAQ

How long does it take to implement RL report automation?

For RL 4.x and RL 5.x with a single SIMRS and an already understood schema, 2–4 weeks is enough: one week for mapping and SQL views, one week for validation, and two weeks for parallel run. Forms with complex definitions such as inpatient indicators usually require additional iterations.

My SIMRS is closed-source and the vendor does not provide database access; what should I do?

Request a scheduled export to CSV option or access to a read-only replica. If it is truly closed, build a standalone reporting database populated from daily export files, then run the same pipeline on top of it. What matters is that the source of the numbers remains single, not a hand-keyed recap.

Can the results be sent directly to e-SIRS?

Not entirely yet. e-SIRS is still file-upload based, so realistic automation stops at a validated Excel file. Click automation using browser automation can be attempted, but its maintenance cost is high every time the e-SIRS interface changes.

How is patient data security handled?

Limit the exported columns: RL reports do not need full names and complete NIK; an encrypted medical record number or hash is sufficient. Apply file retention, role-based access, and logging of who generates and downloads files, in line with UU PDP obligations.

RL report automation is not a large project that must wait for a new budget. Start with the one form that consumes the most time — usually RL 5.1 — prove that its difference is zero for three months, then expand to RL 4.x and inpatient indicators. If your internal team does not yet have the capacity to build this pipeline, our IT consulting and application development services for SIMRS, SIM Klinik, ERP, POS, and websites include technical assistance for hospitals and clinics in Yogyakarta, DIY, and Magelang, Central Java — including database schema audits, creation of reporting views, and training for medical records teams.

This article is part of the knowledge base of Nugroho Setiawan, an IT consultant in Jogja (Yogyakarta). Related solutions: SIMRS (Hospital Information System) Clinic Management System BPJS & SatuSehat Integration. Need a free consultation?