Request for SQL Server Compatibility Documentation for Holistics

Report Bugs
1

Hi Team,

I would like to raise a concern that we’ve been facing while developing SQL reports for Holistics.

Most of the SQL scripts I write work perfectly in SQL Server Management Studio (SSMS). However, when I execute the same scripts in Holistics, I often encounter syntax or validation errors that do not exist in the standard SQL Server environment.

So far, I’ve noticed the following differences:

  • ORDER BY at the end of a query is not accepted by Holistics, even though it is valid SQL Server syntax. This is a significant limitation for us because many of our reports rely on a default sort order, and our clients expect the data to be returned in a consistent order.
  • Statements must end with a semicolon (;). This requirement is completely fine, and we can easily follow it.

However, even after removing the ORDER BY clause and adding semicolons, we are still encountering other SQL behaviors that differ from SQL Server.

In addition, some of the errors returned by Holistics are difficult to interpret because they don’t appear to directly correspond to the SQL being executed. For example, we’ve encountered errors referencing syntax or functions that are not present in our queries, as well as generic transformation errors that provide little indication of the actual cause. This makes troubleshooting significantly more difficult and often requires extensive trial and error.

I’ve already created two support tickets with examples so the team can investigate the specific issues we’re seeing.

In addition, I would like to request the following:

  1. SQL Compatibility Documentation

    It would be extremely helpful to have documentation describing which SQL Server features and syntax are fully supported, partially supported, or not supported by Holistics. A comparison or compatibility guide with standard SQL Server would save a significant amount of development and troubleshooting time.

  2. Better Error Reporting and Diagnostics

    If Holistics performs SQL parsing, transformation, or validation before executing queries, it would be extremely helpful if the reported errors clearly identified the actual issue or the transformed SQL being executed. More descriptive error messages would make debugging much easier.

  3. Support for Essential SQL Features

    Some SQL Server features, such as using ORDER BY in report queries, are essential for report development and for meeting client expectations. If there is a technical reason why these features are currently restricted, we’d appreciate understanding the limitation. Otherwise, we’d like to request that support for these capabilities be considered in future improvements.

Additionally, we’ve encountered several cases where the reported error message does not appear to match the SQL being executed, making troubleshooting extremely difficult.

For example:

  • We received the error “Cannot convert undefined or null to object” , which was ultimately resolved by adding any comment line at the end of the script like (-- Test) but I’m not sure if this will work always. The SQL itself executes successfully in SQL Server, and the reported error did not clearly indicate the actual source of the problem.
  • In another case, Holistics returned “‘DAYOFWEEK’ is not a recognized datepart option” , even though our query uses DATEPART(WEEKDAY, ...) and does not reference DAYOFWEEK anywhere in the SQL.

These types of errors make it difficult to determine whether the issue is related to SQL Server compatibility, SQL parsing, or an internal SQL transformation performed by Holistics. More accurate error messages—or additional debugging information—would significantly reduce development time and make troubleshooting much more efficient.

Having clear documentation, improved diagnostics, and better SQL compatibility would greatly improve the report development experience and reduce the number of support tickets needed for SQL-related issues.

Thank you, and I look forward to your feedback.

2

Please any updates on this?
I see now the Order By is added to the SQL Server secript and there is no issues when I ran the script. However, the report not updated based on the Order By!!

Looks like the script ignore it or something.

3

Hi @Hamza_Rashed1

Thanks for the really thorough writeup - this is helpful, and I want to explain what’s going on:

On the ORDER BY and WITH clause behavior
When you run a query in Holistics, we wrap it as a subquery so we can apply our own query logic on top of it such as joining between models and handling limit/sort/pivot on the chart. This is essential to how Holistics works.

SQL Server doesn’t allow an ORDER BY or a top-level WITH/CTE inside a query that’s being nested this way, so the same script that runs fine in SSMS gets rejected once it’s wrapped. That’s the root cause behind those two limitations.

To make this concrete: say your query is

WITH recent AS (
  SELECT * FROM orders WHERE created_at >= '2024-01-01'
)
SELECT region, SUM(amount) AS total
FROM recent
GROUP BY region
ORDER BY total DESC

Our parser automatically converts it into a subquery-friendly form before running, roughly like this:

SELECT * FROM (
  SELECT region, SUM(amount) AS total
  FROM (
    SELECT * FROM orders WHERE created_at >= '2024-01-01'
  ) as recent
  GROUP BY region
) AS sub

Notice the CTE gets inlined as a subquery so it no longer needs a top-level WITH, and the trailing ORDER BY is dropped, so the whole thing runs cleanly once wrapped.
The nice part is you don’t have to manually rewrite a WITH CTE into a subquery yourself - which is quite troublesome - our processing handles it for you.

For sorting, the solution is to set the sort in the report’s settings rather than in the SQL itself, so your clients still get a consistent order.

DAYOFWEEK bug

The DAYOFWEEK error is a bug on our side, and I’m sorry for the confusion it caused. Your query is correct - DATEPART(WEEKDAY, …) is valid, and you shouldn’t be seeing a DAYOFWEEK error at all. We’ve fixed the issue in the ticket that you shared

The other error you mentioned looks like a separate issue:

We received the error “Cannot convert undefined or null to object”, which was ultimately resolved by adding any comment line at the end of the script like (-- Test)

Could you share the query that triggered this one? We’ll dig into it to see if our parser is mishandling something.

Documentation

You’re correct that this needs documentation to explain our pre-processing/parsing of a SQL Server query. I’ll bring this feedback to my team to discuss further and will let you know the progress on this

Best,