Advice on reporting via ASP

Discussion in 'ASP General' started by Jeff Boyer, Dec 20, 2003.

  1. Jeff Boyer

    Jeff Boyer Guest

    Hello everyone,

    I have recently developed a web application across my clients intranet. We
    used ASP with Interdev 6.0 and SQL server as the backend.

    They have now come to me asking me to develop some reports that can be
    accessed via a drill down within the app. They don't have Crystal Reports so
    this is not an option. Can anyone suggest an alternative to using crystal
    reports to publish dynamic reports via the web and ASP?

    Thanks in advance,
    Jeff
    Jeff Boyer, Dec 20, 2003
    #1
    1. Advertising

  2. Jeff Boyer wrote:
    > They have now come to me asking me to develop some reports that can be
    > accessed via a drill down within the app. They don't have Crystal Reports so
    > this is not an option. Can anyone suggest an alternative to using crystal
    > reports to publish dynamic reports via the web and ASP?


    You don't need a special report writer or reporting component. Merely
    generate all reports using ASP. The output is HTML, which they can print
    if they wish.

    Good Luck,
    Michael D. Kersey
    Michael D. Kersey, Dec 20, 2003
    #2
    1. Advertising

  3. Jeff Boyer

    Jeff Boyer Guest

    Ok, I hear what your saying but for large reports that total columns isn't
    that allot of code to write? I was hoping I didn't have to re-invent the
    wheel. I thought maybe there might be some sort of plugin to use with
    Interdev or maybe a third party application I could call from my page.

    Jeff
    "Michael D. Kersey" <> wrote in message
    news:...
    > Jeff Boyer wrote:
    > > They have now come to me asking me to develop some reports that can be
    > > accessed via a drill down within the app. They don't have Crystal

    Reports so
    > > this is not an option. Can anyone suggest an alternative to using

    crystal
    > > reports to publish dynamic reports via the web and ASP?

    >
    > You don't need a special report writer or reporting component. Merely
    > generate all reports using ASP. The output is HTML, which they can print
    > if they wish.
    >
    > Good Luck,
    > Michael D. Kersey
    >
    Jeff Boyer, Dec 20, 2003
    #3
  4. Jeff Boyer

    Roland Hall Guest

    "Jeff Boyer" wrote:
    > Ok, I hear what your saying but for large reports that total columns isn't
    > that allot of code to write?

    So, your application only displays data and they don't want to have to whip
    our their trusty calculators to add up the columns?

    for i = 0 to ubound(arr)
    sometotal = sometotal + arr(i)
    next
    reponse.write sometotal

    Doesn't seem like a lot.

    > I was hoping I didn't have to re-invent the
    > wheel.
    > I thought maybe there might be some sort of plugin to use with
    > Interdev or maybe a third party application I could call from my page.


    You wrote a custom app. I doubt you'll find a plugin that will not have to
    be customized, although Crystal Reports does have an ASP plugin.

    --
    Roland

    This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose.
    Roland Hall, Dec 20, 2003
    #4
  5. Jeff Boyer wrote:
    > Ok, I hear what your saying but for large reports that total columns isn't
    > that allot of code to write? I was hoping I didn't have to re-invent the
    > wheel. I thought maybe there might be some sort of plugin to use with
    > Interdev or maybe a third party application I could call from my page.
    > Jeff


    You can
    1. fetch the SQL result set into a recordset and then accumulate totals
    on the ASP page by looping through the result set, or
    2. issue a separate SQL query for the required summary columns.

    FWIW you can fetch as many result sets (issue as many SQL statements) as
    you wish on a single ASP page.

    Good Luck,
    Michael D. Kersey
    Michael D. Kersey, Dec 20, 2003
    #5
  6. Jeff Boyer

    Jeff Boyer Guest

    So your both saying my only option is to run some queries and dynamically
    build the tables to display the results?

    Jeff
    "Michael D. Kersey" <> wrote in message
    news:...
    > Jeff Boyer wrote:
    > > Ok, I hear what your saying but for large reports that total columns

    isn't
    > > that allot of code to write? I was hoping I didn't have to re-invent the
    > > wheel. I thought maybe there might be some sort of plugin to use with
    > > Interdev or maybe a third party application I could call from my page.
    > > Jeff

    >
    > You can
    > 1. fetch the SQL result set into a recordset and then accumulate totals
    > on the ASP page by looping through the result set, or
    > 2. issue a separate SQL query for the required summary columns.
    >
    > FWIW you can fetch as many result sets (issue as many SQL statements) as
    > you wish on a single ASP page.
    >
    > Good Luck,
    > Michael D. Kersey
    >
    Jeff Boyer, Dec 20, 2003
    #6
  7. Jeff Boyer wrote:
    > So your both saying my only option is to run some queries and dynamically
    > build the tables to display the results?
    > Jeff


    Your original post stated
    > They have now come to me asking me to develop some reports that can be
    > accessed via a drill down within the app.

    which can be done straightforwardly with ASP by displaying each item to
    be "drilled down" with an anchor, e.g.,
    ....
    <td><a href="drilldown.asp?emp_id=03423">03423</a></td>
    ....
    which, when clicked, takes the user to a more detailed level.

    There _are_ other ways to build HTML tables, e.g., the RecordSet
    object's GetString method which can build most of an HTML table from a
    recordset for you. GetString has a small bug in it that can be worked
    around; see
    http://www.google.com/groups?q= "Ge...ng=r&selm=&rnum=2
    for an example of GetString usage including the workaround.

    Also look at either of the previous posts
    http://www.google.com/groups?q= "Ge...g=r&selm=&rnum=10
    or
    http://www.google.com/groups?q= "Ge...ng=r&selm=&rnum=9
    which describe several techniques for fetching recordsets and building
    HTML tables.

    Perhaps there was some other requirement?

    Good Luck,
    Michael D. Kersey

    ===== Twenty-first-century taxes: ======================
    lottery - tax for sleeping in math class
    XML - tax for sleeping in compiler class
    Web Services - tax for sleeping in data structures class
    ========================================================
    That which is excellent is new forever.
    Michael D. Kersey, Dec 21, 2003
    #7
  8. Jeff Boyer

    Mark Andrews Guest

    You could use our product which uses Microsoft Access Reports on the server.
    The clients only need Acrobat Reader to read the dynamic PDF files which are
    created.

    Use pass-thru queries (in Access) to run stored procs or SQL Server SQL to
    drive the report.
    At runtime you pass in the SQL for the pass-thru query. For example: "Exec
    mystoredproc @CustID=34"

    HTH,
    Mark
    RPT Software
    http://www.rptsoftware.com


    "Jeff Boyer" <> wrote in message
    news:v%OEb.761092$9l5.641061@pd7tw2no...
    > Hello everyone,
    >
    > I have recently developed a web application across my clients intranet. We
    > used ASP with Interdev 6.0 and SQL server as the backend.
    >
    > They have now come to me asking me to develop some reports that can be
    > accessed via a drill down within the app. They don't have Crystal Reports

    so
    > this is not an option. Can anyone suggest an alternative to using crystal
    > reports to publish dynamic reports via the web and ASP?
    >
    > Thanks in advance,
    > Jeff
    >
    >
    Mark Andrews, Dec 22, 2003
    #8
  9. Jeff Boyer

    Chris Barber Guest

    I'd recommend having a look at XML and XSLT (if you are at all familiar).
    You can generate a recordset as XML and then use the XSLT (with a little
    more added info) to do practically anything (also allows for totalling
    columns etc.).

    Chris Barber.

    "Jeff Boyer" <> wrote in message
    news:v%OEb.761092$9l5.641061@pd7tw2no...
    Hello everyone,

    I have recently developed a web application across my clients intranet. We
    used ASP with Interdev 6.0 and SQL server as the backend.

    They have now come to me asking me to develop some reports that can be
    accessed via a drill down within the app. They don't have Crystal Reports so
    this is not an option. Can anyone suggest an alternative to using crystal
    reports to publish dynamic reports via the web and ASP?

    Thanks in advance,
    Jeff
    Chris Barber, Dec 22, 2003
    #9
  10. Jeff Boyer

    Turkbear Guest

    Rather than buying stuff, look into generating HTML tables with ASP code..Should give you all the basic reporting you
    need..
    hth

    On Mon, 22 Dec 2003 10:49:46 -0500, "Mark Andrews" <> wrote:

    >You could use our product which uses Microsoft Access Reports on the server.
    >The clients only need Acrobat Reader to read the dynamic PDF files which are
    >created.
    >
    >Use pass-thru queries (in Access) to run stored procs or SQL Server SQL to
    >drive the report.
    >At runtime you pass in the SQL for the pass-thru query. For example: "Exec
    >mystoredproc @CustID=34"
    >
    >HTH,
    >Mark
    >RPT Software
    >http://www.rptsoftware.com
    >
    >
    >"Jeff Boyer" <> wrote in message
    >news:v%OEb.761092$9l5.641061@pd7tw2no...
    >> Hello everyone,
    >>
    >> I have recently developed a web application across my clients intranet. We
    >> used ASP with Interdev 6.0 and SQL server as the backend.
    >>
    >> They have now come to me asking me to develop some reports that can be
    >> accessed via a drill down within the app. They don't have Crystal Reports

    >so
    >> this is not an option. Can anyone suggest an alternative to using crystal
    >> reports to publish dynamic reports via the web and ASP?
    >>
    >> Thanks in advance,
    >> Jeff
    >>
    >>

    >
    Turkbear, Dec 22, 2003
    #10
  11. Jeff Boyer

    Mark Andrews Guest

    I agree that if your reporting needs are basic enough that you can do it by
    creating HTML you should just code a solution.
    It's more scalable, faster etc...

    However I also think that sometimes a good report designer is needed for
    example our company has
    a intranet site in which they can generate about 150 different web based
    reports from data in our data
    warehouse. All of these reports go to Excel or PDF and print out very
    nicely in PDF format.
    You can buy a high-end package like Business Objects or MicroStrategy or
    Actuate or a low-end package
    like Crystal, Active Reports or our software which uses Microsoft Access as
    the report designer.
    High end costing 10K to 100K, low-end costing a few hundred bucks.

    Another approach is to build OLAP cubes off your SQL data and provide a web
    interface to the cube data.
    Good for analytical reporting in which "user interaction" is needed
    (drilling down to find the answer or
    multi step questions etc...).

    HTH,
    Mark
    RPT Software
    http://www.rptsoftware.com

    PS: Feel free to send me an email directly I have looked at almost every
    reporting package in the market.

    "Turkbear" <> wrote in message
    news:...
    >
    > Rather than buying stuff, look into generating HTML tables with ASP

    code..Should give you all the basic reporting you
    > need..
    > hth
    >
    > On Mon, 22 Dec 2003 10:49:46 -0500, "Mark Andrews"

    <> wrote:
    >
    > >You could use our product which uses Microsoft Access Reports on the

    server.
    > >The clients only need Acrobat Reader to read the dynamic PDF files which

    are
    > >created.
    > >
    > >Use pass-thru queries (in Access) to run stored procs or SQL Server SQL

    to
    > >drive the report.
    > >At runtime you pass in the SQL for the pass-thru query. For example:

    "Exec
    > >mystoredproc @CustID=34"
    > >
    > >HTH,
    > >Mark
    > >RPT Software
    > >http://www.rptsoftware.com
    > >
    > >
    > >"Jeff Boyer" <> wrote in message
    > >news:v%OEb.761092$9l5.641061@pd7tw2no...
    > >> Hello everyone,
    > >>
    > >> I have recently developed a web application across my clients intranet.

    We
    > >> used ASP with Interdev 6.0 and SQL server as the backend.
    > >>
    > >> They have now come to me asking me to develop some reports that can be
    > >> accessed via a drill down within the app. They don't have Crystal

    Reports
    > >so
    > >> this is not an option. Can anyone suggest an alternative to using

    crystal
    > >> reports to publish dynamic reports via the web and ASP?
    > >>
    > >> Thanks in advance,
    > >> Jeff
    > >>
    > >>

    > >

    >
    Mark Andrews, Dec 23, 2003
    #11
  12. Jeff Boyer

    Roland Hall Guest

    "Jeff Boyer" wrote:
    > So your both saying my only option is to run some queries and dynamically
    > build the tables to display the results?


    Hi Jeff...

    Sorry, I was out of town for a couple of days making XMas presents. Santa's
    work is never done! {(O;|>*

    I'm not saying this is your only option. It is an alternative option as you
    asked. If you wrote the application you should have no problem building the
    report. I was just showing an example of calculating an array variable and
    that it didn't require a lot of code to do it.

    You have been given several options from others so now it's decision time.
    I'm not suggesting you pick a product. You have to determine how much your
    time is worth, how long it will take you if you created the reporting engine
    vs the cost of a commercial product, integrating it with your product and
    the learning curve. If time is critical then that should also be
    considered. A last option would be to contract someone to build it for you.
    This option would be if you required something custom that a commercial
    reporting engine could not easily offer.

    Good luck with whatever you decide, and if you should choose to do it
    yourself and need some help, I'm sure someone here can get you past any
    rough spots.

    --
    Roland

    This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose.
    Roland Hall, Dec 23, 2003
    #12
  13. Jeff Boyer

    Jeff Boyer Guest

    Thanks for all the info guys..... I'll look into it all and go from there.
    I'm extreamly interested in a few of the suggestions.

    Jeff
    "Chris Barber" <> wrote in message
    news:...
    > I'd recommend having a look at XML and XSLT (if you are at all familiar).
    > You can generate a recordset as XML and then use the XSLT (with a little
    > more added info) to do practically anything (also allows for totalling
    > columns etc.).
    >
    > Chris Barber.
    >
    > "Jeff Boyer" <> wrote in message
    > news:v%OEb.761092$9l5.641061@pd7tw2no...
    > Hello everyone,
    >
    > I have recently developed a web application across my clients intranet. We
    > used ASP with Interdev 6.0 and SQL server as the backend.
    >
    > They have now come to me asking me to develop some reports that can be
    > accessed via a drill down within the app. They don't have Crystal Reports

    so
    > this is not an option. Can anyone suggest an alternative to using crystal
    > reports to publish dynamic reports via the web and ASP?
    >
    > Thanks in advance,
    > Jeff
    >
    >
    >
    Jeff Boyer, Jan 6, 2004
    #13
  14. Jeff Boyer

    Cathy Jones Guest

    Cathy Jones, Jan 6, 2004
    #14
  15. Jeff Boyer

    Brynn Guest

    I do many of the reports on the intranets I work on. Building reports
    with ASP is incredibly easy. build a couple decent subroutines for the
    table building, maybe go grab my DBConn.asp file from coolpier.com to
    simplify the database stuff.

    Data is some custom for a business, ASP reports will probably be
    easier than using an program anyway.

    This is a subroutine I would use ... I haven't tested yet ...



    <%
    '// The cp_CellWidths and cp_CellTitles need to be seperated by @@@
    '// It's been easier to have a string in my code, and let sub
    make the arrays for the above.
    '// For a blank title box in your string put &nbsp;
    '// For no titles simply put an empty string "" which will
    make the array cp_CellTitlesArray = -1

    '//I occasionally modify this script ... without titles, this that
    whatever
    '// KNOWING my table is going to be 4 columns ... I have done
    the follwing
    '// When I modify it, I just copy the subroutine and paste it
    below named cp_BuildTable2
    '// My cp_BuildTable2 here is an example of how to use CSS
    classes for different columns
    '// My cp_BuildTable3 is a simple example with every other row
    a different CSS class
    '// Where there are changes I put '// **********

    '// In fact, I am going to add cp_CellClasses before I put
    this script on my site

    '// As always, the cp_ coolpier.com prefix is used before all variable
    names as to not conflict with any you may be using.
    '// As always, use this script at your own risk ... I do not guarantee
    the effects of this script.

    Sub cp_BuildTable(cp_DataArray, cp_CellWidths, cp_CellTitles)
    Dim cp_tableHTML, cp_rowHTML
    cp_tableHTML = "<table align=""center"" valign=""top""
    width=""96%"">"
    cp_rowHTML = "<tr>"

    Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
    in loop for easy modifying later
    cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
    cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

    Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
    ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
    With Response
    .Write cp_tableHTML

    '// TITLES
    If Not cp_CellTitlesArray = -1 Then
    .Write cp_rowHTML
    For cp_n = 0 to cp_Columns
    .Write "<td align=""center"" valign=""middle""
    class=""yourTitleCSSclass"" width=""" & cp_CellWidthsArray(cp_i) &
    """>"
    .Write cp_CellTitlesArray(cp_n)
    .Write "</td>"
    Next
    .Write "</tr>"
    End If

    '// DATA
    For cp_n = 0 to cp_Rows
    .Write cp_rowHTML
    For cp_i = 0 to cp_Columns
    .Write "<td align=""left"" valign=""top""
    class=""yourCSSclass"" width=""" & cp_CellWidthsArray(cp_i) & """>"
    .Write cp_DataArray(cp_i, cp_n)
    .Write "</td>"
    Next
    .Write "</tr>"
    Next
    .Write "</table>"

    End With
    End Sub


    Sub cp_BuildTable2(cp_DataArray, cp_CellWidths, cp_CellTitles)
    Dim cp_tableHTML, cp_rowHTML, cp_ClassesArray(3)'// **********
    cp_tableHTML = "<table align=""center"" valign=""top""
    width=""96%"">"
    cp_rowHTML = "<tr>"
    cp_ClassesArray(0) = "myclass1"'// **********
    cp_ClassesArray(1) = "myclass2"'// **********
    cp_ClassesArray(2) = "myclass3"'// **********
    cp_ClassesArray(3) = "myclass4"'// **********



    Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
    in loop for easy modifying later
    cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
    cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

    Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
    ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
    With Response
    .Write cp_tableHTML

    '// TITLES
    If Not cp_CellTitlesArray = -1 Then
    .Write cp_rowHTML
    For cp_i = 0 to cp_Columns
    .Write "<td align=""center"" valign=""middle""
    class=""" & cp_ClassesArray(cp_i) & """ width=""" &
    cp_CellWidthsArray(cp_i) & """>" '// **********
    .Write cp_CellTitlesArray(cp_i)
    .Write "</td>"
    Next
    .Write "</tr>"
    End If

    '// DATA
    For cp_n = 0 to cp_Rows
    .Write cp_rowHTML
    For cp_i = 0 to cp_Columns
    .Write "<td align=""left"" valign=""top""
    class=""" & cp_ClassesArray(cp_i) & """ width=""" &
    cp_CellWidthsArray(cp_i) & """>"
    .Write cp_DataArray(cp_i, cp_n)
    .Write "</td>"
    Next
    .Write "</tr>"
    Next
    .Write "</table>"

    End With
    End Sub




    Sub cp_BuildTable3(cp_DataArray, cp_CellWidths, cp_CellTitles)
    Dim cp_tableHTML, cp_rowHTML, cp_ClassesArray(1), cp_RowClass
    '// **********
    cp_tableHTML = "<table align=""center"" valign=""top""
    width=""96%"">"
    cp_rowHTML = "<tr>"
    cp_ClassesArray(0) = "firstColumnsClass" '//
    **********
    cp_ClassesArray(1) = "secondColumsClass" '//
    **********


    Dim cp_CellWidthsArray, cp_CellTitlesArray '//cell HTML left
    in loop for easy modifying later
    cp_CellWidthsArray = Split(cp_CellWidths, "@@@")
    cp_CellTitlesArray = Split(cp_CellTitles, "@@@")

    Dim cp_n, cp_i, cp_Rows, cp_Columns: cp_Rows =
    ubound(cp_DataArray,2): cp_Columns = ubound(cp_DataArray,1)
    With Response
    .Write cp_tableHTML

    '// TITLES
    If Not cp_CellTitlesArray = -1 Then
    For cp_n = 0 to cp_Columns
    .Write "<td align=""center"" valign=""middle""
    class=""yourTitleCSSclass"" width=""" & cp_CellWidthsArray(cp_i) &
    """>"
    .Write cp_CellTitlesArray(cp_n)
    .Write "</td>"
    Next
    .Write "</tr>"
    End If

    '// DATA
    cp_RowClass = cp_ClassesArray(1) '//**********
    For cp_n = 0 to cp_Rows
    If cp_RowClass = cp_ClassesArray(1) Then: cp_RowClass
    = cp_ClassesArray(0): Else: cp_RowClass = cp_ClassesArray(1): End If
    '//**********
    .Write "<tr class=""" & cp_RowClass & """>"
    '//**********

    .Write cp_rowHTML
    For cp_i = 0 to cp_Columns
    .Write "<td align=""left"" valign=""top""
    class=""yourCSSclass"" width=""" & cp_CellWidthsArray(cp_i) & """>"
    .Write cp_DataArray(cp_i, cp_n)
    .Write "</td>"
    Next
    .Write "</tr>"
    Next
    .Write "</table>"

    End With
    End Sub
    %>


    Anyway, I think it is pretty easy to make nice looking reports with a
    few custom subroutines. Anyway, have fun coding.

    Brynn
    www.coolpier.com

    P.S. I also sometimes modify the scripts to build the html for the
    reports into an array ... then use that array with my ArrayToText.asp
    script and build static reports. For reports that are going to be
    generated like every morning or something ... you can also use vbs
    scripts to execute your .asp files to build these reports on a timer.
    I make write the html files so that they are reading a static page,
    and the database isn't being hit for every report view ... AND the
    time and resources to dynamically build the report isn't there ... AND
    it is nice to have an archive of all the reports ever written.

    You can use my little FilesInFolder.asp script to read the file names
    in the folder with all the reports in it ... or what I like to do is
    enter each file name into a text file as they are being written ... I
    use my ArrayToText_Append.asp for that.

    ANYWAY, just thought I would post :)

    Brynn
    www.coolpier.com





    On Sat, 20 Dec 2003 21:36:00 GMT, "Jeff Boyer" <>
    wrote:

    >So your both saying my only option is to run some queries and dynamically
    >build the tables to display the results?
    >
    >Jeff
    >"Michael D. Kersey" <> wrote in message
    >news:...
    >> Jeff Boyer wrote:
    >> > Ok, I hear what your saying but for large reports that total columns

    >isn't
    >> > that allot of code to write? I was hoping I didn't have to re-invent the
    >> > wheel. I thought maybe there might be some sort of plugin to use with
    >> > Interdev or maybe a third party application I could call from my page.
    >> > Jeff

    >>
    >> You can
    >> 1. fetch the SQL result set into a recordset and then accumulate totals
    >> on the ASP page by looping through the result set, or
    >> 2. issue a separate SQL query for the required summary columns.
    >>
    >> FWIW you can fetch as many result sets (issue as many SQL statements) as
    >> you wish on a single ASP page.
    >>
    >> Good Luck,
    >> Michael D. Kersey
    >>

    >
    >
    Brynn, Jan 7, 2004
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Wayne  Wengert

    Reporting Advice/Help

    Wayne Wengert, Nov 26, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    295
    Wayne Wengert
    Nov 26, 2004
  2. Alex
    Replies:
    2
    Views:
    536
  3. rwboley

    Advice for Python Reporting Tool

    rwboley, Jul 12, 2006, in forum: Python
    Replies:
    2
    Views:
    783
    rwboley
    Jul 13, 2006
  4. C4rtm4N
    Replies:
    1
    Views:
    399
  5. jason
    Replies:
    1
    Views:
    92
    Bob Barrows
    Oct 7, 2003
Loading...

Share This Page