How to Build SQL Views for Custom SyteLine Reporting
How to create SQL views for custom SyteLine reporting
Also searched as
- syteline reporting best practice sql view
- syteline custom report data source
- safe way to query syteline database for reports
- syteline ssrs crystal reports view
Short answer
Reports should read from purpose-built SQL views, not directly from SyteLine's core transactional tables, so an Infor patch cannot silently break them and heavy report queries do not lock rows the ERP itself needs. Put custom views in their own schema or under a clear naming convention, build them on stable base tables, and grant reporting tools read access to the views only.
Applies to: SyteLine 8.x, 9.x, CloudSuite Industrial (CSI) 10.x on SQL Server
Build a reporting view safely
- 1Identify the base tables the report needs (for example item, customer, co_hdr, co_item, job, job_material) by tracing them from the relevant IDO Collection's underlying table or query definition, not by guessing from client UI labels.
- 2Create the view in a dedicated schema or with a clear custom prefix so it is obviously not part of the standard Infor schema and will not collide with anything an upgrade script creates.
- 3Join only what the report needs, and filter out logically deleted or inactive rows at the view level so every report built on it behaves consistently.
- 4Avoid SELECT * and avoid exposing internal-only columns - sync, lock, and audit columns - that reporting users should not see or rely on.
- 5Test the view's execution plan against production-sized data, not a small test database, and add supporting indexes on the underlying tables if the plan shows scans on large tables like job_material or co_item.
- 6Point Crystal Reports, SSRS, Power BI, or Excel at the view rather than the base tables, and grant the reporting login SELECT only on the view.
- 7Document the view - what it is for, which reports use it - and keep its script under source control outside the SyteLine database, since it will need to be reapplied after certain upgrade or refresh scenarios.
Why not just query the base tables directly
Report authors often start by pointing Crystal or SSRS straight at tables like co_item or job because it is the fastest way to get something working. That couples every report to the exact current schema, so an Infor upgrade or patch that renames, splits, or adds constraints to a table then breaks every report built directly on it.
A poorly written ad hoc report against live tables can also place locks that interfere with normal order entry or job transactions during business hours, which is a separate problem from schema fragility but shows up the same way to users: things suddenly feel slow.
Where to put custom views
Many SyteLine sites keep customizations physically separate by using a dedicated schema, or a strict naming convention, purely for custom reporting objects. That lets anyone - a DBA, a consultant, a new hire - tell immediately from the object name alone that it is safe to modify and is not part of the Infor-delivered schema.
This separation also makes it straightforward to script out and reapply everything custom after a database refresh from production to test, or after an upgrade that requires custom objects to be dropped and recreated.
Performance considerations specific to SyteLine tables
Some core tables - job_material, job_route, co_item, itemloc, and various trace or history tables - grow very large in an active plant and are hit constantly by transactional users. A report view that joins several of these without supporting indexes can end up with a plan that scans millions of rows.
Check actual execution plans against realistic data volumes, add covering indexes where the DBA has room to do so, and for anything run frequently during the day, consider a nightly snapshot or small ETL into a dedicated reporting table instead of querying live tables on every refresh.
Exposing a view back into SyteLine as an IDO
When a report needs to be run and filtered from inside the SyteLine client itself, not just an external BI tool, you can create a read-only IDO on top of the view. That lets SyteLine's own security and site-level row filtering apply to it the same way it does for a stock IDO, instead of the report living entirely outside the application's access controls.
Common pitfalls
- !Building views directly against transactional tables during business hours without checking the execution plan, which can lock rows entry clerks need.
- !Naming custom views in a way that could collide with a future Infor-delivered object, instead of using a clearly separate schema or prefix.
- !Exposing internal sync, lock, or audit columns in a view used as a BI data source, leaking implementation detail to end users.
- !Not accounting for multi-site data - environments with multiple sites need the view to filter or expose the site column correctly, or a report silently mixes data across sites.
- !Forgetting to script and store the view definition outside the database, losing it after a refresh or upgrade step.
- !Granting the reporting tool's login broad SELECT rights on base tables instead of narrow rights on the view.
How an ERP-grounded AI assistant handles this
ERPray can answer a reporting question directly - what was on-time delivery by customer last quarter - by generating and running a scoped, read-only query against SyteLine data, without a developer first tracing which of several similarly named tables (co_hdr versus co_item versus co_relhdr) actually holds the answer. For recurring reports, SyteRay can also draft the initial view definition and note which base tables and joins it depends on, so a developer reviews and hardens it rather than starting from a blank query.
Frequently asked questions
Should I report off the SyteLine production database directly?
For light, occasional queries it is usually fine. For anything run frequently or during business hours, prefer a replicated reporting copy of the database or at minimum well-indexed views, so report queries cannot contend with live transaction locking.
How do I find which table actually backs a field I see in the SyteLine client?
Open the IDO Collection for the screen's IDO and look at its underlying table or query definition. The client-visible field label does not always match the physical column name, and some fields are computed rather than stored.
Can I edit data through a custom view?
You can, but it is not recommended outside of exposing it as a proper read-only or carefully validated IDO. Writing directly to base tables through a view bypasses SyteLine's own business logic, validation, and App Events, which can leave data in a state the application does not expect.
Will my custom views survive a SyteLine or CSI upgrade?
Views in their own schema generally survive, but always verify after an upgrade - a patch can rename or restructure an underlying table your view depends on, which is exactly why the view should be documented and scripted for quick reapplication.
Related
How to Improve SQL Server Performance for a Slow SyteLine ERP System
Most SyteLine performance complaints trace back to a handful of SQL Server issues: stale statistics, missing indexes on high-churn IDO tables, blocking caused by the default isolation level, and IDO Runtime connection pool exhaustion. Working through those systematically, with DMV evidence, resolves the majority of slow system tickets faster than guessing at application-layer causes.
AdvancedHow to Add a Custom Method to a SyteLine IDO in C#
Custom IDO methods let you expose new server-side business logic - beyond the standard Load, Update, and Delete - as a named, callable operation from the SyteLine client, a script, or an external integration. You declare the method and its parameters on the IDO Collection, then implement it in an extension class that handles invoke calls for that method name.
How-toHow to Run a Cost Rollup in SyteLine
Run Item Cost Rollup from Product Definition > Costing > Cost Rollup, select the site and item range, run it in Simulate mode first to review variances, then run it live to post new standard costs to Item records and the Costed Bill of Material.
AdvancedHow to Call SyteLine IDOs from an External Application
SyteLine exposes its IDO layer as a SOAP web service, commonly published through an IDORequestHandler endpoint, so an external .NET application, middleware platform, or script can Load, Update, Delete, and Invoke methods on the same IDOs the SyteLine client itself uses. You call it like any SOAP service: reference the WSDL, authenticate, and pass the IDO name, method, and parameters in the request.
Error fixFixing SyteLine's 'Object reference not set to an instance of an object' error
This is a generic .NET NullReferenceException surfacing through the SyteLine IDO Runtime, not a SyteLine-specific error code. It almost always means a form, script or IDO method referenced a field, row or object that came back null, usually after a customization, a missing related record, or a view/IDO method call before the form finished loading. Turn on detailed client logging and check the most recent customization or form event first.
Error fixDiagnosing and fixing SyteLine session timeout errors
SyteLine session timeouts come from one of three independent layers: the IDO Data Service session timeout on the app server, the IIS/application pool idle timeout for the web (Mongoose Web) client, or a load balancer/proxy idle timeout in front of a CloudSuite hosted environment. Fixing the wrong layer is the most common mistake - you need to identify which layer is actually expiring the session before changing anything.
AI for ERPAI for Infor SyteLine and CloudSuite Industrial
Add grounded AI to Infor SyteLine or CloudSuite Industrial: natural-language answers, agents over IDOs and ION, on-prem or CloudSuite deployment.
AI for ERPNatural Language Query for ERP Data: Ask SAP, Infor, or Oracle a Question in Plain English
See how natural language query over SAP, Infor, Oracle, and NetSuite data works: grounded text-to-SQL, role-based permissions, and a visible audit trail.
Stuck on Infor SyteLine (CloudSuite Industrial)?
Talk to engineers who work inside Infor SyteLine (CloudSuite Industrial) every week, and who build private AI that answers these questions from your own ERP data.