Grouped table data repeat on every page

Question

Hi ,

I have a requirement from a client who maintains school students information where the data of a table needs to repeat on every page based on its dependent data. The report spec is grouped as mentioned below,

District-->schools group-->schools.Below are the screenshots of the spec where the report needs to look like.

fig1

fig2

so, per the above sreenshots, the learning commuinty is the schools group and attending school is the school. Looking at fig1, ( the count of students in district and learning community needs to repeat on every page along with its attending schools dynamically).once a new school group starts, again the count of the students under that school group needs to change and its dependent attending schools data should give the details.This should happen on every page sequentially.

so far , i was able to get the schools data on every page by putting the table in a list grouped by its respective school,and this list is again a part of another list which is grouped by its respective school group.( nested list ).But unable to repeat the school group count on every page, it would just display for the fist time when a new school group starts, but i need that count to be repeated until another new school group starts. Also the count of students in districts should also repeat on every page. Hope, this makes clear of what i want to achieve.

Thank You,

Shiv

Answer 1

My suggestion is, instead of using nested list ,  try using Parent-Child grouping.

In your case, for instance

1) "District" as parent

2) "Learning Community" as child of "District"

3) "Attending school" as child of "Learning Community"

Then use Add Total Before from the grouping panel, and place it appropriately.

