asked on

Ad Hoc Crystal Reporting

Is there a way to create a form to gather information that is contained in a ODBC datasource and then
feed a dynamically generated SQL statement or a collection of records into a crystal report template
that can be viewed on the web. I'm trying to come up with a way to allow for ad-hoc reporting within
a web based management tool where the reports will be not only easy to view but to print as well.

Mike McCracken

We do something like that from VB. We built a form that allows the user to select or specify criteria to filter the data. The SQL Query is built from the from and fed to a Crystal Report.

If this sounds like something you would like to see I can provide it to you.

good luck
mlmcc

raj_nbr

1. Get the field collection
2. Display it on a web form
3. Build the SQL after asking the user what he wants
4. Use this sql as the data source of the crystal report by using sqlquery property, by using asp
5. Trigger the report from form.

jchance

ASKER

mlmcc-- I really need a sample that will work on a web based application. I've seen a VB example but I still was unable to transition the code into a working format.

raj nbr ---It sounds just like what I'm looking for, however, I have not done much work using asp. Do you have a code example on how you would call up a crystal report by passing the sqlquery property a value.

dbirdman

I've recently done this two different ways using ASP (and although we have ASP experts here, I am an ASP Novice). The first way creates a Crystal Report entirely on the fly, and does not require an .rpt file to be set up. My test ASP is listed below. You will probably have to play with it a little, as I used a SQL Server 7.0 Stored Procedure to get my data. However, the principles are pretty straight forward: Create an ADO Recordset with the data on which you want to report, create the Crystal Application and Report Objects, then create and insert the elements you require. I found that the difficult part is gathering the necessary information from the user in a easy and comprehensive manner. There is some decent documentation and samples on the Crystal Website: http://www.crystaldecisions.com (let me know if you have trouble finding them). I added some comments in the below code where you will need to make changes. I will post a second comment with the other option. This example assumes that you have the necessary Crystal Reports 8.0 components installed, and uses the Crystal ActiveX Smart Viewer. This report is for a top 10, 20, etc., report - you would change your data accordingly.

ASP SCRIPT:

<%@ Language=VBScript %>

<%
      dim vInstructionTopXXX
      dim vHeaderInstruction
      dim vCityInstruction
      dim vRollYearInstruction

      vInstructionTopXXX="Input Desired Number"
      vHeaderInstruction="Enter Report Title"
      vCityInstruction="Enter City Name"
      vRollYearInstruction="Enter Roll Year"

      bGo = cbool("0" & Request.QueryString("go"))
      strTopXX = Request.Form("TopXX")
'      strReportHeader = Request.Form("RptHeader")
'      strCity = Request.Form("RptCity")
      strRollYear = Request.Form("RollYear")

%>


<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>

<FORM method=post action="dab2.asp?go=1">
<P>Top XXX Report - <%=vInstructionTopXXX%></P>
<P><INPUT type="Text" Name="TopXX"></P>
<P><%=vRollYearInstruction%></P>
<P><INPUT type="Text" Name="RollYear"></P>
<P><INPUT style="WIDTH: 129px; HEIGHT: 24px" type=submit size=43 value="Show Report"></P>
<P>  
</FORM>

<%

      if bGo then
            set cn = server.CreateObject("ADODB.Connection")
            set rs = server.CreateObject("ADODB.Recordset")
            set cmd = server.CreateObject("ADODB.Command")

'            SQL = "select * from _PTX.PTX_USER"

            cn.ConnectionString="Provider=SQLOLEDB.1;persist security info=true;Data Source=YourServerName;initial catalog=YourDatabase;user id=YourUserID;password=YourPassword"
            cn.Open

            if cn.State <> 1 Then
%>

<P>Connection Could Not Be Established</P>

<%
            end if

            'rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
            set cmd.ActiveConnection = cn
            'Set the command Type to Stored Procedure - 4

'***************************************************
'This is where you would change your code to retrieve
'the data into an ADO recordset any way you choose.
'***************************************************
            cmd.CommandType=4
            cmd.CommandText="StoredProcName"
            cmd.Parameters.Refresh
            with cmd
                  .Parameters("@iTopCount").Value = strTopXX
                  .Parameters("@CityCode").Value = "005"
                  .Parameters("@iTaxYear").Value = CInt(strRollYear)
                  .Parameters("@iOption").Value = 1
            end with
            rs.CursorType = adOpenDynamic
            rs.LockType = adLockOptimistic
            set rs.ActiveConnection = cn
            rs.CursorLocation = 3
            'rs.CursorType = adUseClient
            set rs.Source = cmd
            rs.Open
            'Set rs = cmd.Execute

      '**********************************************************************
      'Create Crystal Reports Objects and Display the Report.
      '**********************************************************************
            reportname = server.MapPath(".\dab_test1.rpt")
      '      reportname = "e:\PTX_TopXXX_Test.rpt"
      '      reportname = "c:\My Documents\Projects\GIS_PTX\PTX_TopXXX_Test.rpt"
      '      reportname = "g:\webprojects\devroot\PTX_TopXXX_Test.rpt"

            'Create the Application Object
            If Not IsObject(session("oApp")) Then
                  Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")
            End If

            'Close existing report object, if any, and open the report.
            If IsObject(session("oRpt")) then
                  Set session("oRpt") = Nothing
            End If

            Set session("oRpt") = session("oApp").NewReport
            'Set session("oRpt") = session("oApp").OpenReport(reportname, 1)

            session("oRpt").MorePrintEngineErrorMessages = False
            session("oRpt").EnableParameterPrompting = False
            session("oRpt").DiscardSavedData

      'Create Report Parameter Objects and pass the Parameter Values to
      'the Crystal Report Objects.
            ''set session("ParamCollection") = session("oRpt").Parameterfields
            ''set ParamNumHeader = session("ParamCollection").Item(1)
            ''set ParamRollYear = session("ParamCollection").Item(2)

            'Parameter 12 in SetCurrentValue tags parameter as a String
            ''Call ParamNumHeader.SetCurrentValue (CStr(strTopXX), 12)
            ''Call ParamRollYear.SetCurrentValue (CStr(strRollYear), 12)

            set Database = session("oRpt").Database
            set Tables = Database.Tables

            set session("oRs") = Server.CreateObject("ADODB.Recordset")
      '      Set session.Contents("oRs") = rs
            Set session("oRs") = rs

            Tables.Add "",,session("oRs"),,"p2smon.dll"

            ''set Table1 = DatabaseTables.Item(1)

            ''Table1.SetPrivateData 3, session("oRs")

      'Create the Report Detail Section and Populate with Values
            Set crSection = session("oRpt").Sections.Item("D")
            Set rhSection = session("oRpt").Sections.Item("RH")
            Set phSection = session("oRpt").Sections.Item("PH")

            Set crFieldObject = crSection.AddFieldObject("{ado.business_name}",720,0)
            Set crFieldObject1 = crSection.AddFieldObject("{ado.property_count}",5500,0)

      'Create the Page Header Object and Column Titles
            Set phTextObject = phSection.AddTextObject("Test Col 1", 600, 0)

      'Format the Text Object
            With phTextObject
                  .Height = 350
                  .Width = 4000
                  .HorAlignment = 1
                  .Font.Size = 10
                  .Font.Underline = TRUE
                  .Font.Name = "Times New Roman"
                  .TextColor = "&hFF0000"
            End With

            Set phTextObject = phSection.AddTextObject("Test Col 2", 5700, 0)

      'Format the Text Object
            With phTextObject
                  .Height = 350
                  .Width = 4000
                  .HorAlignment = 1
                  .Font.Size = 10
                  .Font.Underline = TRUE
                  .Font.Name = "Times New Roman"
                  .TextColor = "&hFF0000"
            End With

      'Create the Report Header Text Object
            Set rhTextObject = rhSection.AddTextObject("My Custom Header", 2500, 0)

      'Format the Text Object
            With rhTextObject
                  .Height = 350
                  .Width = 4000
                  .HorAlignment = 2
                  .Font.Size = 16
                  .Font.Bold = TRUE
                  .Font.Name = "Times New Roman"
                  .TextColor = "&hFF"
            End With

            session("oRpt").Save reportname

            'Retrieve the Records and Create the Page On Demand Engine Object
            On error Resume Next
            session("oRpt").ReadRecords
            If Err.number <> 0 Then
                  Response.Write err.Source & " " & err.Number & " " & err.Description
            Else
                  If IsObject(session("oPageEngine")) Then
                        set session("oPageEngine") = Nothing
                  End If
                  set session("oPageEngine") = session("oRpt").PageEngine
            End If

            rs.Close
            cn.Close

            set rs = nothing
            set cn = nothing
            set cmd = nothing

%>


<%
      end if

%> </P>

</BODY>
</HTML>

Good Luck!

ASKER CERTIFIED SOLUTION

dbirdman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial