StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think your data presents a bit of a problem.</p> <p>As you've already figured out, typically for this sort of setup you'd set up a row group with an expression like:</p> <pre><code>=(RowNumber(Nothing) - 1) Mod 6 </code></pre> <p>And a column group expression like:</p> <pre><code>=Ceiling(RowNumber(Nothing) / 6) </code></pre> <p>This would create a six row tablix that would grow horizontally as required.</p> <p>See <a href="https://stackoverflow.com/questions/17749154/ssrs-show-data-in-horizontal-layout/">this</a> SO question for a similar example.</p> <p>However, you currently have the requirement of also grouping by another column - <code>pass_no</code> in your case. Normally you can approximate a group-level row number with an expression like:</p> <pre><code>=RunningValue(Fields!pass_no.Value, CountDistinct, "DataSet1") </code></pre> <p>Unfortunately, when you try to add this into one of the grouping expressions like:</p> <pre><code>=Ceiling(RunningValue(Fields!pass_no.Value, CountDistinct, "DataSet1") / 6) </code></pre> <p>You get the following error:</p> <blockquote> <p>A group expression for the tablix 'Tablix1' includes the aggregate function RunningValue. RunningValue cannot be used in group expressions.</p> </blockquote> <p>Based on all this, my recommendation is to try and get a Dataset that has one row per <code>pass_no</code> value and base the tablix on this, with the above row/column grouping expressions, i.e. no need to group on multiple <code>pass_no</code> rows. So in your example it would have eight rows. You could then have a separate Dataset with all the individual rows and use a <code>lookupset</code> function to concatenate the <code>description</code>, etc.</p> <p>Your other option is to try and get everything on one Dataset only, including the aggregates as required. This might not be possible, but for <code>description</code> at least you can leverage any of the various techniques <a href="https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string">here</a> to get a delimited list. Once you have this list you can replace the delimiter with <code>vbCrLf</code> to split it back over multiple rows.</p> <p>All this is a very long-winded way of saying that I don't know if your requirement is possible with your data, but if you look at having at least one Dataset with one row per <code>pass_no</code> you should be able to make it work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload