لطفا منتظر باشید ...
Integrating Formulas and Logic into your Reports
Few reports are ever created just to list information on a page - most will include at least some basic calculations, summaries, and logic. You wouldn't want an invoice, for example, that didn't have a sales tax calculation and total at the bottom, or a sales summary without any totals. How this logic is incorporated into your report depends on the requirements set out by the eventual user. The following sections give an overview of the different ways that you can incorporate formulas and logic into your report.
The examples in this chapter deal specifically with creating and working with formulas. If you decide that you need to use a method other than writing formulas, turn back to Chapter 6 for examples of how to work with application data, SQL Commands, and SQL Expressions.
Database Structures
Since all reports are based on a data source, and the most common type of data source is a database, it follows that you might place the logic for your report in the database structures that you're using. In addition to creating reports from database tables, Crystal Reports.NET can also grab data from views and stored procedures. Using these devices, you can push most of the processing back to the database server. The advantage of doing this is that you can make further use of the investment you've made in your database server - all of that processing power will translate to reports that run faster and more efficiently (if your server is any good!).In instances where you need to reuse logic and particular representations of the data between different reports, views, and stored procedures can provide a definite advantage over the raw data structures:
Using a view, you can perform joins and consolidation at the level of the database, so that when you design your report, there are no messy joins to configure, and you don't have to figure out how the pieces fit back together. This is especially handy if you have a complex set of data structures, or if you foresee end users creating their own reports some day.
If you have calculations or business logic that gets used repeatedly, a stored procedure can provide those calculations for a number of reports, without having to cut and paste Crystal formulas between the reports themselves. This is sometimes called 2 1/2-tier logic, rather than a true 3/n-tier situation, but the benefits are still tangible: the business logic can still be broken out and reused in other reports. You can also add parameters to your stored procedures, and Crystal Reports will accept input to these parameters as if they were a native Crystal Parameter field.
There will be times when you want a very specific report from multiple data sources, but find it difficult to select and display the data you need using Crystal Report's native features and formulas. For example, you may have data from three or four disparate order entry systems, and need to show information from all of them in one report. The data itself is stored in different formats and tables, with different primary keys and structure.You could spend time creating Crystal Reports from these different data sources, and then attempt to use sub-reports to join the data together. This method would allow you to create composite keys, to pass variables back and forth between the different sub-reports, and to try to massage the information into the format you need. Alternatively, you could write a stored procedure to consolidate the data, and use that as the data source for your report.For example, you could work with various Crystal features to create a report that would show data from the New York Stock Exchange alongside world economic indicators from the UN and your own company's sales - but what would happen if you wanted to add another data source or additional features to your report?
A key component of the report development lifecycle that we talked about in Chapter 2 was technical review. This is where you review the report's planned content, and determine the best way to deliver it. If the best method is to use a stored procedure or a view, don't be afraid to use the tools you have at hand. In an example with three data sources, it would probably make sense to consolidate all of these sources into one database, and then develop a view or a stored procedure to put them together in a format that could be used to create the required output.Invariably, using stored procedures or views to return a result set will cause someone to ask, "If we're writing all these stored procedures, why do we need Crystal Reports at all?" We need Crystal Reports to do what it does best: creating information-rich, presentation-quality reports from the data provided. The better the dataset that we provide to Crystal Reports, the easier report development will be, and the more features we can incorporate into our report.
Application Data
Most applications today are developed on an underlying database or other data source, and Crystal Reports.NET can use this application data as the source for your reports, as we saw in the last chapter.Probably the most compelling reason to use application data is that the logic from the application can be reused in your report. For example, if you have a data-bound grid that displays a number of orders together with calculated fields for order totals, sales tax, shipping, and so on, you could reuse this dataset as the source of your report.If you didn't have access to this data, you would have to re-create the calculated fields using Crystal Reports formulas. When something changed in the application, it would also have to change in each report where it appeared. If you're developing an information-rich application, where the business logic is an integral part of creating that information, you may want to consider using the application data for the logic or calculations that appear in your report.
Crystal SQL Commands
As we saw in Chapter 6, something new to this version of Crystal Reports is the ability to use SQL commands as the basis of your report. In previous versions, all of the tables, joins, groups, fields, and so on that you selected for your report were translated into a SQL statement that was written by Crystal Reports itself. That request was submitted to the database, and the results returned to the report designer for formatting.The problem was that while you could view the SQL statement generated within the Report Designer, you couldn't really change it, or use an existing statement in its place. Setting the SQL statement at run time provided one way of dealing with this limitation, but you still had to rely on Crystal Reports to create the initial SQL statement based on the tables, joins, and so on, that you selected in the report's design.
Clearly, there had to be a better way of doing things. With this release of Crystal Reports, you can now use SQL commands as the data source for your reports, (as opposed to tables, views, stored procedures, and so on).
The report in the screenshot is from Chapter 6, and is based on a SQL Command.
The only drawback to creating a 'virtual table' with an SQL command is that when the database changes (when changing a field type, for example), you will need to go back to the report and change the SQL statement to reflect it.Also, SQL commands cannot be shared between reports, so you could end up cutting and pasting the SQL statement between them. If you're considering implementing a report using a SQL command, you may want to consider creating a database view using that same SQL statement. In addition to being able to be used by multiple reports, a view is probably easier to maintain in the long run than individual SQL statements held within Crystal Reports.
Crystal SQL Expressions
Another key database feature of Crystal Reports is the ability to create SQL expressions using the purpose-built SQL Expression Editor, shown here:
When creating a SQL expression field, you have access to all of the SQL functions and operators that are supported by your particular database server. These expressions are evaluated directly on the server, with their values returned to Crystal Reports. Be aware, though, that the same function or feature may not be available if you change the source of the report from one database platform to another.
If you're planning to use SQL expressions, you may want to turn back to Chapter 6 to review best practice for using this feature.
SQL expressions are a handy way to add discrete calculations to a report, but if you find yourself creating the same expressions for multiple reports, you may want to consider using a stored procedure, a view, or a SQL command to eliminate repetitive coding.
Formulas
Crystal Reports.NET has its own feature-rich formula language for adding calculations and logic to your report. Where the methods listed above can offer advantages in terms of sharing or reusing logic, or employing the power of a database server, Crystal's formula language has advantages of its own.To start with, this language is fully integrated with all of the features in your report. We could use a field in a stored procedure to do a calculation such as computing Sales Tax, but it is with the Crystal Reports formula language that we can conditionally format that field, or print out text when the Sales Tax value is over $10,000, as shown overleaf:
When working with a formula within Crystal Reports, you have access to the report's 'print state' and document properties, which include the page number, summary information, and so on. You could create a formula to print out a message on the last page of the report, showing the title, author, file name, path, and print date. This information is not available from any SQL data source - just the report itself.Another example of where a formula is preferable to working with SQL is when you need to use some of the functions that are inherent to Crystal Reports.NET, like the ones for periods of time like MonthtoDate, YeartoDate, and so on, or financial ratios like current ratio, or A/R (Accounts Receivable) turnover, that would be difficult to create from scratch in SQL.When choosing a method for integrating calculations or logic into your report, you're likely to use a combination of the methods listed above, based on their own merits. A good guideline to follow is that for complex calculations and data manipulation, you'll probably want to use your database server to its fullest capacity, and create summary tables, views and/or stored procedures to provide the information you need. If you're working with an information-rich application that already contains some logic, and performs calculations on a .NET dataset, you'll probably want to use the existing data in your application. But for calculations that can't be sourced from database structures or application data, or that are specific to Crystal Reports' features and functionality, the Crystal Reports formula language can surely hold its own.
