AlwaysRequiredSteps.asp......

[qberta01]

Hello,

I use the AlwaysRequiredSteps.asp to open the necessary objects and pass parameters to my Crystal report. It all works fine. However, I would like to reuse the same AlwaysRequiredSteps.asp for my other Crystal reports that have different input parameters. For some reason, the page does not like it and gives me an error. Hence, I had to create a separate AlwaysRequiredSteps.asp page using only the parameters called for in one report.

Is there a way to alter AlwaysRequiredSteps.asp so it can sucessfully process many reports with varying parameters?

I am using classic ASP and Crystal 11 developer.

Thx,

Q

 

[ReportingAnalyst]

Hi,

I use AlwaysRequiredSteps.asp (ASP sample code for Crystal Reports 8.5) and pass parameters from the ASP site to the crystal reports. I am able to use different reports using the same AllRequiredSteps.asp without modifying the code. FYI, the different parameters passed for the different reports are handled in the ASP page.

Thanks.

 

[ReportingAnalyst]

Talking of passing parameters, do you pass all of your parameters or do you pass only some while the other parameters are prompted by the Crystal Parameter Dialog?

 

[qberta01]

No all parameters are passed - there is no prompting. Maybe there is something else causing my problem. I will investigate further. BTW - is there a way I can automatically save the report in pdf format to a network folder without any user prompting?

Thx

Q

 

[ReportingAnalyst]

Why if you were to click on the save folder, it gives you an option to save in the format you wish to save. Select PDF format and then save it.

Or do you wish to save it directly without that save box popping up? Do you wish to over-ride this step?

Thanks.

 

[qberta01]

I agree with you 100% about the user being able to save the report directly where they want. However, the front-end application currently saves the reports (written in ASP and XML)every time it is run. It's put out on a network drive and there is a form in the front-end application that allows people to select a report and click on it for review. I think it's a gigantic waste of resources to keep all those reports, but I only do what I am told.

We are currently in the process of converting the old reports to Crystal. If it's something easy to do then I will try it and not "rock the boat". If not then the users will just have to save it themselves. So yes...save directly without a pop-up box.

Also, my hack job ASP code was keeping me from using the AlwaysRequiredSteps.asp for multiple reports. Now that I have got that working I want to be able to loop through the parameters passed into AlwaysRequiredSteps.asp. Currently, I check each parameter for a value, but as I add more reports and more parameters it could become a gigantic mess.

Any thoughts?

Thx Again!

Q

 

[ReportingAnalyst]

Talking of old reports, were they also created in Crystal or were created in ASP and XML?

Saving the reports from the front end in that network folder and making the users view the reports and saving it again in their own folder on their local machine, it is waste of the resources. Instead the reports are saved in the web server. Call the reports through a ASP. Ask the users to pass the parameters. They view the reports according to the parameters they pass. Then they can save it in their local machine.

No. I have not worked with over-riding of the save as box. Users save it for themselves in the format they want.

Now, talking of parameters, have you worked with reports in ASP where you passed only some of the parameters and other parameters were prompted by the Crystal Parameter Dialog?

Regarding the ALLRequiredSteps.asp, I have not touched it at all. I got my sample code from the ASP sample files for CR 8.5

When I ask the users to select all the parameters, then the action of the form is directed to another ASP page which looks like this. It is this page which handles the multiple reports and their respective parameters.

<%@ LANGUAGE="VBSCRIPT" %>
<%
'--------------------------------------------------------------------
'REQUEST FOR THE FORM VARIABLES THAT EMPLOYEES SELECTED IN THE PREVIOUS PAGE
'--------------------------------------------------------------------
'Format the Period to a DateTime
d = Request.Form("Period")

'Get the period
strPeriod = Request.form("Period")
Response.Write "Form Period: " & strPeriod & "<br>"

'Get the EmpID from the form
strEmpID = Request.Form("empid")
Response.Write "Employee ID: " & strEmpID & "<br>"

'Request for the Report to be viewed
strReport = Request.Form("crystalradio")
Response.Write "Crystal Report: " & strReport & "<br>"

'Get the year from the period and attach it to the report
strReport1 = strReport & Year(strPeriod) & ".rpt"
Response.Write strReport1 & "<br>"


%>

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

<!-- #include file="AlwaysRequiredSteps.asp" -->

<%
	' WORKING WITH SETLOGONINFO

	dsnName = "DSNName"
	DBName = "DBName"
	userid = "UserID"
	password = "Password"

	set crtable = session("oRpt").Database.Tables.Item(1)
	crtable.SetLogonInfo cstr(dsnName), cstr(DBName), cstr(userid), cstr(password)

	'If it is required to logon to multiple tables in the main report
	'then we can loop through the tables collection and set the logoningo
	'for each table.

	Set DatabaseTablesCollection = Session("oRpt").Database.Tables
	For Each Table in DatabaseTablesCollection
	   Table.SetLogonInfo cstr(dsnName), cstr(DBName), cstr(userid), cstr(password)
	Next
%>
<%
'-------------------------------------------------------------------
'Enable passing of parameters from the ASP page to the CR.
'-------------------------------------------------------------------
'This line creates an object to represent the collection of parameter
'fields that are contained in the report.

Set ParamDefCollection = Session("oRpt").Parameterfields
Set session("Param") = ParamDefCollection

'This line creates an object to reference the 1st parameter in the report.
'Period is datetime. Hence cast as double of type number 10. (See the chart below)

Set MyParam1 = Session("Param").Item(1)
splitdate = split(Request.form("period"), "/")
MynewDate1 = splitdate(2) & "/" & splitdate(0) & "/" & splitdate(1) & " 12:00:00 AM"
MyParam1.SetCurrentValue CDate(MynewDate1)

'This line creates an object to reference the 2nd parameter in the report.
'EmpID which is numeric should be equal to ?EmpID. Hence cast as double of type number 7. (See the chart below)

Set MyParam2 = session("Param").Item(2)
MyNewParamValue2 = strEmpID
Call MyParam2.SetCurrentValue(CDbl(MyNewParamValue2), 7)

%>

<!-- #include file="MoreRequiredSteps.asp" -->
<!-- #include file="SmartVieweractivex.asp" -->

 

[qberta01]

The old reports are just ASP that creates an XML file thats churned out into a report format.

What if you have 2 reports A.rpt and B.rpt.

Report A has the parameters BeginDate and EndDate (all parameters are passed through ASP). Report B has the parameters BeginDate, EndDate and Issue.

Instead of har coding: MyNewParamValue2 = strBeginDate, I wanted to do a loop:

Dim iCount, CrystalParam, Param, Item

For Each Item in Request.QueryString
FOR icount = 1 TO Request.QueryString(Item).count
Parameter = Request.QueryString.key(icount)
CrystalParam = Request.QueryString(Item)(icount)
Session("oRpt").ParameterFields.GetItemByName (CrystalParam).AddCurrentValue(Parameter)
Next
Next

..............

It does not work. Do you think it's even possible?

Thx,

Q

 

[ReportingAnalyst]

I have never done this kind of looping through the querystring or request.form. But before you send it to the report engine, did you try to print it to the HTML browser to see what the querystring variables you have captured?

Also when you do such a looping, how would you know which parameter that you grab are for, say A.rpt and not B.rpt?

See, that is why I was asking if you ever tried sending only some parameters and others are prompted by the Crystal Parameter Dialog of the Crystal Viewer.

I do understand that hardcoding of parameters in the ASP pages as report list grows can be very daunting - changing of the ASP code with new parameters and new report names. In order to automate them, the best way would be to send a common parameter shared by all reports. If none are shared, then also not an issue. Only send the db setlogon information. Open the report. The report should bring forward the crystal parameter dialog to ask the users to fill in the other parameters.

I do not know if I gave you any ideas or input to think in another way...

 

[qberta01]

Yes I was able to see what variables were being captured. As far as knowing which paramerts are for which report. I also pass through the report name and open it before I send the parameters.

Set session("oRpt") = session("oApp").OpenReport(path & Rpt, 1).

I don't know...maybe that is not the right thing to do.

I think a lot of my problems arrive from the fact that I am trying to incorporate the Crystal reports with the old reporting system.

