Crystal Reports WebPart for SharePoint

• Download compiled WebPart - 6.04 KB
• Download source - 179 KB

Introduction

This is a SharePoint WebPart that will enable site administrators to add dynamically generated Crystal Reports to SharePoint web pages without any programming required. The Web Part part is written in C# with .NET Framework 3.5 (should be OK with .NET Framework 2.0).

Background

I was recently asked by a client to write a Web Part that would allow them to dynamically generate and display Crystal Reports for their production control team. The crucial aspect being that the report had to be generated from the latest continually changing information; reports generated overnight and stored in a document library would not be acceptable. Having implemented this for the relatively limited number of reports required, by hard coding the physical location of the Crystal Report definition file, it seemed that a more generalised version that would allow the report definition files to be uploaded to and stored in a SharePoint Document Library and placed on a page by the site administrator would be a useful utility.

Prerequisites

You will need Windows 2003 Server (or later) with Windows SharePoint Services 3.0, Visual Studio 2008 with the Crystal Reports add-in installed, and Windows SharePoint Services 3.0 Tools: Visual Studio Extensions (WSSVSE), this gives you the Visual Studio SharePoint templates. WSSVSE is available from Microsoft downloads.

It is assumed that you have a basic understanding of SharePoint Web Part development.

Set up a Crystal Reports Document Library

In SharePoint, add a document library for the Crystal Reports definition files (.rpt). By default, this is called "Crystal Reports RPT Files". You can change this to suit your own requirements, but you will then need to change the library name in the source code. Upload one or more RPT files to the document library. The RPT files must be written to access the target database directly; the code could be rewritten to access a local dataset, but this is not currently supported.

The Code

Create a SharePoint Web Part

In Visual Studio, click on File, New, Project, SharePoint, and select Web Part. Give the project a suitable name and location, and click OK.

In Solution Explorer, right click on References, and add references to the Crystal Reports libraries. You will need the following:

Open the aspx.cs source file for the Web Part, and add using statements for the above:

using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;

We need to be set a property holding the name of the report file the Web Part will use. This is serialised with the get/set methods in public string ReportSource.

public class CustomCrystalReportWP : System.Web.UI.WebControls.WebParts.WebPart
{
    // for the name of the .rpt file in the document library
    String strReportName = "";
    // an editor part control to display a list of reports
    ReportSourcePart edPart = new ReportSourcePart();

    public CustomCrystalReportWP()
    {
    }
    
    // to serialise the name of the report
    [Personalizable(PersonalizationScope.Shared, false)] // Storage.
    public string ReportSource
    {
        get
        {
            return strReportName;
        }
        set
        {
            strReportName = value;
        }
    }

In CreateChildControls, add a CrystalReportViewer and a Crystal Reports document. Find the .rpt file in the SharePoint document library, as Crystal Reports will only open an .rpt file from a physical file, write it to a temporary file, which is then set as the location of the temporary file in the CR document.

protected override void CreateChildControls()
{
    // add a Crystal Reports Viewer control
    CrystalDecisions.Web.CrystalReportViewer crystalReportViewer1 = 
                    new CrystalDecisions.Web.CrystalReportViewer();
    // and a Crystal Reports report document 
    ReportDocument crdoc = new ReportDocument();

    this.SetPersonalizationDirty();

    base.CreateChildControls();

    if (strReportName.Length > 0)
    {
        // if we have a report selected get the .rpt file
        SPWeb thisSite;
        thisSite = SPControl.GetContextWeb(Context);
        SPFolder folder = thisSite.GetFolder("Crystal Reports rpt Files");
        if (folder.Exists)
        {
            // get collection of Crystal Reports rpt files in the document library
            SPFileCollection files = folder.Files;
      
            // open the rpt file and get the contents
            SPFile srcfile = files[strReportName];
            byte[] content = srcfile.OpenBinary();

            // make a temporary folder
            DirectoryInfo dir2 = new DirectoryInfo("~/temp");
            if (!dir2.Exists)
                dir2.Create();
            if (File.Exists("~/temp/temp.rpt"))
            {
                File.Delete("~/temp/temp.rpt");
            }

            // write the report definition to a temporary file
            BinaryWriter bw = 
              new BinaryWriter(File.Open("~/temp/temp.rpt", FileMode.Create));
            bw.Write(content);
            bw.Close();

            // set up the crystal report
            crdoc.Load("~/temp/temp.rpt");
            // and the Crystal report Viewer                                      
            crystalReportViewer1.ReportSource = crdoc;
            crystalReportViewer1.ReuseParameterValuesOnRefresh = false;
            crystalReportViewer1.HasRefreshButton = true;
            
            this.Controls.Add(crystalReportViewer1);

            // clean up
            File.Delete("~/temp/temp.rpt");
        }
    }
}

Set up up the Tool pane, which is displayed when the Web Part is edited, so that the report can be selected. This is pretty much SharePoint Web Part boiler plate code; the selected filename gets saved in the base Web Part.

// Display a custom editor in the tool pane,
// this gets displayed when you edit the web part settings.
public override EditorPartCollection CreateEditorParts()
{
    ArrayList arEditorParts = new ArrayList();
    edPart.ID = this.ID + "RptSrcEditorPart"; 
    arEditorParts.Add(edPart);
    return new EditorPartCollection(arEditorParts);
}

// Create a custom EditorPart to edit the WebPart.
class ReportSourcePart : EditorPart
{
    DropDownList rptslist = new DropDownList();
  
    // Get settings from web part.
    public override void SyncChanges()
    {
        CustomCrystalReportWP part = (CustomCrystalReportWP)this.WebPartToEdit;
    }

    // Apply new settings to web part.
    public override bool ApplyChanges()
    {
        CustomCrystalReportWP part = (CustomCrystalReportWP)this.WebPartToEdit;
        part.strReportName = rptslist.SelectedValue;
        return true;
    }

Get the list of .rpt files saved in the SharePoint document library and add them to the drop down list in the editor part:

        // Render the control.
        protected override void CreateChildControls()
        {
            // Set the title to display in the properties pane
            this.Title = "Crystal Reports List";

            // add elements to dropdown
            SPWeb thisSite;
            thisSite = SPControl.GetContextWeb(Context);
            SPFolder folder = thisSite.GetFolder("Crystal Reports rpt Files");
            SPFileCollection files = folder.Files;

            foreach (SPFile file in files)
            {
                rptslist.Items.Add(file.Name);
            }

            Controls.Add(rptslist);
            this.ChildControlsCreated = true;
            base.CreateChildControls();
        }
    }
}

You can now build the Web Part and try adding it to a SharePoint Web Part page. In Visual Studio 2008, the retract, create, and deploy solution steps have been automated, so you can simply click on the Debug button and your site should open, and you can try the Web Part by adding it to a Web Part page. There are, however, two apparent problems. There are no glyphs on the toolbar, and any graphics (graphs, charts) don't display. All you see is a box with a red X in it.

To fix these problems:

1. The toolbar glyphs need some files installed on the root website to be available in the SharePoint web site files. Copy from:



Paste the files to:



2. Fixing the graphics not displaying problem is more complicated.

Digging around on the net finds a number of references to the problem which seems to be generally regarded as caused by the Crystal Reports dynamic image HttpHandler (CrystalImageHandler.aspx) being called out of sequence so that its output is never displayed. The most satisfactory solution I could come across was developed from a hint by an observant developer, that Crystal Reports wrote a temporary file in "c:\windows\temps\cr_tmp_image__yoursite" and that the problem could be fixed by having a source code CrystalImageHandler.aspx and its code-behind file in the root folder of your SharePoint web site that sends the temporary file using Response.Transmitfile back to the client, which fixes the out of sequence call.

Copy the two files CrystalImageHandler.aspx and CrystalImageHandler.aspx.cs which I have included in the source file for simplicity, and paste them in the root folder of your site, which for example could be:

c:Inetpub\wwwroot\wss\VirtualDirectories\80

When the page is loaded to display your graphic image, it is compiled dynamically, and you can debug it from within Visual Studio, but only if it compiles successfully. You will not see any compilation errors from within Visual Studio, you need to look in Administration Tools/Event Viewer/Applications and look for errors or warnings, which should allow you to fix any problems.

Here is the CrystalImageHandler.ascx file:

The Register Assembly instances above are required to handle references to Crystal Reports DLLs. To copy the above code, you will need to download the source files.

The C# file builds the name of the temporary file and sends it back to the client.

public partial class CrystalImageHandler : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // get the Crystal Reports temporary file name
        String strtempImagePath = Request.QueryString["dynamicimage"];
 
        if (strtempImagePath != null)
        {
            // send the temporary image file
            SPWeb thissite = SPControl.GetContextWeb(Context);
            String strsitename = thissite.Site.HostName;
            int iport = thissite.Site.Port;
            String strport = Convert.ToString(iport);
            Response.TransmitFile(@"C:\WINDOWS\Temp\cr_tmp_image__" + 
                     strsitename + strport + "\\" + strtempImagePath);
        }
    }
}

Using the Code

You can install the WebPart on your site by copying the source code, or by installing the WSP file in the CRWPCompiledInstall.zip download. The simplest way to install compiled Web Parts is by using the stsadm command line utility which can be found, by default, in: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe.

From a command prompt, with suitable adjustments for file paths, run:

stsadm -o addsolution -filename CustomCRWPInstall.wsp

You will also need to copy the two files CrystalImageHandler.ascx and CrystalImageHandler.ascx.cs to the root folder of your web site (see above for the default path).

Points of Interest

Integrating complicated applications such as SharePoint and Crystal Reports can be a frustrating and time consuming job. Sometimes you can get lucky, and things just work (like they're supposed to), but often they don't, and if no solution is easily found on the net, you are faced with the choice of what could be a long series of trial and error experiments to find an answer, or a long task of study and research to become at least a partial expert on the technologies you are working with, for which you often find you simply don't have the time.

History

• Version 1 - 24 Sept. 2009.