Tag: crystal reports stored procedure

SAP Crystal Reports for Visual Studio

On By windows7bugsIn .net programming, Visual Studio(VB.Net)Leave a comment

Hello guys

My .Net developments were ALWAYS at risk! Whenever I am doing it good, the project gets cancelled & I return to the Oracle world.

Anyway, for the last project that is about be shelved, I chose SAP Crystal Reports in addition to Microsoft’s RDLC for few reasons. Microsoft has stopped shipping Report components with their IDE Visual Studio & getting it work by installing Nuget packages and extension methods are not going to be very easy for most of the newbies (I am a life long newbie when it is all about .NET development)

On the other hand, installing & going online with SAP Crystal Reports for Visual Studio is pretty straight forward. Download the package from SAP, install it and you are all set to go. Well definitely not!

Let us quickly see how to install & get the most out of SAP Crystal Reports.

  1. Installation
  2. Server Runtime
  3. Your 1st Crystal Report
  4. POST BACK and hacks

Installation

Register & download SAP Crystal Reports for Visual Studio. Make sure you install the runtime engine which is prompted during the installation. Simple as it is.

Server Runtime

If you are publishing your application with SAP reports from another computer/Server make sure you install the Runtime package.

If you’ve installed the runtime, this will create the below structure under inetpub\wwwroot folder

Your 1st Crystal Report

If you are a .Net developer, already familiar with dragging and dropping controls from the Toolbox node(s). Crystal Reports is not different. Just drag and drop “CrystalReportViewer” control to your page & you are all set to go.

Dropping the control to the page adds many references to your project. You may scroll through the references to identify them. All the references have “Crystal” in the name.

Now let us create a sample report & view the report on the web.

If this is your 1st attempt to create a report, select the default & continue. Based on the connections already available in the environment, you will be provided existing connections or an option to create a new connection.

Here is the catch. If the database (MSSQL or other) is configured for integrated security & you prefer to continue using the same for your project, you must make sure that from your webform/page you will be sticking to the same authentication method. Said, you cannot use integrated authentication during design and username/password login during the runtime. So be careful when you are creating connections.

We will create a new connection using username and password to local MS SQL server.

You don’t have to change anything here. Just click the “Finish” button to complete the connection.

That’s it. This creates a new connection and the same will be available under “My Connections”.

Now from the connection, you can select a table, view or stored procedure for your report as source. I have selected a table “bal2020” as you could see with the below image.

Once you click the OK button, you will be taken back to object browser window.

You can see that the table you have selected is shown under “Database Fields” node and expanding the Table node will show you the available columns those you could add to the report.

Drag and drop the columns you want to add in the “Section 3 (Details)” area and the act creates relevant titles in the “Section 2 (Page Header)” area. You can always modify the titles.

Unlike RDLC, you can immediately preview your reports from the design window itself by switching to “Main Report Preview” tab.

Let us see how this report viewed from a webform/page.

As we are trying to create a report for the first time, I suggest you to use the GUI for linking your newly created report with the webform/page.

Select “New Report Source” form “Choose Report Source” and select your newly created report. Leave the names as seen for this exercise.

Prior attaching the report, the Crystal Report Viewer control code was minimal like below

<body>
    <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>

and after linking the report to the viewer, you will notice that a number of elements are added to the viewer.

 <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True" 
                GroupTreeImagesFolderUrl="" Height="1202px" ReportSourceID="CrystalReportSource1" 
                ToolbarImagesFolderUrl="" ToolPanelWidth="200px" Width="1104px" />
            <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
                <Report FileName="CrystalReport1.rpt">
                </Report>
            </CR:CrystalReportSource>
        </div>
    </form>

As there are not complex code involved as in with stored procedures, your page will load the report. You can save the webform & try to see whether it truly gets loaded on the browser.

Save everything & build the solution & debug the page that you have just created. Do not use Internet Explorer for debugging. You may end up with “N” number of errors by using IE.

Something is wrong, right? let us see why the report is not loading. With this specific case, an empty page in the place of report viewer is due to missing runtime binaries. If you remember, I asked you to install the runtime while installing the report developer. So there is something wrong…

I don’t have the technical knowledge to confidently say it is a bug , however looks like one, with certain solutions, the symbolic link to rootdrive:\inetpub\wwwroot\aspnet_client is not created within the solution folder after Crystal Report Viewer control is added. Failing to find “aspnet_client” symbolic link to the actual path where the runtime binaries are kept results in failing to load the report on the browser, unfortunately without generating visible errors. While missing the symbolic link being one of the major reasons, there could be few other reasons as well like application pool configured for both 32 Bit & 64 Bit. I have noticed that if your default application pool is configured to cater both 32 & 64 Bit, the 64 Bit runtime will not load & errors will be generated while trying to load reports. If you have installed 64 Bit SAP components, create a new application pool exclusively for 64 Bit only.

To resolve the issues due to runtime binaries, we can either create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” or copy the entire aspnet_client folder to project, which is approximately 17-18 MBs in size.

