Displaying lines for entire body of a page

Question

I have a problem that is very similar to an issue that Jin Chen address back in 2009. I am using Report Builder 3.0 over a SQL Server 2008 R2 database.

My application prints a variety of forms such as Invoices, Sales Orders, Statements, etc. These forms are printed on blank, multi-coloured paper so I have to format all the required boxes and lines for the forms. To use the Invoice as an example, I print the customer and shipping information in the Page header and the Totals for the Invoice in the Page footer.

The details are printed in specfic columns of a table in the body of the report. Each column is separated by a vertical line. The problem that I have is that if the invoice only has 2 or three lines, the vertical lines are only printed for those lines. I require that the lines extend all the way to the bottom of the page terminated with a horizontal line.

Jin's solution was to put the table inside a rectangle and place vertical lines inside the rectangle that would be the required size for the page. The rectangle would then expand to the length of the line. This appeared to work for a 'Single Page' invoice. However, if the report generated an invoice with more than one page or a report with more than one invoice, the vertical lines only showed on the first page. I have numerous forms that I have to create.

Does any one have a solution that would address this 'Multiple Page' scenario.

Thanks

---

Leonard

Answer 1

Does anyone have a solution for this?

I found another post that suggested using a List object and placing the lines inside the listbox. This resulted in the lines to printing on additional pages but the lines still will not print to the bottom of the page.

This is a deal breaker for me. If Report Builder is not capable of handling this requirement I am going to have to look for a different reporting option. It's too bad, as dispite Report Builders obvious limitations in areas such as headers and footers, I was actually begining to understand and like Report Builder. Since SAP stopped supporting the Crystal VLC in Delphi I have over 300 reports I need to convert to a new reporting platform. SSRS seemed the obvious choice as I am already heavily tied MS SQL.

Please, if anyone has any suggestions, it woudl be greatly apprciated.

Thanks

---

Leonard

Answer 2

Hi ldmann,

Thanks for the confidence in Microsoft. For your requirement, I would suggest you test it from below two directions.

First, drag the data region and the vertical lines into one list control, set the height of the list equals to the report’s height, set the height of the vertical lines equal to the report’s height.

I had handled one similar thread, please refer to: Repeating Lines through multiple pages in SSRS.

Second, display the Totals for the Invoice and horizontal line in the Page body instead of Page footer.

Hope them helps you. If you any other questions, please feel free let me know.

Thanks,
Sharp

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 3

Hi Sharp,

Thanks for responding to my issue. Plese bear with me as I am new to Report Builder and just want to make sure it will meet the majority of my requirements.An example of my scenario is that I have a query that returns information on 2 invoices. One invoice has 2 detail lines and one has 34 detail lines. I need to display the customer data at the top of the report, the detail lines in the middle and the totals at the bottom. The invoice needs to display the page count somewhere (either at the top or bottom). The page count on the first invoices would show 'Page 1 of 1' and the second invoice would show 'Page 1 or 2' on the first page and 'Page: 2 of 2' on the second page. Total pages for the reprot willb e 3. I have been able to accomplish the page count requirement, but from what I understand, the page count MUST be placed in either a Page Header or Page Footer.

 I am not sure what you mean by 'Drag the data region into one list control'. If I place a list control on the pallette and attach my Invoice Dataset to it then make it the size of my page (11"), I have a list control with a very large detail section. I then place a vertical line in the list control. I then drag the line number, description, qty, price and total to the list control. When I run the report, I get 36 pages. Each page has one invoice detail line with a vertical line running the full length of the page from top to bottom.

I found your thread: Repeating Lines through multiple pages in SSRS but was not able to get the vertical lines to show past the last detail line.

Can you provide me with more detailed steps or perhaps a sample report that accomplishes this.

Your help is very much appreciated.

---

Leonard

Answer 4

Hi ldmann,

Thanks for your detail reply, please test below detail steps in your report.

Assuming you display the two invoices in Tablix1.

1. Drag one list control into the design surface of the report, such as Tablix2. Set Tablix2’s height equals to the report’s height, set Tablix2 has the same dataset name of Tablix1.

2. Click anywhere in the Tablix2, right-click the default row group in the Row Groups section. Select Group Properties, click the Add button, type 1 in the drop-down list of Group on, click OK.

3. Drag Tablix1 into Tablix2, drag lines into Tablix2 above Tablix1. Set these lines in vertical style and set the height of the vertical lines equal to the report’s height.

Please preview the report, if it doesn’t achieve your requirement, please let me know.

Thanks,
Sharp

  

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 5

Hi Sharp,

While waiting for a response I tried numerous scenarios in an effort to accomplish my desired result, including your proposed solution above.

In order to effectively test your proposed solution, I need enough detail lines to span multiple pages so I added another invoice to my query. The first invoice has 2 lines, the second has 35 lines and the third has 60 lines. The following explains my results of the simplest of tests.

I begin with a clean palette with a page length of 11”. I add a table to the palette (tablix1) that is attached to my invoice query. I then add a List control (tablix2) that is also attached to my Invoice query and make sure the height of the list control is the same as the report ( 11”). I add a Row Group ‘1’ to my list control using the ‘Row Group’ properties. I dock the list control to the top of the page. I then drag tablix2 into the List control and dock the table at the top of the List Control. I then drag a vertical line into the List Control and set the height to 11” and make sure it is docked at the top of the list control.

When I run the report I get lines 1-2 of the first invoice, lines 1-35 of the second invoice and lines 1-7 of the third invoice on page 1 with a vertical line running from top to bottom. Lines 8-53 of the third invoice print on page 2 with NO vertical line and lines 54-60 print on page 3 again with no vertical lines.

If I add a page header and page footer I get lines 1-2 of the first invoice, lines 1-32 of the second invoice on page 1 with a vertical line running from top to bottom.  Lines 33-35 of the second invoice and lines 1-33 of the third invoice print on page 2 with a vertical line printing on the first 9 detail lines. The rest of page 2 and page 3 have no vertical lines.

If I change the Group on the List control to use the Invoice# I get some rather strange results where the vertical line prints on the first 3 pages, ¾ of the 4^th page and nothing on last page. I attributed this strange result to the page size of the List Control still being 11” even though I added page headers and footers.

So I made the list control smaller to accommodate for the page header and footer and did a page break on my group (which is now Invoice#). This resulted in Page 1 printing lines 1-2 of the first invoice with a vertical line running from top to bottom of the detail section (Perfect!). Page 2 printed lines 1-34 of the second invoice with a vertical line from top to bottom of the detail section (Perfect!). Page 3 printed line 35 of the second invoice with NO line (not good!). Page 4 printed lines 1-35 of the third invoice with a vertical line (Perfect!).  Page 5 printed lines 36-60 of the third invoice with no vertical line (not good!).

It appears that the line does not print when the detail data of tablix1 spans multiple pages.

My struggles continue.

Any more help is appreciated.

Thanks,

---

Leonard

Answer 6

I have now spent far too long trying to accomplish this simplest of tasks in Report Builder 3.0. As much as I would like to continue with a Microsoft Reporting Solution for my application, it is apparent to me that Report Builder is simply not going to be able to meet my basic reporting needs.

I don’t understand how this issue has not arisen before, but if anyone has any ideas as to how I can get lines to print properly for a ‘form’ it would be greatly appreciated.

Thanks

---

Leonard

Answer 7

Hi ldmann,

I should apology for the late reply.

If it is convenient to you, please post the SQL Server version, the DDL files and sample data, and the expect result to the following E-mail address, and then I will try to assist you solve this issue.

E-mail: sqltnsp AT Microsoft.com (Please replace AT with @)

(Note: Please begin with “Hi, Sharp Wang” in the body of E-mail)

Thanks,
Sharp Wang

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 8

Hi ldmann,

I had reproduced the scenario based on the posted data, detail steps just like below.

1. Drag one table into the design surface, such as Tablix1. Bind LINE_NUMBER, QUANTITY and UNIT_PRICE to Tablix1.

2. Drag one list into the design surface, such as Tablix2, set it has the same dataset as Tablix1.

3. By default, Tablix2’s Row Group is Detail1. Right-click Detail1, select Group Properties. Click Add button, select INVOICENO in the drop-down list of Group on. Click Add button,type in the And on textbox with expression: =Ceiling(rownumber(nothing)/15).

4. Click Page Breaks in the left pane, check the check box of Between each instance of a group, click OK.

5. Drag three lines into Tablix2, set their height equals to Tablix’s height.

6. In order to avoid the blank page when exported to PDF, please set report’s margins are 0cm.

You can get the sample report rdl from here: Displaying lines for entire body of a page_final.rdl.

Hope it helps you. If there is anything unclear, please feel free let me know.

Thanks,
Sharp

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 9

Hi Sharp,

Thanks so much for working on this problem with me.

The solution you proposed works wonderfully for the simplest of examples where the size and number of detail rows in the detail section is consistent. However, an invoice also contains descriptions of the product and ‘possibly’ additional comments for the detail line. I apologize for not including more complex data in the DDL that I provided to you.

The problem I now have is that if a description is quite long, it needs to word-wrap. When a word-wrap occurs, the height of the line increases and as a result, the detail prints beyond the length of the line and may even cause an additional page break to occur.

The same issue occurs for the comment as well. In addition to this, if a comment does not exist for the detail line, then I need to suppress the detail row that contains the comment.

Is there a way that Report Builder can handle variable heights and suppressions of detail lines and still maintain a consistent line length for each page?

Thanks,

---

Leonard

Answer 10

Hi ldmann,

If the comment is for the invoice, you can drag the second list into the design surface, then add one textbox into this list, bind the comment field to this textbox, finally, drag the previous list into this new list too. Detail steps as below.

1. Drag one table into the design surface, such as Tablix1. Bind LINE_NUMBER, QUANTITY and UNIT_PRICE to Tablix1.

2. Drag one list into the design surface, such as Tablix2, set it has the same dataset as Tablix1.

3. By default, Tablix2’s Row Group is Detail1. Right-click Detail1, select Group Properties. Click Add button, select INVOICENO in the drop-down list of Group on.

4. Drag three lines into Tablix2, set their height equals to Tablix’s height.

5. Drag one list into the design surface, such as Tablix3, set it has the same dataset as Tablix1.

6. By default, Tablix3’s Row Group is Detail2. Right-click Detail2, select Group Properties. Click Add button, type in the Group on textbox with expression: =Ceiling(rownumber(nothing)/15).

7. Click Page Breaks in the left pane, check the check box of Between each instance of a group, click OK.

8. Drag one textbox into the bottom of Tablix3, such as Textbox2, bind the field of comment to Textbox2.

9. Drag Tablix2 into Tablix3 above Textbox2.

10. In order to avoid the blank page when exported to PDF, please set report’s margins are 0cm.

You can get the new sample report rdl from here: Copy of Displaying lines for entire body of a page_final.rdl

Hope it helps you. If there is anything unclear, please feel free let me know.

Thanks,
Sharp

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 11

Hi Sharp,

I apologize for not being clear on my description of the problem. Your proposed solution for the comment works well if the comment is applicable to the invoice as a whole. However, the comments I am referring to are applicable to the detail line. (ie. would be included as a 'second' detail row in Tablix1.) This second detail row may or may not be suppressed depending on whether or not a comment exists for that detail line.

Additionally, the 'Description' I referred to earlier is also part of the first detail row in Tablix1. So, to use the example above, I would bind LINE_NUMBER, DESCRIPTION, QUANTITY and UNIT_PRICE to Tablix1 (First Row). The DESCRIPTION field may need to word-wrap which would, in effect, cause the height of the entire first detail line to increase. This would then then have an effect on my line count and, in turn, cause a problem with the vertical lines in the body of the report.

Any suggestion on how to resolve this issue? I can send you a revised DDL if required.

Thanks,

 

---

Leonard

Answer 12

Hildmann,

If it is convenient to you, please post the DDL files and sample data, and the final expect result to the following E-mail address.

E-mail: sqltnsp AT Microsoft.com (Please replace AT with @)

(Note: Please begin with “Hi, Sharp Wang” in the body of E-mail)

Thanks,
Sharp Wang

---

Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Answer 13

I guess I can safely assume that this fairly basic requirement cannot be accomplished in SSRS.

---

Leonard

Answer 14

I have the same problem,  in Visual Studio 2008 I created reports that have vertical lines extending down each page and I use the repeatwith property to accomplish new pages.   Now I have upgrade to VS2010 and suddenly all these vertical lines have disappeared and I cannot get them back on the report.

This really makes me sick, -  I have had 10 years of Crystal Report experience and 8 years of SSRS and it is still not possible to do simply things. Crystal is page based and SSRS is object based and this does actually not work well in the real world as report are generally paged based. 

The repeat with functionalty and tablix header etc is very badly designed with too many option an frankely it just does not work -  try dragging a rectangle into a static row header and the suddenly nothing is repeated. 

The real problem is the fundament structure of SSRS is badly designed and the MS guys cannot get things to work on such a bad structure.

-- Stay as far away for SSRS as possible, as one cannot implement the simplest of request.

Answer 15

Thanks for the input SteerB.

Coincidently, 6 months after originally looking at this issue, I recently thought I would take another stab it.

I may have found a solution for this particular issue. (As hokey as it may be, it appears to work)

I created a bitmap image using 'Paint' (or whatever) that had the vertical lines where needed. I then used this bitmap as the 'BackGround' image of the report of the body. I just ran some simple test with growing 'text' fields and results have been positive. I still have to size the report to 'Actual' size in the Development screen and dick with the top an bottom lines but at least I seem to have some hope. I haven't completed the report yet as I have other priorities but will post my results when I have them.

I agree that SSRS has some serious limitations when it comes to basic tasks. So far I have been able meet most of my requirements. There are some things I like about SSRS. Like its flexibilty of options, Although you may be right, that this is what is hampering some of its capabilities. There are other things that are just brutal to have to go through to accomplish the simplest of results. I was resigned to the fact that I would have to use a different product for my forms, but if this bitmap option works I will be very happy.

I am sure there many things, both good and bad that I have not yet experienced with SSRS but I will keep plugging along. I just wish I could get a response from MS about this. Are they going to fix it? Do they even think it is a problem? I have not heard from MS since sending the DDL and sample data 6 months ago.

---

Leonard

Answer 16

Hi Leonard,

Thank you for that suggestion, I think it will work in my case as all the pages have the same format.

Actually I have an application that I developed in 2000 with 50 reports in it. All these reports where written in Crystal V8 and whilst the development environment of Crystal was a bit flaky I managed to do all the report formatting with very little problems.

It is now 2012 and I am redesigning the app from scratch in Asp.net and C# and I am really stuggling with the reports. There are a lot of cases where what was simply implemented in Crystal cannot be done in SSRS and can only be acheived through round about tricks.  There is twelve years of difference surely they should be further down the road in terms of formatting.

Sorry I am just absolutely frustrated as I cannot spend all this time on silly issues - but thanks  it is also good to hear that I am not the only one struggling.

I just don't believe MS are putting enough effort into fix these simple formatting problems.

Answer 17

Hi lerko,

I am a little confused. Are you trying the proposed soution that is highlighted in your post? If so, that is a solution proposed by Microsoft and it does not solve the problem. At least I could not get it to work to my satisfaction.

The solution I proposed was to use a bitmap image of the lines as the background image in the body of the report. I have not done extensive testing on the proposed solution but initial tests were positive.

---

Leonard

Answer 18

Sorry Leonard,

My question was to Sharp.

Answer 19

Hiya Leonard,

I'm just starting to do more complex reporting with RB 3 with our LightSwitch apps.

Your suggestion using a background Image for a form is really helpful.  Many thanks for posting.

---

Garth Henderson - Vanguard Business Technology

Answer 20

Glad it was helpful. It's nice to be able to contribute something for a change.

Just as point of interest. You know how RB3 has trouble with rendering a double underline at the bottom of a page/report? It drove me crazy as I have a number of financial reports in my ERP application that require double underlines. Sometimes I would get a double underline sometimes not. I tried a number of suggestions I found on the subject but nothing worked to my satisfaction.

So I applied the same principle and created a 'Double Underline' bitmap and use it as the background of the text box. Works like a charm.

---

Leonard

Answer 21

Aloha Leonard,

Another great tip.  Thanks.

What technology did you use to write your ERP solution?

---

Garth Henderson - Vanguard Business Technology

Answer 22

Hi Garth,

It sounds like your home base is Hawaii. I'm choked - being that I'm from the figid North of Edmonton, Alberta.

The system is written in Delphi 7 but is scheduled for migration to Delphi XP. This was one of the reasons why I am now using RB3. Since acquiring Crystal Reports, SAP dropped Delphi VCL support for its newer Crystal products. Therefore, I was faced with using an 'Unsupported' VCL available in the Development Community or dropping Crystal and using another reporting platform. Since our back end is exclusively MSSQL 2008 R2 I thought it would make sense to go with SSRS for my reporting needs.

So the migration is kind of on hold until all the reports are converted from Crystal to SSRS. Crystal was OK. It had its Good and Bad just like SSRS.

I'm sure you have your own experiences with RB3. I find that RB3 has some very powerful capabilities but seriously lacks in some 'very basic' functionality and has some quirks that I find quite frustrating having to deal with.

---

Leonard

Answer 23

Yes, I grew up in Hawaii but did spend 11 years in LA trying to get a record contract (I play sax and keyboards).   My wife and I decided to have a family and I had to get a day job to pay for everything.

I've been very active in the LightSwitch forum since it started.   I'm just getting started with SSRS.   From this point, I figure that I'll be splitting my development time between LS and SSRS with integrated applications.  

I'm looking to figure out the best way to programatically move SSRS "stuff" between servers.  We will be hosting our apps and need to automate deployment and management.   We use HyperV VMs with www.cybercon.com and are very pleased with them.

I'd love to collaborate with other ERP developers to do as much as we can with SSRS.  Please don't hesitate to contact me if anyone is interested in discussing this and sharing the load: [e-mail]  

This is pretty much day 1 for me on this forum.

---

Garth Henderson - Vanguard Business Technology