Crystal Reports 9 and ASP code examples

I am lookg for some code examples of how to pass ASP variables to a Crystal Reports Formula. I have never done this and would like to know how to get started. I have set up the Sample pages that crystal comes with and set values for the session variables. Now what I need to know is how I can pass parameters to a specific report to generate a different output according to who is logged in and what options they select.

I am using straight ASP not .NET. Is this a factor? (My company is too cheap to buy .NET) :)

Anyone that could help would be of great assistance.

Thanks
UugemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajuareCommented:
Pls. go thru the following example. if you want more ask me ....

************************************************


<%@ LANGUAGE="VBSCRIPT" %>

<%
'===========================================================================
' WORKING WITH THE REPORT DESIGNER COMPONENT AND ASP TO LOGON TO SUBREPORTS
'===========================================================================
'
' CONCEPT                                                            
'                                                                    
'  ALWAYS REQUIRED STEPS (contained in AlwaysRequiredSteps.asp)
'   - create the application object                                
'   - create the report object                                    
'   - open the report  
'
'  LOGGING ON TO SUBREPORTS
'   - create a reference to the main report object
'   - logon to the main report using setlogoninfo
'   - create a reference to the subreport object
'   - logon to the subreport using the setlogoninfo method
'        setlogoninfo "dsn/server name", "database name", "userid", "password"                                            
'
'  MORE ALWAYS REQUIRED STEPS (contained in MoreRequiredSteps.asp)
'   - retreive the records                                        
'   - create the page engine
'
'  DISPLAY THE REPORT
'   - display the report using a smart viewer
'==================================================================
%>

<%
' This line creates a string variable called reportname that we will use to pass
' the Crystal Report filename (.rpt file) to the OpenReport method contained in
' the AlwaysRequiredSteps.asp.

'reportname = "SubReportLogonExample.rpt"
'reportname = "buyer.rpt"
'reportname = "order.rpt"


'************* Input Date ***********************************

'=============> From Date <==================================

fromdate = trim(Request.Form("fromyear"))  & "/" & trim(Request.Form("frommonth")) & "/" & trim(Request.Form("fromday"))

'=============> To Date   <==================================

todate = trim(Request.Form("toyear"))  & "/" & trim(Request.Form("tomonth")) & "/" & trim(Request.Form("today"))

'************************************************************



if trim(request.querystring("status")) ="product" and trim(request.form("region")) = "USD" and trim(request.form("type")) = "Bar" then
      reportname = "product.rpt"
elseif trim(request.querystring("status")) ="product" and trim(request.form("region")) = "RS" and trim(request.form("type")) = "Bar" then
      reportname = "product1.rpt"
elseif trim(request.querystring("status")) ="product" and trim(request.form("region")) = "USD" and trim(request.form("type")) = "Pie" then
      reportname = "productusp.rpt"
elseif trim(request.querystring("status")) ="product" and trim(request.form("region")) = "RS" and trim(request.form("type")) = "Pie" then
      reportname = "productrsp.rpt"
elseif trim(request.querystring("status")) ="sales" and trim(request.form("region")) = "USD" and trim(request.form("type")) = "Bar" then
      reportname = "sales2.rpt"
elseif trim(request.querystring("status")) ="sales" and trim(request.form("region")) = "USD" and trim(request.form("type")) = "Pie" then
      reportname = "sales2.rpt"
elseif trim(request.querystring("status")) ="daily" and trim(request.form("region")) = "USD" and trim(request.form("type")) = "Bar" then
      reportname = "salesperson.rpt"
elseif trim(request.querystring("status")) ="Tot1" then
      reportname = "gpus.rpt"
elseif trim(request.querystring("status")) ="Tot2" then
      reportname = "gprs.rpt"
elseif trim(request.querystring("status")) ="PTot1" then
      reportname = "product.rpt"
      fromdate=trim(request.querystring("fdt"))
      todate=trim(request.querystring("tdt"))
elseif trim(request.querystring("status")) ="PTot2" then
      reportname = "product1.rpt"
      fromdate=trim(request.querystring("fdt"))
      todate=trim(request.querystring("tdt"))
end if


'response.write reportname & "<br>"
'response.write fromdate & "<br>"
'response.write todate & "<br>"

'response.end


%>

<%
'==================================================================
' ALWAYS REQUIRED STEPS
'
' Include the file AlwaysRequiredSteps.asp which contains the code    
' for steps:
'   - create the application object
'   - create the report object
'   - open the report
'==================================================================
%>                                                                    
<!-- #include file="AlwaysRequiredSteps.asp" -->                      

<%
'==================================================================
'        LOGGING ON TO SUB REPORTS USING SETLOGONINFO
'==================================================================


'Set the userid and password for your database.
'UserId = "your database user id"
UserId = "sa"

'Password = "your database password"
Password = "mypassword"

'Create a reference to the tables collection of the main report
Set mainReportTableCollection = Session("oRpt").Database.Tables

For Each Table in mainReportTableCollection
   'Table.SetLogonInfo "Pubs Sample Database", "pubs", CStr(Userid), CStr(Password)
      Table.SetLogonInfo "mspl", "pubs", CStr(Userid), CStr(Password)
Next


if trim(request.querystring("status")) ="product" then
      session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('" & fromdate & "') to date('"  & todate & "') and {orderdetails.Currency} ='" & trim(request.form("region")) & "' and isnull({OrderDetails.Chargeback}) and isnull({OrderDetails.Adjusted}) and isnull({OrderDetails.Declined})"
elseif trim(request.querystring("status")) ="sales" then
      session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('" & fromdate & "') to date('"  & todate & "') and {orderdetails.Currency} ='" & trim(request.form("region")) & "' and isnull({OrderDetails.Chargeback}) and isnull({OrderDetails.Adjusted}) and isnull({OrderDetails.Declined})"
elseif trim(request.querystring("status")) ="daily" then
      session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('" & fromdate & "') to date('"  & todate & "') and {orderdetails.Currency} ='" & trim(request.form("region")) & "' and isnull({OrderDetails.Chargeback}) and isnull({OrderDetails.Adjusted}) and isnull({OrderDetails.Declined})"
elseif trim(request.querystring("status")) ="PTot1" then
      session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('" & fromdate & "') to date('"  & todate & "') and {orderdetails.Currency} ='USD' and isnull({OrderDetails.Chargeback}) and isnull({OrderDetails.Adjusted}) and isnull({OrderDetails.Declined})"
elseif trim(request.querystring("status")) ="PTot2" then
      session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('" & fromdate & "') to date('"  & todate & "') and {orderdetails.Currency} ='RS' and isnull({OrderDetails.Chargeback}) and isnull({OrderDetails.Adjusted}) and isnull({OrderDetails.Declined})"
end if





'session("oRpt").RecordSelectionFormula = "{orderdetails.orderdate} in date('2002/05/01') to date('2002/02/02') and {orderdetails.Currency} ='RS'"
'session("oRpt").RecordSelectionFormula = date("{orderdetails.orderdate} >= '2001,10/10/2001' and {orderdetails.orderdate} <= '10/10/2001'")
'session("oRpt").RecordSelectionFormula = date("{orderdetails.orderdate} in currentdate ")

'Create a reference to the subreport object by opening the subreport
'by using its name as specified in the report designer.
Set subReportOne = session("oRpt").OpenSubReport("Titles")

Set subReportOneTablesCollection = subReportOne.Database.Tables

'Provide the logon credentials for the subreport
For Each Table in subReportOneTablesCollection
    Table.SetLogonInfo "Pubs Sample Database", "pubs", CStr(Userid), CStr(Password)
Next

'Create a reference to the subreport object by opening the subreport
'by using its name as specified in the report designer.
Set subReportTwo = session("oRpt").OpenSubReport("Publishers")

Set subReportTwoTablesCollection = subReportTwo.Database.Tables

'Provide the logon credentials for the subreport
For Each Table in subRerportTwoTablesCollection
    subReportTwo.SetLogonInfo "Pubs Sample Database", "pubs", CStr(Userid), CStr(Password)
Next
%>

<%
'==================================================================
'
'  MORE ALWAYS REQUIRED STEPS
'
'  Include the file MoreRequiredSteps.asp which contains the code
'  for the steps:
'   - retreive the records                                        
'   - create the page engine                                      
'   - create the smart viewer and point it to rptserver.asp
'
'==================================================================
%>
<!-- #include file="MoreRequiredSteps.asp" -->

<%
' INSTANTIATE THE REPORT VIEWER
'
'When using the Crystal Reports in an ASP environment, we use
'the same page-on-demand Report Viewers used with the Crystal Web Component Server.
'There are six Report Viewers:
'
'1.  Report Viewer for ActiveX
'2.  Report Viewer for Java using Browser JVM
'3.  Report Viewer for Standard HTML with Frames
'4.  Report Viewer for Standard HTML
'5.  Report Viewer for Java Using Java Plugin
'6.  Report Viewer for Netscape Plug-in (ActiveX)
'
'The Report Viewer that you use will based on the browser's display capablities.
'For Example, you would not want to instantiate one of the Java viewers if the browser
'did not support Java applets.  For purposes on this demo, we have chosen to
'define a viewer.  You can through code determine the support capabilities of
'the requesting browser.  However that functionality is inherent in the Crystal
'Reports RDC and is beyond the scope of this demonstration application.
'
'We have chosen to leverage the server side include functionality of ASP
'for simplicity sake.  So you can use the SmartViewer*.asp files to instantiate
'the smart viewer that you wish to send to the browser.  Simply replace the line
'below with the Smart Viewer asp file you wish to use.
'
'The choices are SmartViewerActiveX.asp, SmartViewerJava.asp, JavaPluginViewer.asp,
'ActiveXPluginViewer.asp. SmartViewerHTMLFrame.asp, and SmartViewerHTMLPAge.asp.
'Note that to use this include you must have the appropriate .asp file in the
'same virtual directory as the main ASP page.
'
'*NOTE* For SmartViewerHTMLFrame and SmartViewerHTMLPage, you must also have
'the files framepage.asp and toolbar.asp in your virtual directory.


'=============================================================================
'  DISPLAY THE REPORT
'   - display the report using a smart viewer
'
' Include one of the Smart Viewers.
'  - Report Viewer for ActiveX                  =   SmartViewerActiveX.asp
'  - Report Viewer for Java using Browser JVM      =   SmartViewerJAVA.asp
'  - Report Viewer for Standard HTML            =   SmartViewerHTMLPage.asp
'  - Report Viewer for Standard HTML w/ Frames      =   SmartViewerHTMLFrame.asp
'  - Report Viewer for Java Using Java Plugin      =   JavaPluginViewer.asp
'  - Report Viewer for Netscape Plug-in            =   ActiveXPluginViewer.asp
'=============================================================================
%>
<!-- #include file="SmartVieweractivex.asp" -->                    


           


0
UugemanAuthor Commented:
What I need is to be able to pass the report basically a recordset. Once I have the user login I only want them to access certain reporting parameters (ex: project IDs that are related to that specific user) So Only ASP page I can pull back that information. How would I go about passing that information to Crystal? Is it possible? Or do I need to create a different report for each User that will be accessing thier own reports?
0
UugemanAuthor Commented:
I found some example code to pass a querystring to a report.

C:\Complete Work Orders.rpt?user0=&password0=&prompt0=Hipsman?prompt1=12/11/2003?prompt2=12/11/2003

I am attempting this and it is not working. My report opens but doenst take the parameters. Is there something I need to change on my report? Do the fields need to be modified to accept these values?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.