|
Return to Newsletter Contents...
Using Parameters In Record & Group Selection
by: Kristen St. Jean, Sr. Consultant, CRCP
Parameters are used mainly to filter the records returned
in our reports. In this article we
will discuss another way to use parameters to manipulate what is displayed on
your reports.
Parameters have been a very useful part of Crystal Reports
for many years now. A parameter is
simply a question to the user running the report.
Once they answer this question, the report designer controls how that
parameter affects the report.
The majority of the reports that I have seen which use
parameters, use them to filter the records that are returned on the report.
As many of us know, this is very useful.
Reports that contain all the data in the database are usually not very
helpful to many of us. We want to
look at data that meets certain criteria.
This is where record selection comes in.
We can create a selection formula that compares a field in the database
to a parameter that we’ve created, thereby allowing us to run the report for a
different value each time. In this
way, parameters help us tremendously if we have 10 report users who all want the
same report, but they need to look at different customers.
Instead of writing the report 10 times with a different record selection
in each report, we can create one report with a parameter for ‘Customer’ and
just run the report 10 different times, once for each user’s desired customers.
This is the most common use of parameters in Crystal Reports, and it is
definitely a useful one.
However, there are other ways that we can take advantage of
parameters. We’ve already discussed
using parameters in our record selection formulas, but what about group
selection? For example, our company
has 5,000 customers and the CFO wants a report listing the top 10 customers by
the amount of money that they’ve spent with us.
The CEO wants a report with the top 25 customers.
We can’t filter our records, because we need to sum the order amount for
each customer to see who has the highest totals.
Therefore, in our report we need to return all of our customers’ orders
and then group by customer and sum the order amounts by customer.
After we accomplished that, we’ll need to select the top 10 and the top
25 for the reports requested. The
most efficient way to do this is to use the Group Sort Expert and choose a Top N
sort. We can then manually type in a
number in the Group Sort Expert to define how many top groups to return.
However, I have 2 reports, and I don’t want to have to edit the design of
my report each time one of these needs to be run.
Instead, we can create a parameter that asks for a ‘Number of Top
Customers’. This parameter can then
be placed in the Formula Editor in the Group Sort Expert next to the box where
we would type in the value for N.
Now, when we run the report, we can type in any number for our parameter and
that will be the number of customers returned on the report.
This way, if we get a request for the top 100 customers, all we need to
do is refresh our report and enter 100 as the number in our ‘Number of Top
Customers’ parameter.
Now we know that not only are parameters useful in our
record selection, but they can be useful with group selection also.
Don’t forget that a parameter is just a question to the user, and you as
the report designer can use that parameter in many different ways to not only
filter records, but to sort groups, and to manipulate the formatting of your
reports which we’ll discuss in the next installment of Crystal Corner.
Go to Top |
Return to Newsletter Contents
|
