Category: Visual Basic .NET

Posted on

Fixing the CrystalReportViewer “Next Page” Navigation Problem

logos for Visual Studio and Crystal Reports

Note: This is an update to my last post.

I discovered that the version of the SAP CrystalReportViewer for VS2010 (13.0.2000.0) that I am using appears to have a bug that prevents the user from proceeding past page 2 when using the Next Page button. Pages can still be directly accessed by typing in the page number, but clicking the Next Page button keeps you on page 2.

I read quite a few possible solutions to this, such as moving the code that loads the Crystal Report from Page_Load to Page_Init, but this did not work for me. Also, there is an update to 13.0.2000.0 that might fix this issue, but I am not in a position to update the version of the SAP Crystal Reports software at present.

Fortunately, I was able to code around this bug. First, I disabled the navigation buttons on the viewer itself. Next, I put my own navigation buttons above the viewer component on the ASP.NET Web Form. Lastly, I wrote the code that handles the click events for these buttons.

Here is the code for the Reports.aspx page:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html>

<html>
    <head runat="server">
        <title></title>
        <meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server" AsyncPostBackTimeout="0" EnableHistory="True">
            </asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Label ID="Label1" runat="server" Text="Page navigation:   "></asp:Label>
                    <asp:Button runat="server" ID="btnFirst"
                        Text="|<--" ToolTip="Go to first page"/>
                    <asp:Button runat="server" ID="btnPrev"
                        Text="<<" ToolTip="Go to previous page"/>
                    <asp:Button runat="server" ID="btnNext"
                        Text=">>" ToolTip="Go to next page"/>
                    <asp:Button runat="server" ID="btnLast"
                        Text="-->|" ToolTip="Go to last page"/>
                    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"
                    HasCrystalLogo="False" GroupTreeStyle-ShowLines="True"
                        Width="100%" Height="100%"
                    EnableDatabaseLogonPrompt="False" ReuseParameterValuesOnRefresh="True"
                        HasPageNavigationButtons="False" ShowAllPageIds="True"
                        EnableParameterPrompt="False" />
                </ContentTemplate>
            </asp:UpdatePanel>
        </div>
        </form>
    </body>
</html>

