Dynamic Reports UsingSQL Queries

• Download demo project - 40.1 Kb
• Download source files - 61 Kb

Introduction

Microsoft Visual Studio .NET provides Crystal Reports for report printing and throws the DataReport component (Visual Studio 6) away from .NET. Although, Crystal Reports is a good report creator, sometimes when we want to create dynamic reports, maybe for listing something or for data summation, it is not so suitable. Crystal Reports can be used for dynamic reports but it has a fixed set of column types and the number of maximum columns.

So I use the PrintDocument component, in the System.Drawing.Printing namespace of the .NET base library. PrintDocument is a component like a plain paper. You can draw or print anything on it. Normally, you have to set the object coordination and other properties just like when you use a pen to write on the paper. But for this code, you just use a SQL query or a dataset only!

Using the code

Add a form to a project and a PrintDocument component to it. And use a DataSet, and specify the column of the first table (Table(0)) by strict format (1 set per 1 report column):

• Column 1 -> Column name.
• Column 2 -> Start position on paper, I set that all width has range between 0-99.
• Column 3 -> End position on paper, I set that all width has range between 0-99.
• Column 4 -> Justify (L-Left, R-Right, C-Center).
• Column 5 -> Has summarize in this column (Y/N).
• Column 6 -> Display format (such as #,##0.00).
• Column 7 -> Rest in line? Begin with 1.
• Column 8 -> Data column.

Please look at the example, it's easy to understand.

We can set the properties of PageSetDlg and prnSetDlg objects for the print options. I use the Form component and I put the PrintDocument, PrintPreviewDialog, PageSetupDialog, and PrintDialog components on it. Then, we can use these for direct printing or use a print dialog too. We can set the properties of those objects for a specific print option such as setting the page orientation to landscape, etc.

'-------- Example use, If you haved data in
'           dataset already. --------------

'--- We can set properties of PageSetDlg
    ' and prnSetDlg object for print option 
    'frmDynamicPrint.PageSetDlg.PageSettings.Landscape = True

    '---If it has sum column is this report,
    '   we have to set hasSum flag to true. 
    frmDynamicPrint.hasSum = True

'---Initialization
    frmDynamicPrint.FontName = "Tahoma"
    frmDynamicPrint.FontSize = 12
    frmDynamicPrint.FontSizeHead = 16
    frmDynamicPrint.FontSizeHead2 = 14
    frmDynamicPrint.LineHeight = 1
    frmDynamicPrint.SkipLinePerRecord = 1

    '---Call InitPrint for inital some properties,
    ' you can modify it.
    frmDynamicPrint.InitPrint()

    frmDynamicPrint.HeadFirstString = _
      "Report for Product Order Value Summary"
    frmDynamicPrint.HeadSecondString = "Test Report"
    frmDynamicPrint.DateString = Now.ToString
    frmDynamicPrint.PrintPreview()
    frmDynamicPrint.Dispose()

Points of Interest

I will develop an HTML report later or will add a feature to export a report to another format.

History

• November 27, 2005 - Fixed a little bug in the example in setting the page number.
• November 24, 2005 - Fixed a big bug in the query loop and the summation method. I'm very sorry for my late bug fixing.
• October 05, 2005 - Added the wrap text feature.
• September 13, 2005 - Add feature for multiple lines and setting height of each line. It can be used like the wrap text feature too.
• September 12, 2005 - Changed the column position addressing on paper. Use start-end positions instead of one position.