Creating A Custom Report (Part 2) – Designing the report

Creating custom reports in Junxure (Part 2) The Reports

Recap

In the previous post,  I covered the most basic parts of Access, and described how Access has 2 parts, the database and the programming environment.  Using the first part to hook up to the data, you can then build queries to extract the data that you want.  You can use the queries to limit what fields you see and how that data is “Joined” together to show you want you want.  You can also put filters on the queries so you further limit what is returned from the database.    Here is a link to Part 1

Creating the Report

When you create a query that displays the fields that you want and contains the appropriate filtering, you can look at it in “Datasheet view”, and see what data is returned.   It will look something like this.

This is great for making a simple list of data, but if you want a printable, formatted report, you will need to do a bit more work.  The good news is that you can leverage all of the work you did in creating the query, all you need to do is to create a report that pulls it’s data from your query.

I will show you a step by step creation of a report using the query that we made in the previous blog entry.  In the sample database that I have provided, I have called the query qryDemoActions.

If you need a copy of the custom database you can get it http://s3.amazonaws.com/Junxurebasics/CustomDemo.mdb

Finding the Report Wizard

You will need to open the database container.  There are a couple of ways to accomplish this.   Hit {F11} and it should open up, or you can click on the menu item Window | Database,  (If you do not see Database on the window menu, you will have to click Window | Unhide Window to make it visible).  Once you have the database container open, you will click on the reports tab, and then click New Report.  That will open the starting page of the report builder.

Here there are 6 items that you can select from

• Design View – This will just open a report in design view and you will have to place all content on the report.  Access does nothing for you.
• Report Wizard – This will walk you thru some choices and you will wind up with a basic report,  ready for additional editing.  (This is the one we are going to walk thru.)
• Auto Report: Columnar – This option will create a report with each field entered one on top of another, with the labels going down the left side of the page and the data on the right.
• Auto Report: Tabular – This option will create a report similar to a spreadsheet, but you will be able to edit it further.
• Chart Wizard – If you query had data that was suitable for charting, you could select his and create a chart.
• Label Wizard – This option will allow you to use the Access label wizard and create many different labels.

In our case you will select Report Wizard at the top, then select the query that we built in the previous blog entry.  qryDemoActions.  That tells Access that you want it to create the report automatically for you.  Once you click OK you will then get a screen where you can select the fields that you want in your report.

On this form, on the left side you will see all of the fields in the query.   You can double click on any of the fields to add them to the report.  Once added to the report, they will disappear from the left column and appear in the right column.  In the above screen shot, I have selected all of the fields, so Access will create controls for each field on the report.  I will discuss the controls a bit later in this entry, but you should know that Access will place a text box on the report and when you run the form, the data for that field will appear in the text box.  When you click Next from this screen, you will be prompted for any grouping that you would like on the report.  Grouping is useful when you want to group report records under a particular field.  For instance, I would like to group all of the actions in the qryDemoActions by who they have been assigned to.

By selecting Assigned to, my report will have a group header and the records in the query will be grouped by the person who they have been assigned to.  This will make it easy to see all of the record by each employee.  You can select additional groups if you like, but for this example we are going to stick to one grouping.  When you click next you will be prompted for the sorting.  Again you can select multiple sorting levels.

Here we have chosen to sort by Last name, First name and Date.   This will produce a report that is grouped by the employee the actions are assigned to and then sorted by clients in last name, first name order, then in date order.  You can change the order of any field so it is either ascending or descending order, so if you want the oldest actions at the top of each group, you would select Descending order.   When you click next you will be prompted for the alignment of the fields within the report.  You can try each setting to see how it will look, but other than cosmetic purposes, it does not matter what you select on this screen.  You can also choose between landscape or portrait, along with forcing all of the fields to appear on one page.  If you force all fields to appear on one page, you may have to do some adjusting to make it look right.

When you click next you will be prompted for a format for your report.  Access provides several different formats and you can choose any of them.  You will just have to play around with each one to see what style you prefer.

Finally, once you have selected your style, you are ready to create your report.  When you click finish, you will view your report in design view with the data from your query, and if you were to print it, that is exactly what it would look like.  Let’s click Finish and see what it looks like.

Modifying your Report

If you click on View Design view, you will be taken from the above print preview view into design view of the report.  In design view you will be able to edit the look and feel of the report.

In the above screen shot there are several things that I have pointed out.

• Pink Arrow at top left – This is how you can switch back and forth from design view to print preview.  Click the little arrow and select either design or print preview.
• Red Arrows -  This is the button that toggles the field chooser on and off.  You can drag any field from this list and it will be linked up to the query behind the report.  Notice where the black arrow is pointing to the lastname field.  That is a field that Access created for you, but if you were to drag it to the report again, you would get something that looked just like that.
• Green Arrow – This toggles the toolbox on and off.   There are a number of controls in the toolbox.  The top two icons, highlighted in orange in this screenshot  are the selector and the autowizard tool.  The rest are listed below going across then down.
  • Large Italic Aa This is a label control and it allows you to type any text in it, an that text will just show up on the report.  The top control on the report, that says “Actions By Assigned to Employee for 2000″ is a label control and I have set the text in it to what I wanted.  Changing the reports underlying data does not change the contents of a label control
  • ab|  – This is a textbox control and generally it will display the underlying data if you bind it to the field.  If you look at the properties box for the lastname textbox, you will see that the control source is set to lastname. This tells access that when you run this report, take the data from the lastname and display it in this box.
  • Small box with xyz on top.  This is what you call a group by box.  We are not using it here, but it can be used to hold buttons  or checkboxes along with grouping controls for cosmetic reasons.
  • Toggle button – This can be bound to a yes no field and it will either display a depressed button or a raised button depending on the selection.
  • Radio button – These are usually placed inside of a group by box, and only one can be selected.
  • Check Box – This is usually bound to a yes not field, like I did on the action required field in this report.  If the action had action required, you will see a checked box, otherwise you will see an empty box.
  • Combo Box – This is a drop down control that holds both the value for the field and a list of items that you can choose from.
  • List box – Similar to a drop down, but it is a list of items and the selected item will be highlighted.
  • Button – Not usually used on reports, but it is just a button that you can click.
  • Image control – This is a control that you can bind to an image.  If you wanted a logo on your report you could place an image control and then set the properties for the path to the image.
  • Picture box – Similar to an image control but with some other options.  The image control is the lighter control and will give better performance.
  • The picture with the xyz is a bound object frame and you can use it to display an object, like a word document inside of your report.  Typically you do not use this item and there are a lot of things that you have to setup to make sure it works properly, including having the proper program installed to display the object.
  • Page Break – This will force a page break wherever you place this control.
  • Tab Control – This is a control that give you selectable tabs.  It is useful in a report for showing only certain controls at a certain time.  (This is a more advanced control)
  • Subform / Report  – This is a powerful control, that allows you to place another report inside of an existing report.  For instance, for the assigned employee, you could have a separate report that shows all of the information for that employee.   It would actually run the report for each employee and only show the data for the employee that it is linked to.   For more information, I would google Access Subreports to learn more.
  • Diaganol Line  – This allows you to draw lines on your report.  Notice in our report we have several lines.  The best way to make a line totally flat is to select it and set the height to 0.
  • Square – This allows you to draw squares on your report.
  • Toolbox – This is a link to additional active x controls on your system.
• Black Arrows – This button toggles the property pages for the selected control.  The property pages are where you can set the value of properties on the control.  Properties can affect the behavior and appearance of a control.  Here are some examples.
  • Name – This is what you will refer to this control as, when you are in code or macros.
  • control Source – This tells Access what fields contains the data for this control
  • Decimal Places – Used when the control contains numeric data.  You can accomplish rounding to 2 decimal places by setting this to a 2
  • Can Shrink and Can Grow – Allows the control to either get taller or shorted depending on how much data is in the contro.  If you have a note field that can contain anywhere from no data to paragraphs, then setting this to Can Grow=True will allow it to display all of the data without having blank spaces in records with only a small amount of data.
  • Font Bold Size etc,  changes the size and attributes of the font.
  • Visible – If you can see the control in print or print preview mode.
  • Text Align – Left Center or right justified.
  • Width and height These set the size of the control
  • Others – there are many other properties but this covers the most used ones.
• Green box in lower left – This is a text box that was dropped on the report, and =Now() was added as the control source.  It will cause the report to print the current date and time when the report is printed.
• Blue text box at the lower right.  This is a special code called [Page] and [Pages].  Access is smart enough to know how many pages of data there will be when the report is printed and what page it is on.  Using these special codes you can display that information on  your report

Additional Formatting

Now you should have enough information to get started playing around with the format of your report.   You can try changing the size, alignment etc and see if you can make it look exactly like what you are wanting it to look like.

Creating a custom report – (Part 1) Tables and Queries

Because of our open architecture, you can easily create custom reports in Junxure.

Every piece of data that is used by Junxure is accessible to you to create custom reports with.  Knowing where this data is and how to use it, is the key to having any kind of report that you can imagine.  You will need a few things before you can get started.

• Some way to access the data -  I will show you how to use MS Access to do that.
• An understanding of data types in databases.  This is important because when you begin to want to limit the data, you need to know what kind of data type you will be filtering on.
• A bit of knowledge on how to access and aggregate that data.  This is basic database knowledge, and while there are many tutorials, book and courses available, I will try to give you the basics.
• A Reporting package that will let you build the reports that you want to build.  Again I will show you how to use MS Access, but you could use Crystal Reports or Sql Server Reporting Services if you like.
• An understanding of data relationships.  What is means when we say “One to One”,  “One to Many” and “Many to Many”.  How to use those relationships to get the appropriate data back.

Let’s get Started.

Since you are going to be using Microsoft Access to be the vehicle where you access your data and create your reports, you must first learn a bit of how to use the Program.   As mentioned before, there are whole books on this subject and entire courses that are taught at colleges and adult education classes.  I would strongly recommend that if you want to become proficient in learning how to do anything you can think of with custom reporting, then you should do what you can to get a thorough understanding of how Access works.  I have worked with many users who already had an understanding of MS Access and were pretty proficient with it, and these users were able to get started producing reports almost immediately.  I will do what I can, but you may want to deleve further into Access with a book or an online tutorial.

Here are a few links to help you find more information.

http://www.officetutorials.com/accesstutorials.htm

http://www.amazon.com/s/ref=nb_ss_gw?url=search-alias%3Daps&field-keywords=msaccess&x=16&y=19

Access is really two programs in one.

Most people do not realize this, but Access is really two programs in one.

It is a Database

Every access file can contain its own database tables.  There can be a virtually unlimited number of tables.  Although it can contain its own data, the file does not have to actually contain the data, because it also has the ability to link out to other data sources.  When it does this, the tables that it linked out to are called “Linked” tables.  For our programming examples, we are going to use Linked table, and we will be Linking out to the data that is stored in the Junxure tables on the SQL Server database.  If you do not want to use your live data, I will also provide a sample MDB file that contains a set of sample data that you can use instead of your live data.

It is a programming Environment

With the exception of the tables, everything else in the Access file is part of the programming environment.  When you think of a file on a computer system, you usually think of a single entity, like a word document, an excel spreadsheet, or a text file.  With Access, the files are a bit different.  They contain many items with them and those items are viewed by opening the file with the Access program.  The types of items are listed below

• Tables
• Queries
• Forms
• Reports
• Macros
• Modules

Tables

These are the items that contain the actual data.  A table is made up of Records and fields.  Most people are familiar with spreadsheets.  When you look at a spreadsheet like the one below you see rows and columns.  In this example, we have these columns

Name

Address

Phone

and we have 3 entries for each column

Ken

Bill

Tom

If you were to translate what you know about spreadsheets into database tables, you would see the following

Columns become Fields, and in a database you need to specify what type of data is stored in these fields.  In a spreadsheet, you can just type whatever you like into a cell, but in a database, you need to determine ahead of time, what kind of data you want to store in that field.  There are many types of data that you can store,  but I will discuss the basics.

• Text- Any type of characters that you can type with your keyboard
• Numbers – Any type of numeric input, but they are even split further
• Integers – Numbers that do not allow decimal points
• Doubles – Numbers that do allow decimal points
• Currency- Numbers that allow 4 decimal points
• Dates

(There are actually more types but for this discussion, you need to understand that some numbers do not allow decimal places)

Rows become Records with each record contains one copy of each field.   The drop down list shows you want kind of data each field can hold.   Here is a screen shot of a table that will hold the same data as the spreadsheet above.  This is the design view of that table, the bottom picture is the data sheet view, where you can see the data.

In the picture below, you will see an additional column.  ID  This column was added by Access and it is a type of Autonumber.  That means each time a record is added to this table, Access will automatically insert the next available number into that field.  Once it is inserted, it can always be used to identify that record.  This is very useful when trying to find a record.  Since it is a number, it can be indexed (Pre sorted) internally to the database and Access will be able to find it very quickly.  If you have 2 records with the same data in every field, the ID field will allow access to be sure to find the proper record.  It is called a primary key and they are very useful.  In Junxure, the clients table has a field called ID and it uniquely identifies each individual contact record.  Once a number is used, it will never be reused, even if you delete the record.

Tables are “NEVER” sorted by any predictable method.  The data can be in the order that you enter it, or any random order.  This is important for you to understand.  You can sort the data however you want using queries.

Too Many Tables

