Controlling row numbers in Matrix

Question

Controlling row numbers in Matrix

lskidgel

SSC-Addicted

Points: 456
More actions
July 29, 2015 at 11:30 am

#299272

I need to display a specific row number for each page; #1-17 on page 1, 18-30 on page 2 etc.
My matrix column grouping include 4 sections; 'week 1', 'week 2', 'week 3'...
Each section has the same questions, so I only want the questions to display once on the row grouping and their answers in each corresponding column. That display is working properly.
My problem is, when I try to use an expression such as this; IIF(RowNumber(Nothing)<= 17, false, true)
or this; IIF(RowNumber(Nothing)>= 17 And (RowNumber(Nothing)<= 40), false, true)
as a hidden expression on the Question Row Group, to display only the designated # row, the table is blank.
I checked how the matrix is counting the rows: RowNumber(Nothing), so I see the problem, I just don't know how to fix.
Can anyone tell me how I can count the actual # of rows displayed rather than the row # from dataset?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mohamed I. SSCrazy Points: 2774 More actions · Answer 1

lskidgel (7/29/2015)
I need to display a specific row number for each page; #1-17 on page 1, 18-30 on page 2 etc.
My matrix column grouping include 4 sections; 'week 1', 'week 2', 'week 3'...
Each section has the same questions, so I only want the questions to display once on the row grouping and their answers in each corresponding column. That display is working properly.
My problem is, when I try to use an expression such as this; IIF(RowNumber(Nothing)<= 17, false, true)
or this; IIF(RowNumber(Nothing)>= 17 And (RowNumber(Nothing)<= 40), false, true)
as a hidden expression on the Question Row Group, to display only the designated # row, the table is blank.
I checked how the matrix is counting the rows: RowNumber(Nothing), so I see the problem, I just don't know how to fix.
Can anyone tell me how I can count the actual # of rows displayed rather than the row # from dataset?

Hi

You need to play with the page break property of the group "WeeklyNrRow"

the Grouping Expression for "WeeklyNrRow" must be :

=Int(RowNumber(Nothing)/17)

If this does not work:

It appears RowNumber does not fit in the matrix on the contrary of the table data region

Then I think you will have to attach a rownumber in sql for each grouping set using the ranking function

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

lskidgel SSC-Addicted Points: 456 More actions · Answer 2

Thank you for your reply, but I am not trying to specify only 17 rows per page, as the 2nd and subsequent pages will have different # of rows, and modifying page breaks will not help me.

Changing my dataset is unfortunately not an option.

Is there any other expression that might help me control the # of rows that display on the report, rather than what is on output?

Mohamed I. SSCrazy Points: 2774 More actions · Answer 3

Ok.

Can you tell me if you page break follow a specific rule ?

you said first page show rows from 1 to 17

then page 2 shows rows 18 to 30

and page 3...

?

If you have specific rules for each page maybe you have to create a specific dataset with static values

such as

PageNumber RowBreak

1 17

2 30

3 ??

Then match the page number with this table to determine when to break the page

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

lskidgel SSC-Addicted Points: 456 More actions · Answer 4

I apologize, I'm not sure I made myself clear so let me try again.

I have a set of data (questions) my users access once a week and enter some kind of answer.

They are the same questions every week so rather than display my data in a table with the same questions repeated for each week, I am grouping by Row - Fields!QuestionText.Value and then grouping by Column - Fields!Week.Value.

I am creating a separate table for each page, just because I have other information at the top that I can't put into the header, so I can't have 1 table and have it page break.

But when I couldn't get my table to display the rows I wanted, I created an extra column to show me the actual row numbers (RowNumber(Nothing)), but instead of seeing 1,2,3,4, etc. I am seeing the numbering start at 30, 35, 40, 45, etc. Which are Q#s from the overall data generated by the user. So I cannot use the row number from the generated data.

Is there a way to count the rows after they are displayed in the table?

Mohamed I. SSCrazy Points: 2774 More actions · Answer 5

Ok.

sorry I effectively did not understand your problem

I apologize

Can you post your query please ?

I want to see how you display data.

Can you solve your problem by using the ranking function ROW_NUMBER() ?

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t: