SSRS Row Group + Column Group = RowNumber Issue

Question

I'm back with another SSRS question :-)

I'm dealing with survey data. I have a procedure that's returning an organization's response counts per question. So my report is defined as Group on Organization for row and Group on answer for columns. Both the number of organizations and answers are variable. That's working as expected. I've tried adding a RowCount next to the organization so that I can show rank, but the fact that each org has one row per question means that I'm getting eight rows per org.

Here's an example:

Here is my report definition:

The rank expression is currently: =RowNumber(Nothing)

Ideally, the rank would be 1, 2, 3, 4, etc... I've tried scope to the row group, column group and nothing. No help.

Any assistance would be greatly appreciated!

Silvereye

Answer 1

I seem to have found a solution, but it feels like a hack... I'm leaving this unanswered to see if someone else can provide a better solution (read less hackish).

My Rank Expression is now:
=RowNumber(Nothing)/Count(Fields!AnswerText.Value)

Everything seems to be ok. I suppose I should IIf(Count... = 0, Then RowNumber, else what I've got...

Silvereye

With nothing else showing up, this approach is working even though it seems like a hack. To me, if it's displaying aggregated data, it should RowNumber on the aggregate NOT the raw data... – Silvereye

Note: This solution only works when each group contains the same number of detail records. – Alboran

Answer 2

Had same frustrating issue; lots of time wasted. Eventually, this solution also helped:

=RunningValue(CountDistinct("YourTableName"),Count,"YourTableName")

Trick here is NOT to use the name of the group within the table/matrix, but the name of the table itself. And yes, one would think that using the table name for the scope in the function RowNumber should work, but it doesn't.

Delano

Thank you, this sorted a problem that has been doing my nut for days. – Bifacial

+1 for this .. this helped do wonders !! the table name for me is the name of DataSet – Gandzha

I think we should enter DataSetName instead of "YourTableName" - so it would be something like =RunningValue(CountDistinct("YourTableName"),Count,"YourDataSetName") – Electrum

Answer 3

Try using:

runningvalue(Fields!AnswerText.Value,CountDistinct,"NameOfOrganizationGroup") 

If its a matrix, change the name of the scope from the row scope to the matrix scope.

Antilogarithm

Answer 4

I do with custom code.

Add this to code section on report config:

Dim private count as integer = 0
Dim private iniRow as integer = 0
Public function nroFila(Byval rowNum as integer) as integer
    if iniRow = 0 then
        iniRow = rowNum
    end if

    if rowNum = iniRow then
        count = 0
    end if

    count = count + 1
    Return count
End function

Then, call the function in a cell inside the group:

=Code.nroFila(RowNumber(Nothing))

Komsa

Answer 5

Best thing to do here, is make the Rank column equal to =RowCount()/8

Since your sure each visible row contains a total of 8 rows, this should work fine.

Brentwood

Jon, thanks for the reply. The issue is that I forgot to mention that the number of answers is variable... I neglected to mention that... – Silvereye

Answer 6

Add another rank column next to the existing one and put another expression in that one which takes the value from rank (rowcount?) and divide it by 8. Then make the old rank column invisible.

Hadwin

Answer 7

Are you absolutely certain that using RowNumber("NameOfOrganizationGroup") doesn't work?

Click on the matrix, click the upper-left corner selection box to select the entire thing, then right-click on the selection border and get properties. Switch to the Groups tab and look at the names of the groups in the Rows section. That's what goes in the scope of the RowNumber() function.

If you already know this and tried it, my apologies—I didn't mean to assume you didn't know. It's just not 100% clear from your question that this is not the solution.

Hildegardehildesheim

Answer 8

I got it by using a windowed function in the SQL query, this counts the row correctly within the column set.

dense_rank() over (partition by mgr.employee_sk order by e.employee_sk) as row_format

where mgr.employee_sk is my Lvl 2 Row Group, and e.employee_sk is my Lvl 3 Row Group (the detail level).

Then the SSRS expression then refers to this column from the query. To avoid it aggregating I used a min function, and to avoid it not displaying for rows that don't have data in all columns of the column group I specified the scope as my Level 3 Row Group.

=Iif(min(Fields!row_format.Value, "Employee") mod 2 = 1, "white", "aliceblue")

Signor