If you look at all of the tables in Junxure you will see that there are over 300 tables and it appears that the data is scattered all over the place.  Your observations are correct. It is scattered all over, but there is a method to the madness. If you are familiar with spreadsheets, you are probably used to viewing data in rows and columns. While this is a valuable way to store data it is very limiting.  You can do pivots and sorts but the amount of reports is fairly limited. That is why a program like Act or Goldmine cannot compare with Junxure when it comes to database flexibility. In a typical spreadsheet where you track accounts you may seem something like this.

Client Address Phone Account1 Account2 Account 3

Ken Golding 123 lucky lane 444-4444 YG66855 FB-55544 KF7706

Bill Smith 234 Bull Market Ln. 4454455 GF06855 000-9985 5467765

This creates a real problem with trying to add the 4^th, 5^th and 6^th account. What if you get a client that has

100 accounts? In a relational database we would split that sheet into 2 tables. Clients and Accounts so you would have something like this

Clients table Accounts Table

ID Name ClientID AccountNumber

1 Ken Golding 1 YG665855

1 FB-55544

1 KF7706

2 Bill Smith 2 GF06855

2 000-9985

2 5467765

Each piece of data goes in its own table and it is related to additional tables.

If you look at table alone, it would be pretty hard to use that data unless you had an exceptional memory

Some things to remember about tables:

• They can have certain fields that populate themselves, for instance the ClientIDs in the above client table
• They can have constraints on the data that is entered. You cannot enter a date into a money field.
• The data in a table is in no particular order, usually it is in the order it was entered but not necessarily.

So how do you view the data in a meaningful way? That is where Queries come into play.

QUERIES

At this point, I am going to introduce a sample database for you to work with.  It has the same tables as Junxure, but you don’t have to worry about messing up any live data.

Download it at this link http://s3.amazonaws.com/Junxurebasics/CustomDemo.mdb

I have included the tables in the database so you can feel free to delete, edit add as you like. All of the data in these tables is local and not “live”

The tables we will be working with are

tblClients The main Client Table

tblClientclassifications A table that has unlimited classifications for each client

tblclientKeywords A table that has unlimited keywords for each client

tblclientActions This is the table that stores actions in Junxure

tblClientDBCamIDs This is the table where we store the account Numbers

ContactsAccountXref This is a table that links accounts to client.

FAS-Assets This is where we store the Asset position information

There are more tables but for the purpose of this demonstration these are the one we are going to use.

If you open any of these tables you will see a lot of fields that you don’t necessarily want to see while you are working with the custom database.

When working with queries you should remember that you are just gathering the data that you are going to include in your report.

Here I will show you how to limit what you see to a subset of the entire table.

Take a look at qryClients. This is a query that I have created in the customdemo.mdb file.

These are the fields that we are going to have available. If we need more fields, we can just drag them to the grid. Notice that I have added a field called Addline and Cityline.

Here I have joined two fields together so that later you will not have to do it in your reports.

This is called an Alias. The syntax for this is Addline:[HomeStreet] & “ “ & [HomeStreet2]

Also notice that we selected Ascending under the last name on the sort row. That will return the records sorted by lastname.

If you switch this from the design view to the data sheet view, this is what you will see.

Now lets take a look at a query that uses this query to show all of the classifications for each client.

In the above image, we have joined the previous query with the classification table. The line with the arrow pointing out tells us to show us every record in the qryclients and only the classifications if they exist. If a client has multiple classifications then they will appear multiple times.

So you can see by using queries you can begin to make sense of this seemingly disconnected data.

Lets make another query that shows actions for clients. In this query I am going to show all actions with the clients name

Here I have joined the two tables to show all of the actions and the client for each action.

I have added the fields that I want to see.

If I switch to data sheet view this is what I see

Now we are beginning to get something that we can use, but notice that the EmpID and ActionAssignedtoID are showing numbers and not the Employees names. If we what the names we will need to add some more joins out the the employees table. Since we have 2 fields that we are looking for we will add the employees table twice. When we do this Access will append a _1 to the second table.  Then we will use the alias to display a field that shows the lastname of the Employee. For the fields with a zero on a blank, no name will show up. The syntax for the new column is Emp:tblEmployees.Lame

Notice that we now have the name of the employee along with the data. Another thing that you can do with queries it so filter the data by adding criteria. I will filter for only actions in the year 2000 and with a type of planning or service.

Here is the example for that

Notice that I have the critera on 2 lines. That is because I want to “OR” the filter.

I am saying between ((1/1/200 and 12/31/200) and Letter) OR ((1/1/200 and 12/31/200) and Service)

