Posts

Posted on

Running SSRS Reports with Lotus Notes Databases as the Original Data Source

SSRS logo

I had a need to be able to report on data inside a set of Lotus Notes 7.0 databases, and I wanted to use SQL Server Reporting Services (SSRS) to do this. There is no direct interface between SSRS and Notes, but I found that IBM provided an ODBC connection from Notes using their NotesSQL drivers.

I have set up an interface between Lotus Notes and Microsoft SQL Server 2000 using IBM’s NotesSQL ODBC drivers. The Lotus Notes 7 Client, NotesSQL 8.5.1 drivers, and an ODBC DSN (using the NotesSQL client) for each Lotus Notes database are all installed on the SQL Server. These ODBC drivers are compatible with 32-bit SQL 2000 but not SQL 2008 R2 (64-bit). I have not had an opportunity to test on any other platform as yet, though it’s certainly safe to assume that other versions of Lotus Notes up to 8.5.1 should work, since that’s what the NotesSQL drivers were written for.

I created a Linked Server on the SQL Server for each ODBC DSN; that is, each connected Notes database. The views and tables in the Notes DBs can be accessed via OPENQUERY commands, which can be included in a SQL database on the SQL Server. Because the connection between the two using only the Linked Server seemed so slow, I built a SQL Server database called Noteslink.

To test, I connected Query Analyzer to the NotesLink database on the SQL Server. I created Stored Procedures with the convention sp_DBname_AllDocuments to pull the All Documents view from the Notes database whose DSN had the name DBname. (In each case where there is a “DBname” below, substitute the name of the ODBC DSN.)

USE [NotesLink]
GO

/****** Object:  StoredProcedure [dbo].[sp_DBname_AllDocuments]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_DBname_AllDocuments] AS
BEGIN
SELECT * FROM OPENQUERY (DBname,
'Select * from All_Documents')
END

GO

Run the command “EXEC sp_DBname_AllDocuments” in Query Analyzer and this will pull the All Documents view from the DBname database.

From here, I built various jobs that would insert the results into tables in the “NotesLink” SQL Server database. I connected the SSRS reports to the “NotesLink” database, and fun was had by all. This exact configuration will not work if you need real-time reports; you would have to use OPENQUERY statements in the SSRS queries to pull directly from the Notes databases.

Please let me know if you have tried this and what other ways this could be accomplished. As SQL Server 2000 is reaching end-of-life, a newer solution using SQL 2005/2008/2012 could become necessary.

Posted on

Implementing a Factory Pattern in ASP.NET

factory pattern diagram

A question posed by @Ramsharma1234 on Twitter this morning asked how to implement Factory Patterns in ASP.NET.

(definition of Factory Pattern)

Below is what would be considered a very basic form of Factory Pattern.  This method will essentially instantiate a generic Object, which is the parent of all types of Objects, and later be treated as if it were the type of child object that is used as a parameter in calling the method.  On a Web site where I needed to build Web controls dynamically, based on values from a database query, I created a method called “AddControl” that would add a generic Object to a Placeholder on my Web form:

Private Sub AddControl(ByVal oControl As Object)
   Placeholder1.Controls.Add(oControl)
End Sub

To call this method, I would instantiate a Web control such as a Button and add it to the Placeholder with the method:

Dim btnPrint As New Button
btnPrint.Attributes.Add("onclick", "javascript:window.print();")
btnPrint.Visible = False
AddControl(btnPrint)

Any type of Web control (TextBox, Label, Literal, etc.) could be instantiated and then added using this method.  For instance, if the table value for a particular field indicated the creation of a TextBox, this would be how that could be accomplished:

Dim oControl As New Object
'...
oControl = New TextBox
With oControl
.ID = strFieldName & "_mltxt"
.TextMode = TextBoxMode.MultiLine
.MaxLength = 2000
.Style("overflow") = "hidden"
.Height = 300
.Width = 500
.BorderStyle = BorderStyle.None
.Enabled = True
.ReadOnly = True
End With
AddControl(oControl)
Posted on

Setting the Connection String Programmatically on a Crystal Reports ReportDocument

logos for Visual Studio and Crystal Reports

Programmatically setting the connection string for a Crystal Reports ReportDocument will allow you to have the report automatically use the active connection string for the rest of the application (as in previous posts). In this case, the report is created using a method that is triggered by clicking a button on a form. The log on information must be applied to each table in the ReportDocument.

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim crReportDocument As ReportDocument
            crReportDocument = New ReportDocument
            crReportDocument.Load(<<name of reportdocument>>)

            Dim sConn As SqlConnectionStringBuilder = New SqlConnectionStringBuilder(<<connection string>>)

            Dim tables As CrystalDecisions.CrystalReports.Engine.Tables = crReportDocument.Database.Tables

            For Each table As CrystalDecisions.CrystalReports.Engine.Table In tables
                Dim tableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo = table.LogOnInfo
                tableLogOnInfo.ConnectionInfo.ServerName = sConn.DataSource
                tableLogOnInfo.ConnectionInfo.DatabaseName = sConn.InitialCatalog
                tableLogOnInfo.ConnectionInfo.IntegratedSecurity = True
                table.ApplyLogOnInfo(tableLogOnInfo)
            Next

            ' ...

       End Sub
%d bloggers like this: