# How to Build SQL Views for Custom SyteLine Reporting > Source: https://netray.co/erp-help/syteline-sql-views-custom-reporting-how-to > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind advanced ## System syteline ## Erp Infor SyteLine (CloudSuite Industrial) ## Module Reporting - SQL Server ## Query How to create SQL views for custom SyteLine reporting ## Alternate Queries - syteline reporting best practice sql view - syteline custom report data source - safe way to query syteline database for reports - syteline ssrs crystal reports view ## Applies To SyteLine 8.x, 9.x, CloudSuite Industrial (CSI) 10.x on SQL Server ## Steps ### Build a reporting view safely **Items:** Identify 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. Create 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. Join 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. Avoid SELECT * and avoid exposing internal-only columns - sync, lock, and audit columns - that reporting users should not see or rely on. Test 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. Point 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. Document 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. ## Sections ### Why not just query the base tables directly **Body:** 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 **Body:** 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 **Body:** 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 **Body:** 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. ## 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. ## Ai Angle 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. ## Faq ### Should I report off the SyteLine production database directly? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms syteline sql views reporting, syteline custom report data source, syteline crystal reports view, syteline ssrs query, syteline database schema reporting, syteline co_item co_hdr tables, safe syteline reporting queries, syteline read only ido view --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json