This changes the results to a smaller subset of the existing data.

<!–[if !mso]> <! st1\:*{behavior:url(#ieooui) } –>

By adjusting the criteria I could further filter this to only actions assigned to Mary Doe. In fact I could create as many different scenarios as I could imagine. This is the beauty of using queries to query a relational database.

You may be interested in knowing that the graphical interface is only a representation of the action

“Structured Query Language” or Sql that is behind this query. Here is the actual SQL text.

SELECT

LastName,

FirstName,

Date,

Type,

LName AS Emp,

ActionReq,

DateReq,

Note,

tblEmployees_1.LName AS AssignedTo

FROM

((tblClientActions LEFT JOIN qryClients ON tblClientActions.ClientID = qryClients.ID) LEFT JOIN tblEmployees ON tblClientActions.EmpID = tblEmployees.EmpID) LEFT JOIN tblEmployees AS tblEmployees_1 ON tblClientActions.ActionAssignedTo = tblEmployees_1.EmpID

WHERE

(((tblClientActions.Date) Between #1/1/2000# And #12/31/2000#) AND ((tblClientActions.Type)=”Letter”))

OR (((tblClientActions.Date) Between #1/1/2000# And #12/31/2000#) AND ((tblClientActions.Type)=”Service”))

ORDER BY qryClients.LastName, qryClients.FirstName;

The importance of knowing about the SQL text is that you can copy this sql and paste it into another database and access will draw the query assuming that the tables are available. It is also useful if you are using the Forum to ask a question about a query. You could paste the sql from your query and I could load it in my database to see what it is doing.

The SQL keywords are in this query are

Select

From

Where

Order by

In my next post, I will discuss how to take the data from the queries that you build and show you how to make them into a report.

New Feature (Multi Custom Fields for Accounts)

Tracking Account Information

Over the last year, I have had many requests from our users,  some work in the accounts area of the program.  Basically they were asking for 2 things.

More fields that we did not have

Better ability to report on the Accounts.

Due to the enormous efforts what went into rewriting Junxure 7, we did not have time to get these items into the main release, but we were listening and they will be in the next release.

Here is what is coming.

Unlimited Custom Fields

Accounts Rule Builder

Account Report Wizard

Enhanced Client User fields

I have just added a few new features to the Accounts details form.   These features are in the latest code, and they will be released as soon as testing gets underway.  Probably within 3-4 weeks.

 

Unlimited Custom Fields on Accounts

In order to use them, you will have to first set them up.

Go to List Data Maintenance click on the Program Setup Category,  and then select Multi Custom Fields.

There you will find a form where you can setup unlimited fields that will be associated with each account.

There are 4 attributes that make up each field

Data type – This determines the User interface control that you will use when you access this field.  The following types are available

Combo – this will present a drop down for the field.

Date – this will present a date time picker for the field.

Number – this will present a numeric editor for the field.

Text – this will present a text box for the field.

Currency – this will present a currency editor for the field

Y/N – This will present a check box for the field

Field name - This will be the name of the field on the Account form

Sort – This determines the order that the fields appear.  They will appear in two columns, down then across.

Key- This is a button that you can click to enter the contents of the drop down if you select Combo as the data type.  The button will only appear when you select Combo

Here is a screen shot where you setup the fields.  Another benifit of this enhancement it that it will allow us to expand the custom fields to the insurance at a later date.

Once the fields are setup, you can see them on any account from the clients form.

Account Rule Builder

This is a rule builder, that works for Accounts.  You can use any of the fields that are on the account form, along with any of the custom fields that you have setup.

You can create unlimited rules and check them on the fly, or from the Account Rule list.

Account Report Wizard

This is a report wizard, similar to the Report wizard and the Action Report Wizard, but it is for Account information.  You use an Account Rule to determine what accounts you want to see.  You can then enter a client rule to limit the list to a group of clients, and then you select what fields you want to see in your report.  Just like the other, you can send the results to a portrait or landscape report, or to an Excel spreadsheet.

Enhanced Client User Fields

I have taken the ability to select the data type for a custom field,  and have applied that to the existing user fields on the client form.  By default, they will all be combo fields, but you can change them to any of the above listed data types, so no conversion is necessary.

It is important to be sure that you do not change a field that has text in it to a Number.  I have put some checks into the system so you will not be able to do this.  When using this feature, if you do not see one of the data types in the drop down, it is because you have data already entered in that field, and it would conflict with the missing data type.

 

 Hopefully, these changes will go a long way to making Junxure a more capable program, assisting you to run a better practice.