This is just the first of some English posts that I’ll publish by translating most popular posts in this blog. Original version (in Spanish) is here.
First of all, I must asume that creating reports is one of that things I like worst in programming. But it’s quite obvious that few serious applications don’t need them, and the one I’m developing now isn’t an exception to this rule. So I’ve been creating some reports recently and I’ve discovered a new way to do it. And that’s what I explain in this post.
As I’ve said sometimes before in previous posts, I develop with VisualBasic.NET and MySQL database. And I use Crystal Reports to create reports, as this tool is integrated in VisualStudio.NET. Since now, I used to use an ODBC connection configured in each PC to connect to MySQL server. But I didn’t like this system much, because actually I’m not working just with one database but with some with different names. They have the same structure, tables and data, but just one is the good one, as the rest are just for developing purposes. It’s really easy to use one or other connection string to make the application connect with one or other database, but with reports it isn’t so easy as they take data using that ODBC connection (and it just can connect with one database).
But now I’ve discovered how to create reports with just a DataTable and an XML schema, needing nothing else. Actually it’s possible to use a DataSet instead of a DataTable as well. So I’m going to explain it with an easy example and some images.
I’ll work with two tables in my MySQL database where I’ll keep information about bills. First table is the one with information about headers and has this data:
| blh_num | blh_dat | blh_cus |
+---------+------------+-------------------------+
| 1 | 2008-07-30 | CERAMICAS PEPE, S.A. |
| 2 | 2008-07-30 | TALLERES GOMEZ, S.L. |
| 3 | 2008-07-31 | DEPORTES DAMIAN, S.L. |
| 4 | 2008-07-31 | SOFTWARE ALBERTMATA.NET |
+---------+------------+-------------------------+
Second table is the one with information about positions and has this rows:
| blp_num | blp_pos | blp_art | blp_pri | blp_qty |
+---------+---------+------------------------+---------+---------+
| 1 | 1 | RATON LOGITECH | 15.95 | 1 |
| 2 | 1 | MONITOR LG 19 PULGADAS | 210.5 | 1 |
| 3 | 1 | ROUTER DLINK | 56 | 1 |
| 4 | 1 | RATON LOGITECH | 15.95 | 2 |
| 4 | 2 | TECLADO LOGITECH | 12.95 | 1 |
| 4 | 3 | RECEPTOR GPS ZAPPA | 59.95 | 1 |
| 4 | 4 | PAQUETE 500 FOLIOS | 3.7 | 4 |
+---------+---------+------------------------+---------+---------+
It’s something really simple and not normalized, but will be enough for this example, as we’re going to create a report that will be the inovice for purchase number 4 (the one with customer SOFTWARE ALBERTMATA.NET). Obviously, we’ll need information about both tables but I just want to work with one DataTable, so first of all I’m going to create a MySQL view with this sentence:
(
SELECT
blh_num AS BILL_NUMBER,
blh_dat AS BILL_DATE,
blh_cus AS BILL_CUSTOMER,
blp_pos AS LINE_NUMBER,
blp_art AS LINE_ARTICLE,
blp_pri AS LINE_UNITPRICE,
blp_qty AS LINE_UNITS,
blp_pri * blp_qty AS LINE_TOTALPRICE
FROM
blh_billheader LEFT JOIN blp_billposits ON blh_num = blp_num
WHERE
blh_num = 4
);
So, from now on the report will be created using this zbl_bill2print view. Let’s go with the .NET part.
Along this post we’ll work with these three things:
1) a Windows form (frmMain) where we’ll have the report viewer object.
2) a class (clsReportCreator) we’re going to create right now.
3) a report (rptBill) that will be the invoice we want to print.
So let’s start creating clsReportCreator class. It’ll have only one attribute (the name of the table or view), one constructor method, one method to load DataTable object and one last method to generate the XML file. Here is the full code for this class:
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Needs: MySQL.Data reference.
' Description: Class to create a report using just an XML file.
'--------------------------------------------------------------------
Imports MySql.Data.MySqlClient
Public Class clsReportCreator
'----------------------------------------------------------------
' Attributes.
'----------------------------------------------------------------
Private TableOrView As String
'----------------------------------------------------------------
' Constructor method.
'----------------------------------------------------------------
Public Sub New(ByVal TableOrView As String)
Me.TableOrView = TableOrView
End Sub
'----------------------------------------------------------------
' Returns DataTable corresponding to TableOrView.
'----------------------------------------------------------------
Public Function GetDataTable() As DataTable
Dim DA As MySqlDataAdapter
Dim DS As New DataSet
Dim DT As DataTable
Dim ConnectionString As String
Dim SQL As String
'Setting connection string to connect to MySQL database.
ConnectionString = "Database = blog; " _
& "Data Source = localhost; " _
& "User ID = root; " _
& "Password = mypassword"
'Setting SQL string.
SQL = "SELECT * FROM " & Me.TableOrView
'Getting data and filling DataSet and DataTable.
DA = New MySqlDataAdapter(SQL, ConnectionString)
DA.Fill(DS, Me.TableOrView)
DT = DS.Tables(Me.TableOrView)
'Returning DataTable.
Return DT
End Function
'----------------------------------------------------------------
' Creates XML file in desired path.
'----------------------------------------------------------------
Public Sub CreateXMLFile(ByVal FilePath As String)
Dim DT As DataTable
'Creating DataTable.
DT = Me.GetDataTable()
'Writting XML file in desired path.
DT.WriteXmlSchema(FilePath & Me.TableOrView & ".xml")
End Sub
End Class
And we also create frmMain form, which only code by the moment will be this:
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Description: Form to show how to create a report using just an XML
' file.
'--------------------------------------------------------------------
Public Class frmMain
'----------------------------------------------------------------
' As a first step, creates XML file.
'----------------------------------------------------------------
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Creating XML file.
Dim RC As New clsReportCreator("zbl_bill2print")
RC.CreateXMLFile("C:\")
End Sub
End Class
Right now we have a first application. If we execute it we’ll get C:\zbl_bill2print.xml file with the structure of zbl_bill2print view. So we run it and get that file.
First, we add a report to our project and give it a name like rptBill.rpt. We create it choosing empty report option, so desestimating any templates.
Now we go to Fields explorer menu and right-click the first option (Database fields). In new contextual menu we click on Database assistant option.
After this we get the Available data source menu, where we choose Create new connection and after that ADO.NET option.
Making this, we’ll see a new form where we’ll be asked about File’s path. In this point we have to find XML file we’ve created before (in my example C:\zbl_bill2print.xml) and then press Finish. We have NewDataSet option including our just added zbl_bill2print in Available data source menu now.
So we select it and press button to move it to Selected tables menu. Done this, it’s time to click on Accept.
With all this stuff we’ve gotten that zbl_bill2print structure available in Fields explorer menu with all its fields, as shown in image below:
Nothing special to say here. Just adding fields from Fields explorer menu, inserting text objects where needed, sums, text formats, images and so on…
I’ve just created a very simple design like this:
Finally we’re going to create the bill. To do that, we add a CrystalReportViewer object in frmMain form. I call it crvBill. After that it’s necessary to modify frmMain source code to make it look like this:
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Description: Form to show how to create a report using just an XML
' file.
'--------------------------------------------------------------------
Imports CrystalDecisions.CrystalReports.Engine
Public Class frmMain
'----------------------------------------------------------------
' Creates XML file (just once) and creates and loads a report.
'----------------------------------------------------------------
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Creating XML file.
Dim RC As New clsReportCreator("zbl_bill2print")
'RC.CreateXMLFile("C:\")
'Creating report.
Dim RD As ReportDocument = New rptBill()
'Setting data source for report.
Dim DT As DataTable = RC.GetDataTable()
RD.SetDataSource(DT)
'Setting data source for possible subreports.
For Each SR As ReportDocument In RD.Subreports
If SR.Database.Tables.Count > 0 Then
SR.SetDataSource(DT)
End If
Next
'Setting recently created report must be shown in viewer.
Me.crvBill.ReportSource = RD
End Sub
End Class
It’s important to note that the line where the XML file is created is commented now, as we just need to create this file once to use it to create the source data, but from now on we don’t need to generate it every time.
What we’re mainly doing in this code is:
1) creating a report object same kind we’ve designed in step 3,
2) getting a DataTable with data we want to show (in this example and according to the way we’ve defined MySQL view, we want to show invoice number 4),
3) setting this DataTable as the report’s source data,
4) asking CrystalReportViewer to show this report.
We execute the application again and get desired invoice:
Of course there should be quite more information, images and legal texts in a real invoice, but this is just an easy example of how to do the report itself.
So we’ve seen how to create a report in VisualBasic.NET just using an XML file. Of course there are plenty of things to improve, as optimizing how database connection is done, or avoiding WHERE condition directly in MySQL view and so on… but what I was looking for with this example was just a very minimum guide to show the process.
PS. Some menu and option names can be different as I develope in VisualStudio Spanish version and I’ve just translated them as I’ve thought they could appear in English version. Sorry about that!
There is a second part for this post explaining how to pass parameters from form to report, but it’s still only in Spanish.
Estos últimas días he estado mirando un tema que me preocupaba porque iba a necesitarlo en la aplicación que ando desarrollando… y tras no haber encontrado nada, finalmente me he tenido que poner a fondo con ello hasta sacarlo de una u otra manera. El tema consiste en hacer una consulta de referencias cruzadas en MySQL y, como digo, tras bastante investigar he descubierto que en realidad MySQL no admite esta clase de consultas. Para explicar mejor lo que pretendía hacer voy a basarme en un ejemplo consistente en la siguiente tabla sfc_salefrcast:
Hoy algo de teoría. Como sabemos, el lenguaje estándar para realizar consultas en bases de datos es el SQL (Structured Query Language). Este lenguaje permite definir y manipular bases de datos relacionales, que son las bases de datos con las que solemos trabajar (como 
Cuando diseñamos una tabla en 
Este post es muy simple, pero su contenido me facilita mucho la vida desde que lo programé. Se trata de un par de simples funciones para convertir los dos formatos de fecha que más utilizo en el proyecto que estoy desarrollando. Estos dos formatos son por una parte el habitual DATE de MySQL (YYYY-MM-DD) y por otra parte un formato texto VARCHAR(8) que simplemente elimina los guiones (YYYYMMDD) de la fecha. Lo de utilizar este formato en modo texto viene porque recopilo mucha información de tablas en una base de datos externa en la que las fechas están almacenadas de esta manera.