I have a 2 level cube based on iServer log calculating most active Users by their region and office. <br />
The idea was to show top 10 most active offices for every region report, like this:<br />
REGA --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFA5 ---- 10hr<br />
OFFA4 ---- 9hr<br />
OFFA3 ---- 8hr<br />
OFFA2 ---- 7hr<br />
OFFA1 ---- 6hr<br />
..........10 lines</p>
REGB --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFB4 ---- 10hr<br />
OFFB2 ---- 9hr<br />
OFFB5 ---- 8hr<br />
OFFB7 ---- 7hr<br />
OFFB5 ---- 6hr</p>
..........10 lines<br />
<br />
While trying to show top 10 (based on total run time) offices - plain crosstab wouldn't do it as it was displaying top 10 for all regions. <br />
REGA --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFA1 ---- 10hr<br />
OFFA2 ---- 9hr<br />
OFFA3 ---- 8hr</p>
<br />
REGB --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFB1 ---- 10hr<br />
OFFB2 ---- 9hr<br />
OFFB3 ---- 8hr</p>
REGC --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFD1 ---- 10hr<br />
OFFC2 ---- 9hr<br />
OFFC3 ---- 8hr</p>
REGD --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFD1 ---- 10hr</p>
that's it.<br />
So my solution was to have a table with grouping and "hide details" option checked. Then in the header I placed a crosstab for office only. While this solved top 10 issue, now I am facing a sorting problem. 1st region shows offices sorted correctly from most busiest to least. However on a region break sorting is not done so for the rest of the regions sort order is alphabetical on office name.<br />
Like this<br />
REGA --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFA5 ---- 10hr<br />
OFFA4 ---- 9hr<br />
OFFA3 ---- 8hr<br />
OFFA2 ---- 7hr<br />
OFFA1 ---- 6hr</p>
..........10 lines<br />
REGB --<br />
<p class='bbc_indent' style='margin-left: 40px;'>OFFB1 ---- 1hr<br />
OFFB2 ---- 9hr<br />
OFFB3 ---- 5hr<br />
OFFB4 ---- 3hr<br />
OFFB5 ---- 2hr</p>
..........10 lines<br />
<br />
Any suggestions would be appreciated.