I will always prefer the 1st option. So let us see how we can create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” from our project.

The above symbolic link is explained as below.

Your project is created under user’s home folder. For example, my username is Rajesh. Hence Visual Studio has created a path “sources\repos” where all new solutions will be created and stored (unless I change the VS options)

As I have chosen “CrystalReportsDemo” as my solution name, a folder with the same name is created inside “sources\repos” and the solution related files and folders are kept within a subfolder with the solution name. Finally, the path will look like below once after the solution is created.

"C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo"

We are expected to create the symbolic link to “aspnet_client” folder inside the “CrystalReportsDemo” subfolder.

The symbolic link must have a name, hence you will provide “aspnet_client” as the link name (no other names please!) and refer the original path of aspnet_client as source.

mklink /D "C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo\aspnet_client" "C:\inetpub\wwwroot\aspnet_client"

I know it sounds bit complex and I hope you will get a hang of it with practice. A successfully created symbolic link will look like below. If you have grouping enabled for the files in explorer, the newly created symbolic link will be listed under folders.

Let us try to view the webform once again after creating the symbolic link.

Basically, when you add a crystal report to your solution, it is treated as an “Embedded Resource” & not copied to output directory. Unless you change these properties, your published application will not able to access the reports. Let us see quickly how these changes are made.

Now the output directory will copy the report files each time the solution is built.

PostBack & hacks

What is PostBack? PostBack is the name given to the process of submitting an ASP.NET page to the server for processing. This has a big effect on how Crystal Reports behave.

Consider a case when you have a webform that has few user choices & a crystal report attached to it. By default much of the ASP.Net controls support “AutoPostBack” property & very useful when a developer wants to refer to other controls values programmatically. Well, each instance of AutoPostBack causes the Crystal Report to refresh itself.

As you are already aware ASP.Net is stateless and Crystal Report will loose all functionalities like progressing to next page or previous page etc when associated buttons on the toolbar is pressed immediately after a postback. In addition to losing these functionality, you may be prompted to enter the database connection details. SAP recommends using Page_Init() instead of Page_Load() method in addition to using session variables to load the report after postback. We will see a complex sample now, which is from a production environment.

Scope of the report

Generate a report for Finance department that lists all salary elements for a chosen company, department & few other selections by the end user.

So this particular report has a total of 5 parameters received from the user & clicking the “Print Report” should show the report with relevant data.

The above report uses multiple stored procedures to fetch the relevant data for the report & copying the script here will defy the intend. Hence I will copy a sample that I posted as an answer to my own question over StackOverFlow & explain it.

You may refer to the stackoverflow thread here.

The below example generates the same report sample you have seen above, however much of the parameters are hardcoded & the only user interactable control is the button. This report uses database stored procedures for report data.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c4.aspx.cs" Inherits="CrystalTest.c4" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Print Report" OnClick="Button1_Click" />
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>
</html>

Code behind for the webform is like below:

using CrystalDecisions.CrystalReports.Engine;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CrystalTest
{
    public partial class c4 : System.Web.UI.Page
    {
        protected void Page_Init(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                CrystalReportViewer1.ReportSource = (ReportDocument)Session["Report"];
            }
        }
        private void ShowReport1()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GETMONTHSALARY", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@pProcessSection", SqlDbType.VarChar).Value = "9";
                    cmd.Parameters.Add("@pProcessSite", SqlDbType.VarChar).Value = "1";
                    cmd.Parameters.Add("@pProcessCatg", SqlDbType.VarChar).Value = "1";
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "SalaryDT");
                    ReportDocument oRpt = new ReportDocument();
                    oRpt.Load(Server.MapPath(@"~/dataset/CrystalReport1.rpt"));
                    oRpt.DataSourceConnections.Clear();
                    oRpt.SetDataSource(ds);
                    oRpt.Subreports[0].SetDataSource(FillOverTime());
                    CrystalReportViewer1.Visible = true;
                    CrystalReportViewer1.ReportSource = oRpt;
                    Session["Report"] = oRpt;
                }
            }
        }
        private DataSet FillOverTime()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetEmployeeOverTime", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pEmployeeCode", SqlDbType.VarChar).Value = DBNull.Value;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds1 = new DataSet();
                    adapter.Fill(ds1, "OverTimeDT");
                    return ds1;
                }
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport1();
        }
    }
}

The above sample uses 2 different stored procedures to generate data for main report and a sub report. ShowReport1() method is triggered when the button is clicked. Once the report is generated, it is saved into a session object & whenever a post back happens, this saved object is assigned to report source. Simple as it is. Each button click on the reports toolbar is treated as post back, triggering the report source being assigned again and again. You might notice that I don’t have a Page_Load() method with the code behind!

I know this is just a beginning & including more detailing might make the post irrelevant. With my next post, I will try to explain Crystal Reports parameters, sub-reports etc.

2nd Part. Passing parameters to Crystal Reports

regards,

rajesh