Query behind Seniors Living Loan Summary report

Kinetic ERP
1

I have been asked to build a BAQ that contains components of the Seniors Living Loan Summary Report which is an inbuilt crystal report for which the query is not direct available in a text format anywhere.
When I asked support how I can find the query the response I got was -
"Apparently, it is not possible to see the query from application front end support point of view.

Since you are hosting the MSSQL Database on premise and have a full access to MSSLQ, I believe you may try to use “SQL Server Profiler” function available within MSQL Management Studio to try to trace the application process and see if it displays the query being execute and the result it generates."

I did the trace but it generated 88,000+ rows and I have not been able to find the query that actually generates the report data.

Does anyone either a) have the query or b) know the best way to get the query?

Thanks

Steve

2

I’m not on that product specifically, but a couple of thoughts. Sometimes those built-in reports that have no BAQ/RDD associated with them exist as Stored Procedures or Functions in the database. There might be another location where you can see the source but I can’t recall it.

Also, in the SQL profiler, you should be able to filter for certain types of data, specifically the SQL Statements being processed, while ignoring everything else. I did a quick CopIlot ask and this is what it said:

How to Capture SQL Statements in SQL Profiler

  1. Start SQL Server Profiler and connect to your SQL Server instance.
  2. Create a New Trace:
    • Go to File → New Trace.
    • Choose your server and authentication method.
  3. Select a Template:
    • You can start with the TSQL_Replay or TSQL_Duration template, or create a custom one.
  4. Choose Events:
    • In the Events Selection tab, include:
      • SQL:BatchStarting – Captures ad-hoc SQL before execution.
      • SQL:BatchCompleted – Captures ad-hoc SQL after execution.
      • RPC:Completed – Captures stored procedure calls (including parameter values).
      • SP:StmtCompleted – Captures individual statements inside stored procedures.
  5. Add Columns:
    • Ensure TextData is selected — this contains the actual SQL statement text.
    • Optionally include LoginName, DatabaseName, StartTime, Duration, etc.
  6. Apply Filters (optional):
    • Use Column Filters to limit by database, application name, or login to reduce noise.
  7. Run the Trace — you will now see the SQL statements in the TextData column.
3

Thanks @MikeGross great summary. I did have a quick response in the breech, for some reason it did not send.

I now know why support said to use profiler now. This report being a SLS one is not like standard ones it has it’s own service that does not seem to be part of the standard (not surprised as it is a vertical on the base).

I had a friend help me and let’s say there are a as number of tables involved.

Here’s a Rundown:

Master/config:

  • Company
  • UserFile (language only)
  • AC_Syst (RlsClassSLS)
  • XaSyst, ARSyst (existence checks)

Customer mapping and accommodation:

  • AC_RAHead
  • RlsHead
  • Customer
  • AC_Customer
  • AC_SegAccom
  • AC_SegCT
  • AC_RABSchHead

Loan snapshot and contract:

  • AC_RALoanAUS
  • AC_LoanContract

Payments:

  • CashHead
  • CashDtl
  • AC_RALoanAUS (join by Company/GroupID/HeadNum)

Invoice transactions:

  • InvcHead
  • InvcDtl
  • InvcTax
  • AC_RAInvDtl
  • AC_BCD

Rate derivation:

  • AC_RABSchHead
  • AC_RABSchDtl
  • AC_RABSchDtlRates
  • AC_BCD
4

If you need further help let me know