Crystal Reports with Dynamic Data
Crystal Reports with Dynamic Data Field and Prompt Drop Down
This article discusses how to set up a dynamic Crystal Report to use a Variable to pull data from the AdventureWorks2014 database on a SQL server 2014 instance for a Vendor in a Selection Formula. Also, using the same report, steps to set up a Parameter Field with a Select Expert (using Record) to create a prompt with a Drop Down box of all Vendors to select the Report Criteria by Vendor will be explained.
In SQL Server Management Studio, create a Views from the AdventureWorks database for Vendor and Order information. The Views in will be for Product details (vw_PurchaseOrder), and for Vendor and Purchase Order information (vw_PurchaseOrderVendorList).
Create a report for Vendors with a subreport for the POs and Subtotals of each, add some Formula fields for Total of All POs and start showing how to use data to see a Preview of report. Add new item, select Crystal Report and name it, click OK to start the Wizard.
Select the view as the Data Source.
Select the fields to display in the report.
Add the Special Field "Print Date" from the Crystal Report Field Explorer on the left side of the screen and add the Vendor and Address fields to the Page Header.
Create Formula Fields in the Field Explorer for TotalAmt, TotalTax, TotalSub, TotalFreight.
Right click on each field and click on Edit to bring up the Formula Editor. Add the Field name from the Data Source with a SUM function.
Create another report based on the SQL View vw_ProductOrders and Add an Order Total Text box and a Formula field to Sum the Order Total.
Open the Main report created PurchOrderVend.rpt and right click in the Details section and select Insert - Subreport.
Next, select the report name from the Drop down to Insert as a Subreport.
The Subreport will appear in the Details section and the Formula Fields may be added to the main report with some text boxes next to them to describe them.
Select Expert in Report with 1 hard coded variable. go to Menu CR\Report\Select Formula\Record.
Add code to the Record Selection box to use the Views Vendor field and set it to Beaumont Bikes.
Click on Main Report Preview on the bottom of the report to view the results.
An Option to using Hard Coded Variable Data: To use a variable selected from a drop down box, click on Parameter Field to create a New field named vendorID. Select a New Value and pick Vendor and click the field. Enter some Prompt Text, select the Description, and the Parameter of the vendorID field just created.
From the Menu: Select Crystal Reports\Report\Select Expert\Record to edit a Selection Formula for the report to get dynamic data. Clieck on Formula Editor button to set up the fields.
Select the vendorID parameter field from the Report fields listed and drag and drop it in the editing window. Click Save and Close which will return you to the original window and then click OK to close the Select Expert window.
To test the dynamic prompt, click on Main Menu Report Preview and the Vendor Parameter Prompt will appear. From here, you can select the Vendor Name that you would like for the report.
The values for the Selected Vendor will appear in the report preview.
Hint: to get the report to bring the Prompt back and enter a new Vendor, Click on Refresh in the report.
Select Prompt for new parameter values to get the Prompt to come up and select a different Vendor.
