How do you custom sort column in a paginated report?

[Anonymous]

in a ssas tabular we've got balance aggr with 8 levels and custom sort order for each level. Is it possible to make a matrix in a paginated report and keep custom sort order just like it works in Excel and usual Power BI ?

[d_gosbell]

This is possible, but in a paginated report you would need to include the custom sort columns in your data set manually and you would either need to add an ORDER BY clause to your query using these columns or you would have to manually add sorting to the properties of the dataset itself. 

The first option will force the tabular engine to do the sorting and will be more efficient on large data sets, the second option does not require you to edit the DAX query by the sorting will be done on the report server and so may use extra resources there.

[Anonymous]

Darren, thank you

Why it's so daunting, why it's not like DAX Studio 🙂

I do not want to bother you, but if there is something to read about manual sorting, please advise

We've got 30-40 specialized tabular cubes and do not know what to do:

1. make one more global cube and loose some granularity (not all dates, products...) and rest to do in a usual PBIRS

2. give a try to Paginated Reports PBIRS, it's only for top bosses and interactivity is not top priority, but tell them to open 20 reports ... 

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

Why it's so daunting, why it's not like DAX Studio 🙂

---

🙂 Well DAX Studio does not do any implied sorting either. If you want sorted results you need to add an ORDER BY clause to your query. I don't know why Microsoft changed this behaviour compared to MDX queries which automatically apply the sort by properties. But I suspect it 

I do not want to bother you, but if there is something to read about manual sorting, please advise

---

The Microsoft docs https://docs.microsoft.com/en-us/dax/dax-queries

and Dax.Guide https://dax.guide/st/order-by/  

both have sections that describe the use of the ORDER BY clause

---

We've got 30-40 specialized tabular cubes and do not know what to do:

1. make one more global cube and loose some granularity (not all dates, products...) and rest to do in a usual PBIRS

2. give a try to Paginated Reports PBIRS, it's only for top bosses and interactivity is not top priority, but tell them to open 20 reports ... 

---

If you want to build a report that mixes and matches measures from 20 different models then you might need to look at creating a global cube.

But the other variation of option 2 is that you can have multiple datasets in a single paginated report and each of those datasets can be connected to a different model. So you could create 1 big report, with 20 different tablix controls connected to 20 different datasets the only thing you can't easily do is to put measures from different datasets in the one tablix or chart (you can use the lookup() function in the report itself to do a pseudo join across data sets, but you would not want to do too many of these)