Four valuable steps I've found from building custom X3 views that actually work

I've built dozens of custom views in Sage X3 over the years. I'm not a Sage X3 consultant, but I have worked extensively with the X3 data. And I've learnt something that might surprise you: the best Sage X3 views I've found aren't built in Sage X3 to start!

They're built in SQL Server Management Studio first - well that's my view (lol) as a data person rather than an X3 consultant that may work it differently.

Here's why that matters, and the four steps that'll potentially save you hours of frustration. But will also save development time and cost.

The Problem with Going Straight to X3

I get it, you have been asked for a query in Sage X3 and the team needs a new, possibly complicated Sage X3 View to feed into the SEI and naturally it feels right to dive straight into Sage's SQL query area. In my experience this can be a mistake.

SQL inside X3 doesn't always behave like normal SQL Server. It's got quirks. Limitations. Things that work perfectly in SSMS can throw errors or return wrong data in X3 adding to frustrations and time. These differences mean writing queries in Sage X3 might feel restrictive compared to regular SQL Server, especially for users that are used to using SQL in SQL Server Management Studio (SSMS) and expecting full SQL Server features.

Yes, maybe I'm biased, and set in my ways; I like using SSMS and it's a first choice if available for SQL. I apply simple frameworks to my data work. So my personal approach, developed over the years, regardless of simple or complicated views is four fold:

1. Get a clear plain English (or other language!) explanation of what is needed from the user/client.
2. Get to that Data output using SSMS first.
3. Translate that into SAGE X3 SQL query or queries.
4. Create the SAGE X3 VIEW(S)

Step 1: Start with Plain English or other language! (Seriously)

Before I touch any code, I get the user to explain exactly what they want in plain English, i.e. a very clear plain description with no jargon of what they need. If a term or process is not clear I get it explained. Also get them to show you where any related information sits in the system that they currently use, if at all.

Not "I need sales data with customer information." That's too vague.

I want: "Show me all invoices from the last 6 months, grouped by customer, from sales region X with their total spend and average order value, and outstanding balances."

This sounds obvious, but you'd be amazed how often requirements change once you pin them down. Better to catch this early than rebuild a view three times. If they do change you have an audit trail!

My tip: I actually write this requirement at the top of my SQL file as a comment. Keeps me focused when the query gets complex. I find my comments help, be it block or line comments.

/** Show me XYZ - SPDATA - 23/7/25 **/

Step 2: Build and Perfect in SSMS

This is where the data gets found and sorted…like magic - not quite it's feedback and getting to the bottom of the user request.

I connect SSMS directly to the Sage X3 database and build the view there. No X3 interface. No limitations. Just pure SQL.

My Tip: I like to create views into the core tables and use those views as the first building blocks. I then create sub or source views, that do the work and that build the picture of what I need and then roll these up into final OUTPUT views. It's a simple data structure using the Medallion Architecture principle, which uses Bronze, Silver, Gold. This works for me!

• Bronze = Raw data
• Silver = Source Views (with transformations and calculations)
• Gold = Final Output

X3 TABLE VIEW > SOURCE VIEWS > OUTPUT

CREATE VIEW view_X3_TABLE AS SELECT * FROM [SERVERIP].[sagex3].[LIVE].[TABLE]

Here's my process:

• Start simple with the core tables I need from X3
• Add JOINs one at a time from my source views
• Test the data at each step
• Create output views
• Check the results with the client before moving on…

My Tip: Game changer: I always ensure I have front-end user access to Sage X3 during this stage. Being able to navigate the actual screens, look-up fields, and see how data flows through the system is invaluable. The more I work with a client's operations through the UI, the better I get at nailing the right data in SSMS without bothering them with basic questions.

Very Important caveat: SSMS bypasses all of Sage's business logic, validation rules, and data transformations. The raw data you see might not match what appears in Sage X3 screens. This is why having UI access helps - you can cross-reference what you're building against what users actually see and adapt views to support any logic. Depending on what you are looking for, your users may see data in the UI but it doesn't exist anywhere in the database tables or views - that's for another day - hint: SAGE X3 FUNCTIONS.

The key is getting the data structure and relationships right in SSMS. Don't worry about X3 compatibility yet. Just focus on pulling the correct raw data.

I'll run sample queries, export small datasets, and verify everything myself and with the client. "Does this look right? Are these the customers you expected? Do these totals make sense compared to what you see in Sage?"

This back-and-forth is crucial. Data that looks correct to me might reveal edge cases the client knows about.

Step 3: Translate to X3 SQL QUERIES (Expect Surprises)

Now comes the tricky part: making it work in Sage X3.

I copy my working SSMS queries into X3's SQL query area. And almost always, if you start with the OUTPUT query it will error on the first go!

My Tip: As I use source queries, these need to be replicated first so; run each in SQL Query, then create the VIEW for the sub or source query in STEP4. Then rewrite the OUTPUT queries to read the source VIEW you created within X3. Yes you could have a monster view but I prefer the source join structure I have used over many years as it's easier to replace elements if iterations are required.

This is where core differences can come into play also. Note these may be issues and error checking in X3 is fun…NOT!!

• Rewrite CTEs as subqueries or stored procedures
• Change parameter syntax from @param to %1%
• Restructure JOINs to avoid row size limits

I test the X3 SQL version against my SSMS results, understanding that some differences might be due to business logic that X3 applies but SSMS doesn't. This logic may need building into your source views once it can be defined.

Step 4: Create the X3 SQL Views

Once the SQL query works in X3 and the client confirms the data looks right, then I create the actual internal views. This becomes available to SEI dashboards and reports.

But I still cross-check the final view output. Trust and verify.

X3 Tip: A common tip for SAGE X3 is when creating custom records prefix with Z so e.g. your view in SSMS might be v_OUT_CUSTSALES_V1 in sage x3 it's ZCUSTSALESV1

My Tip: Version control all views!

Why This Approach Works For Me

Four reasons this method saves time for me:

Faster debugging: SSMS has better error messages and debugging tools than X3.

Client confidence: They can see the data structure and relationships before it's locked into X3, even if the final numbers might differ due to business logic that has been identified and accounted for.

Future maintenance: I've got a working SSMS version I can quickly modify/test without needing to touch X3 first when requirements change.

Back-up set: I take backups of all the stages but the most important for me is the SQL version.

Some Key Differences To Be Aware Of: Sage X3 SQL vs Standard SQL Server

Here are SOME differences you may encounter or be aware of:

• Business Logic Focus: Sage X3 SQL queries are meant to pull data for business reports and screens, not for general database tasks.
• Complex Queries Limited: Certain advanced query features (like Common Table Expressions—CTEs using WITH) don't work in Sage X3; workarounds like stored procedures may be required.
• Automatic Query Hints: Sage X3 can automatically add an "OPTION (FAST 1)" optimisation hint to queries, which changes how results are fetched. This can be changed or turned off by settings in Sage.
• Parameter Format Is Different: Instead of @parameter, Sage X3 uses %1%, %2%, etc., for query parameters (placeholders for filters/values).
• Row Size Errors: It's easy to hit the Microsoft SQL Server row size limit (8,060 bytes) in Sage X3, especially when making queries that join many tables or include lots of fields. This can cause errors that might not appear in standard SQL tools.
• Doesn't Support All SQL: Some complex queries that run fine in SQL Server Management Studio (SSMS) may fail or not be allowed in Sage X3's SQL query requester.
• Security Filters Are Skipped: Custom SQL queries in Sage X3 can bypass normal role-based or site-based security, showing data that might otherwise be hidden in regular screens.

These differences mean writing queries in Sage X3 might feel restrictive compared to regular SQL Server, especially for advanced users expecting full SQL Server features.

The Bottom Line

Building Sage X3 views isn't just about knowing SQL. It's about understanding the user requirements and that X3 has its own personality and business logic layer.

Start in SSMS where you can work fast and debug easily. Get the data structure right. Know the data, know the business processes and what the target output required is. Then carefully translate/map to X3, accounting for business logic differences.

You or your clients will get exactly what they asked for. And you'll avoid those 2am debugging sessions wondering why perfectly good SQL isn't working.

That's a win for everyone.