Package Connector and sort order

[mvaspal]

Hi

We try to import dimensions from Report Studio reports, using the Custom Query option in the TI and it seems it ignores the sort order that we set in the report. Now, as this is a dimension update TI, we cannot live without the sort order.
The only workaround we could find was that we set up one SQL view per hierarchy, import these to FM, and create a report over each view. Or forget package connector.

Anyone managed to use sorting from RS reports?

tested with tm1 v10.2.1 and 10.2.2

Thanks
Matyas

[jim wood]

Are you using a subset as the data source? Have you tried setting the order you need in the subset?

[mvaspal]

No, I use a report studio list report as data source, and package connector to connect to the report

[jim wood]

You can set the dimension order in architect. Would this help or is your order custom?

[mvaspal]

I checked the native SQL in both BI and TM1 TI, and it seems thet the package connector simply omits the 'order by' clause from the query, otherwise the queries are identical. We will register it with IBM now.

Jim, we have sort order IDs in the DWH for all alternative hierarchies within the dimension, we use that for sorting.

[jim wood]

With it ignoring the order by, it will be defaulting to the index within the dimension. If you make sure you set then order when building the dimension / alternate hierarchies in TI then you might get at the very least closer,

Jim.

[dr.nybble]

How do you specify sorting for the list report? Let me know and I will do some investigation.
TM1 Package Connector / Transformer simply issue a query to the BI query engine. A component called the Report Adapter uses the Report Server to generate the SQL based on the report specification.

[mvaspal]

Hi

By simply clicking on the header and set sort in the icon bar.

As I wrote above, I compared the 2 generated SQLs (but only the Native SQL), and the only difference is that the TI omits the order by clause

Thanks for help!

[dr.nybble]

Yes, I am able to reproduce the issue. I believe that the problem is in the Report Adapter. Follow up with support.