Dynamic personalized reporting reporting tools Crystal Reports

This post is for those who are faced with the task of dynamic personalized distribution of reports or any analytical materials.

Suppose your company has a network of branches and you need to adjust the weekly distribution of reports with the results of the work to branch managers. The report should contain the results of the branch’s activity over the past week - tables, graphs, and analytical information. Accordingly, for each branch the information in the report is unique.

I'll tell you how this can be done quickly and easily.
To solve the problem, we need Crystal Reports 2008 and Visual Studio 2010 (you can use earlier versions of the products).
To automate the distribution of reports, it is necessary that all analytical information for the preparation of reports be contained in databases. As a result, it is good to send data for each branch in one or several database queries before sending reports.
Example query result:

')
Using Crystal Reports we can generate a report containing data on sales, expenses and other indicators for a separate branch. We use the results of this query as the data source in the report, and in the Record Selection Formula we use a filter for one branch only. For example, the branch number is 1:

{data.branch} = 1 

Since Crystal Reports is quite a flexible tool, using such tools as formulas and worksheets, you can create any document format: from a simple one-page report to a lengthy analytical study with tables and graphs.

Further, by changing the Record Selection Formula, we can display information on any branch in the report.
All we need to do next is to export the report to one of the formats (pdf, xls, rtf) and send it by email.

Consider how this can be automated.

First of all, we will need a table containing the numbers of the branches and the email of the recipient of the report. We need to implement a cycle on the lines of this table. Next, for each row, we take the branch number and substitute it in the Record Selection Formula of our report. Next, export the report to a pdf file and attach this file to the letter. We send the letter to the appropriate address.

Crystal Reports not only provides a reporting development environment, but also development components for the NET platform.
Below is a sample of Visual Basic code:

 Imports System.Net.Mail Imports System.IO Imports System.Net.NetworkCredential Imports CrystalDecisions Module Module1 Sub Main() '      Dim Rows1 As DataRow Dim Table1 As New myData.MailListDataSet.Query1DataTable Dim myDA As New myData.MailListDataSetTableAdapters.Query1TableAdapter Dim strDate = Format(CDate(Today()), "ddMMyyyy") Dim myBranch As Integer Dim myBranchMail As String Dim myAppPath As String Dim myLogFilePath As String Dim myLogStr As String myAppPath = My.Application.Info.DirectoryPath ' ,      Dim myDirInf As New DirectoryInfo(myAppPath + "\" + strDate) If Not myDirInf.Exists() Then myDirInf.Create() End If '          myLogFilePath = myAppPath + "\" + strDate + "\log.txt" Dim fs As StreamWriter = File.CreateText(myLogFilePath) '   ( ,   ) myDA.Fill(Table1) '  ReportDocument     Dim myReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument myReport.FileName = myAppPath + "\report1.rpt" '   For Each Rows1 In Table1.Rows '     myBranch = Rows1.Item(0) myBranchMail = Rows1.Item(1) ' Record Selection Formula myReport.RecordSelectionFormula = "{data.branch} = " + CStr(myBranch) '  myReport.Refresh() Try '   pdf (   -    ) myReport.ExportToDisk([Shared].ExportFormatType.PortableDocFormat, myAppPath + "\" + strDate + "\Report for Branch " + CStr(myBranch) + " " + strDate + ".pdf") '       myLogStr = CStr(Now()) + ": " + "generate file for Branch " + CStr(myBranch) + Chr(13) + Chr(10) Console.Write(myLogStr) fs.Write(myLogStr) Catch exp As Exception '      Console.WriteLine("Erorr:" & exp.Message) myLogStr = CStr(Now()) + ": Erorr" & exp.Message & " when generate file for Branch " + CStr(myBranch) + Chr(13) + Chr(10) Console.Write(myLogStr) fs.Write(myLogStr) End Try ' Smtp Client      Dim mySmtp As New System.Net.Mail.SmtpClient("company.com") mySmtp.Credentials = New System.Net.NetworkCredential("user", "password") ' ,     Dim myMsg As New System.Net.Mail.MailMessage("mail@example.com", myBranchMail, "   X", _ "   " + CStr(myBranch) + ",      .") '     pdf  Dim myAtt As New System.Net.Mail.Attachment(myAppPath + "\" + strDate + "\Report for Branch " + CStr(myBranch) + " " + strDate + ".pdf") myMsg.Attachments.Add(myAtt) Try '  mySmtp.Send(myMsg) '       myLogStr = CStr(Now()) + ": " + "sending file for Branch " + CStr(myBranch) + ", on " + myBranchMail + Chr(13) + Chr(10) Console.Write(myLogStr) fs.Write(myLogStr) Catch exp As Exception '      Console.WriteLine("Erorr:" & exp.Message) myLogStr = CStr(Now()) + ": Erorr" & exp.Message & " when sending file for Branch " + CStr(myBranch) + Chr(13) + Chr(10) Console.Write(myLogStr) fs.Write(myLogStr) End Try Next Rows1 fs.Close() End Sub End Module 

Using code, you can make a console application and run it on a schedule, or you can use it in the ETL SSIS package (MS SQL Server Integration Services).