Adding info to dynamic parameters

Thursday 22 February 2018 @ 5:26 pm

I created an unusual dynamic parameter for a customer this week. They wanted to select from a list of customers, but their customer list included lots of dead weight. To shorten the list they decided to include only customers who have sales of over $10K in the past 2 years.

To filter the results of a dynamic parameter you need to base it on a SQL command, rather than pulling it directly from a table. And because this filter involved a total our command needed a nested subquery. The inner query took all orders in the past two years, and did a sum of the order amount, grouped by CustomerID.  Then the outer query selected records from the inner query when that sum was greater than $10K.

The field for our parameter was the Customer ID but Crystal parameters allow you to bring in a second ‘description’ field along with the filter field. The Customer Name was our description field.

This worked fine, but we noticed that if a customer had multiple accounts, the customer name appeared multiple times with slight variations. I thought it would help if the user could see the sales along with each name. Since the SQL we had written already included the total sales for each customer, I figured we could make that part of the description field. In the outer query I replaced the original description field (customer name) with an expression like this:

A.CustName + '($' + Ltrim(Str(A.TotalAmt/1000,5,0)) + 'K)' As CustName

This generated a parameter list that looked something like this, with total sales shown as part of the parameter description:

AMCB3 – AMC Bonding Co., Inc.($15K)
BKDA1 – BKD Associates($10K)
CRSU2 – Credit Suisse AG ($13K)
DEVG2 – Deveraux Foundation($25K)
FOSE1 – Four Seasons($19K)

I have done something similar when the description field needed to combined  multiple fields, like FirstName and LastName. It can be done with any combination of fields.

Leave a Reply

Recrystallize Pro

Crystal Reports Server