1

I need to design a report that has some very specific requirements that I am having problems with.

The report needs to have fixed margins at the top and bottom (to allow for pre-printed content on paper). In the body of the report, there needs to be two seperate columns of data (student information). Below this, there needs to be a section that will contain information that will wrap to a new column depending on its length (student course and grade information). In addition, the course/grade information cannot break up a given academic year. Finally, there needs to be messages indicating "(End of Column)" and "(End of Transcript)."

Also, due to the nature of the data, I currently have the different sections of the report broken into sub-reports.

What would be the best way to design this report?

Bill
  • 11
  • 2

1 Answers1

1

You can use a Tablix to allow your data to be displayed in two columns.

For the rows in the tablix you can leave "CanGrow" as "true" to ensure that it wraps and you will need to set grouping on your datasource based upon year.

You may need to set up a new field in your database that is set to only the year, then you can create a group on that field in your report. If this is SQL Server, you can add the following to the end of the "SELECT" statement before the "FROM" statememt 

,DATEPART(YEAR,DateField) AS Year

For "End of column" and "End of Transcript" you will use grouping. You can set group headers and footers to display messages as required.

tutorial on setting up groups:

http://database.blogs.webucator.com/2010/09/10/add-format-and-grouping-to-a-report-in-sql-server-reporting-services-2008/

Update in response to op comments:

In addition, you can use expressions in to group on for data, so you can probably group with the following expression:

=FORMAT(Parameters!YearField.Value,"yyyy")
Frank Pearson
  • 872
  • 5
  • 16
  • Setting up a column grouping on year won't really work (since the data for any one year only takes up a small portion of the page), and, there is usually going to be 4 years worth of data. I have considered trying to add additional data into the dataset to group on (based on the amount of data from the SP), but, would rather not have to go that route (because it would get messy). – Bill Jul 25 '11 at 12:44
  • What do you mean by "course/grade information cannot break up a given year"? If you mean you want that data averaged or aggregated in some other way, then delete the details row and only use a footer row and use AVG or SUM expressions as required and group on an expression like I just posted. If not, let me know. I know we can resolve this stuff :) – Frank Pearson Jul 26 '11 at 18:30
  • Sorry...I meant that a given year's information cannot get broken up across columns. Also, I want to avoid trying to set up a column grouping by year since there isn't any hard and fast way to ensure the data gets broken up correctly (for example, academic year w and x fit in one column and academic years y and z). – Bill Jul 29 '11 at 18:35
  • You should be able to use KeepTogether to true and wrap each row's cells content in a rectangle to ensure that they are not broken up between pages. That way you can get one or many years on a single page, and still ensure that you don't break any individual year up. Does that work? – Frank Pearson Aug 01 '11 at 15:40
  • KeepTogether on the contents of the rectangle. – Frank Pearson Aug 01 '11 at 15:49
  • It still doesn't really help me solve my problem (that is, getting the grade data (currently in a subreport) in a multiple column format (and, having it be a fixed height on the report)). In order to use column grouping, I can not use academic year (since there will usually be between 2-6 on a report) and, I have not been able to come up with another way to group the data accordingly. Hopefully that makes sense. – Bill Aug 02 '11 at 17:58
  • So you have two columns on the top of the report, left to right, that contain student information? Then you have another row under the top row that contains two columns that contains 1 to n (or is there a fixed number of columns?) columns of course/grade info that will not break up a year, but will wrap (left to right) to the next column? I want to verify that I have an accurate picture of your report. If you could link to a visual representation this could help me more. – Frank Pearson Aug 04 '11 at 04:03
  • Here is a screenshot of the format of the report: http://imgur.com/Fy8g7.jpg The top section will be fairly staticly sized. The middle section that has the academic information needs to break into multiple columns if it exceeds a certain height (probably only ever 2 columns), and, the academic year needs to stay together. I currently have it broken out into subreports due to the different sources of data. Does this help (and, does what I'm trying to do make sense)? – Bill Aug 10 '11 at 15:49
  • Yes it does make sense. Here is something on making a multi-column report: http://stackoverflow.com/questions/1015098/ssrs-how-to-build-a-simple-multi-column-report As far as your school year, you are going to want to set a field in the database, for example 2009 for the 2009 to 2010 school year and you will want to group on that. Sorry it took so long to respond. I have been busy. – Frank Pearson Aug 10 '11 at 18:34
  • you could also write an expression like the following to group on for year. =IIF(DATEPART(DateInterval.Month,fields!datevalue.Value) < 8,DATEPART(DateInterval.Year,fields!datevalue.Value) - 1,DATEPART(DateInterval.Year,fields!datevalue.Value) – Frank Pearson Aug 10 '11 at 18:40
  • There isn't a problem being able to group by the academic year. The problem is making that section (the section with grades) be multiple columns, while the section above it is not (the student information needs to be in a fixed format). I cannot fit all of this disparate data into one dataset, which is why I don't think a straight multi-column report will work, and, multiple column sub reports do not work. – Bill Aug 11 '11 at 12:22
  • You could construct a table for the middle section with two columns and one row. In each of the rows (delete the detail row and use a header or footer),drop a table in each of the two columns with details iterating through the all of the contents on both sides. Inside of each column's inner tables, set the visibility on the rows based upon the RowNumber function. You will know how many rows will fit in your report on the page. Use that number. On the left hide all rows greater than the number. On the right hide all smaller. – Frank Pearson Aug 11 '11 at 18:28
  • In the first line, second sentence, it should read "In each of the columns" not "In each of the rows". – Frank Pearson Aug 11 '11 at 18:35
  • That would work for getting the content in two columns, but, there would be no guarantees that the data for a given academic year would not get split up. – Bill Aug 19 '11 at 19:20
  • You can also group on academic year on both the left and the right. If you wrap your content in a rectangle that will ensure that it does not break. – Frank Pearson Aug 20 '11 at 00:11