Click here to Skip to main content
Click here to Skip to main content

Dynamic Reports UsingSQL Queries

, 23 Nov 2005
Rate this:
Please Sign up or sign in to vote.
Dynamic report by using the PrintDocument component with SQL queries.

Sample Image

Sample Image- Multilines

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Pakorn Indhatep
Web Developer
Thailand Thailand
No Biography provided

Comments and Discussions

 
QuestionC#??? Pinmemberrobertdlm18-Jul-12 17:57 
QuestionDisplaying the values of field PinmemberElie Ghanoum19-May-08 11:48 
GeneralError on printing - Infinity page print!!! PinmemberMember 179106330-Mar-08 22:49 
GeneralNew comer Need Help...Please PinmemberSamRST22-Sep-07 20:01 
GeneralDbnull Values and Columns Not found after Count Pinmemberjcviera6-Feb-07 7:37 
GeneralSource code Bugs Pinmemberdanmarin9-Dec-06 7:39 
GeneralPage Numbers PinmemberRichard Elias11-Sep-06 22:02 
GeneralPrint problem PinmemberRichard Elias11-Sep-06 3:02 
QuestionPage header & footer? Pinmemberpoetyip24-Aug-06 5:40 
Questioncan't print multiple page problem PinmemberTony567887657-Jul-06 6:46 
GeneralReport Inflated on Printed Copy Pinmemberhing_jl15-May-06 21:59 
QuestionHow to Group Data in Report? Pinmembersricse27-Mar-06 7:52 
AnswerRe: How to Group Data in Report? Pinmemberbryantlaw9-Jan-07 20:50 
GeneralDB problem perform operation Pinmember__Shah__22-Mar-06 0:51 
Generaldid you actually make marks on paper Pinmemberbquick15-Mar-06 1:38 
Generalexport function to word Pinmemberwooohoo10-Jan-06 5:28 
GeneralReport will not Print (Error) Pinmembervb_dot_net26-Dec-05 9:12 
GeneralRe: Report will not Print (Error) Pinmemberanishmm30-Jan-06 17:59 
GeneralRe: Report will not Print (Error) PinmemberPakorn Indhatep4-Feb-06 5:05 
GeneralRe: Report will not Print (Error) Pinmembershigeeru26-Feb-06 11:24 
GeneralColumn sum PinmemberRichard Elias19-Nov-05 5:20 
GeneralRe: Column sum PinmemberPakorn Indhatep23-Nov-05 22:06 
Generalsum (data) PinmemberMarc-André Parizeau14-Oct-05 2:33 
GeneralRe: sum (data) PinmemberPakorn Indhatep23-Nov-05 22:07 
GeneralDetail explaination Pinmembermansii12-Oct-05 20:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 24 Nov 2005
Article Copyright 2005 by Pakorn Indhatep
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid