Click here to Skip to main content
11,719,903 members (79,597 online)
Click here to Skip to main content

Dynamic Reports UsingSQL Queries

, 23 Nov 2005 169.6K 3.7K 88
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

You may also be interested in...

Comments and Discussions

 
QuestionC#??? Pin
robertdlm18-Jul-12 17:57
memberrobertdlm18-Jul-12 17:57 
QuestionDisplaying the values of field Pin
Elie Ghanoum19-May-08 11:48
memberElie Ghanoum19-May-08 11:48 
GeneralError on printing - Infinity page print!!! Pin
Member 179106330-Mar-08 22:49
memberMember 179106330-Mar-08 22:49 
GeneralNew comer Need Help...Please Pin
SamRST22-Sep-07 20:01
memberSamRST22-Sep-07 20:01 
GeneralDbnull Values and Columns Not found after Count Pin
jcviera6-Feb-07 7:37
memberjcviera6-Feb-07 7:37 
GeneralSource code Bugs Pin
danmarin9-Dec-06 7:39
memberdanmarin9-Dec-06 7:39 
GeneralPage Numbers Pin
Richard Elias11-Sep-06 22:02
memberRichard Elias11-Sep-06 22:02 
I noticed that the pagenumber is not increased while having multiple pages.
This could be fixed in the same area that resets dbCursor
I solved it in the following manner and that works fine, including printing and reprinting.
If hasSum Then
If Not (dbCursor = dbSet.Tables(0).Rows.Count) And (Not printSumComplete) Then
e.HasMorePages = True
PageNumber += 1
Else
e.HasMorePages = False
dbCursor = 0
PageNumber = 1
End If
Else
If Not (dbCursor = dbSet.Tables(0).Rows.Count) Then
e.HasMorePages = True
PageNumber += 1
Else
e.HasMorePages = False
dbCursor = 0
PageNumber = 1
End If
End If

Richard
GeneralPrint problem Pin
Richard Elias11-Sep-06 3:02
memberRichard Elias11-Sep-06 3:02 
QuestionPage header & footer? Pin
poetyip24-Aug-06 5:40
memberpoetyip24-Aug-06 5:40 
Questioncan't print multiple page problem Pin
Tony567887657-Jul-06 6:46
memberTony567887657-Jul-06 6:46 
GeneralReport Inflated on Printed Copy Pin
hing_jl15-May-06 21:59
memberhing_jl15-May-06 21:59 
QuestionHow to Group Data in Report? Pin
sricse27-Mar-06 7:52
membersricse27-Mar-06 7:52 
AnswerRe: How to Group Data in Report? Pin
bryantlaw9-Jan-07 20:50
memberbryantlaw9-Jan-07 20:50 
GeneralDB problem perform operation Pin
__Shah__22-Mar-06 0:51
member__Shah__22-Mar-06 0:51 
Generaldid you actually make marks on paper Pin
bquick15-Mar-06 1:38
memberbquick15-Mar-06 1:38 
Generalexport function to word Pin
wooohoo10-Jan-06 5:28
memberwooohoo10-Jan-06 5:28 
GeneralReport will not Print (Error) Pin
vb_dot_net26-Dec-05 9:12
membervb_dot_net26-Dec-05 9:12 
GeneralRe: Report will not Print (Error) Pin
anishmm30-Jan-06 17:59
memberanishmm30-Jan-06 17:59 
GeneralRe: Report will not Print (Error) Pin
Pakorn Indhatep4-Feb-06 5:05
memberPakorn Indhatep4-Feb-06 5:05 
GeneralRe: Report will not Print (Error) Pin
shigeeru26-Feb-06 11:24
membershigeeru26-Feb-06 11:24 
GeneralColumn sum Pin
Richard Elias19-Nov-05 5:20
memberRichard Elias19-Nov-05 5:20 
GeneralRe: Column sum Pin
Pakorn Indhatep23-Nov-05 22:06
memberPakorn Indhatep23-Nov-05 22:06 
Generalsum (data) Pin
Marc-André Parizeau14-Oct-05 2:33
memberMarc-André Parizeau14-Oct-05 2:33 
GeneralRe: sum (data) Pin
Pakorn Indhatep23-Nov-05 22:07
memberPakorn Indhatep23-Nov-05 22:07 
GeneralDetail explaination Pin
mansii12-Oct-05 20:01
membermansii12-Oct-05 20:01 
GeneralRe: Detail explaination Pin
Pakorn Indhatep14-Oct-05 4:30
memberPakorn Indhatep14-Oct-05 4:30 
GeneralRe: Detail explaination Pin
mansii17-Oct-05 22:33
membermansii17-Oct-05 22:33 
GeneralRe: (No) Detail Rows? explaination Pin
adhsys23-Nov-05 11:59
memberadhsys23-Nov-05 11:59 
GeneralRe: (No) Detail Rows? explaination Pin
Pakorn Indhatep23-Nov-05 22:03
memberPakorn Indhatep23-Nov-05 22:03 
GeneralRe: (No) Detail Rows? explanation (OK) Pin
adhsys24-Nov-05 0:44
memberadhsys24-Nov-05 0:44 
GeneralRe: (No) Detail Rows? explaination Pin
mansii24-Nov-05 0:53
membermansii24-Nov-05 0:53 
GeneralNice Work [Suggestion] Pin
Muhammad Waqas Butt26-Aug-05 0:39
memberMuhammad Waqas Butt26-Aug-05 0:39 
GeneralRe: Nice Work [Suggestion] Pin
Vertyg027-Aug-05 7:46
memberVertyg027-Aug-05 7:46 
GeneralThank you Pin
Pakorn Indhatep27-Aug-05 9:37
memberPakorn Indhatep27-Aug-05 9:37 
GeneralRe: Thank you Pin
Vertyg029-Aug-05 2:29
memberVertyg029-Aug-05 2:29 
Generalversion 1.1 Pin
Pakorn Indhatep11-Sep-05 22:35
memberPakorn Indhatep11-Sep-05 22:35 
GeneralRe: version 1.1 Pin
Muhammad Waqas Butt12-Sep-05 4:39
memberMuhammad Waqas Butt12-Sep-05 4:39 
Generalversion 1.1.1 Pin
Pakorn Indhatep13-Sep-05 0:34
memberPakorn Indhatep13-Sep-05 0:34 
GeneralRe: version 1.1.1 Pin
Muhammad Waqas Butt21-Sep-05 5:23
memberMuhammad Waqas Butt21-Sep-05 5:23 
GeneralRe: version 1.1.1 Pin
Pakorn Indhatep21-Sep-05 19:28
memberPakorn Indhatep21-Sep-05 19:28 
GeneralVersion 1.2 Add wrap text feature Pin
Pakorn Indhatep4-Oct-05 18:28
memberPakorn Indhatep4-Oct-05 18:28 
QuestionSome Questions Pin
| Muhammad Waqas Butt |5-Oct-05 5:06
member| Muhammad Waqas Butt |5-Oct-05 5:06 

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 | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 24 Nov 2005
Article Copyright 2005 by Pakorn Indhatep
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid