Sorting by non-grouped column

[bogdan_elastic]

Hi there,

I'm looking for some guidance or hint on the below case I'm trying to solve.

I'm working on an ODBC-based connector for a SQL datasource that lacks currently the capacity to sort/order by non-groupped columns.

In case DirectQuery is enabled, PowerBI will generate statements of the (simplified) type:

select a from (select a, b, c, d from table group by a, b) order by c, d

(below the actual null-handling sorting query). To this the datasource complains that it "[c]annot order by non-grouped columns".

There seems to be no capability in the ODBC spec to indicate support (or lack of) for this type of query. The AstVisitor functionality seems unfit for this case.

I was wonding if there's any way to indicate this incapacity to PowerBI?

Thanks in advance for any hint!

--b

Actual statement submitted for preparation:

select "date0"
from 
(
    select "date0",
        "C1",
        case
            when "C1" is not null
            then "C1"
            else ?
        end as "C2",
        case
            when "C1" is null
            then ?
            else ?
        end as "C3"
    from 
    (
        select "date0",
            "date0" as "C1"
        from "calcs"
    ) as "ITBL"
    group by "date0",
        "C1"
) as "ITBL"
order by "ITBL"."C2",
        "ITBL"."C3"

[Mariusz]

Hi @bogdan_elastic 

Would you be able to visualize your problem, as a sample of data in the current state and what is the outcome that you expect?

Please, can you clarify, what do you mean by "I was wonding if there's any way to indicate this incapacity to PowerBI?"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[bogdan_elastic]

Hi @Mariusz ,

---

Would you be able to visualize your problem, as a sample of data in the current state and what is the outcome that you expect?

Please, can you clarify, what do you mean by "I was wonding if there's any way to indicate this incapacity to PowerBI?"

I am looking for a way to flag to PowerBI that the data source can't order by a column, if that column is not used for grouping.

Ideally, there would be a setting in SqlCapabilities passed to Odbc.DataSource, as an example.

So, in the full example I've given, ideally PowerBI would also group by "C2" and "C3" if ordering by these columns.

Regards,

Bogdan.