• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1215
  • Last Modified:

Setting Datasource Location at Runtime in Crystal XI/Vb.NET

Crystal XI  with vb.NET

Is there a way of setting the "Datasource Location" at runtime using vb.NET code. I need the datasource location to be dynamic and based on the users sessionID. The datasource is a SQL View, which is generated each time before a  report is requested but with a different VIEW name when request is made. The SQL view has all the data filelds needed by the report but the VIEW name is derived from the session.id so it is unknown at design time. The web user supplies the VIEW name which is the used as the set the datasource for the report. The report is then refreshed and then loaded on the report viewer. The view is dropped after report is refreshed and displayed rendered on the web page. All these parts are working for me except I am unable to set the location at runtime as part of the code

I am not sure what part of my current code one would need for this request so I have attached the code on the page containing the report viewer.

Help



Imports System
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Web.UI.HtmlControls
Imports System.Web.Security
Imports System.Exception




Public Class MAINT_Rpt
    Inherits System.Web.UI.Page
#
    Dim SQLString, ShowMessage As String
    Protected WithEvents rptVwer As CrystalDecisions.Web.CrystalReportViewer
    '// Crystal Reports XI
    Private ShowCRXI As ReportDocument
#End Region

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub



#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
           Session("Module") = "System Admin-Reports"
        Try
            ConfigureCrystalReports()
            With rptVwer
                If Session("RptView") = "vw_nfsINV_DETP" Then
                       .DisplayToolbar = True
                Else
                    .DisplayToolbar = True
                End If
                .DisplayToolbar = True
                .DataBind()
                .Visible = True
            End With
            ShowCRXI.Refresh()
        Catch exCept As Exception
               '////Error generating report. "
        End Try

    End Sub

#Region "Crystal Reports"


    Function ConfigureCrystalReports()

        Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

        Dim NFSCn As New SqlConnection(nsNFSDB.dbNFSDB)
        Dim cmd As New SqlCommand(SQLString, NFSCn)
        Dim sqlr As SqlClient.SqlDataReader
        NFSCn.Open()
        Dim CredUID, CredPWD As String
        Try


            SQLString = "select * from n_SYS_MSC where NFS_AREA = 'INFOS' AND NFS_KEY_DATA = '" & Session("SourcDB") & "'"
            cmd.CommandText = SQLString
            sqlr = cmd.ExecuteReader()
            Do While sqlr.Read()
                If Not IsDBNull(sqlr("NFS_DATA_01")) = True Then
                    CredUID = Trim(sqlr("NFS_DATA_01"))
                    CredPWD = Trim(sqlr("NFS_DATA_02"))
                Else
                    CredUID = ""
                    CredPWD = ""
                End If
            Loop

            '   myConnectionInfo.DatabaseName = "SUN426"
            myConnectionInfo.UserID = CredUID
            myConnectionInfo.Password = CredPWD

            ShowCRXI = New ReportDocument()
            rptVwer.ReportSource = ShowCRXI

            ShowCRXI.Load(Session("RptPath"))

            SetDBLogonForReport(myConnectionInfo, ShowCRXI)

        Catch exCept As Exception
           '//// ShowMessage = "Unable to update access credentials & present report: Please contact your systems administrator:"
            Exit Function
        Finally
            NFSCn.Close()
        End Try

    End Function



    Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)
        Dim myTables As Tables = myReportDocument.Database.Tables
        Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
        For Each myTable In myTables
            Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo = myConnectionInfo
            myTable.ApplyLogOnInfo(myTableLogonInfo)
        Next

    End Sub
0
JenebyM
Asked:
JenebyM
1 Solution
 
JenebyMAuthor Commented:
Hi,
Please keep open. I have just returned to work and will be testing the suggested solution during this week.
Thank you
0
 
mlmccCommented:
When do you expect to get to this?

mlmcc
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
JenebyMAuthor Commented:
Please close now. The solution provided works with database 'tables'. We have been unable to get this to work with 'views' - which is what we needed. Thanks for your indulgence in keeping this open.

0
 
mlmccCommented:
What seems to be the problem with views?  Do the users have permissions for the views?

mlmcc
0
 
JenebyMAuthor Commented:
Users have the appropriate permissions fto access the tables and views.

I will restate the problem as follows:

The crystal report design time datasource location is a view. This is called vw_nfs_INVOICE and used only at design time.

At run time the a new view name is created by the application when a report is requested. This new view name is unique and random. ie the run time view name is vw_nfs_XXXXX where XXXXX is the last five characters of the users session ID. The Crystal XI report then needs to refresh the data after changing the location from vw_nfs_INVOICE to vw_nfs_XXXXX and displaying the data from that view.

After the data has been displayed on the crystal report, view vw_nfs_XXXXX is dropped.

The problem is finding the code snippet that will change the design datasource location from vw_nfs_INVOICE to vw_nfs_XXXXX at run time.



0
 
mlmccCommented:
Did you try the idea?  As far as Crystal is concerned a view is a table so the same method should work.

mlmcc
0
 
JenebyMAuthor Commented:
This idea does not work with tables.

If you read the sypnosis, it addresses the issue of changing the database name NOT table/view name within the same database. I have attaempted to make changes to the location within the same database. NO joy there.

0
 
JenebyMAuthor Commented:
Finally solved this:

I have used a suggested solution from the business objects site:

This is
http://support.businessobjects.com/library/kbase/articles/c2010371.asp

Your suggestion was quite close. The above KB article was closer since all I had to change.
1. The corrected connection info in the original code by using 'crConnectionInfo.ServerName = "DSN" instead of "Server Name"
Then

2. Specify the view name required at run time using "crTable.Location = "Northwind.dbo." + session.ID"

Works perfectly for my purpose.

Your suggestion certainly led me to this solution

Please close

 
0
 
mlmccCommented:
Since our dev and prod servers are named the same on separate networks we never had any problems with passing or changing the database.

Glad i could help

mlmcc
0
 
leirbag-gtCommented:
The link you suggested is no longer valid, can you give me another link or an alternate way to get the solution?

Regards.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now