SSRS row column grouping issue

Question

I have a report that groups audits performed per day by auditor. Days grouped in columns and auditors by row. The data area lists the audits performed. My problem is exactly the same as this one: example of problem and solution that I can't use

But I can't use the solution which involves changing the query to rank and partition the results first. Below is example of the problem and how I need it to look. The solution needs to be an SSRS solution not modify the query.

The report pulls this way with the first auditors audits for wk 1 grouped and then week 2 audits start further down with blanks rows and wk 3 starts further below wk 2 and so on. Any help is appreciated.

 Week1 | Week2 | Week3
-------+-------+-------
  123  |       |
  456  |       |
       |       |  789
       |       |  012

What I want:

 Week1 | Week2 | Week3
-------+-------+-------
  123  |       |  789
  456  |       |  012

Can you provide detail on why you can't use the "solution which involves changing the query to rank and partition the results first". Is this a database restriction, how you are obtaining your results, or post-processing the data? — Jonnus
A SQL pivot might be a possible solution. However, there is not enough information here to provide any further guidance. Perhaps look here [simple-talk.com/sql/t-sql-programming/… — MiguelH
Thank you all for the responses so far. While I could in theory implement an SQL solution (whatever the case may be - pivot or rank/partition), I'm looking for a way to get the job done without modifying the SQL. If anyone knows conclusively that this is NOT possible, that would be the next best answer to an actual solution to my problem. — MrFeet
So I determined that it is NOT possible to do what I wanted. I ended up implementing the SQL solution, which involved creating a comma delimited list in SQL to aggregate all the information for each row in the report and then formatting the delimited list in report builder for cleaner presentation, which I did not want to do. Was hoping for a standard way to do it in SSRS Report Builder so it would be easy to apply it to other situations. A co-worker is having a similar issue, but my SQL solution will not work for him (helping him implement a possible dynamic pivot solution).. — MrFeet

MrFeet MrFeet · Accepted Answer · 2016-01-06T17:59:51

So I determined that it is NOT possible to do what I wanted. I ended up implementing my own SQL solution, which involved creating a comma delimited list in SQL to aggregate all the information for each row in the report and then formatting the delimited list in report builder for cleaner presentation (did a replace on the comma with a CrLf).

Was hoping for a standard way to do it in SSRS Report Builder so it would be easy to apply it to other situations. A co-worker is having a similar issue, but my SQL solution will not work for him (helping him implement a possible dynamic pivot solution or the partition rank solution).

SSRS row column grouping issue

1 Answers