• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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

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?
0
sah18
Asked:
sah18
  • 11
  • 4
1 Solution
 
aki4uCommented:
Hi,

Try this:

Dynamic Reports UsingSQL Queries
http://www.codeproject.com/vb/net/dynamic_report_with_query.asp
0
 
ptleitchCommented:
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.
0
 
sah18Author Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ptleitchCommented:
cool,

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.
0
 
sah18Author Commented:
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!
0
 
ptleitchCommented:
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
        oTemplateFile.Close()

        Dim sReport As String = BuildReport(sTemplate)

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

        Response.Redirect("REPORTLOCATION" & strReportUserName & ".doc")
    End Sub
0
 
ptleitchCommented:
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.
0
 
ptleitchCommented:
also to stay consistant with my example

this line above:

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

should look like this

Response.Redirect("REPORTLOCATION" & strUserName & ".doc")
0
 
ptleitchCommented:
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
        oCnn.Open()
        Return ocmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
0
 
ptleitchCommented:
Private Function BuildReport(ByVal Template As String) As String
'PASS IN A VALUE FROM SESSION FOR THE SELECT STATEMENTS WHERE CLAUSE
        Dim strUserName As String = CType(Session("UserName"), String)
'STRING TO COMPARE BIT DATA TYPE TO
        Dim strYes As String = "True"
       
'THEN DO SOMETHING LIKE THIS FOR EACH TABLE
        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"))
            Else
                Template = Template.Replace("[FName]", "")
            End If

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

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

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

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

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

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

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

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

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

        End If
        odr = Nothing
        GC.Collect()

'THEN AFTER ALL YOUR TABLES ARE COMPLETE

Return Template
End Function
0
 
ptleitchCommented:
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.  

0
 
ptleitchCommented:
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>"
        Loop
        sTable1 += "</table>"
        odr = Nothing
        GC.Collect()

Template = Template.Replace("[Children]", sTable1)
0
 
ptleitchCommented:
let me know how it goes
0
 
sah18Author Commented:
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!!


0
 
ptleitchCommented:
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")
                Else
                    Template = Template.Replace("[HaveNetwork]", "No")
                End If
            Else

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
0
 
sah18Author Commented:
Thank you so much!  You've been a tremendous amount of help!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 11
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now