Solved

Laying out a report in SSRS to export in Word

Posted on 2013-11-27
13
3,680 Views
Last Modified: 2013-12-02
I have a report that we want to be the starting point of a final project report that will be saved as a Word document.  There is data to pull from the database and then project managers will put their feedback in certain sections.  I've already designed the report and it looks great but I'm having trouble getting the page to break where I want it to.  I'm using tables to position static information as well as data, even using tables within cells to line up things perfectly (I should probably remove those but I thought it would keep a table together if it was inside another table's row).  But my problem is tables spawning 2 pages.  I thought there were settings for rendering specific to the output you would like but I can't seem to find anything online.  

Is there a way tweak a report so it is Word-export friendly?
0
Comment
Question by:HSI_guelph
  • 6
  • 4
  • 3
13 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39682934
I thought there were settings for rendering specific to the output you would like but I can't seem to find anything online.

Are you referring to the RenderFormat properties?  Through RenderFormat.Name, you can determine what format the user is exporting to and hide/show items accordingly.

Check here for a list of possible values: Hide/Show Items Dependant On Export Format (SSRS)
0
 

Author Comment

by:HSI_guelph
ID: 39684048
@ValentinoV - I think that was what I remember from before.  But is there a way to format a report for optimal output to Word?  I've got it looking pretty good except for where tables split even though I've gone into the Tablix Properties and checked off Keep together on one page if possible.  Is there a way to force it to stay together?
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39684063
Can the output even fit on one page?  As in if more rows are returned than what would fit on a page no matter what it will spit out on two pages?
0
 

Author Comment

by:HSI_guelph
ID: 39684210
@FriedTyGuy - No the output is 4 pages, 5 because one page only has 2 lines on it due to my attempt to format it, lol.  I would like to tell it where to split into the next page but I can't find out how or if it even is possible.
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39684290
All over the place...  You can page break by table, rectangle, group, etc.  What version of VS are you creating this in?  2005, 2008, 2010, 2012 or 2013?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39684743
Something isn't clear to me yet: is your table splitting horizontally or vertically?

In case of vertically, you'll have to play with the cell widths a bit to stop this.  A vertical split happens when the width of the table exceeds the page width.

Interesting to read:
Pagination in Reporting Services (Report Builder and SSRS)
Add a Page Break (Report Builder and SSRS)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:HSI_guelph
ID: 39685541
It is splitting the tables horizontally.  I know this is hard to control since the data is being generated dynamically but some of the tables are not dynamic but static such as the Project Goals table and since it's so close to the edge of the page I had hoped the 'keep together if possible' feature would drop it one line and keep them together.

example of page split
0
 

Author Comment

by:HSI_guelph
ID: 39685880
This is the format feature I was thinking of
http://myrshost/ReportServer?/myreport&rs:Format=PDF

Does anyone know how to set that value in the URL?
0
 
LVL 1

Expert Comment

by:FriedTyGuy
ID: 39685888
That feature just bypasses the report viewer and opens the report in PDF.  All you need to do (if the report isn't parameterized) is open the report in the report view, copy the link from the address bar and add "&rs:Format=PDF" to the end of it.

As for your page breaks, are you creating these reports in visual studio?  If so, what version?
0
 

Author Comment

by:HSI_guelph
ID: 39686061
I am using Microsoft Visual Studio 2008 Version 9.0.30729.4462 QFE with Microsoft .NET  Framework Version 3.5 SP1.  So that '&rs:Format=PDF' would open in Adobe Reader if it was a link in the report?  I'm thinking I could create a link and have them click on it in the report to open a Word document with the report rendered in it.  I find the changes I've made make for a readable Word document but the actual report feels a bit odd in the browser.  Way over to the far left and lots of whitespace.  I think I may have misunderstood the capabilities of the render.  I know you can export reports into a few different formats but I thought that there were things you could format in a report to make a better export in Word.  Kind of like having a web page and creating a printer-friendly version.
0
 
LVL 1

Assisted Solution

by:FriedTyGuy
FriedTyGuy earned 250 total points
ID: 39686078
You can try &rs:Format=Doc or &rs:Format=Word

Are you still having issues with the page break? You should be able to set EACH table/group to "Keep Together" then it should page break to keep the tables together when possible.

I'm pretty sure you can &rs:Format=HTM also, to which there would be no page breaks (browser).

You could also consider &rs:Format=XLS and have it all on a single spreadsheet.

There are several formats you can export direct to, if you want to look them up on MSDN.

You can test all the links by creating shortcuts on you desktop to the report in the report viewer and appending the link with the &rs:Format=xxx
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 39689332
"I know you can export reports into a few different formats but I thought that there were things you could format in a report to make a better export in Word.  Kind of like having a web page and creating a printer-friendly version."

There's no "printer-friendly" setting or anything like that but you can implement one yourself by making use of that RenderFormat.Name property mentioned earlier.  Basically you implement two different tablixes: one for web and one for print (or Word/PDF).  Then, depending on render format, you hide one of them.
0
 

Author Closing Comment

by:HSI_guelph
ID: 39690836
I tried adding a textbox and setting the action to go to URL but when I add the &rs:Format=XXX it gives me an error.  Doesn't matter what I set for the XXX (Doc, Word, PDF, HTM) it won't open the report.  

I did try making each table have the 'Keep together if possible' open checked off but it didn't always work, I'd still find one line off a page.  So I think we'll just pass the information along that the report will need minor formatting when rendered.  It looks pretty good otherwise, I'm just surprised the report can't be tailored more for exporting to Office programs.

Thank you both for your replies and input!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now