Why I Always Start Sage X3 Views in SSMS (And You Should Too)

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 its a first choice for me 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 and yes...4 simple steps:

1. Get a clear plain 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)

Before I touch any code, I get the user to explain exactly what they want in plain language 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 **/

This is where the data gets found and sorted…like magic i hear you say - not quite its 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. 

Its a simple data structure using the Medallion Architecture principles, which uses Bronze, Silver, Gold. This works for me!

My Tip: Use the Sage X3 Data Models for reference when you struggle to find those elusive bits of data.

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, lookup 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.

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. Remember to "Validate " the query. 

Then rewrite the OUTPUT queries to read the source VIEW you created within X3. 

Yes you could have a monster view with  inline selects but I prefer the source join structure that I have used over many years as its easier to replace elements if iterations are required.

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

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!

X3 Tip: A common tip for SAGE X3 is when creating custom records prefix with Z  e.g. your view in SSMS might be v_OUT_CUSTSALES_V1 in Sage X3 its  ZCUSTSALESV1

My Tip: Version control all views!

Yes I'm sure if you work with X3 and Views you may have an alternative approach, and prefer diving into X3 off the bat, (drop me a line to share),  BUT this work for me for these reasons and saves me time:

Faster debugging: I find SSMS  error messages more helpful than X3 .

Early data sets: I can provide quicker early data sets for testing.

Client confidence:  clients can see the data structure and relationships before it's locked away into X3.

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.

• Clear brief: Get a clear description from the client or user and make sure two way dialogue is easy.

• Business Logic Focus: Sage X3 SQL queries are meant to pull data for business reports and screens, not for general database tasks. Remember SSMS will bypass any logic.

• Z prefix: Prefix saved Views and Queries with Z

• Errors: Error checking in Sage X3 can be a pain - remember you can export the error reports.

• X3 Data Models: use these and the X3  table guide to find fields and links.

• X3 Login: Get front end access early!

• Complex Queries Limited: Certain advanced query features (like Common Table Expressions—CTEs using WITH) don't work  well in Sage X3; workarounds like stored procedures may be required.

• Parameter Format Is Different: Instead of @parameter, Sage X3 uses %1%, %2%, etc., for query parameters (placeholders for filters/values).

• Row Size Errors: You may hit row size limits (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.

Building Sage X3 Views isn't just about knowing SQL. It's about understanding the user requirements and that X3 has its own nuances 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.