Login
Important alert: (current site time 7/15/2013 11:42:46 PM EDT)
 

winzip icon

ASP Report Wizard 0.3

 Email
Submitted on: 7/18/2003 12:26:38 PM
By: Sine45  
Level: Advanced
User Rating: By 45 Users
Compatibility: ASP (Active Server Pages), HTML, VbScript (browser/client side)
Views: 38679
author picture
(About the author)
 
     It gets event better...From user requests I have made some additions to the ASP Report Wizard 2.1. This version includes the ability to save reports and queries. It also includes the absolute capability of exporting reports to different format including Excel, XML, Text and CSV formats. If you need more functionality, please let me know. If you like this code, please vote for me. Thanx

 
winzip iconDownload code

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com

 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.


Other 15 submission(s) by this author

 
Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:
Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 
Other User Comments

7/18/2003 4:36:24 PM: 

AWESOME - Keep up the nice work!!!!

- Ras
(If this comment was disrespectful, please report it.)
 
7/19/2003 6:34:45 AM: 

This code just gets better by the day GOOD JOB
(If this comment was disrespectful, please report it.)
 
7/19/2003 7:22:29 AM: Mark Buckley

Awesome Stuff! I love it!
Request: Ability to add more then one criteria field to your query
(If this comment was disrespectful, please report it.)
 
7/19/2003 9:41:40 AM: 

Your report wizard looks like it is very cool, but whenn I point to the default.asp page I get an error message: Can't display this page. I am using IIS 5 and a DNSless connection.
Can you tell me what I may be doing wrong?

Thanks,

Bud
(If this comment was disrespectful, please report it.)
 
7/19/2003 10:29:53 PM: 

Looking better - how about some subtotals and multiple filters.
(If this comment was disrespectful, please report it.)
 
7/20/2003 12:24:12 PM: 

In response to Bud's question...

Change the:

conn.open "PROVIDER=Microsoft......"

line to:

objConn.open "PROVIDER=Microsoft......"

BTW...this utility is fantastic. Thanks!
(If this comment was disrespectful, please report it.)
 
7/21/2003 2:16:25 PM: 

your code is Excellent.
I sure that many people agree with me.
I have a sugestion:
1. Ability to add more then one criteria field to your query.


(If this comment was disrespectful, please report it.)
 
7/23/2003 7:44:54 AM: 

Well done, a very useful tool.

Some enhancement suggestions are:

1: Include a button with function to select or unselect all fields.

2: Cater for additional criteria conditions

3: Build reports on multliple tables.

Did come across one bug, when you build a report with criteria that the saved report listing is corrupted.
eg. instead of the report name Acer1
it appears as

"href='default.asp?t=tblPcEquip&f= [Location], [Make], [Model], [SerialNo]&c=[Make] LIKE 'Acer%'&sf=&st=&sd=&sd2= &rpt=Acer1&sort=&lastsort=&cp=&ps=10 '>Acer1 x"



(If this comment was disrespectful, please report it.)
 
7/24/2003 8:14:34 AM: 

Congratulations for this code. But I will really like to see multiple filters
Thanx
(If this comment was disrespectful, please report it.)
 
7/24/2003 8:58:07 AM: Tanwani Anyangwe

I am working on all of your requirements and will have the next version ready for soon. And Please continue to vote for me.
Thanks for all your votes.

(If this comment was disrespectful, please report it.)
 
7/25/2003 7:43:51 AM: 

Is it possible to only display specific table fields instead of all? I have specified the tables but don't see an easy way to also be able to show only a few field names instead of all that are in the table.

Thanks!

Love this application!
(If this comment was disrespectful, please report it.)
 
7/25/2003 9:11:52 AM: Tanwani Anyangwe


In the query_form.asp, 

Replace the following code:

For i=0 To objRS.fields.Count-1 
   fname = objRS.fields(i).Name 
   Response.Write ....fname
Next

With this:

For i=0 To objRS.fields.Count-1 
   fname = objRS.fields(i).Name 
   If CanDisplay(strTable, fname) Then
       Response.Write ....fname
   End If
Next

and then add the following function:

Function CanDisplay(strTable, strField)
     Dim ValidFields
     Select Case strTable
         Case "Table1": ValidFields = "_Field1_Field2_Field3"
     End Select
     CanDisplay = Instr(ValidFields, strField)
