Okay, all. I just spent 4 days tearing my hair out trying to figure out how
to pass CF variables to a Crystal Report(8) (while hiding the db userid and
password). I searched the archives and the Allaire site and noticed that
many other people have been gnashing their teeth over the same topic, so I
thought I would share our solution.
It's not pretty, and I'm not looking forward to supporting it, but it will
do until we can upgrade to 5.0. Is it just me, or does anyone else here feel
like sometimes the only solution is to slap some duct tape on the problem?
;)
Hopefully this will help someone else out. And, if anyone out there has any
other ideas on how this could have been done, I welcome the input. So, here
it is...
Crystal Reports Case study
Goal: To pass user-specified parameters from a ColdFusion web page to
Crystal Reports for display through the user's browser.
Scenario: ColdFusion Server 4.0 running on NT, IIS4 (we'll call it the CF
Server)
Trying to access a Crystal report (8) (we'll call it report.rpt) on a
DIFFERENT server (we'll call it the report server) that is NOT running CF
(IIS4 and the Crystal Reports Web Component Server).
Challenge: We needed a way to access and display these reports from
ColdFusion without passing the database username/password information in the
source code. We also are having users enter their NT userid and password
when they request a report, and we are calling an Oracle stored procedure on
the CF action page to validate the id/pw. If it's valid, a session id is
returned, which we need to pass to the Crystal report.
Details: Our first thought was that this project would be simple because we
could just pass all of our variables as either URL or hidden form variables.
However, I realized that the Crystal report prompts for both a database id
and password. I could not pass it as a hidden form variable, because a
hidden form field is only hidden from the display, not from the source code.
I then thought that we could use Encrypt() to encrypt the userid/pw and
pass it in a URL or form variable. This would not work, though, because the
Crystal report would have no way to decrypt it. The Encrypt() function would
only have been useful to us if we were passing information from a CF page TO
another CF page. Not being too familiar with Crystal Reports (I am not
creating them--someone else is), I wasn't sure if Crystal Reports even has a
similar Decrypt() function.
So Why didn't we use the CFREPORT TAG?
Well, believe me, I would have liked to. But, no matter what I did, I just
could NOT get it to work. Even after combing through the archives and the
Allaire site and implementing all of the suggestions, I still kept getting
errors like "the report server is too busy" (even though I knew I was the
only one on it), "File does not exist or is not in a directory that is
accessible to the ColdFusion service" (this one happened whether I was
calling the report on the report server, or whether I was calling the report
from the SAME directory using an absolute path.), and a host of other
errors.
Final solution: We ended up creating the actual form in Coldfusion, then
using another CF template for the action page. On that page, we called our
stored procedure to verify that a valid employee was logging in. If not, an
error is displayed indicating such. If so, then a continue button is
displayed and all of the fields from the first form are carried over via
hidden form fields (minus the db userid/pw). Both of these CF templates are
on the CF server, of course. This form on the CF action page (that displays
the continue button) submits to an ASP page that is sitting on the report
server. That ASP page then passes all of the form fields from the CF page,
as well as the database userid/pw to the crystal report (on the report
server).
TIPS: Go to www.crystaldecisions.com, and in their download area, search for
ASPXMPS8. These are all of the ASP examples that you should need.
Specifically, we used the "SimpleSetLogonInfo.asp" file as our primary
example for doing this, as well as "SimpleParameterFields.asp". Just rename
the template and replace their database info with your own. Our variable
names were Param1, Param2, Param3, etc., and we were sure to name them as
such when we were passing them from the CF template. The Crystal Report will
expect the parameters to come in order, so make sure that the first value
that you are passing to the report (i.e. Param1) is the first prompt that
would pop up in the report (after the db logon and password info).
THE GOOD NEWS: According to my MM rep, CF 5.0 will provide complete support
for Crystal Reports 8.0. I hope so, because I'm not looking forward to
going through this for the dozen or more reports that we're going to have to
call! J
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