…and the code file Reports.aspx.vb…

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Web

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String
    Protected intLastPage As Integer
    Protected intCurPage As Integer

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = _
               Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = _
               Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = Functions.GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
               Utilities.AppSettingsFunction.getValue("ReportFolder") _
               & strReportName))

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.ShowLastPage()
            CrystalReportViewer1.ShowFirstPage()
            Session(strQueryString + "_pagenum") = 1
            Session(strQueryString + "_lastpagenum") = GetLastCRPageNumber()
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
        GetPageNums()
    End Sub

    Private Sub GetPageNums()
        intCurPage = Session(strQueryString + "_pagenum")
        intLastPage = Session(strQueryString + "_lastpagenum")
        ButtonsCheck()
    End Sub

    Private Sub SetCurPageNum(intPage As Integer)
        GetPageNums()
        Session(strQueryString + "_pagenum") = intPage
        ButtonsCheck()
    End Sub

    Protected Sub btnNext_Click(sender As Object, e As System.EventArgs) Handles btnNext.Click
        GetPageNums()
        If intCurPage < intLastPage Then
            SetCurPageNum(intCurPage + 1)
            CrystalReportViewer1.ShowNthPage(intCurPage + 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Protected Sub btnPrev_Click(sender As Object, e As System.EventArgs) Handles btnPrev.Click
        GetPageNums()
        If intCurPage > 1 Then
            SetCurPageNum(intCurPage - 1)
            CrystalReportViewer1.ShowNthPage(intCurPage - 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Private Function GetCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.PageNumber
    End Function

    Private Function GetLastCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.LastPageNumber
    End Function

    Protected Sub ButtonsCheck()
        If intCurPage = 1 Then
            btnFirst.Enabled = False
            btnPrev.Enabled = False
        Else
            btnFirst.Enabled = True
            btnPrev.Enabled = True
        End If

        If intCurPage = intLastPage Then
            btnLast.Enabled = False
            btnNext.Enabled = False
        Else
            btnLast.Enabled = True
            btnNext.Enabled = True
        End If
    End Sub

    Protected Sub CrystalReportViewer1_Navigate(source As Object, e As CrystalDecisions.Web.NavigateEventArgs) Handles CrystalReportViewer1.Navigate
        SetCurPageNum(e.NewPageNumber)
    End Sub

    Protected Sub btnFirst_Click(sender As Object, e As System.EventArgs) Handles btnFirst.Click
        CrystalReportViewer1.ShowFirstPage()
        GetPageNums()
    End Sub

    Protected Sub btnLast_Click(sender As Object, e As System.EventArgs) Handles btnLast.Click
        CrystalReportViewer1.ShowLastPage()
        GetPageNums()
    End Sub
End Class
Posted on

Spawning a New Window for Each CrystalReportViewer

logos for Visual Studio and Crystal Reports

Update to this post: I have found a bug in the CrystalReportViewer that I address here.

In creating a ASP.NET Web Forms site for viewing Crystal Reports, I needed to open multiple dates for the same report to compare values. The previous setup I used would not allow this; only one report could be open at a time. To remedy this, I changed the way reports would be loaded, and made a separate window for each one.

Excerpt of the home page code:

<table style="border-width:0px; border-spacing:1px; border-collapse:collapse;">
    <tr style="vertical-align:top;">
        <td class="fcel1">
            
<strong>Report</strong>
                <asp:DropDownList id="ddlReports" runat="server" AutoPostBack="True" />
            
        </td>
       <asp:panel id="pnlreportdate" runat="server">
            <td class="fcel1">
                
<strong>Report Date</strong>
                    <asp:TextBox id="txtSingleDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender1" runat="server" TargetControlID="txtSingleDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
        </asp:panel>
        <asp:panel id="pnlstartenddates" runat="server">
            <td class="fcel1">
                
<strong>Begin Date</strong>
                    <asp:TextBox id="txtBeginDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender2" runat="server" TargetControlID="txtBeginDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
            <td class="fcel1">
                
<strong>End Date</strong>
                    <asp:TextBox id="txtEndDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender3" runat="server" TargetControlID="txtEndDate">
                    </ajaxToolkit:CalendarExtender>
                
            </td>
        </asp:panel>
        <td class="fcel1" style="vertical-align:middle;">
            <asp:Button id="btnGenerate" runat="server" Text="Generate"></asp:Button>
        </td>
    </tr>
</table>

…and its code behind:

Imports AjaxControlToolkit
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.IO

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        If Not Page.IsPostBack Then
            txtSingleDate.Text = Now.Date()
            txtBeginDate.Text = Now.Date()
            txtEndDate.Text = Now.Date()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim dirpath As String = Utilities.AppSettingsFunction.getValue("ReportFolder")
            Dim thedir As DirectoryInfo = New DirectoryInfo(Server.MapPath(dirpath))
            Dim filenames As FileInfo() = thedir.GetFiles()

            ddlReports.DataSource = filenames
            ddlReports.DataBind()

            For Each report As ListItem In ddlReports.Items
                Dim reportFileName As String = report.Value
                Dim reportText As String = reportFileName

                reportText = Replace(reportText, ".rpt", "")

                report.Text = reportText
            Next
        End If
        ShowPanels()
    End Sub

    Protected Function SetParameters() As String
        Dim strOnClientClick As String
        Dim BeginDate As String
        Dim EndDate As String
        Dim SingleDate As String
        Dim RptName As String

        BeginDate = txtBeginDate.Text
        EndDate = txtEndDate.Text
        SingleDate = txtSingleDate.Text
        RptName = ddlReports.SelectedValue
        strOnClientClick = "window.open('Pages/Reports.aspx?guid=" & newGuid().ToString & "&rpt=" & RptName
        If pnlstartenddates.Visible Then
            strOnClientClick += "&begin=" & BeginDate & "&end=" & EndDate
        ElseIf pnlreportdate.Visible Then
            strOnClientClick += "&single=" & SingleDate
        End If
        strOnClientClick += "','_blank');"
        Return strOnClientClick
    End Function

    Protected Sub ShowPanels()
        Dim strSelectedValue As String
        Dim bSingleDate As Boolean
        Dim bMultiDate As Boolean

        bSingleDate = False
        bMultiDate = False

        strSelectedValue = ddlReports.SelectedValue.ToString()

        Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
        Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

        If Array.IndexOf(arrSingleDate, strSelectedValue) <> -1 Then
            bSingleDate = True
            bMultiDate = False
        ElseIf Array.IndexOf(arrMultiDate, strSelectedValue) <> -1 Then
            bSingleDate = False
            bMultiDate = True
        End If

        pnlreportdate.Visible = bSingleDate
        pnlstartenddates.Visible = bMultiDate
    End Sub

    Protected Function newGuid() As Guid
        Dim guid As Guid
        guid = guid.NewGuid()
        Return guid
    End Function

    Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
        Dim strOnClientClick As String = SetParameters()
        Page.ClientScript.RegisterStartupScript(Me.GetType(), newGuid().ToString(), strOnClientClick, True)
    End Sub
End Class

The reports.aspx page that is used for all reports:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"     Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />

</pre>
<form id="form1">
<div>

</div>
</form>
<pre>

…and the code behind for reports.aspx:

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
                  Utilities.AppSettingsFunction.getValue("ReportFolder") _
                  & strReportName))

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
        ElseIf Session(strQueryString) IsNot Nothing Then
            'Dim rptView As CrystalReportViewer = FindControl("rptViewer_" & report.Text)
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Function GetConnectionInfo() As ConnectionInfo
        Dim connInfo As New ConnectionInfo

        'setup the connection
        With connInfo
            .ServerName = Utilities.AppSettingsFunction.getValue("ServerName_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .DatabaseName = ""
            .UserID = Utilities.AppSettingsFunction.getValue("UserID_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .Password = Utilities.AppSettingsFunction.getValue("Password_" & Utilities.AppSettingsFunction.getValue("Environment"))
        End With
        Return connInfo
    End Function
End Class
Posted on

Changing Oracle Databases at Run-time with Crystal Reports Viewers in ASP.NET

ASP.NET logo

As part of the legacy application upgrade I’ve been doing, I decided to move the Crystal Reports to a Web application rather that having the users access them directly on Crystal Reports XI via Citrix, which has been the method they have been using.  This has several advantages, the primary one being speed.  One problem that plagued me was how to change the logon information at run-time, such that the application would automatically point at the correct Oracle database (development, test, or production) based on which server the report was being accessed from.

The solution I found was to set up the Oracle Database connection as a DSN in ODBC, and connecting to the ODBC DSN rather than using the Oracle Client directly.  This is not the only way to do it, but it seems to be the best way for my purposes.

In the code-behind file for the page that contains the Crystal Reports Viewer, I placed the following code that handles the same event that renders the viewer.

Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
Dim connInfo As New ConnectionInfo
Dim rptDoc As New ReportDocument

' setup the connection
With connInfo
.ServerName = "oracledsn" ' ODBC DSN in quotes, not Oracle server or database name
.DatabaseName = "" ' leave empty string here
.UserID = "username" ' database user ID in quotes
.Password = "password"  'database password in quotes
End With

' load the Crystal Report
rptDoc.Load(Server.MapPath(Utilities.AppSettingsFunction.getValue("ReportFolder") & ddlReports.SelectedValue))

' add required parameters
If pnlstartdates.Visible Then
rptDoc.SetParameterValue("REPORT_DATE", txtSingleDate.Text)
End If

' apply logon information

For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
repTblLogonInfo.ConnectionInfo = connInfo
tbl.ApplyLogOnInfo(repTblLogonInfo)
Next

' Set, bind, and display Crystal Reports Viewer data source
Session("rptDoc") = rptDoc
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
 End Sub

The logon info above can easily be stored in web.config instead of hard-coding it as above.

Incidentally, I chose to put my Crystal Reports Viewer in an ASP.NET AJAX Update Panel, which is why the ReportSource of the viewer is stored in a Session variable.  If you choose to do this, the viewer must be databound in the Init event (not the Load event) to show up properly.

Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
If Not Page.IsPostBack Then
txtSingleDate.Text = Now.Date()
ElseIf Session("rptDoc") IsNot Nothing Then
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
End If
 End Sub