Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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).

View Product Order Details

View Purchase Order Vendor List

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.

Start new Crystal Report

Select the view as the Data Source.

Select SQL Server View

Select the fields to display in the report.

Select Crystal Report Fields

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.

Vendor Report

Create Formula Fields in the Field Explorer for TotalAmt, TotalTax, TotalSub, TotalFreight.

FIeld Explorer Formula Fields

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.

Crystal Reports Formula Editor

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.

Subreport for Purchase Order Details

Open the Main report created PurchOrderVend.rpt and right click in the Details section and select Insert - Subreport.

Insert Subreport Right Click

Next, select the report name from the Drop down to Insert as a Subreport.

Select Crystal Reports 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.

Sompleted Crystal Report Main and Subreport

Select Expert in Report with 1 hard coded variable. go to Menu CR\Report\Select Formula\Record.

Crystal Report Selection Formula for Record

Add code to the Record Selection box to use the Views Vendor field and set it to Beaumont Bikes.

Record Selection Formula Editor

Click on Main Report Preview on the bottom of the report to view the results.

Main Report Preview

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.

Parameter Field In Crystal Reports

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.

Selection Formula for Dynamic Data

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.

Record Selection Formula Editor

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.

Parameter Prompt to Enter Value

The values for the Selected Vendor will appear in the report preview.

Report Preview with Selected Parameter Value

Hint: to get the report to bring the Prompt back and enter a new Vendor, Click on Refresh in the report.

Crystal Report Refresh

Select Prompt for new parameter values to get the Prompt to come up and select a different Vendor.

Prompt for Parameter Values