Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Running reports Automatically

Posted on 2001-07-17
Medium Priority
Last Modified: 2012-05-05
I would like to automatically refresh the data, then export it to excel if I can. The filename does not matter, as Crystal Reports 7 will save the exported data as the same name as the .rpt file. The excel file can be in .csv or .xls format, thats not important, but it must be able to be read by excel.

Question by:dave_g
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

shahzad73 earned 400 total points
ID: 6297662
about refreshing the data in report automatically we have a workaround for this problem. insert a paramter in report and hide it from appearing in the report. then pass a random value to this parameter. this will refresh the report.

for exporting report have a look at this automation ASP code whcih i got from the samples with the software



' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' Exporting a Crystal Report to a different file format
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' This application is designed to demonstrate how to export a Crystal Report
' to a different file format, then point the browser to the new file.

'Create the Crystal Reports Objects
'You will notice that the Crystal Reports objects are scoped as session variables.
'This is because the page on demand processing is performed by a prewritten
'ASP page called "rptserver.asp".  In order to allow rptserver.asp easy access
'to the Crystal Report objects, we scope them as session variables.  That way
'any ASP page running in this session, including rptserver.asp, can use them.

reportname = "ExportReport.rpt"

'This line creates a string variable called reportname that we will use to pass
'the Crystal Report filename (.rpt file) to the OpenReport method.
'To re-use this code for your application you would change the name of the report
'so as to reference your report file.

' CREATE THE APPLICATION OBJECT                                                                    
If Not IsObject (session("oApp")) Then                              
Set session("oApp") = Server.CreateObject("Crystal.CRPE.Application")
End If                                                                

'This "if/end if" structure is used to create the Crystal Reports Application
'object only once per session.  Creating the application object - session("oApp")
'loads the Crystal Reports automation server (cpeaut32.dll) into memory.
'We create it as a session variable in order to use it for the duration of the
'ASP session.  This is to elimainate the overhead of loading and unloading the
'cpeaut32.dll in and out of memory.  Once the application object is created in
'memory for this session, you can run many reports without having to recreate it.
' CREATE THE REPORT OBJECT                                    
'The Report object is created by calling the Application object's OpenReport method.