Instead of having one asp page per report, I think it would make more sense to have one asp page that lists all the reports and when one is selected it gets opened and all the parameters can be prompted for directly through the Crystal report.

I think I will leave my reports as they are for now while they go through end user testing. In the mean time, I think I will work on creating that one asp page that lists all the reports.

What do you think?

BTW - I really appreciate your help!!!!

Q

 

[ReportingAnalyst]

Yes, I also 1 asp page that deals with all the reports.

I have a list of reports which are brought from the DB displayed on the html page. Then the users select a particular report. Based on the report selected, the users get to select the parameters. When they click on the submit button, they get to see the report.

The action of this ASP page can go to ViewReport.asp page which toggles between the different reports.

For instance:

'Request for the Report to be viewed (Radio Group)
strReport = Request.Form("crystalradio")

'Based on the kind of report requested by the user, go to that if-then block

If strReport = "Report1" OR strReport = "Report2" Then

	'Get the period
	strPeriod = Request.form("period_Comm_Pay")

	'Get the EmpID from the form
	strEmpID = Request.form("empid")

	'Specify the format the report is to be labeled.
	strReport1 = strReport & Year(strPeriod) & ".rpt"

        'This line creates a string variable called   reportname that we will use to pass
	'the Crystal Report filename (.rpt file) to the OpenReport method.
	reportname = strReport1
	%>
	<!-- #include file="AlwaysRequiredSteps.asp" -->
	<%
	' WORKING WITH SETLOGONINFO
        'Set the logon Information
        'Pass the parameters to the ParameterCollection
End If
%>
<%
If strReport = "Report3" Then
.....
End If
%>

Something like that. It works fine. But as reports grow, so does my work with the ASP page where I have to hardcode the parameter values for the report.

I wish I could get the Parameter Dialog Box working :(

If you do not mind. Can you try working with a parameterized report. Pass only 1 parameter from the ASP page. Bring that report through the browser. See if you are prompted for other parameters through the Crystal Parameter Dialog.

Thanks.

 

[qberta01]

It's the hard coding of parameters that I would like the avoid - it's bad coding practice. So you want me to use a report with say 3 parameters and only actually pass one parameter through the asp page? Okay - I will try that tomorrow and see what happends.

 

[ReportingAnalyst]

Thanks. I would love to hear about your results.

Thanks.

 

[ReportingAnalyst]

Did you get stike gold?

Thought of sharing this with you...I do not see you mention if you use Classic ASP or ASP.Net

I use classic ASP so this would not work.

But you may want look into this article:

http://aspalliance.com/456

THanks.

 

[qberta01]

Me strike gold? Nope....sorry....I am using classic ASP (for now).

I did, however, figure out how to use dynamic parameters to pass to Crystal through ASP. That way I don't have to hard code the parameters. I have only tested it with two reports so far and hope it doesn't crash later on!

Let me know if you are interested in the code and I will paste it here. I also pasted it in a thread under Micorsoft Active Server Pages (ASP) forum.

Will review the link you suggested.

Thx!

Q

 

[ReportingAnalyst]

Can you share the code with me, if you do not mind? I would like to test it with a simple report where there are 3 parameters. I pass 2 parameters from the report, but the 3rd one I would like it to come up. Anyway let me see how I can apply your sample with my situation.

Thanks.

 

[qberta01]

Okay - let me know if it works for you with that 3rd parameter:

dim sStr
sStr = request.QueryString
dim aParams : aParams = split(sStr, "&")
dim aKeyValue, i, sIDX
dim sKeyword, sParam
dim dList : set dList = Server.CreateObject("Scripting.Dictionary")
Dim Keys, Items, param, crystalparam

'/// Collect the Values wanted into the dictionary object
for i = lbound(aParams) to ubound(aParams)
aKeyValue = split(aParams(i),"=")
skeyWord = request.QueryString.Key(i+1)
sIDX = skeyWord
sParam = aKeyValue(ubound(aKeyValue))
dList.add sIDX, aKeyValue(ubound(aKeyValue))
next

dList.Remove("Report")

'Get dictionary Keys
Keys = Dlist.Keys

'Get dictionary Items
Items = DList.Items

'Loop through Keys array
For iCount = 0 To UBound(Keys)
param=Keys(iCount)
crystalparam=Items(iCount)
Session("oRpt").ParameterFields.GetItemByName(param).AddCurrentValue(crystalparam)
Next

'/// Clean up
set dList = nothing


Also, I think I should mention that I copied a lot of this code from a post by damber in the ASP forum - so I don't want to take any credit away from dambler. I simply added skeyword. I also removed report from the dictionary. The part where I loop through the dictionary and assign the variables I got from code off some website (don't remember where) and modified it for my purposes.

Let me know how it works!

Q

 

[ReportingAnalyst]

Hi Q,

Can you tell me where you inserted your setlogon information, alwaysrequiredsteps.asp, morerequiredsteps.asp in the code you provided?

My browser hangs.

Thanks.

 

[ReportingAnalyst]

A few questions here - what is sKeyword in the code?

'dim sStr
'sStr = request.QueryString
dim aParams : aParams = split(strQuerystr, "&")
dim aKeyValue, i, sIDX
dim sKeyword, sParam
dim dList : set dList = Server.CreateObject("Scripting.Dictionary")
Dim Keys, Items, param, crystalparam

for i = lbound(aParams) to ubound(aParams) 
    aKeyValue = split(aParams(i),"=")
    [b]skeyWord[/b] = request.QueryString.Key(i+1) 
    sIDX = skeyWord
    sParam = aKeyValue(ubound(aKeyValue)) 
    dList.add sIDX, aKeyValue(ubound(aKeyValue)) 
next

It looks like you taking the entire querystring and then using the key(i+1) to get the value of the specified key. But why key(i+1) and not Key(i)?

Even though I built my querystring from my Request.Form array object, I get this string:

empid=40281&period=11/30/2006&crystalradio=SalesRanking

This line splits the above by removing the & delimiter:

dim aParams : aParams = split(strQuerystr, "&")

So your array would have values like empid=40281
period=11/30/2006
crystalradio=salesranking

Then you delimit the '=' to get the empid and 40281.

aKeyValue = split(aParams(i),"=")

From the next line, can you explain what you are doing?

for i = lbound(aParams) to ubound(aParams) 
    aKeyValue = split(aParams(i),"=")
    [b]skeyWord = request.QueryString.Key(i+1) 
    sIDX = skeyWord
    sParam = aKeyValue(ubound(aKeyValue)) 
    dList.add sIDX, aKeyValue(ubound(aKeyValue))[/b] 
next

 

[qberta01]

This is login info after the parameters portion:

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

For Each mnTable in mainReportTableCollection
With mnTable.ConnectionProperties
.Item("user ID") = "SQLAdmin"
.Item("Password") = "xxxxx"
.Item("DSN") = "Reports"
.Item("Database") ="Reports"
End With
Next

---------------

Sorry I posted something I was testing....I was experimenting with querystring and dictionaries. Forget the sKeyword....This is the correct code (although the prior did work):


dim sStr
sStr = request.QueryString
dim aParams : aParams = split(sStr, "&")
dim aKeyValue, i, sIDX
dim iIntMax : iIntMax = 0
dim sparam
Dim Keys, Items
dim dList : set dList = Server.CreateObject("Scripting.Dictionary")

'/// Collect the Values wanted into the dictionary object

for i= lbound(aParams) to ubound(aParams)
aKeyValue = split(aParams(i),"=")
sIDX = aKeyValue(lbound(aKeyValue))
sParam = aKeyValue(ubound(aKeyValue))
dList.add sIDX, sParam
next
dList.Remove("Report")

'Get dictionary Keys
Keys = Dlist.Keys
'Get dictionary Items
Items = DList.Items

'Loop through Keys array
For iCount = 0 To UBound(Keys)
param=Keys(iCount)
crystalparam=Items(iCount)
Session("oRpt").ParameterFields.GetItemByName (param).AddCurrentValue(crystalparam)
Next

'/// Clean up
set dList = nothing

-----

Let me know if it works for you!

Q