Hanselman of the East

I am sure that some of our readers will see the title of this post and think that it is a bit strange.  There actually is a story behind it.   A lot of people do not understand the world that computer programmers live in.   For those of you who use Junxure, it is pretty easy to use the program and I hope we have done a pretty good job at hiding the complexity.  If you could peek into our world, you would see that Junxure is actually a blend of technologies, all working together to allow you to run your practice in the most efficient manner.

All of these technologies allow us to do some incredible things, but at the same time they are very complicated and forever evolving.  The challenge for me and our development staff is how do we keep up with the technology and it’s ever changing status.  The problem of keeping up is not unique to the programmers who develop Junxure,  it is a universal problem.  To help developers keep up with the changes, Microsoft has put together a team of people whose main purpose is to spread the news of what is new, what is coming, and how do you use these technologies.  Additionally,  just like we at Junxure listen to our users to find out what is working and what need to be added or changed, they listen and meet with many of their users to do the same thing.

They do this via a variety of ways, and one of them is something that is called “Code Camp”.  This weekend Microsoft, Infragistics and a host of other companies hosted a “Code Camp” and they were able to have it at a local community college.  Orlando Code Camp This Saturday, they put together 50 speakers, and 65 sessions designed to help developers use the technology.  There we sessions on Dot Net Nuke, Sharepoint, Data Services, Sql Programming, Programmer related management and development stuff, The Dotnet Framework, MVC and a whole bunch of other stuff.    You can click the link above if you are really interested in the details.

So if you have gotten this far in the post,  then you are probably asking yourself what the title “Hanselman of the East” has to do with all of this.  We it all goes back to a maintenance man at a theater in Fort Lauderdale.  You see, about 4 or 5 years ago, I went to another event that was hosted by Microsoft,  and this event was at a theater.  I arrived a a bit early and got a seat near the front.  While they were setting up it looked like there where having trouble with the audio visual system and in walks a maintenance man wearing a hard had and a old leather tool belt.

I have to admit, I thought it was a bit funny seeing all of these highly trained Microsoft employees standing around while this maintenance man was called in to make it all work.  He fiddled with some cables  and settings a low and behold, as if a by magic, the Microsoft logo appears on the huge theater screen.  Internally I had a bit of a chuckle thinking how the maintenance man saved the day.  Then they said that they were going to get started.  Much to my surprise, the same maintenance man goes to the front of the room and introduces himself as Russ Fustino, host of the, now world famous Russ’Tool Shed.  Russ gave a fantastic presentation on how to program on what was then the new Visual Studio and some of it’s new features.  Russ is one of the guys who helps us learn all of this new stuff.  His discussion and demonstrations of the tools that are available to developers is top notch.

So again you are probably asking again “What does this have to do with Hanselman of the East”.   Well there is another person who has devoted lot’s of his time to helping developers learn about technology, and his name is Scott Hanselman.  Scott is very well known but mostly hangs out in the West,  but he has a great reputation in this field.   Many people when they think of great leaders in the area of Microsoft development they are can’t help but immediately think of these two guys, along with a slew of many others.

To give you an idea of how many people Russ has helped over the years, I’ll share a story about a session this weekend.  Ryan Morgan was giving a presentation on Jquery, and a lady walked into the room and said she had some extra tickets to Russ’ TV show.   There were about 40-50 people in that room, with many standing in the back of the room because of  Ryan is an awesome presenter and everyone wanted to hear him.  Anyway, when she said she had a few tickets to the show, one poor guy asks “Who is Russ Fustino?”  The whole room went silent and almost in unison whole room cried out “WHO’S RUSS FUSTINO?”  like they were so surprised that someone in this field, would not know who Russ is.  The poor guy then had to listen as people began to say “EVERYONE KNOWS WHO RUSS IS.”  He as probably feeling pretty bad by then, but that shows the impact that Russ has made on the developer community.

Overall it was a great weekend for the Junxure developers.  We were able to fan out and cover a huge variety of topics and the knowledge that we gained will help us to make Junxure, Clientview and Junxure Mobile much better products.

So this is the end of the weekend and the Junxure developers have spent a couple of days reviewing our code, looking at things that we are working on and refining our road map.  We topped it all off with a whole day at Code Camp, and now we are going to be heading back to work, fully charged, loaded with new ideas,  and excited to get started using them.

Hopefully you now have a better appreciation of what we do behind the scenes to keep up to date on all of the current technology.

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.