Path = Request.ServerVariables("PATH_TRANSLATED")                    
While (Right(Path, 1) <> "\" And Len(Path) <> 0)                      
iLen = Len(Path) - 1                                                  
Path = Left(Path, iLen)                                              
'This "While/Wend" loop is used to determine the physical path (eg: C:\) to the
'Crystal Report file by translating the URL virtual path (eg: http://Domain/Dir)                                                                        

'OPEN THE REPORT (but destroy any previous one first)                                                    

If IsObject(session("oRpt")) then
      Set session("oRpt") = nothing
End if

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

'This line uses the "PATH" and "reportname" variables to reference the Crystal
'Report file, and open it up for processing.
'Notice that we do not create the report object only once.  This is because
'within an ASP session, you may want to process more than one report.  The
'rptserver.asp component will only process a report object named session("oRpt").
'Therefor, if you wish to process more than one report in an ASP session, you
'must open that report by creating a new session("oRpt") object.

set session("oRptOptions") = Session("oRpt").Options
session("oRptOptions").MorePrintEngineErrorMessages = 0

'These lines disable the Error reporting mechanism included the built into the
'Crystal Report Print Engine (CRPE32.DLL).  This is done for two reasons:
'1.  The print engine is executed on the Web Server, so any error messages
'    will be displayed there.  If an error is reported on the web server, the
'    print engine will stop processing and you application will "hang".
'2.  This ASP page and rptserver.asp have some error handling logic desinged
'    to trap any non-fatal errors (such as failed database connectivity) and
'    display them to the client browser.
'**IMPORTANT**  Even though we disable the extended error messaging of the engine
'fatal errors can cause an error dialog to be displayed on the Web Server machine.
'For this reason we reccomend that you set the "Allow Service to Interact with Desktop"
'option on the "World Wide Web Publishing" service (IIS service).  That way if your ASP
'application freezes you will be able to view the error dialog (if one is displayed).

' Exporting the Crystal Report

session("filename") = Request.Form("filename")
session("filetype") = Request.Form("filetype")
'These lines collect the values passed from the calling HTML page for the export
'type and filename

Set session("ExportOptions") = Session("oRpt").ExportOptions
'First we create an export options collection which allows us access
'to the exporting properties of the automation server.

session("ExportFileName") = Path + "exports\" + cstr(session("filename"))
'The filename we pass to Crystal Reports must include a path.

Select Case cstr(session("filetype"))
      Case "Crystal Report" ExportType = 1
      Case "Microsoft Word" ExportType = 14
      Case "Microsoft Excel" ExportType = 21
      Case "Microsoft Excel (tabular)" ExportType = 22
      Case "HTML" ExportType = 24
      Case "Paginated Text" ExportType = 10
      Case "Rich Text Format" ExportType = 4
      Case "Text" ExportType = 8
      Case "Tab Seperated Values" ExportType = 6
      Case "Comma Seperated Values" ExportType = 5
End Select
'This Select/Case structure uses the value passed for the export format
'type and sets the ExportType variable to the appropriate "Format Type"
'integer value.  For the complete list of Format, and Destinatation types
'you can search for "ExportOptions" in the Developer's Help.

If cint(ExportType) <> 24 then
    session("ExportOptions").DiskFileName = session("ExportFileName")
    session("ExportOptions").HTMLFileName = session("ExportFileName")
End If

If cint(ExportType) = 10 then
    session("ExportOptions").NumberofLinesPerPage = 50
End if
'If the export format is paginated text we must set the number of
'records that appear per page.

'We must test to see if the report is to be exported to HTML or another
'file type.  If the report is exported to HTML, we must use the HTML File
'name instead of Disk file name.

session("ExportOptions").FormatType = cint(ExportType)
'This line sets the file type that the report will be exported to.  We
'use the value that we collected from the calling HTML page.

session("ExportOptions").DestinationType = 1
'This line sets the destination of the exported file.  1 means that
'we are writing the file to disk.

Session("oRpt").Export False

session("ExportVirtualDirectory") = "/scrsamples/aspsamples/exports/"
'The ExportVirtualDirectory variable is used to reference the a web directory
'so that we can point the browser to the newly exported file.

' Point the browser to the newly exported file
' (but only if the file is previewable by the browser)

Select Case session("ExportOptions").FormatType
      Case 14
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 21
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 22
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 24
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 10
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 4
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case 8
          Response.Redirect session("ExportVirtualDirectory") & cstr(session("filename"))
      Case Else
End Select
LVL 12

Expert Comment

ID: 6301066

What environment are you wanting to run this report in?  Native CRW or VB/Delphi launch?  Win 98/NT/2000?  More details generally result in a more complete answer.


Author Comment

ID: 6301311
The environment I am running this on is Windows NT. I am looking at puting the report onto a web server once we organise one.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 12

Expert Comment

ID: 6301420
You can cause report data to refresh automatically by saving your report format file (xxx.rpt) without saved data (make sure that Save Data With Report on File menu is not checked before saving). Some will say you must also force a verify data on every print (on the database menu), but that has not been my experience.

If you put the "report onto a web server once we organise one" then you don't have to worry about the export format if you know your audience will be using an ActiveX report viewer (ie - an intranet where everyone uses IE).  If you must support all comers (ie - Internet traffic) where you have no control over the browser in use, you will probably find that you have to provide either xxx.XLS files directly, or even worse, accomodate users who do not have Excel.

Author Comment

ID: 6306972
Very good! Everything I asked for, and more

Expert Comment

ID: 7323739
I tried your Code Shahzad but its giving Error
Server object error 'ASP 0177 : 800401f3'

Server.CreateObject Failed

/ams7/test/cr.asp, line 7

Invalid class string

Line 7 contains Server.creatobject line. Please guide me what to do

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question