ssrs 2012 report have page break parameter based upon the 'sort' parameter value

Question

ssrs 2012 report have page break parameter based upon the 'sort' parameter value

wendy elizabeth

SSCoach

Points: 16915
More actions
December 20, 2019 at 2:30 am
Go to Answer
#3709291

I would like to know how ssrs 2012 report can have page break parameter set up based upon the 'sort' parameter value that is selected.
In a new ssrs 2012 report, I need to setup a page break based upon the sort parameter that a user requests.
Basically the sort parameter values are an integer value the parameter values are the following:
parameter value = 0 means sort the student name alphabetically,
parameter value = 1 means sort by the grade level of the student,
parameter value = 2 means sort by the 'team' the student is assigned to,
parameter value = 3 means sort by the Homeroom teacher name the student is assigned to,
parameter value = 4 means sort by the Homeroom number that the student is assigned to.
The default value = 0 where the means sort the student name alphabetically.
I will have a page break parameter that default value is a 'text' value = 'False' which means 'No',
There will be a page break parameter value = 'True' which means 'True'.
The 'main' dataset that is brought in has all the information that can be selected in the sort by a
unique student number.
The data appears in a 'table' object. Each item in the table has 'group' level properties that I can see
sort and page break properties that can be selected.
David Burrows

SSC Guru

Points: 65138
More actions
December 22, 2019 at 12:18 pm
Answer
#3709838

This maybe due to mixed data types and if grade levels is numeric then this would be the case. You could try to make all sort values character and add leading zeros to converted numeric values ie 0009,0010,0011,0012.
You could pass the sort parameter to sql and get it to return a sortvalue varchar column and reference that in the group.
Far away is close at hand in the images of elsewhere.
Anon.

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

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

David Burrows SSC Guru Points: 65138 More actions · Answer 1

Add a parent group and set the 'Group on' using an expression that selects the Field(s) depending on the sort value and set page break on that group.

Far away is close at hand in the images of elsewhere.
Anon.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

Could you give me visual examples of what you are referring to? What do you mean by 'Group on'?

Below is an example of the parent group that can be used. Would you tell me what I need to do from this point forward? Untitled

David Burrows SSC Guru Points: 65138 More actions · Answer 3

In yours it is 'Group by', click in the fx button to show the expression box and use something like this

=Choose(Parameters!SortParam.Value+1,
Fields!StudentName,
Fields!Grade,
Fields!Team,
Fields!Teacher,
Fields!HomeroomNumber
)

Far away is close at hand in the images of elsewhere.
Anon.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 4

Thank you for your answers so far! Based upon your logic the page breaks are occurring at the correct spots!

However I still need more work.

For the query you suggested, I replaced the logic with the following:

=switch(Parameters!pagebreak.Value ="True" and Parameters!sort.Value=1,Fields!grade.Value,Parameters!pagebreak.Value ="True" and Parameters!sort.Value=2,Fields!SchedulingGroupname.Value,Parameters!pagebreak.Value ="True" and Parameters!sort.Value=3,Fields!HomerooomTeacher.Value,true,nothing)

The page breaks are occurring, however the values are not in ascending order.

When I select a high school the grade levels appear in the following order:

10,12,9,11. However I would like the grade levels to appear in the following order: 9,10,11,12.

Thus I am wondering what you can suggest to fix this problem.