FlashReport - Getting Started

(c) 2004 - 2005 TUFaT.com, created by Darren Gates, Maksim Galkin, and Serguei Pisarev. Re-sale or re-distribution of FlashReport in any form in strictly prohibited.

FlashReport is a way to create PDF reports from MySQL data. A Flash 7 interface forms the core of the report layout system, and lends the name to FlashReport.

This tutorial will step you through the report-creation process for FlashReport (previously known as MyPDF). To get started, please use the SQL data in the "sample_data.sql" file, which comes with your FlashReport download (in the "beginner tutorial" folder), to create a new table of 5 fields and about 500 sample records. This sample data shows the regional sales for various employees of a fictitious company. This tutorial will demonstrate how to create a simple PDF report of a single table.

This tutorial assumes that FlashReport is installed on your server (see the README.txt file for details on how to install FlashReport). It also assumes that you have logged-in to the report builder by creating a new username & password, which is a fairly standard procedure. FlashReport is a multi-user report system, so if you have a company with many employees, each employee can have his or her own set of reports.

You can use a tool like phpMyAdmin (or even better: phpFlashMyAdmin, available from www.tufat.com/phpflashmyadmin.php) to add this sample data to your MySQL database. There are 5 fields in this sample table: _id, _rep, _date, _region, and _sales. Please note that _date is a field of the DATE data type. This will be important later in the tutorial.

Data Criteria - Sorts & Filters

To get started, you must specify a data source for your report. You do this by choosing at least one sort. You MUST choose at least one sort for your report, or the report will be blank! In this example, I have chosen to sort the report by the "_rep" field, and then by the "_region" field. _rep will therefore be the "first order" sort, and _region will therefore be the "second order" sort.

Sorting is important for another reason, too. The sort order determines the "grouping" or "summary" order of your final report, if you choose to add a summary section to the report layout. If you have multiple summary sections, the system looks at the global sort order to determine what the grouping levels are. This is very similar to how FileMaker Pro manages grouping levels in a report. Thus, if you wish to group your data by the _rep - i.e., you want to see a summary of the sales by employee/rep - then _rep must be the top-most sort, as shown below.

The [ ? ] buttons are just to provide some quick information on these sections, in case you forget what they are for.

In the above screenshot, you will also notice that I have added a "filter condition" to the report. In this example, I have chosen to filter by the _date field, and I want to show only dates that are more recent than January 1st, 1995. Thus, the filter condition is:

> '1995-01-01'

Please note that you must use a MySQL-compatible date format (YYYY-MM-DD) when specifying date filter conditions, and the filter condition should be enclosed in single-quotes, unless you are using the LIKE operator, in which case you do not have to enclose the condition with quotes (as shown below). You can also specify additional filter conditions by clicking on the "+" button, and specify whether to combine the filters by boolean criteria like "AND" or "OR". Using "AND" will make the report more restrictive (showing LESS data), while using "OR" will make the report less restrictive (showing MORE data).

If you wanted, for example, to only show Employees (designated by the "_rep" field) whose names started with the letter B, you could use something like this as the filter condition, using the LIKE operator.

b%

Please note that when you are specifying a filter condition for a VARCHAR or CHAR field using LIKE, you do NOT need to surround it by quotes. For example:

In this case, the resulting report would only show Bridgette's sales data between 1995 to the present date. For the remainder of the tutorial, please note that I use only the _date filter criterion, not the _rep filter criterion.

IMPORTANT: Syntax errors are common when specifying filter conditions for FlashReport. Please be sure to TEST the report by clicking on the TEST button at the bottom of the screen if you are unsure of the correct syntax for the filter. Also, be sure to SAVE your report frequently!

When saving your report, you may specify to "share" the report. This means that other registered users of FlashReport will be able to use your report as a template for their reports. Other users will not be able to save the changes to your template, but they will be able to *use* your report as a starting point for their reports. The "Save" button is located at the bottom of the screen.

For this tutorial, we'll ignore the "Relationships" section of the "Data Criteria" screen. This section is covered in the advanced tutorial, and is used to create multi-table reports.

Report Layout Builder

FlashReport comes with an advanced, Flash 7-based report layout builder, which is very similar in form and function to report builders like those which come with FileMaker Pro or Microsoft Access. Click on the "Build Report" tab at the top to switch to the layout builder. Be sure that you have specified at least one sort in the "Data Criteria" tab, and also be sure that you have saved the report.

As the first step, we will specify the overall grouping for the report. For example, suppose that we wish to create a report of the sales by _rep (employee) - i.e., who sold how much. Since the first-order sort is by _rep, we can create a first-order summary by _rep. To do this, click on the "SUM SECTION" button, then add the new section above the "Body" section by clicking on the divider line between "Body" and "Header", as shown below.

VERSION NOTE: "SUM SECTION" = "SUMMARY" = "SUMMARY SECTION". This button may appear as "SUM SECTION" or "SUMMARY" or "SUMMARY SECTION" depending on the version of FlashReport that you are using. They are the same thing. In other words, in this context, "SUM" does NOT mean "addition".

When you add the summary section to the report layout, you will get a popup window from where you may choose the field to group by. You should use the "_rep" field.

VERSION NOTE: Depending on the version of FlashReport that you are using, this dialog box may show the table name in addition to the field, or it may not. In other words, it may show "sample_data._rep" or just "_rep". This is also true when adding fields to the report (see below).

Now, click on the "FIELD" button at the left side, and add the "_rep" field to the new SUMMARY section, as shown below. You may also apply some basic formatting to this object by using the toolbar buttons. If you click on any text object, you will see a variety of text-formatting controls, like alignment, bold, italic, font size, etc. Fields are considered types of text objects, so anything that you can do to normal text, like align it or re-format it, you can do to fields as well.

In this example, I have enlarged the object to about 16pt, made it bold and blue, and also right-aligned it. For purposes of this example, I have put this in the middle of an A4-sized page, although in a true report situation this would probably be better positioned at the far-right or far-left of the page.

After adding an object to the page, you can edit it by double-clicking on the object. If you position the object on the far-left side of the page, it may be occluded by the "Summary (GROUP BY _rep)" label. You can hide this label temporarily by clicking on the "Hide Buttons" checkbox.

Now, you might be wondering about some of the other objects on the left-side panel, and also about the various report sections. Here's a summary of what the toolbar buttons mean. Note: if you choose to load a JPG image from an HTTP address, then the JPG MUST be a non-progressive image, and it MUST be JPG (not GIF or PNG).

OBJECT	MEANING
	Add a field to the report layout. The list of available fields draws directly from the data sources that you specified in the "Data Criteria" part of the FlashReport. You must specify at least one sort for the report to have fields available during the layout process.
	This allows you to add custom calculations to the report, like SUMs, AVERAGEs, and COUNTs. You can also use MySQL-specific syntax in the "Custom" calculation section, for example CONCAT( last_name, ', ', first_name ) or DATE_FORMAT( _date, '%m/%d/%y' ). For a complete list of MySQL functions that are available for database fields, refer to the MySQL documentation at http://www.mysql.com MySQL functions are not case sensitive.
	Adds a summary section to the report layout. You may ONLY summarize by fields listed at the TOP of the sort order. Also, when adding a FIELD object to a SUMMARY section, you may ONLY add the field that the summary section is summarizing by. Depending on the version of FlashReort that you are using, this may appear as "SUM SECTION" or "SUMMARY SECTION".
	Hides section labels, so that you can see objects behind the labels.

Here's a summary of what the various report sections mean, and a few notes that you should keep in mind regarding these sections:

At this point, we have only a single summary section in the report. We really don't have a report at all until we add things to other sections of the report. So, let's first add a line below the sub-summary section title. Before adding the line, I clicked on the "Constrain Shape Proportions" button to ensure that the line is straight. The constraint tool can also be used to constrain the proportions of ellipses and rectangles to make perfect circles and perfect squares.

To make some more space for the line, I enlarged the summary section slightly by clicking and dragging the divider line between the "Summary" section and the "Body" section.

Now, let's add a summary section *below* the body data, and specify _region as the field to summarize by. As in the summary section for _rep, I've added the field itself to the summary section so that we can see what field we're grouping by in the final report. Before I added the _region field, you will notice that I first added a light-blue rectangle as the background for the _region field data. The "layer order" (or more technically, the Z-Order) of the objects is determined by the order that they are added to the report. Thus, if I want a rectangle to appear behind a field, I must FIRST add the rectangle, and THEN add the field.

If, by accident, you added the field first, then the rectangle, you can change the Z-Order of the objects - that is, which objects appear on top, and which on bottom - by using the reorder layers buttons.

Here's what the result should look like after adding the new summary section for regions.

You will also observe that I've added a date object, and some page numbers, to the report. To add a date, simply click on the icon that looks like a little calendar in the left-side panel. The date object can be formatted just as any text item or field with the various font options that come with FlashReport. I've added this date section to the "Title Header" because I only want this to appear at the top of the first page of the report.

You may use any of the following codes for dates:

In addtion to these date codes, you may also use the PHP-specific date codes from www.php.net. This is only recommended if you have a substantial amount of coding experience in the PHP language. You may also embed commas and other punctuation marks to format dates, for example like January 14, 2003.

For page numbers, you can use an ordinary text object. The system will automatically detect the "##" and "####" text, and make the appropriate substitutions. In the above screenshot, I've put the page numbering in the "Footer" section rather than the "Title Footer". This is because I want this to appear on every page of the report, not just the last page. Here's a quick summary of the page number codes:

Ok, so at this point we have two summary sections, a title header, and a footer. Let's add some more objects, like some fields and a simple calculation. In the below screenshot, you will see that I've added "Sale Amount" and "Sale Date" in the Header section. This text will appear at the top of every page of the report. Think of this as the header column in an Excel spreadsheet, which identifies the column data.

Also, I have added the _sales field to the report layout, in the body section, as shown below. At this point, I have made this is an ordinary field object, but later we will convert it to a calculation. In addition, I have added a calculation to the summary section for _region. Since the calculation is in the _region summary, it will be applied ONLY to the contents of the containing summary region. In other words, this is a SUM BY REGION. I have also given this calculation a name: "sum_sales". The reason for naming one's calculations is so that they can be used in other parts of the report. For example, it would be possible to create a "sum of sums".

In the screenshot below, I have chosen the "Hide Buttons" checkbox, so the section labels have been hidden. Those parenthetical comments in red are just so that you know which sections these items have been added to.

At this point, we should have a fully-functioning report. You can play with the design elements later to clean it up and 'beautify' the report. It is a very good idea to TEST your report frequently to check for errors, and of course SAVE your report after every change. If you observe any SQL syntax errors in the resulting PDF document, or if you see a blank PDF document in the result, then you have an error, most likely in the filter step of the Data Criteria tab. There are two things that you should do after every step of the report creation process:

1) TEST the report
2) SAVE the report

Now, let's add the _date field to the report layout. But instead of adding it as a standard field, we will add it as a calculation. This will permit us to apply a custom calculation to the _date field. MySQL has a function called "date_format", with which we can modify the output style of a date field. The field MUST be a "date" or "datetime" data type to do this. In the "Calculation" window, I have used this calculation, as you can see below:

date_format( _date, '%m/%d/%y' )

I have named this calculation "formatted_date" so that it's easy to remember that I'm applying a special format to this object.

For more information about MySQL date calculations and a list of date format codes, please see the MySQL documentation here: http://www.mysql.com/doc/en/Date_and_time_functions.html

Let's finish the report layout by also adding a "commissions" calculation. The idea here is that one's commission for any particular sale is 8% of the sale value. Thus, a $100 sale receives an $8 commission. You can do this in FlashReport by choosing the "ADD, SUBTRACT, MULTIPLE OR DIVIDE" option in the Calculation window. In this case, I have chosen to multiply _sales by 0.08. I have also added a "Commissions" header at the top of the report, in the "Header" section since I want this header to be repeated on every page of the PDF.

You will recall that when we added the _sales field to the report, we did so as a standard FIELD object. However, now that we know how to use a custom calculation, we can format this data a little nicer. For example, it may be desirable to display 2 decimal places for the sale data, and also a "$" symbol to show that this is money that we're dealing with.

To accomplish this custom formatting, we will need another custom calculation. Click on the _sales field and delete it by using the trash icon in the left-side toolbar. Then, add a CALCULATION object to the layout where we previously had the _sales field. The custom calculation that we will use is this:

concat( '$', format( _sales, 2 ) )

The "format" function puts the _sales figure in ###,###.00 format, and the "concat" function allows us to append the "$" symbol to the front ('concat' is an abbreviation for 'concatenate', which means to combine). These are built-in MySQL functions. You can read about more MySQL functions in the documentation at <a target=_blank href=http://www.mysql.com>MySQL.com</a>. There's a function for everything!

I've called this new calculation object "formatted_sales" so that it's easy to remember. Of course, you may call it whatever you wish.

We will also add a calculation at the very end of the report to provide us with a global sum of all the sales across all of our various reps. This is added using the Field Total SUM function in the Calculation popup window. This calculation is placed in the "Title Footer" section, so that it only appears at the bottom of the overall report.

ALL DONE! So what does our report look like? Well, the date obviously appears at the top of everything, since it was placed in the Title Header section, and the column headers appear at the top of every page, since they were placed in the Header section. In addition, we can see that the _sales, _date and commission information repeats throughout each body section, and also that it's formatted in a specific way, according to our custom calculations.

We can also see that in the summary section above each body, we have the _rep name and a line separator below the _rep name (for example "Ana").

Below each body section, we have a simple SUM of the _sales data, as well as a field to indicate the _region that we have summarized by (for example, "Asia").

Then, at the bottom of the report, we have another SUM for all the sales figures across all reps. Of course, you can further format the sum of sales and commissions figures so that they have $ signs in front of them, as you did for the _sales figure. This is an ugly report, no doubt... but you get the idea.

Thanks for completing this tutorial! Although this tutorial probably demonstrates the most common uses of FlashReport, there are more features, which you may wish to explore. For example, you can add your company logo to the report by adding a picture object. Please note, however, that ONLY non-progressive JPG images are allowed, and you must specify the full HTTP path to your image. Thus, the image must reside on a web server or other network server.

You can also add multi-level groupings beyond the 2 levels that have been demonstrated here. A large dataset might have > 5 levels of summary, and calculations of calculations of calculations, etc.

Finally, you may create reports using more than one database table. The advanced tutorial demonstrates how to do this.

Thanks for choosing FlashReport!
Darren

LEGAL INFORMATION: The license for FlashReport allows you to install & use this software on ONE DOMAIN (.com, .org, .net, etc.). You may not resell or redistribute FlashReport in any manner. Modifying the source code to FlashReport does not give you resale or redistribution rights. You may not claim credit for any part of FlashReport. You use FlashReport at your own risk. I make no claims with regard to the reliability or accuracy of the reports created by FlashReport. So, you should test it with your data thoroughly first to ensure that it produces the results that you expect.