Thursday, September 29, 2016

SSRS Report Prints Extra Pages

Problem

Recently run into an issue that caused me spend more time trying figure out what to do that it did to fix it. I got a very simple ticket. Client reports that extra pages are being printed on SSRS report when it is being sent to a specific printer but other printers are fine, additionally printing to PDF is fine.

After some research, I found multiple articles online that talk about improper page and body setup that results in extra pages. Since I'm not used to working on SSRS report inside BIDS (Bussiness Intelligence Development Studio) which was a precursor to SSDT (SQL Server Data Tools), It took me for longer than I would expect to accomplish a simple task. Therefore I'm hoping the following screenshots will save someone (most likely me) time in fixing this issue.

Solution

Step 1:

Open report in Design mode which is causing issues.

Step 2:

Verify printer margins by right clicking on empty space and selecting Report Properties


Step 3:

Check all margins, for some older printers margins, should be at least .25in to be on safe side. Newer printers can go less but some of older printers can not do it.


Step 4:

Calculate maximum report body size by subtracting margins from width and height, in my case

Width - Left - Right (11 - .5 - .6) = 9.9in

Height - Top - Bottom (8.5 - .5 - .25) = 7.75in

Step 5:

Click on the body of the report to see height and width in the properties window. Now you can adjust the size to be less than calculated size from previous step.


Conlusion

Now you are all done, all that is left is to publish changes and deploy report to the client.

Wednesday, September 14, 2016

Workaround to upgrading SSRS project

This guide is more of work around for specific opportunity I faced at work. Hopefully, this can help someone else to create a workaround for a problem that should be rather easy to be fixed, but there are limitations that prevent you from taking the simple solution.

Scenario:

You have upgraded to newest Visual Studio 2015 and you are excited (not me) to make some reports. You get the path for the project and you create working directory using Visual Source Safe ( an old tool that got replaced by Team Foundation Server). Now that files are on your local machine, you fire up Visual Studio and try to open the solution only to get the following screen.
Now What? You talk to other people and they are still using Visual Studio 2010 and you are not allowed to upgrade and break it for everyone.

Solution:

  • Create new project and name it something different from original project to avoid confusion
  • Now that we have a project we need to copy all report definitions to the new location that was created for the project. To do that just find all .RDL files in old folder and copy them to new folder that was created for the project.
  • Now just add reports to the project by right-clicking on Reports Folder in Solution explorer and select all reports that have been copied over.
  • At this point reports are in new project and you are ready to develop (or are you)
  • You find the most amazing report that looks something like this and you click Preview just to get the following error.
  • This error even though it is cryptic, is actually missing Shared Data Source file that existed in the original project.
  • Now all we have to do is copy .RDS file found in the original directory to the new directory where we placed .RDL files and then add Existing Item under Shared Data Sources
  • At this point we can preview report but now arises the issue of deploying it the server. Since we created the new project we don't know where it needs to be deployed. Luckily this can be easily fixed by opening .rptproj file in Notepad++ or another editor and looking for Configurations element. It should look something like this.
  • 
        <Configuration>
          <Name>Debug</Name>
          <Platform>Win32</Platform>
          <Options>
            <OutputPath>bin\Debug</OutputPath>
            <TargetServerVersion>SSRS2008R2</TargetServerVersion>
            <TargetServerURL>http://MyServer/ReportServer_MyServer</TargetServerURL>
            <TargetFolder>/MyProjectTarget</TargetFolder>
            <TargetDataSourceFolder>/MyProjectTarget/Data Sources</TargetDataSourceFolder>
            <TargetDatasetFolder>Datasets</TargetDatasetFolder>
            <TargetReportPartFolder>Report Parts</TargetReportPartFolder>
            <StartItem>MyAwesomeReport.rdl</StartItem>
          </Options>
        </Configuration>
    
  • Once you have that information just place it project properties of your new project and you are done.

Notes:


Couple things to consider, but doing this we have removed the project from original source control solution and it will take additional effort to commit it back to source control. The easiest way to overcome would be to check out and check-in files from new location using Source Control utility. In my case, I can easily check out and check files back in using Visual SourceSafe Explorer.

As always questions and comments are welcomed and I if I can add any details to help out fellow developer I would be happy to do so.