Anticlue

Unleash the power of the field explorer

I'm a high level global thinker, and I really like going into the nitty gritty detail. So reports that I think have quite a flair to them show the high level, and then let me get at the detail. Of course others don't have the same appeal to the global high in the sky summarization and immediately into the weeds detail. Categorization is needed. However before you categorize, you need to show your stuff.

The Field explorer is a tree view of fields that you can add to your report. It is comprised of database fields, formula fields, SQL expression fields, parameter fields, group name fields, running total fields, and special fields. All which are available for use as quick as you can drag and drop. From one of the previous reports, take a look at the field explorer. Fields that have the check mark next to them are used in the report.

So let's review some of the places one can explore with the field explorer. Open the Database Fields, by clicking the plus sign in front of the database icon. You will see the tables, views, and stored procedures to which you have access to within this report. At this point, there is not a way to distinguish whether you are reporting on a view, stored procedure, or table. It is seemless to the enduser. Another added feature is the ability to browse the data within the fields. If you highlight a field like quality and click the browse icon, second one from the left, then you can see the data contained within that field.

Another feature of the field explorer is the ability to create formulas based on results of automatically analyzing the data at hand. In order to create a new formula, please right click on the Formula Fields. A window requesting the formula name will appear. For our exercise here, I added the formula that calculates the Days from Ordered Date to Shipped Date. Only please don't misspell Ordered. Once the name has been entered click the ok button.

I need to find the difference between one date to a next. So under functions in the middle window, I select date time functions. Within this tree there is the Date Diff function. Only problem is that I'm unfamiliar with the syntax for this function. I select the function and move it to my code window.

Once there we can place our cursor over the Date Diff function and press F1, instantly a help window on this formula. The interval syntax we are looking for is "d" for days. Let's say that we already knew that the function start with date, we can kick off Crystal Reports intellisense by hitting the ctrl key and space bar at the same time.
Next from our report fields, we add the OrderDate, and Shipped Date appropriately. Final syntax check occurs, by clicking on the formula check icon, right below save and close. Our syntax is correct, now we can save and close.

For a test run, we should try adding a new formula field to the report. Clicking print preview always you to see your results.

Let's say you were analyzing large amounts of data, and performance was a necessity. In this case it is better to utilize SQL Expressions for formula manipulations. If speed is a need, let the database do its job.

To add a SQL Expression, right click on the SQL Expression within the field explorer. Click on add a new field. A SQL Expression Name window will appear. In this case, I'm creating a name format for the employee, so this will be employee name. Once completed, please click ok.

While I'm in the SQL Expression builder, I need to remember to use proper SQL format for this item since we are computing in the database. I enter the formula as indicated below, this will yield "Elyse Nielsen" for my name. SPACE is a function of SQL server. A word of caution here is that most database have nuances to their SQL so be sure to check your syntax to make certain it is good. Once completed, click save and close.

Now I have the Employee Name field available for my reports.

Finally let's take a look at the special fields that Crystal Reports provides. These are system fields which enhance the quality of a report.

• Content Locale is the locale setting of the machine which runs the report.
• Current CE User ID is the ID number of the current BusinessObjects Enterprise user.
• Current CE User Name is the name of the current BusinessObjects Enterprise user.
• Current CE User Time Zone is the time zone of the current BusinessObjects Enterprise user.
• Data Date is the date the data was last refreshed or retrieved.
• Data Time is the time the data was last refreshed or retrieved.
• Data Time Zone is the time zone where the data was last refreshed or retrieved.
• File Author is the author of the report, indicated in the Document Properties dialog box.
• File Creation Date is the date when the report was created.
• File Path and Name is the file system location of this report.
• Group Number is the autonumber field for labeling each group in a report.
• Group Selection Formula is the current group that was selected for the report.
• Horizontal Page Number is the number of horizontal pages. (Normally used with crosstabs or OLAP grids
• Modification Date is the date the report was last modified.
• Modification Time is the time the report was last modified.
• Page N or M is the current page out of the total pages for the report.
  
• Page Number is the number of the current page.
• Print Date is the date when the report was printed.
• Print Time is the time when the report was printed.
• Print Time Zone is the timezone where the report was printed.
• Record Number is the an autonumber of each report printed in the detail section.
• Record Selection Formula is the selection formula used to garner the reports information.
• Report Comments are the comments specified in the Summary Info.
• Report Title is the title specified in the Summary Info.
• Total Page Count is the total pages printed in the report.