I know for all this you need to rework :(

Hope this helps.

Answer 2

This does not make the values repeat on every page.

Answer 3

Hi Shiv,

According to your description, I have reproduced the scenario. It seems that you have placed the text box which displays the school group data in the outermost list. To achieve your goal, you only need to move the text box from the outermost list to the innermost list (embedded list).

Reference:
Adding a List

If you have any questions, please feel free to ask.

Regards,
Mike Yin

Answer 4

Hi Mike,

I tried that option, it was giving me the count at Attending school level rather than its group school level( learning community in this case).

Eg: in the above screen shot, the count at

Learning Community: Central Secondary LC is  28 and this count should be repeated on every page until that school group ends or new school group starts.

when i tried the above option given by you, i was getting the count as

Learning Community:Central Secondary LC 2 , which actually its grouping at its lower level ( ie attending school in this case).

The list is grouped at attending school level , so i'm unable to get the count for a text box or any table which is inside this list and get a count of stuednts for a group which is a  level higher that it . Is there any possibilty of using variables ??

Thank you,

Shiv

Answer 5

I've created another dataset to calulate the count at district level and schoolgroup level and used that count in the list which is grouped at attending school level.

The count for district level works, and is repeating on every page as i want but the schoolgroup count is not working.

Eg: in the above screen shot, what i get is

District: 98 ( i've put in a seperate table getting the count from a different data set, the table is grouped at district level and is inside the list)

Learning Community:Central Secondary LC 2 ( i've put in a seperate table getting the count from a different data set, the table is grouped at school group level and is inside the list)

Please let me know how i need to get this corrected.

Thanks,

Shiv

Answer 6

Hi Shivn,

Thanks for your post.

From your description, I think that you get the number of students using the Count(Fields!Attending_School.Value, scope) function. In this condition, if we move the textbox from the outermost list to the embedded one, we cannot get the correct value by using the Count function due to the scope restrictions.
At this time, I assume that you get the number of students for “District” by using the expression =Count(Fields!Attending_School.Value, “DataSet1”). To avoid the issue, there is a work around for your reference. In this workaround, only one List control is required.

1. Create a table the same as the one in your screenshot. Drag a List control to the Design surface, and then drag the table into the list control.
2. Set the “DataSetName” of the list to “DataSet1”, group the list on “[Attending_School]” field and modify the group name to “Attending_School”.
3. Add a parent group “Learning_Community” for the details group of the list. Right-click on the textbox which contains the newly created “Learning_Community” group, and set the visibility of the textbox to “Hidden”. Then, drag the right border of this textbox to the left border of the list to minimize the textbox.
4. Drag proper textboxes to the list above the table. And then use the expressions below to return values for corresponding items:
   District:      =Count(Fields!Attending_School.Value, “DataSet1”)
   Learning_Community:       =Count(Fields!Attending_School.Value, “Learning_Community”)
   Attending School:       =Count(Fields!Attending_School.Value, “Attending_School”)

The report design surface and report preview layout looks like:

Hope this helps.

Regards,
Mike Yin

 

Answer 7

Hi Mike,

Thank you, for the post. I almost got it, but a smallo glitch. Below are the results which i get,

the student count is

=count(Fields!StudentPIF.Value)

I used the another dataset at schoolgroup level (learning community) but no use, it just replicates teh value at the highest level( i.e district level).

even if pull from one dataset also, i get teh count at the highest leve. I dont understand why is it aggegating at highest level for school group.

Please let me know how you got it.

Thanks,

Shiv

Answer 8

Hi Shivn,

Thanks for your post.

For your concern, I would like to explain that the newly created dataset2 still contains all the data based on the District level even it doesn’t include the “District” field. If you want to retrieve data from dataset2 based on school group level, you need to add a filter to the dataset2. If you do so, you have to create a dataset for each school group. So, it doesn't make sense. 

To resolve the issue, we need to add a parent group “Learning_Community” for the list details group (i.e. Attending_School) and then get the correct values for school groups from this scope. As I have posted above, you can hide and minimize the textbox which contains the “Learning_Community” group in the list. To get the correct values, you can use the expressions below:
District: =Count(Fields!StudentPIF.Value, “DataSet1”)
Learning_Community:   =Count(Fields!StudentPIF.Value, “Learning_Community”)
Attending School:    =Count(Fields!StudentPIF.Value, “Attending_School”)

When adding page breaks to the list details group, please make sure to check the “Also at the end of a group” option.

Hope this helps.

Regards,
Mike Yin

Answer 9

Hi Mike,

I'm really sorry to ask you again, but i dont understand i get the count again at the highest level. Below is the screenshot of my report

Added the pagebreak as you have said but it wasnt working.The count for school group is not working even if i use a text box or a table. Shall i send the stored proc if that helps...

Thank you,

Shiv

Answer 10

i get the learning community count correctly if i place the textbox inside the newly created learning community group and make the text box visibility set to "show".

But it will be beside the main table. Below is the screen shot

Answer 11

Hi Shivn,

Thanks for your feedback.

What is the expression that you use to get the value for “Learning_Community”? The scope in the expression should be set to “Learning_Community” like:
=Count(Fields!StudentPIF.Value, “Learning_Community”) 

If you have any questions, please feel free to let me know.

Regards,
Mike Yin

Answer 12

Hi Mike,

Thank you very much , actually i was using wrong expression and now the above expression works perfectly. I have about 10 reports based on this logic and i'm really happy and thanks again for taking time and helping me.

Regards,
Shiv

Answer 13

Hi Mike,

I have an additional requirement from a client where the text boxes( which show the summary) when report is exported is exported to pdf format need to repeat on every page but when exported to excel format should not repeat on every page. When exported to excel only the details table needs to be seen. Can you please let me know if this is possible in SSRS 2008. Actually, the main assignment is to convert Jasper  reports to SSRS 2008, so in that process the Jasper reports were able to achieve the above mentioned task and I'm unable to know if that is possible in SSRS2008. Please advice

Answer 14

Hi ,

I got the solution, we need to use the below expression in the hide\show option under visibility section of any textobx or tablix or table.

=(Globals!RenderFormat.Name = "EXCEL")

we can use the same expression for any rendering formats by replacing the word "excel" to any other formatting type like pdf,xml etc.

Answer 15

Hi Shivn,

Thanks for posting and sharing your answer.

You are right. We can configure the visibility of the textboxes by using the expression:
=iif(Globals!RenderFormat.Name="EXCEL", true, false)

Regards,
Mike Yin