We had a requirement in our project to view crystal report off an hyper link embedded into sharepoint web part developed using ASP .NET. The report accepted few parameters from the sharepoint page and passed those on to the SQL server to retrive data and display the data in the report. The challenge was to open the report in the web browser.

The first difficulty was to find the right control to use, the report viewer tool that comes with Visual Studio 2010 is not the right one to use to view Crystal reports, I realised this after spending half a day trying to figure out why wouldn't the report load. I then downloaded the Crystal Report Viewer for VS 2010 Standard from http://www.businessobjects.com/jump/xi/crvs2010/us2_default.asp. After you install this, you get the crystal report viewer in your toolbar. All you have to do is drag and drop it on to the web part and configure it to use your report. If any one needs help with this let me know I will show how to do that.

So far so good. The biggest challenge was to deploy this on to the production server which took better part of 3 days to figure out how its done. If you deploy the WSP file as it is expecting that the crystal DLLs would be embedded in it then be ready to expect a rude shock because it isn't. Here are the steps -

1. Deploy the WSP to the sharepoint server

2. Install the crystal dlls used in the project (CrystalDecisions.CrystalReports.Engine.dll, CrystalDecisions.ReportSource.dll, CrystalDecisions.Shared.dll, CrystalDecisions.Web.dll) into the GAC using GacUtil command

3. Add following entries to Web.Config file (C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.congif)
    <SafeControl Assembly="CrystalDecisions.CrystalReports.Engine, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.CrystalReports.Engine" TypeName="*" Safe="True" SafeAgainstScript="False" />
      <SafeControl Assembly="CrystalDecisions.ReportSource, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.ReportSource" TypeName="*" Safe="True" SafeAgainstScript="False" />
      <SafeControl Assembly="CrystalDecisions.Shared, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Shared" TypeName="*" Safe="True" SafeAgainstScript="False" />
      <SafeControl Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TypeName="*" Safe="True" SafeAgainstScript="False" />

4. Install crystal redistributable package which can be downloaded from http://www.businessobjects.com/jump/xi/crvs2010/us2_default.asp ( SAP Crystal Reports runtime engine for .NET Framework 4 (64-bit))

5. Copy aspnet_client folder from C:\>inetpub\wwwroot and overwrite it on C:\>inetpub\wwwroot\wss\VirtualDirectories\80

And we are done. Fingers crossed it should all work.

 

 

What is your question?

Hi Clayton,

I think Prasad wants to share his experience here. So i change its type to Discussion.

 

Hi Clayton,

I think Prasad wants to share his experience here. So i change its type to Discussion.

 

And the forum is not for that.  It's for asking questions.  He can post his experience on a blog and then ask about it if he has any questions.  If everyone just wanted to share their experience, the forum would be incredibly cluttered.

Then why is there a "Discussion" type?  If there's a problem with discussions littering everywhere, then perhaps a new forum specifically for the discussions is prudent.

Thanks Prasad Matkar ! 

hi Prasad,

      Thanks for your post. Could you explain me how and where can we use connection string for connecting share point lists in visual studio project.

How to connect sharepoint list data source for Creating Crystal Report

Thanks & Regards

Hi Liakath,

Sorry about the late reply. To be honest I have not connected sharepoint list to crystal. I have my own web part that has crystal report view componet which renders the report. We do the follwing to in the .cs file of the webpart.

 

Make sure you are using the following namespaces -

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

Obviously you will have to add references to the crystal dlls first as explained in my original post.

You will have a method that shows the report by binding it to the report -

 

  ReportDocument reportDoc = new ReportDocument();
  reportDoc.FileName = "C:\\Reports\\nameofreport.rpt"; // You can actually store the report in report library rather than on disk

            ConnectionInfo connection = new ConnectionInfo();
            connection.ServerName = "nameofsqlserver";
            connection.DatabaseName = "DBName";
            // connection.IntegratedSecurity = true;
            connection.UserID = "userid";
            connection.Password = "password";
            TableLogOnInfo logon = new TableLogOnInfo();
            logon.ConnectionInfo = connection;
            reportDoc.SetParameterValue("@parameter1", par1);
            reportDoc.SetParameterValue("@parameter2", par2);
// etc


            foreach (CrystalDecisions.CrystalReports.Engine.Table t in reportDoc.Database.Tables)
            {
                t.ApplyLogOnInfo(logon);
            }

            CrystalReportViewer1.ReportSource = reportDoc;

Please let me know if that helps.

thanks for posting this. I have the same need I am looking into for a client and its nice to be able to do a quick santiy check on an approach.

Let me know how it goes Bill

Prasad,

I am having trouble geting this to work. My webpart displays the parameter form and when I click OK I get the failed to open the connection TEST2 {1123415145h134...}.rpt error.

I tried using a 32 bit ODBC system connection added to both front ends and I also tried hard coding the connection info like your example above.

Any ideas?

• Edited by Developer_46038 Tuesday, April 24, 2012 5:31 PM

In the actual crystal report there is an option which is usually selected by default which sets it to optimised for printer. Can you unselect that option and try again?

Found out the problem was with IE and some registry settings for WebDav as coverd in KB Article "KB 943280" once I ran the registry fix on both front end servers and restarted the WebClient service the reports started working.

HI Prasad,

Thanks  for the Post,

I am having trouble geting this to work. My webpart displays the parameter form and when I click OK  am getting an error like no valid report source is available .

can you please suggest me Solution.

Hi Mahesh,

How are you loading the report in your webpart? This is what I have done. I have tried to explain what I am doing in the program comments. Hope this helps. Good luck.

                SPSite mySite = new SPSite(SPContext.Current.Site.Url.ToString());
                SPWeb thisSite = mySite.OpenWeb();

                // Writing to event log after every step in debug mode so that you get to know if there was a failure what step the failure occured after.

                EventLog.WriteEntry("Reports", "Inside PAge Load ", EventLogEntryType.Error);

                SPFolder folder = thisSite.GetFolder("Reports");
                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[reportToLaunch];
                    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
                    // EventLog.WriteEntry("Reports", "Before Creating temp report ", EventLogEntryType.Error);
                    //EventLog.WriteEntry("Reports", "Before Create ", EventLogEntryType.Error);
                    BinaryWriter bw =
                      new BinaryWriter(File.Open("~/temp/temp.rpt", FileMode.Create));
                    bw.Write(content);
                    bw.Close();

                  //  EventLog.WriteEntry("Reports", "After Create ", EventLogEntryType.Error);
                    // set up the crystal report
                    ReportDocument reportDoc = new ReportDocument();
                  
                    reportDoc.Load("~/temp/temp.rpt");
                  
                    EventLog.WriteEntry("Reports", "after report load ", EventLogEntryType.Error);
                    ConnectionInfo connection = new ConnectionInfo();
                    connection.ServerName = "SQLServerName";
                    connection.DatabaseName = "SQLDatabase";
                    connection.UserID = "sqluser";
                    connection.Password = "sqlpassword";
                    TableLogOnInfo logon = new TableLogOnInfo();
                    logon.ConnectionInfo = connection;
                    reportDoc.SetParameterValue("@Parameter1", parameter1);
                    reportDoc.SetParameterValue("@Parameter2", parameter2);                                               
                    EventLog.WriteEntry("Reports", "after assigning parameters ", EventLogEntryType.Error);
                   

                   foreach (CrystalDecisions.CrystalReports.Engine.Table t in reportDoc.Database.Tables)
                    {
                        t.ApplyLogOnInfo(logon);
                    }

                    EventLog.WriteEntry("Reports", "after applying login info ", EventLogEntryType.Error);

                    // and the Crystal report Viewer                                      
                    CrystalReportViewer1.ReportSource = reportDoc;
                    EventLog.WriteEntry("Reports", "Before delete ", EventLogEntryType.Error);
              
                    // clean up
                    File.Delete("~/temp/temp.rpt");
                    EventLog.WriteEntry("Reports", "after delete ", EventLogEntryType.Error);

HI Prasad,

Thanks for the Reply, I did the same.when i call the method from the button click event i am getting the error.[no valid source is available].But when i call the method in page load it is working fine.

My method contains all the above stuff.

Method:

 if (rbtnlReports.SelectedValue.Equals("Indent"))
                {
                    string indentNumber = null;
                    indentNumber = txtIndentOrder.Text;
                    rptDoc.Load(@"xxxx.rpt");//Loading the report from the Physical Location
                    rptDoc.SetParameterValue(0, indentNumber);//setting the parameter.

                    ConnectionInfo connection = new ConnectionInfo();
                    connection.ServerName = "xx";
                    connection.DatabaseName = "xx";
                    connection.UserID = "sa";
                    connection.Password = "xx";
                    TableLogOnInfo logOnInfo = new TableLogOnInfo();
                    logOnInfo.ConnectionInfo = connection;

                    foreach (CrystalDecisions.CrystalReports.Engine.Table t in rptDoc.Database.Tables)
                    {
                        t.ApplyLogOnInfo(logOnInfo);
                    }
                    crvViewIndent.Visible = true;
                    crvViewIndent.ReportSource = rptDoc;
                    crvViewIndent.RefreshReport();
                }

i am passing the parameter from the text box..

Could you please explain why am getting error in button click event..

Thanks,

I see what you are trying to do. I have done something similar however on click of button I launch another webpart where the report gets loaded in the page load event of the webpart. Is that something that you could possibly do to resolve your issue?

I will investigate about click event however other alternative would be to include the above code in prerender event which would be fired in the end and therefore can be loaded on same control where the button is.

Hope that makes sense.

Thanks,
Prasad

Any change you might now why on SP 2007 I get a file is not checked out error? FYI. We are holding off on putting 2010 in production and jumping ahead to 2013. But in the mean time we are trying to get this up on 2007.

Thanks,

Phil

HI Prasad,

It works for me , after i removed the crvViewIndent.RefreshReport(); in the code.

Now facing one more issue,

I am loading the reports from the Layouts folder (14 hive). when i am trying to Export the Report to Excel , i am getting an error like Access denied{xxxx.rpt}it is used by another.

Please help me out to solve this.

Thanks and Regards,

Mahesh.

HI Prasad,

I tried by loading reports from the Document Library, i am able to Export the some of the formats like pdf,RTF,CR to admin but un able to export to excel toadmin also.

When i testing with the normal users i get the below

error:

 Access denied. Error in File ViewIndent {C433FBDE-5763-4D16-B7F3-6B7A35CED5F7}.rpt: Access to report file denied. Another program may be using it.

i gave the full control  to the users group in document library.

suggest me the way to do,

Thanks in advance,

Regards,

Mahesh.

Prasad,

I changed in impersonate to false in web.config file. that solved my issue.

Thanks,

Mahesh.

That's fantastic Mahesh.

Regards,

Prasad

Hi Prasad,

I have another requirement in that crystal report ,Let me explain clearly , the reports(.rpt) i am loading are given the client to us.They are using them in their classic asp site.

In newly report files i need to add some other extra fields (we restore the db of client in our development server).i changed the db server and i got the report , all issues resolved, that is history.

Now i need to add the some fields in rpt file,( some of the columns are created in db tables) i updated the database it is showing a message like the database is up to date but i have not find any changed columns in the tables.I deleted the connection and added new one still it is same.

Can you please share any ideas how to do it???

And one more thing i am passing the servername and dbname in while reporting is loading.Can't i use the web.config value(connection string) , I tried but problems with convertion.If you suggest me that will be appreciated.

   ConnectionInfo connection = new ConnectionInfo();
                    connection.ServerName = "xx";
                    connection.DatabaseName = "xx";
                    connection.UserID = "sa";
                    connection.Password = "xx";

Thanks in advance,

Regards

Mahesh

Sharing in the forum as he did is to answer the question someone else will no doubt ask while the information is fresh in his mind. That's how I found it, and why I'm thankful he was willing to take the time to share it with the community.

Hello Clayton,

I have SharePoint Foundation 2010 and I'm running reports developed in Crystal Reports.

When I run report from one site it runs fine. But when I try to run the same report from different site it gives me the error below.

Both sides have the same permissions.

The table 'VwOpenOrdersWithType' could not be found. Error in File 14d715d6-ae5f-492c-9ad6-77693c7e0ac7 {3CCA8D50-1776-4CDB-A894-011B21ADB621}.rpt:
The table could not be found.

Any ideas ?

Thanks,

Michael

I successfully Load crystal report in with SharePoint 2013 visual web part with data table using following code.

This code works fine. but i am not able to display data on Report.

I also add my CrystalReportViewer1 in CreateChildControls Methods as below :

 protected override void CreateChildControls()
        {
            
            this.Controls.Add(CrystalReportViewer1);           
        }

Please help me.

      rptDoc.Load(path);
            rptDoc.SetDataSource(dt);
           
            Page.Response.Buffer = true;
            Page.Response.ClearContent();
            Page.Response.ClearHeaders();
            rptDoc.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Page.Response, true, "PDfName");

• Edited by falguni Chauhan 28 minutes ago