?
Solved

Crystal Reports 9 and ASP code examples

Posted on 2003-12-10
7
Medium Priority
?
1,344 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
[X]
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
  • 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 500 total points
ID: 12991560
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

800 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