Solved

Crystal Reports 9 and ASP code examples

Posted on 2003-12-10
7
1,270 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:Uugeman
  • 2
7 Comments
 
LVL 3

Expert Comment

by:rajuare
ID: 9912774
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
 

Author Comment

by:Uugeman
ID: 9913822
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
 

Author Comment

by:Uugeman
ID: 9928673
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
 
LVL 13

Accepted Solution

by:
EwaldL earned 125 total points
ID: 12991560
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now