We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


ASP.NET - VB.NET - How can I create dynamic reports from sql server db data?

sah18 asked
Medium Priority
Last Modified: 2008-03-03
This is my first time creating a large ASP.NET application.  I've got the form creation (for data entry) down pat, but I'm pretty clueless as to what route to take to create some nice summary forms from the data that's being entered into the db.  I'm hoping for a method that is fairly quick and not too complicated to use, as I don't have a lot of time to learn a new system.  I am using Visual Studio .NET 2003 for development.  What are my options for creating nice reports that can be printed?
Watch Question


Try this:

Dynamic Reports UsingSQL Queries

I make an html file called reporttemplate.html  and put [Tokens] where I want the dynamic data to appear and programatically replace the [Tokens] with values from my database.  I then save the resulting html file with a *.doc extension so it can be opened in word and is printer friendly.  Whats nice about this method, is that if you want to change the format of the report you just edit the template.html file.  All of the tokens have to be different like [FName], [LName], [Address1], etc.  

If interested, write back and I will paste some of my code.  If you wanted to get started, make a template file in html and format it how you want the result to look and place unique tokens for every value.


ptleitch- yes, i am very interested in this approach!  I would love to see your code examples.  One of the things I will need to do, is have a series of if statements checking the values of each field in the db:  for example, if field1 = Y, then print a line on the html document (if it is N, I don't want a line to be printed).  This way I can build up a listing of all the items that have been selected for that record.  I will mostly be generating a single report for a single record (person).  If you have any examples that involve if statements in this way, that would be especially useful!!

thank you very much for your time.


I do the if statement thing for almost every single value to check if it is not null, and to change the true and false of the bit data type to yes and no.  I have just one more question.  How many tables will you be accessing?  I ask because I wrote a function that executes the query with the query string passed in as a parameter because I use 23 tables, so i wouldn't have to do the same thing 23 times.  If, say, you were only going to use info from one table, a function like this wouldn't be needed.


I actually have two different situations currently where I'd be using this kind of report generation.  One situation does involve only a single table.  The other one would involve 8 different tables.  So, I guess if you're willing to share both methods, that would be extremely helpful!

Ok for starters i have this in a page load, but you could also have it on a button click or some other event that you want to initiate the report.  Also i pass the value for my Where clause on my select statements in from a session, but you could do it any number of ways.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strUserName As String = CType(Session("UserName"), String)
        Dim oTemplateFile As New System.IO.StreamReader(Me.MapPath("TEMPLATELOCATION.HTML"))
        Dim sTemplate As String = oTemplateFile.ReadToEnd

        Dim sReport As String = BuildReport(sTemplate)

        Dim oReport As New System.IO.StreamWriter(Me.MapPath("REPORTLOCATION" & strtUserName & ".doc"))

        Response.Redirect("REPORTLOCATION" & strReportUserName & ".doc")
    End Sub

well i will just share the method for multiple tables, and hopefully if you can follow the code, you will see where each select table access the executequery function.  For use in one table, you just get rid of the function and but put some lines of code from it in the report building function.

also to stay consistant with my example

this line above:

Response.Redirect("REPORTLOCATION" & strReportUserName & ".doc")

should look like this

Response.Redirect("REPORTLOCATION" & strUserName & ".doc")

this is the function that executes the query for use with numerous tables.  you will pass the select statement in as a string

Private Function ExecuteQuery(ByVal query As String) As SqlClient.SqlDataReader
        Dim oCnn As New SqlClient.SqlConnection("YOURCONNECTIONSTRING")
        Dim ocmd As SqlClient.SqlCommand = oCnn.CreateCommand
        ocmd.CommandText = query
        Return ocmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Private Function BuildReport(ByVal Template As String) As String
        Dim strUserName As String = CType(Session("UserName"), String)
        Dim strYes As String = "True"
        Dim odr As SqlClient.SqlDataReader = ExecuteQuery("select * from TBL_EXAMPLE where UserName = '" & strUserName & "'")
        If odr.Read Then

            If Not IsDBNull(odr("FName")) Then
                Template = Template.Replace("[FName]", odr("FName"))
                Template = Template.Replace("[FName]", "")
            End If

            If Not IsDBNull(odr("LName")) Then
                Template = Template.Replace("[LName]", odr("LName"))
                Template = Template.Replace("[LName]", "")
            End If

            If Not IsDBNull(odr("Address1")) Then
                Template = Template.Replace("[Address1]", odr("Address1"))
                Template = Template.Replace("[Address1]", "")
            End If

            If Not IsDBNull(odr("Address2")) Then
                Template = Template.Replace("[Address2]", odr("Address2"))
                Template = Template.Replace("[Address2]", "")
            End If

            If Not IsDBNull(odr("City")) Then
                Template = Template.Replace("[City]", odr("City"))
                Template = Template.Replace("[City]", "")
            End If

            If Not IsDBNull(odr("State")) Then
                Template = Template.Replace("[State]", odr("State"))
                Template = Template.Replace("[State]", "")
            End If

            If Not IsDBNull(odr("ZIP")) Then
                Template = Template.Replace("[Zip]", odr("ZIP"))
                Template = Template.Replace("[Zip]", "")
            End If

            If Not IsDBNull(odr("Phone")) Then
                Template = Template.Replace("[Phone]", odr("Phone"))
                Template = Template.Replace("[Phone]", "")
            End If

            If Not IsDBNull(odr("Fax")) Then
                Template = Template.Replace("[Fax]", odr("Fax"))
                Template = Template.Replace("[Fax]", "")
            End If

            If Not IsDBNull(odr("Email")) Then
                Template = Template.Replace("[Email]", odr("Email"))
                Template = Template.Replace("[Email]", "")
            End If

        End If
        odr = Nothing


Return Template
End Function

I am just piecing together this example pretty fast without thinking very hard about it, the value i am passing in from session as an example "UserName" should probably be something like "UserID" since the table example i am using has FName and LName in it.  

for tables that have multiple records for the same user like you can piece together html to make tables.

odr = ExecuteQuery("select * from TBL_Example2 where UserName = '" & strReportUserName & "'")
        Dim sTable1 As String = "<table><tr>"
        sTable1 += "<td>ChildFName</td>"
        sTable1 += "<td>ChildLName</td>"
        sTable1 += "<td>Age</td>"
        sTable1 += "<td>Notes</td>"
        sTable1 += "</tr>"
        Do While odr.Read
            sTable1 += "<tr>"
            sTable1 += "<td>" & odr("ChildFName") & "</td>"
            sTable1 += "<td>" & odr("ChildLName") & "</td>"
            sTable1 += "<td>" & odr("Age") & "</td>"
            sTable1 += "<td>" & odr("Notes") & "</td>"
            sTable1 += "</tr>"
        sTable1 += "</table>"
        odr = Nothing

Template = Template.Replace("[Children]", sTable1)

let me know how it goes


I got it working!!!  I tried a very simple example, with a single record being returned in the query, and also I placed only 3 fields onto the template.  I also hard-coded the id that I wanted to query on in my sql statement, just to make it easy for now.  This is great!!!  I'm really excited that this is working!!!!!

I do have a couple followup questions for you on this:

1. I am still not quite sure how to create a new line in the report only if the field contains a Y (or sometimes it's a 1) for YES.  For example, if the field Kit = 'Y', I would like a line to appear in the output "Kit was provided." (or some other text that I would code).  Do you know how to do this?  And if Kit = 'N', I don't want anything additional to appear on the report (not even an extra blank line).  Does it make sense what I'm asking about?

2. Is there a way to get the toolbar with the "Save" option to appear along with the document, so that the user could easily know how to save the doc?

3. This is probably way beyond to ask for, but do you know if this can be converted into a true word document (or some other common format, pdf, etc), instead of it being an html doc being viewed in word?  I'm sure this is complex, and may not be necessary, but I was wondering how this might be done.  Again, probably way beyond this question.

I truly appreciate you helping me out so much!!

1.  I don't understand your yes/no situation completely, but you might need to use a nested if statement.  I use one like this to replace the true and false value in the DB with yes and no in the report.

If Not IsDBNull(odr("HaveNetwork")) Then
                If odr("HaveNetwork") = strYes Then
                    Template = Template.Replace("[HaveNetwork]", "Yes")
                    Template = Template.Replace("[HaveNetwork]", "No")
                End If

In the above examples, if a field was null, the label would still exist in the report  but an  empty string would be next to it.  If you didn't want a blank line to appear, you would probably have to mess with the html similar to what i did to build the tables for users with multiple children.  Like i replace the [token] where the table should be with html to build the table, but you aren't limited to just using the [token] you could select an html string that perfectly matches part of your template and replace it with what ever html you want.  You are pretty much on your own on this one.

2.  Maybe if you exported to *.pdf it would be easy to save for the user with the adobe toolbar that opens in the browser, the method i described causes the document to open in whatever program the user associates with *.doc files. In my case open office, in most others word.  

3.  Well when i first started researching this process, some one suggested to me that *.doc files basically use html for their formating and layout.  Which is why this process works.  There might be other formats that are also essentially html with a proprietary extension that this method will also work for.  As far as *.pdf, i know its possible, maybe not necessarily using this method, but can't give you any insite.  I use open office and when i open these documents, they are displayed by default in online view.  if you want  it to resemble a normal *.doc file you need to change it to print view in word, then it will divide it up by pages with the margins and everything.  

this is all the insight i can give, anything beyond this, you are on your own

glad i could help

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thank you so much!  You've been a tremendous amount of help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.