This series covers the basics of creating a report in the Web Report Designer. Previousy, you learned how to create a Detail Report band.
In this lesson, you will learn how to add filters to your report. In the below image, you will notice customers with no invoices:
With filtering, we can choose not to include those customers in the report.
NOTE: Normally, it is best practice to filter the data before it hits the report. In the above example, Method should filter the customers before even generating the report. This ensures the report is flexible and useable in different situations, and the filtering can be done outside of the report.
However, for the purpose of showing how filters work, we will create a filter in this report.
Filter the Report
Filtering can apply at many levels. You can filter in individual bands, or you can filter the whole report. In this case, we will filter at the report level.
Deselect whatever is selected, so that the Properties on the right will show XtraReport (Report). You will see Filter String with an ellipsis (...).
Click the ellipsis (...) beside Filter String, and the Filter Editor pops up.
Hover your mouse beside the And, and a green plus appears.
Click the green plus and you will get the choice between Add Group and Add Condition.
Remember, a condition is like a question which results in yes or no. In this case, we will ask this question for each record in the report and if the answer is yes, the record will be included in the report. Select Add Condition.
A condition has three parts that need to be defined:
Blue dropdown: This is the field in the table to look at.
Green dropdown: This is the operator to be used to compare the blue and grey dropdown.
Grey dropdown: This is the value used to compare to the blue dropdown.
Change each dropdown to add the following condition:
Field = Balance
Operation = Does not equal to
Value = type in the number "
0".
Click OK to close the Filter Editor and Save the report.
Now, this filter will go through each record and compare its Balance to "0". As long as this is not zero, then the record will show up in the report.
Preview the report. The customers are now filtered to include only those with a postive or negative balance.
Of course, we could style it up a bit. So the next session will fix the fields being displayed.
Styling the Fields
In this next session, you will be styling the report using the same skills you learned from the earlier lessons.
First, style the date. Select TxnDate field.
Set TxnDate's Text Format String to any date format you like. I chose one that didn't display the time.
Style the RefNumber field.
Set RefNumber's Text Format String to General and prefixed with a
#sign.
Style the amount. Select the Amount field.
Set Amount Text Format String to a currency type.
Still for Amount, under Appearance, find Text Alignment and set it to Top Right.
Style the Name field so that it too will stick out. In this example, I added a background color, and then added some line objects to either side of it.
Add a Group Header band (make sure no band is selected when you do this).
Using labels, add some headers for each of the columns. In my example, I also added a panel to group them, and then added a light background color to the panel.
Save the report and hit Preview.
Show Total Balance
Once last thing we'll do in this lesson: we should show the total balance. We do not need to do any math in this part, because the total balance is already available from the Customer table.
You will put the value in a group footer band, but the band is embedded within the detail report. So it's important to pay attention to what is selected when you add bands or change property values.
Select the DetailReport1 band. Again, this is important!
Once selected, click the Insert Group Footer icon. Note how this new band is contained within the DetailReport1 band.
From the Field List, drag the Balance field from the Customer table into the group footer you created above. Position it so it's underneath the amount column. (Make sure you drag it from the correct table! The Customer.Invoice table may still be expanded.)
Select the Customer.Balance field and change its Text Format string to a currency format.
Change the Customer.Balance field's Text Alignment to Top Right.
Further style the Customer.Balance field by adding a top border.
Finally, drag in a Label control and call it "Total:". Place it to the left of the Customer.Balance field. Style it how you want.
Save the report and hit Preview.
Great! However, you may have noticed one thing about the first entry: the math doesn't add up. This is because we included all invoices, whether they are paid or not.
Filter out Paid Invoices
Again, you should do this outside of the report, to make the report.
At the beginning of this article, the filter was applied to the Customer's balance, and applied to the whole report. This one will be applied to the invoices of the customer, and so the filter is added elsewhere: the DetailReport1 band.
Select the DetailReport1 band.
Add a filter for this band. First click the ellipsis (...) beside Filter String.
Add a conditional where the
IsPaidfieldEqualstoFalse.
Click OK.
Now your report will not have paid invoices included.
In the next article, you will learn about calculated fields.