End Function

(If this comment was disrespectful, please report it.)
 
7/25/2003 12:42:04 PM: Tanwani Anyangwe

Function Correction:

Function CanDisplay(strTable, strField)
Dim ValidFields
Select Case strTable
Case "Table1": ValidFields = "__Field1__Field2__Field3"
End Select
CanDisplay = Instr(ValidFields, "__" & strField)
End Function



(If this comment was disrespectful, please report it.)
 
7/30/2003 11:29:43 AM: 

This wizard is great now our database info can be online and real-time. Especially great is when the reports can be downloaded as Excel files for the users to figure out how they want the reports to look like.

I had the same problem as the 7/23/2003 7:44:54 submission. When I added a report with a field criteria, the report.txt file is not written correctly. I don't know how to, but is there a fix for this? Many thanks.

Seems like one way of achieving multi-table capability is to have the wizard work on database queries and views.

Many Thanks,

Jack
(If this comment was disrespectful, please report it.)
 
7/31/2003 2:26:47 PM: 

I amde the suggested update but now the report won't generate and the database fields are not available in the drop down menu? This thing is great by the way!!!

Kevin
(If this comment was disrespectful, please report it.)
 
8/15/2003 12:55:16 PM: 

Here's a workaround for the garbled text leave the queryform.asp with the original code in the reports.asp replace this line: response.write("
  • .... href='default.asp?" & qs(1) & " '>" & qs(0) & "... ") with this one: response.write"
  • <.... href="&chr(34)&"default.asp?" & qs(1) & chr(34)&" >" & qs(0) & ".... " ps had to replace "
    (If this comment was disrespectful, please report it.)
     
    • 8/27/2003 4:41:55 PM: Tanwani Anyangwe

    Updated to handle spcial characters
    like the single quote and percentage
    signs

    (If this comment was disrespectful, please report it.)
     
    9/1/2003 8:28:08 PM: 

    Hi,
    Just tried the updated version, the previous problems with criteria based reports are fixed, but there seems to be a new problem. If you try to export a criteria based report it generates
    a syntax error now.

    Microsoft JET Database Engine error '80040e14'

    Syntax error in query expression '[Make] LIKE _qtI_perc_qt'.

    /export.asp, line 20

    Kevin B


    (If this comment was disrespectful, please report it.)
     
    10/9/2003 2:12:59 PM: 

    I have the same problem when exporting criteria based reports the error I get is:Invalid column name '_qt1_qt'.

    (If this comment was disrespectful, please report it.)
     
    10/10/2003 1:43:49 AM: Tanwani Anyangwe

    Go to where the export link is added to the page and do a Server.URLEncode like below:

    dim strExportUrl
    strExportUrl = "export.asp?" & Server.URLEncode(strQueryTable & "&" & strQuerySort)
    (If this comment was disrespectful, please report it.)
     
    11/2/2003 8:41:38 AM: 

    Excellent !
    (If this comment was disrespectful, please report it.)
     
    11/19/2003 2:15:52 PM: 

    Great application. But when I tried to copy parts of it into an application I'm working I got the following error:

    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'Task_ID > 100 '.

    Can you please help me.
    (If this comment was disrespectful, please report it.)
     
    11/19/2003 2:18:37 PM: 

    HI Great application. Can you please help I have this problem: I have created a new GUI to select the fields and criteria in the report but when I try to run the query on the database I get the following error: Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'Task_ID > 100 '.

    What could be the problem?

    Thanks
    (If this comment was disrespectful, please report it.)
     
    12/15/2003 1:53:14 PM: 

    this is great stuff! please keep at it.
    (If this comment was disrespectful, please report it.)
     
    12/22/2003 8:07:37 PM: 

    thanks for sharing this, it help me out alot. BUT for your next upgrade can you save the report to a table in the database ?
    Thanks Again !
    (If this comment was disrespectful, please report it.)
     
    12/24/2003 11:47:38 AM: 

    This looks like a great application. How do I get started in creating my own reports? Is there some documentation for this? For some reason the default.asp page is not comming up for me either. Any help would be greatly appreciated.
    (If this comment was disrespectful, please report it.)
     
    2/20/2004 3:08:13 AM: 

    Nice codes!
    Keep up the good work...
    (If this comment was disrespectful, please report it.)
     
    4/3/2004 1:17:09 PM: frankshin

    hi, i have tried using the server.URLEncode but i still can't export the criteria based reports. The error i get is:
    Microsoft JET Database Engine (0x80040E14)
    The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
    /asprpt1/export.asp, line 21

    Can you help me? Hope to get your reply soon. Thanks
    (If this comment was disrespectful, please report it.)
     
    4/6/2004 9:14:57 AM: Tanwani Anyangwe

    There is a naming conflict between one or more of your tables and a database reserved word. Try to qualify your SQL statements field & table names (e.g. []-ACESS & SQL Server, `` - for MySQL etc.)
    For more information, go to http://aspwebsolution.com

    (If this comment was disrespectful, please report it.)
     
    4/26/2004 1:35:59 PM: 

    I am having trouble getting the filter to work with Dates... Any luck with that?
    (If this comment was disrespectful, please report it.)
     
    4/26/2004 2:23:32 PM: Tanwani Anyangwe

    It probably depends of the database/date format you are using. Download the latest version of the report wizard from http://aspwebsolution.com


    (If this comment was disrespectful, please report it.)
     
    5/3/2004 4:09:30 PM: 

    Nice code. One question though. If I wanted to setup and extra field that would allow the user to do a custom select, how would I go about it?
    e.g. The below query in a text box and a checkbox.
    SELECT GControlNum, sku, COUNT(*) AS COUNT
    From tblOutput (nolock)
    Where GControlNum = 1
    GROUP BY GControlNum, sku
    ORDER BY sku, count
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 9:58:51 AM: 

    Hi,

    This report wizard is great! I have one small problem. When I try to export data to excel after a criteria, I would receive the following error:

    Microsoft JET Database Engine
    error '80040e14'

    Syntax error in
    query expression '[name] LIKE
    _qthello_perc_qt'.

    /export.asp, line 20

    I read on in this forum, and found the solution dim
    strExportUrl
    strExportUrl =

    (If this comment was disrespectful, please report it.)
     
    6/1/2004 9:59:55 AM: 

    I read on in this forum, and found the solution dim
    strExportUrl
    strExportUrl =
    "export.asp?" &
    Server.URLEncode(strQueryTable &
    "&" & strQuerySort)

    When I put that into my code, I am now getting the following error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.

    /WebFormsDev/UPwardFB/Reports1/export.asp, line 20

    Any advice is greatly appreciated.

    Thanks.
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 10:10:29 AM: Studio54

    Make sure your SQL statment is correct.
    i.e. make sure you have spaces before and after the 'FROM' word in your SQL statement.
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 11:20:13 AM: 

    Hi, I verified my sql statement in default.asp and the syntax appears to be ok...I see 1 space before and after the keyword From.

    After I build the report, my sql script looks like this:

    SELECT * FROM TBLRESPONSES WHERE segment LIKE '%h%'.

    But I'm still getting Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 1:22:35 PM: Studio54

    put the following line before you execute your SQL statement in export.asp and tell me the SQL string displayed follows:

    Response.Write(strSQL)
    Response.End

    Set objRS = objConn.Execute(strSQL)
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 3:52:42 PM: 

    hmmm... it says the following: SELECT FROM [] ...that's definately incorrect...
    (If this comment was disrespectful, please report it.)
     
    6/1/2004 4:16:41 PM: Studio54

    The make sure you are sending your request parameters in you query string so the following values should not be empty:

    strTable = Request("t")
    strFields = Request("f")


    (If this comment was disrespectful, please report it.)
     
    6/2/2004 10:46:49 AM: 

    Sorry to bother you again... I do have request paramaters passed to my query string as such:

    Dim strTable,strFields,strCriteria,strSort,strSQL,strReportName
    strTable = Request("t")
    strReportName = Request("rpt")
    strFields = Request("f")
    strCriteria = Request("c")
    strSort = Request("sort")

    strSQL = "SELECT "& strFields & " FROM " & strTable & ""

    But when I do a response.write at the location you told me, all I get is Select From
    (If this comment was disrespectful, please report it.)
     
    6/3/2004 12:45:08 PM: Todd Hughes

    I am having the same problem as the person above I get the incorrect syntax near FROM keyword.
    (If this comment was disrespectful, please report it.)
     
    6/3/2004 1:02:58 PM: Tanwani Anyangwe

    1. If your table name or field names has spaces, remember to use URLDecode
    2. Remember to quantity your table name or field names with the appropriate characters e.g. '[' & ']' for ACCESS and SQLServer
    For more information go to http://aspwebsolution.com

    (If this comment was disrespectful, please report it.)
     
    6/3/2004 1:34:42 PM: Todd Hughes

    This is what I have at the begining of my export.asp file

    Dim strTable,strFields,strCriteria,strSort,strSQL,strReportName
    strTable = Request("t")
    strReportName = Request("rpt")
    strFields = Request("f")
    strCriteria = Request("c")
    strSort = Request("sort")

    strSQL = "SELECT "& strFields & " FROM [" & strTable & "]"
    If strCriteria <> "" Then
    strSQL = strSQL & " WHERE " & strCriteria
    End If
    If strSort <> "" Then
    strSQL = strSQL & " ORDER BY " & strSort
    End If

    Dim objRS, i,j
    Set objRS = objConn.Execute(strSQL)
    (If this comment was disrespectful, please report it.)
     
    6/7/2004 11:06:07 AM: 

    How do I add scroll bars to the query_form.asp page?
    You can't see enough of it on lower resolution 600x800
    Thanks,
    Scott
    (If this comment was disrespectful, please report it.)
     
    6/7/2004 4:24:55 PM: Tanwani Anyangwe

    in default.asp change the following entry (line 38):

    change this:
    var settings = 'height='+h+',width='+w+',top='+TopPosition+',left='+LeftPosition+',scrollbars=0,resizab le=1'

    to     this:
    var settings = 'height='+h+',width='+w+',top='+TopPosition+',left='+LeftPosition+',scrollbars=1,resizab le=1'
    (If this comment was disrespectful, please report it.)
     
    7/8/2004 2:17:15 PM: 

    Doesn't work with date fields.
    (If this comment was disrespectful, please report it.)
     
    12/14/2004 12:29:30 PM: 

    This is a very nice piece of work. There are two things that appear not to function correctly.
    1) When trying to filter for a numeric value with "equal to" you get an error.
    2) After filtering for text value unable to export to Excel though it works fine when filtering <> numbers.
    (If this comment was disrespectful, please report it.)
     
    1/26/2005 12:36:05 PM: 

    there some problems trying to query with "group by", you should do this before opening de recordset:
    objRS.CursorLocation=3
    objRS.Open ......etc
    objRS.CacheSize = intPageSize
    objRS.PageSize = intPageSize


    (If this comment was disrespectful, please report it.)
     
    4/19/2005 5:22:34 AM: 

    Good job! I faced one problem i.e when I convert my table names to arabic or other language it gives an error,although I have changed the charset to desire language but it gives error,i.e DB cant execute line 59...can you give a solution for this.
    (If this comment was disrespectful, please report it.)
     
    4/22/2005 6:59:14 AM: 

    This is really nice
    (If this comment was disrespectful, please report it.)
     
    7/6/2005 9:29:03 AM: onkelz

    one word -amazing-
    thanx alot dude...
    (If this comment was disrespectful, please report it.)
     
    8/8/2005 4:13:08 AM: Scorcel

    Wow!!! I was able to adapt it right away to my existing databases.... I only changed the path of my database. Thanks for sharing!!!! wonderful!!!
    (If this comment was disrespectful, please report it.)
     
    9/22/2005 10:03:00 AM: Yalin Meric

    I used your code to make users download output as Excel files. It works %100 perfect. Thanks a lot.
    (If this comment was disrespectful, please report it.)
     
    2/18/2006 9:55:27 AM: Cesar

    WhichMAN, Congratularions, its what I was looking for. Im wondering if U've been working on a way to select multiple tables before generating the reports, so we could join field.

    Could U give me some hints.
    Thanks
    CESAR
    (If this comment was disrespectful, please report it.)
     
    2/21/2006 3:46:38 PM: WhichMan

    Check out these links:

    http://www.aspwebsolution.com/products/report_wizard/
    http://www.aspweb solution.com/products/report_wizard/v1/

    (If this comment was disrespectful, please report it.)
     
    3/8/2006 1:31:43 AM: Jorge

    Hi wichman...congratulations..is a great cod..keep working hard...tks for share your code...I have a problem when i try to save the report this error appear

    Microsoft VBScript runtime error '800a0046'

    Permission denied

    /prueba/default.asp, line 416

    what i can do..please help me..I need yor code really for my proyect in my school..tks

    (If this comment was disrespectful, please report it.)
     
    3/8/2006 8:35:43 AM: WhichMan

    Your folder/file is read-only.
    Make sure your folder has rights or is recognized as application under IIS.

    Or just try creating the file from scratch
    (If this comment was disrespectful, please report it.)
     
    11/15/2006 11:35:37 AM: simflex

    Hi, I hope I can get a response from you about this issue. Someone posted this problem:
    Did come across one bug, when you build a report with criteria that the saved report listing is corrupted.
    eg. instead of the report name Acer1
    it appears as

    "href='default.asp?t=tblPcEquip&f= [Location], [Make], [Model], [SerialNo]&c=[Make] LIKE 'Acer%'&sf=&st=&sd=&sd2= &rpt=Acer1&sort=&lastsort=&cp=&ps=10 '>Acer1 x"

    I am having the same problem. Can you, please explain clearly how to fix this?

    This happens when you save a report like John, for instance.

    Also, when you save that report and click on Excel, you get an error like:

    Invalid column name _qtJohn_qt, something like that. Thanks, great, great code
    (If this comment was disrespectful, please report it.)
     
    11/16/2006 2:31:51 AM: Zachary

    I am getting this error message.

    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Undefined function ' ' in expression.
    /test1/Default.asp, line 292
    (If this comment was disrespectful, please report it.)
     
    11/17/2006 8:56:31 AM: Sine45

    Make sure you have regular field names that have only alpha numeric characters.

    Do you get that error with every table?
    (If this comment was disrespectful, please report it.)
     
    11/17/2006 9:28:26 AM: Sine45

    The error "Undefined function '' in expression" seems to be VBScript error. The TRIM function is not working well.

    Go to line 288 and insert the following 2 lines of code before the line (if stype<>"" and sfield<>"" Then) as follows:

    -- insert ---
    sfield = trim(sfield)
    if sfield=chr(160) then sfield=""

    -- before ---
    If stype<>"" and sfield<>"" Then



    (If this comment was disrespectful, please report it.)
     
    11/17/2006 1:23:55 PM: simflex

    sine45,
    Are you comments intended for me?

    I am not sure since you didn't indicate which post you are responding to.

    Just in case it is for me, all my field names are regular. I actually created a view from the tables so I can do something like:

    select fname as [First Name], lastname as 'Last Name'. Is there something wrong with doing this?

    But even if I don't use any aliases, I still get same error.
    (If this comment was disrespectful, please report it.)
     
    3/12/2007 12:30:37 PM: Roberto Salazar

    I think we should be paying for this sr.
    (If this comment was disrespectful, please report it.)
     
    12/11/2009 10:23:02 PM: Hyourinmaru

    love it!! your such a genius.. thank you very much.. it is very usefel. :D
    (If this comment was disrespectful, please report it.)
     
    12/12/2009 12:08:14 AM: Hyourinmaru

    ASP server finds errors during running.
    Error Code : 0x80040E37
    Error Source : objRS.Open Replace(Replace(strSQL,"_perc","%"),"_qt","'"), objConn, 3,3
    Error Description : The Microsoft Jet database engine cannot find the input table or query 'coursetable?t=coursetable'. Make sure it exists and that its name is spelled correctly.
    File Name : c:\documents and settings\annily\desktop\asp codes\asp report the best\default.asp
    Line No. : 220

    can somebdy help me i receive this error everytime i click the link to the next page records.
    (If this comment was disrespectful, please report it.)
     
    Add Your Feedback
    Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
     

    To post feedback, first please login.

    Search:

    Newest postings for ASP/ VbScript.

    Add this ticker to my site

    Daily code email

    Click here to join the 'Code of the Day' mailing list!

    This site on CD

    Over 7,000 submissions on a super fast CD!

    Code/Articles

    Top Code

    Community

    Jobs

    Other

    Goto…

    Copyright© 1997-2013 by Exhedra Solutions, Inc. All Rights Reserved.
    By using this site you agree to its Terms and Conditions. Planet Source Code™ and the phrase "Dream It. Code It"™ are trademarks of Exhedra Solutions, Inc.