We help IT Professionals succeed at work.

Ad Hoc Crystal Reporting

jchance
jchance asked
on
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.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Commented:
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.

Author

Commented:
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.

Commented:
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")

%>

<!-- #include virtual = "/includes/adovbs.inc" -->
<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>&nbsp;&nbsp;
</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

%>
<!-- #include file="SmartViewerActiveX.asp" -->

<%
      end if

%>    </P>


</BODY>
</HTML>




Good Luck!
Commented:
OK, Here's option 2, which I would recommend if it fits your needs, as it allows you to set up a much more visually pleasing report in any manner you want.  This method required you to create a Crystal Reports .rpt file with the Crystal Designer, just like a normal report.  However, you do NOT connect to a database.  Instead, you create a text 'format' file, which acts as a field place-holder for the real data, which will be an ADO Recordset.  You then create parameter fields within the .rpt file such as title, description, etc., and what you end up with is a report template that is not bound to a database, and does not know what data or headings it will use until you pass it in.  I have used this quite a bit and it works very well.  I put a sample format file at the end, and you will have to set up your own .rpt file to test.  I went in to this project without prior knowledge of ASP or Crystal, so it is possible.  Let me know if you have problems and I will try to help.  

Good Luck!

<%@ Language=VBScript %>

<%

      dim vInstructionTopXXX
      vInstructionTopXXX="Input Desired Number"
      
      bGo = cbool("0" & Request.QueryString("go"))
      strTopXX = Request.Form("TopXX")
      
%>

<!-- #include virtual = "/includes/adovbs.inc" -->
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>

<FORM method=post action="dab.asp?go=1">
<P>Top XXX Report - <%=vInstructionTopXXX%></P>
<P><INPUT type="Text" Name="TopXX"></P>
<P>Your Parameter Is <%=strTopXX%></P>
<P><INPUT style="WIDTH: 129px; HEIGHT: 24px" type=submit size=43 value="Show Report"></P>
<P>&nbsp;&nbsp;
</FORM>

<table border=1>

      

<%

      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=YourServer;initial catalog=YourDatabase;user id=YourID;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
'********************************************************
'* Same as before, change to get the appropriate data
'********************************************************
            cmd.CommandType=4
            cmd.CommandText="YourStoredProcName"
            cmd.Parameters.Refresh
            with cmd
                  .Parameters("@iTopCount").Value = strTopXX
                  .Parameters("@CityCode").Value = "005"
                  .Parameters("@iTaxYear").Value = 2000
                  .Parameters("@iOption").Value = 1
            end with

            Set rs = cmd.Execute
'*******************************************************

      'Create Crystal Reports Objects and Display the Report.
            reportname = server.MapPath(".\PTX_TopXXX_Test.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"      
%>
<P>ReportName =  <%=reportname%></P>
<P>Field Value =  <%=rs.Fields(2).Value%></P>

<%

            '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").OpenReport(reportname, 1)
            
            session("oRpt").MorePrintEngineErrorMessages = False
            session("oRpt").EnableParameterPrompting = False
            session("oRpt").DiscardSavedData
            
            set Database = session("oRpt").Database
            set Tables = Database.Tables
            set Table1 = Tables.Item(1)

      '      Set session.Contents("oRs") = rs
            Set session("oRs") = rs
            Table1.SetPrivateData 3, session("oRs")            

            '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 "An Error has occured on the server in attempting to access the data source"
            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

%>
<!-- #include file="SmartViewerActiveX.asp" -->

<%

      end if

%>    </P>

</table></P>

</BODY>
</HTML>


SAMPLE FORMAT FILE.  It is a Text File, though
MUST have a .ttx extension.  A Tab separates the name,
type and number of characters (only for String):

         Product ID      Number            
         Product Name      String      50      

Explore More ContentExplore courses, solutions, and other research materials related to this topic.