Now that our sample report is looking pretty good let's explore some tricks that you can use for your reports. A common report obstacle is printing information that is sorted more than one time. In this lesson we are going to change the report from printing in Zip Code sequence to printing a report sorted first on the State, then the City, then the Zip Code database fields.
In lesson 7 we sorted the report on the Zip Code field of the report by creating a Sorting Band. We will still use a Sorting Band to perform the sorting - we'll just be using more of them to perform the sorting that we want for the report!
It is important to figure out the correct sequence of events before you start adding Sorting Bands to your report. Take a good look at your database information and plan on paper how you want the report to be sorted. This will save you time in the long run, and prevent frustration later on. For the example above we want the report sorted on three different database fields. The most important sort is on the STATE field; within the STATE information we want it to be sorted by the CITY. Within the CITY we want the Zip Codes sorted sequentially.
The first order of business for this example is deleting the existing Sorting Band that we have created in our sample report. To delete a Sorting Band you place the mouse cursor directly over the words Sorting Band, then click the right mouse button. From the pop up menu select 'Delete Band' - the sorting Band is now gone, and the report is unsorted for the moment.
If you are modifying a report that already has a Sorting Band created, and also has associated Header and Footer bands for this Sorting Band DO NOT delete the band!
If you want to change the existing sort for a Sorting Band you only need to right click once on that Sorting Band, select PROPERTIES from the pop up menu, then modify the field to sort by!
We will be adding Sorting Bands in the same sequence as their order of importance - the primary sort on the STATE field is first, then CITY, then ZIP. From the menu select BAND, then NEW, then SORTING BAND. The mouse cursor changes to the hand cursor; place it inside the DETAIL band and left click the mouse. Inside the Sort Expression box you click the Dictionary button, then select the STATE field from the Dictionary, then click SAVE.
Repeat the above steps two more times for the CITY, then the ZIP database fields, adding a Sorting Band for each field. The final result will look like this:
New Sorting Bands
You can continue adding more Sorting Bands if you need to; for example, if you wanted the names to be sorted alphabetically, add a fourth Sorting Band with the database field Res:Name selected. Once you are done adding Sorting Bands save the report, then print a sample report to see if the output is what you expected:
Sample report, sorted by State, City and Zip Code
Looking at the information above we see three names of people in Los Angeles; within those 3 names it is sorted by Zip Code, and the other names are all sorted first by State then by City. Perfect!
It may take some trial and error to get the information sorted exactly as you want it to print. Remember that you can cause no damage to the database file(s) using Report Builder, so experiment as much as you want.
An important point to remember about multiple sorts - they will slow down the report generation, as more work has to be done to get the data as you want it to appear. If you have fairly small database file(s) you won't experience much (if any) of a delay; on large database file(s) you will need some patience for the report to be created.
To recap; in this lesson you learned:
|•||How to add additional sorts for your reports to "nest" information through multiple levels|
|•||How to delete a Sorting Band|
|•||The performance impact of multiple Sorting Bands in a report|