Secured Databases
Most databases have security implemented in them. They require valid user credentials before accessing the data. This means that you have to pass a User ID and Password to the database prior to printing the report. Crystal Reports handles security differently, depending upon how the report is bound and the database used.
In most circumstances, a Windows application displays a login dialog box prompting the user to enter their user credentials. After entering the credentials, the user can login. However, this isn’t always the case. If you are using an MS Access database with a database password or if you are printing from an Excel spreadsheet using OLE DB, the login dialog box isn’t compatible. The user won’t be able to print the report. Rectifying this problem requires passing the user credentials during runtime. See Chapter 17 for a thorough discussion of connecting to every type of data source during runtime.
Connecting with Stored Procedures
As mentioned earlier in the chapter, stored procedures can be used as a data source just like a table. Crystal Reports can open a stored procedure, retrieve the data and print it. The one thing that can make a stored procedure unique is if it has input parameters.
For a stored procedure to execute, it must have a value for every input parameter. Crystal Reports automatically creates report parameters when it sees that the stored procedure has one or more input parameters. There is one report parameter for every stored procedure parameter and they will have identical names and data types. When the report runs, the user is prompted to enter a value for each parameter. Internally, Crystal Reports passes these report parameters to the stored procedure.
The following code is a sample stored procedure from the Northwind database.
CREATE PROCEDURE CustOrderHist
@CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID
In this stored procedure, there is one input parameter called @CustomerId that is a 5 character string. When this report is selected as the data source for a report, Crystal Reports automatically creates a parameter called @CustomerID as a String data type. The report prompts the user to enter a Customer ID when it is run. The report passes this value to the stored procedure; the stored procedure only returns records with a matching ID.
If you want your application to directly pass the parameters to the stored procedure, you have to set the parameter objects during runtime. The user won’t be prompted with the parameter dialog boxes and the report will run seamlessly. See Chapter 17 for the steps to set report parameter values during runtime.
If you are using two stored procedures and linking them together, make sure that the parameters have different names. When Crystal Reports creates parameters for each stored procedure, it doesn’t have the capability to create a new alias for the parameter names. Consequently, it will only create one report parameter with that name and it won’t know which stored procedure to assign the parameter to. Make sure that parameters use unique names.
