Anticlue

An Example of One Data for all Lookup types

Earlier this month, I blogged on having one table to contain all reference data. There were a several good comments on the subject, so I thought I would take this opportunity to expand.

First, in the healthcare environment, there is a need for flexibility within applications to add new tables to track new items very frequently. This level of meta data is then passed across the enterprise via an interface engine. All the data is passed at an application level from application to application. It isn't as simple as at the data level or having a generalized Service Pool for all meta data for the instititution. Also when mneumonic data is passed from Application to Application, the export goes throught a translation table, and then in the receiving system, the import also goes through another translation table on its way into the receiving system. For example in the registration system, a discharge disposition of release to home is AHR, in the coding system, the mneumonic value displayed to the users maybe 1, and then in the billing system is may just be AH. Confused yet?

So with this requirement, the other item that happens is regulation come along or new systems arrive, and the thought is to convert the meanings of some mneumonics to synchronize display across the institution. IE in the example above all the systems would display the value of 1 for releasing a patient to home. When we completed this option, it included a data conversion of several production systems. Therefore, it became obvious to have a key field for the lookup data id, being separate from the mneumonic and description.

Having system managers or application analysts, maintain and keep this data up to date is another good practice. The tricky part is moving the administration into the hands of the system managers. This is has a couple of considerations, first the ability to report off of this data is critical and normally handled via SQL Views of the data. The ability to add a field to the system for the display of this information, this is a little trickier.

So how to go about this and what about performance hits? In healthcare the data is interfaced in a realtime fashion to from system to system via HL7. Therefore there is no noticible performance hit from a users perspective, because the interface has already encumbered all of the information. I've seen this data system in several large applications, and the user performance hit was negilible when the indexing was done correctly.

The other added benefit is that this is expandable to any application, the developer only needs to be worried about the views of the information, and the addition of the data elements to an existing table. Over time I hope to include the automation of these items too.

For a little further investigation into what I am describing, please download the zip file. The data management application uses CF, SQL Server and Mach-ii. It is quickly put together for further expansion an idea. Please let me know of any bugs, I'll try to fix them.

Bringing clarity to the which language to use decision

I've got to say a large thank you to Sean for pointing out the TIOBE Programming Index. Determining which tool to use for future development, without cutting oneself on the bleeding edge is not an easy task. Especially, since I'm among the risk adverse managerial club.

I think the problem is larger than just choosing a single language to do-it all. My view is that there are certain languages which excel for a certain task at hand. The trick to the mix is choosing the right tool that will help to provide the best technical solution to the business problem at hand. I've been of the opinion for a while that it isn't a good practice to restrict a shop to a language, just because everyone else needs to know it. If you want to bring a tool or technology in house, show others how to utlize it and its benefits to certain situations. Also analyze it across the business. Having flash forms is really nice, but is it worth having to have an individual update locked down workstations for the next six months?

One just really needs to have a good set of the basics in play for any business solution..

People working as a team.
A standardized source control mechanism is a must. The key here is ensuring everyone understands it.
A specification or set of requirements is needed next.
A centralized issues database is a pretty much must have.
Happy programmers and application analyst with the desire to learn new skills.
A reusable testing plan with lessons learned from all previous tests.
Tester comprising the people who will use the application, system, and/or tool.

After that the technology, which is alway changing, isn't about choosing only one. It become about using the best tool to get the job at hand completed.

To spread Holiday Cheer at Work or Not to Spread?

Is the spreading of holiday cheer or lack there of an indicator of morale? I've received the customary invitations for this year, the office christmas party and the team christmas party. Last year I also received these same invitations as I had the years before. The past couple of years, there have been little to no decorations placed up by anyone. A christmas card here or there. A santa hidden in a cube. What I don't know if that is indicative to this environment or standardized practice now. The other item that has me waffling between indifference and massive cheer spreading is that to what degree is it correct to spread the holiday cheer?

The Tango Project

One item worth checking out is the Tango Desktop Project from Steven Garrity and friends. The project is an effort to standardize and make a consistent UI for the desktop and the web. A great effort to see in action.

One table to contain all lookup data

Between the world of CRUDs and maintaining lookup data, programming for multiple tables can be painful, and in many causes does assist with performance at all.

So in some scenarios is maybe a good idea to contain all reference data within one single table, ie tblReference.

This design technique has a couple of advantages:

1. First, it is very extensible when we come to the conclusion that we need to add a new type of reference data. Let’s say we just wanted to add the doctor numbers to this application.
2. It is easier to maintain from a coding perspective. Imagine only one business model to handle all reference data with a gateway, bean, and DAO.

Only see what you want to review.

Until now, we have been creating reports that see everything all at once. It's a big picture view, but let us say we wanted to only see a smaller snapshot. For example, instead of seeing all patient visits in one report, we just wanted to see the ed patients admitted during the last week. Crystal reports offers a cool way to apply constraints or filters to reports through the Select Expert.

The Select Expert is a GUI enabled way to create a where clause for a report. We can take our drill down example report and apply a selection to it. Under the report menu, select Selection Expert. For our example, select orders over a quantity of 100, so select Order_Details.Quantity and click ok.

The Select Expert window appears, please take a moment and notice that the requested field, Order_Details.Quantity is the first tab. Now it is time to apply our algorithm, we select is greater than 100, as indicated below. Once we have finished, we can click ok.

Crystal Reports then request if it is ok to use the save data set, or if we want to refresh. Since Northwind is a stagnant data set, refreshing doesn’t harm any analysis.

Now we see the report with our filter applied, however for shits and giggles, let us see what happened in the year 1997. To add a new constraint, click on the selection expert, and click on the <New> tab.

Choose the order date field and click ok.

So for the order date, we want all orders between January 1st, 1997 and January 1st, 1998. Once you are set, just click ok and we are good to go.

To finish up this example, by adding a selection for a string. There are two wildcard characters that are available within the Select expert. ? is a wildcard for just one character, while * is a wild card for many characters, these are available when using the is like and is not like filter. We are going to look for all products that are like Pa, but remove the year they were ordered by selecting the order date tab and clicking delete. Now click on the <New> tab, and add our filter on product name.

When we get back into the report, we can note that we have not sold any products starting with PA and have a quantity above a 100.

Drill Downs, the best of both worlds.

To be frank, sometimes there is just an enormous amount of detail contained within a report. Now there are detail freaks, like myself, who really get into analyzing and comprehending the detail. But generally a report is to be informative, and yet give the user access to the necessary information. This is where the drill down report mechanism really reveals its power.

How about we create a new report from the Northwind database? In our detail section, use the company name, order date, order id, product name, quantity, discount, and unit price. Because we have several fields, let’s use landscape for our print option. In our report, we can group by company name in ascending order, order date by week, and order id. Once we preview, we should see the following:

So we have all of our details nicely displayed, but to be honest it iss a little too much information right now. Why don’t we hide the details? In the design tab, right click in the detail section. Select Hide (Drill Down OK), now preview the report. To the left of the page, one will notice a group tree. This is a neat feature that is also included in the crystal report viewer active X control, used for viewing crystal reports. The report reader can drill down to the interesting level of data contained within the report.

Group your way to understanding

We have the tools to make an excellent detail reporter, but sifting through all that detail is grueling. In order to make areports usable tool, levels of categorization are needed. Categorizing the information is called grouping.

So let's start off by understanding how to insert a group into a pre-existing report. First, create a new detail report. From the Northwind database, have the fields Employee Last Name, Order ID and Order Date.

In order to create groups, click on the insert group icon. For this report group on the Employee's Last Name. Ascending order is good for our needs. Next click on options.

Under options, I would like to change the group field name. Instead of just the Employee's last name also include the employee''s number. So we choose to use a formula as the Group Name. We can prevent the employee from breaking across the middle of the page, because may we would like to give this report to each employee. So select to keep the group together, which prevents breaking information in the middle of a page. Since we have the possibility to cross many pages, repeat the group header on Each Page. Once the config settings are completed, click the formula icon to setup the formula.

The Formula Workshop Editor appears after clicking the formula buttom. In the code window, select the EmployeeID, concatenate a colon and then concatenate the employee's last name.

Probably by now you are beginning to comprehend the power of groups. So let's review a couple of cool features with groups. For our group we didn't want the employee's listed in alphabetical order. We wanted to specify the order. In our report, undo the last grouping and start fresh. In the InsertGroup dialog, select in specified order as indicated below. The specified order tab appears.

In the specified order tab, under name groups, select Peacock, Fuller, and Davolio. As indicated below. You will notice that there are up and down arrows to the left that indicate how to set the order. Finally an others tab appears, please select the others tab.

Under others we have a choice how to handle the remaining group categorizations, we can standardize them under the same name, we can leave them in their own groups,or we can just discard all others. For our purposes here, discard all others.

Now, when we go back to our report and click the preview tab. One notices that there are only Peacock, Fuller, and Davolio on our report.

Perhaps we wanted to add more than just one group on a report or we wanted to change the settings of our groups. The tool for the job is the Group Expert dialog box. For our report, maybe we would like to see the regions for which our employees are accepting orders. So we can add the customer region to our grouping selection, by highlighting the selection and selecting the single arrow to move this field across. Now we have a primary grouping of Employee Last Name and a secondary grouping of customer region. If we wanted to refine the customer region grouping, all we would need to do is click the options button and the change group options dialog box appears. If we wanted to change the order, it is a simple click of the up arrow.

As we head back to the drawing board, and get an idea of how to group on dates! To start off we create a simple detail report containing the order date, the employee who placed the order, company the order was for, and the order id.

Now we add a grouping on order date, we notice that the default date grouping is for each day. However for this report, try using a weekly period. When we click the drop down combo box, select for each week and apply. As we see crystal reports handles a lot of the grouping logic for time periods with dates for us.

In the real word data often has hierarchical relationship, hierarchical refers to a parent / child relationship. This can be seen in organizations under the manager employee relationship. This can be seen in healthcare, under the labs and reflex testing sequences.

So let's build a report with hierarchical grouping. One of the main items to remember is that eligible fields for a report have to be the same field type as the field that is currently grouped upon. Therefore it would be impossible to group on employee id, and then create a hierarchical grouping based upon windows username.

First, create a blank crystal report connected to the Northwind database.
Next, select the employee table. From the employee table create a report with the last name, title, and birthdate. Insert a group on the employee's ID, but instead of displaying the id, display the employee's last name.

We are now ready to apply a hierarchical grouping to the employee table. Under the reports menu item, select Hierarchical Grouping Options. The dialog box appears select the check box for Sort Data Hierarchically, and set the parent id field to whom the employee reports. Also set the Group Indent to .25 inches. Our completed dialog box appears below. When we preview the report, we can see the results.

The Brain Drain

A company's most vital asset is the tacit knowledge. Knowing what has occurred, why items are the way they are, and applying the ways to resolve and fix these problems in the future. This unwritten, undocumented operational knowledge is often what runs the business.

The other item is that not everyone knows this knowledge, only a couple of people here and there. The well of operational knowledge continues to run dry as companies change. The really surprising item is that even though this is a continuous process, there are few to minimal proactive measures to keep the tanks filled and not lose this vital knowledge.

It is a very expensive endeavor to try to gleam the business rules from lines of code.

One simple proactive solution would be to encourage sharing knowledge organizational mentality instead of the alternative of hording the knowledge within oneself for job security.

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.

From Maintenance Programmer to Developer

One of the items I've been struggling with is how to change an organization's skill sets from MainFrame cobol to a newer set of technologies. Not to mention choosing which technology. Since I'm quaffling in the land of indecision for a programming tool, I've started out on the other end of the house with reporting tools. Today is hopefully the first of many instructional lesson type posts on Crystal Reports XI and Business Objects Enterprise.

Off to the Report Wizard

Needless to say it's a bit clunky having to move an position all those reportable objects by oneself. But let's keep it simple with the report wizard.

Click New Report under the File menu item, select standard new report. The standard new report creation wizard window will appear. First select the tables, you would like to report off of. We can choose the Customers and Order tables. When you are finished click Next.

The Wizard then allows you to link the tables, since this is already established in the Northwind database, click next.

The next step is to select the fields one is going to need to report. When completed please click next.

If you would like to Group the records, indicate that here. When you are finished, once again hit next.

If there are any filters that need to be applied, this window of filtering fields is the place to complete this task.

Finally to nicely finish off the report, there are some predefined templates available with crystal reports, I choose the blue template for this exercise. We will go over the manner in which to create company specific templates later.

The end result is a quick report that just needs a little formatting.

The Hard Way

Let's start off by creating a report from scratch.

First from the special fields, select the report title, data date and place those in the report header section. Next take the Order Date, Product ID, Unit Price, Quantity and Discount fields into the detail section. Notice how the field title always appears in the Page Header section. Finish off the report by placing the Page N of M in the page footer. This way a count will occur for each page out of the total pages.

To preview the report, hit the print preview button. Those of us familiar with previous versions of crystal reports will miss the lightening bolt. Print preview has similar functionality.

Introduction to the Crystal Reports Designer

In the report designer, please notice that a report is made up on sections.

• Report Header section is generally used for the title, data creation data, report number or other information that one wants to appear at the beginning of a report.
• Page Header section is used for information that needs to appear at the top of each page. An excellent use of this section is to display the field titles for a report.
• Details section contains the records for the report. The detail section is printed once for each record.
• Report Footer section is used to display information desired at the end of the report such as grand totals and counts.
• Page Footer is an excellent place to put any information that will display on the bottom of each page. For example the page number

Another feature/function that is available are the report design explorers.

• Report Explorer provides a view of each section and the fields and or objects contained within that section,
• Field Explorer contains the database fields, formulas, SQL expressions, parameters, running totals and special fields. All of which are available for use in the report.
• Repository Explorer is a view of the objects contained and available from the Crystal enterprise report repository.

Your First Report

Your first report

When crystal reports firsts opens up, you will see the following screen.

For this lesson, please select the Blank Report under new Reports.

Accessing the Data
Crystal Reports has a cool tool for managing all data access for you, it is called the Database Expert. The Database Experts allows you to view all datasources in one location.

I'm going to create a New Connection to my Northwind Database. Double Click on the ODBC(RDO) and the following window will appear.

This is a listing of all ODBC DSNs available to this machine and user. Select cnNothwind and hit next.

Enter the password of the reporter account and click Finish.

Now in the Database Expert, you will see the Northwind connection. If you click on it under dbo, the tables, views, and stored procedures associated with the Northwind database will appear. Highlight Views and click the plus sign in front of the views.

Select the Sales_Totals_by_Amount view and click the arrow to move it into Selected Tables. Click OK when completed.

The Database Expert gives you an opportunity to view current connections when access a previously written report. In addition you can use favorite connections, which keeps a history of your most frequently utilized database connection. Finally for recently utilized connections, check out the history folder in the Database Expert.

Now let's say, we needed a report where we had to check at the raw data, not the nice views. Go back into the Database Expert, and in the Northwind database select two tables, the Order_Details and Orders tables. Please click OK.

Suddenly instead of finishing the job, off the Database Expert appears with a new tab, Links. Links are how one table relates to another table.

Join Types are a strategy for relating the data in the tales so that the query results make sense.

• An Inner join is the standard type of join. The result set from an Inner join includes all the records in which the linked field value in both tables is an exact match.
• A Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table.
• The result set from a Right Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary table.
• A Full Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table, and a row for every record in the lookup (right) table for which the linked field value has no match in the primary table. This is a bidirectional join resulting in the display of all records from both tables.

For this example, the Order table links to the Order Detail table. There maybe one Order with many detailed products associated with it. Since we have our primary keys and foreign keys defined within the database, these links have been created for us. Please Click OK.

Now notice in the field explorer, that the Order_Details and Orders tables are ready for you to utilize.

Setting up an ODBC Data Source

For this reporting lesson, We will be connecting via ODBC, so we need to set up an ODBC connection.

From the administrative tools section, click on DataSources (ODBC). You should see the ODBC Data Source Administrator. In the ODBC Admin, you have a choice of making the connection a User Based, System Based, or File Based.

A User Based connection only works for this user and this machine. A System Based connection works for all users of this machine. A File Based connection will create a file on the network with the desired information, for all machines to use. For this example, we will be using a System based connection.

Accessing Data with Crystal Reports Designer

Reports and Data are dependent on one another. If there wasn’t data, there really wouldn’t be any reason to report. Reports are basic ways that people can get at their data in an understandable format.

Understanding Data and Data Sources

A Database is a place to manage data within your company. SQL is the programming language that allows one to perform operations on that data.

Most if not all database tools have some common components:

• SQL is the programming language.
• Tables are tasked with containing all of the data stored within a database.
• Views are for displaying the data in a customized format for easier usability. Views hide the unnecessary information from the user.
• Stored Procedures are pre-compiled queries, they are also used to set requested values which are passed into the query. For example a query that reports requested days of for each individual. A parameter would be the individual

Crystal Reports is an excellent tool for reporting data contained within a database.

There are a variety of database tools available. The types are very diverse on the low end, one can find MS access and on the high end there is Oracle. There are open-source databases, such as MySQL, and middle of the road, such as MS SQL. The most common thread across all database access is accessing the database.

With crystal reports there are two very common ways to access a database. One can directly connect through direct access or use Open Database Connectivity, ODBC.

Just another interface

I was in another meeting last week discussing getting a field, total charges, from the hospital billing system to the auditing tool of the coding system. We are right now in a starting position for this type of endeavor, we don't have centralized charging ororder entry yet, so the only place to obtain centralized charging is from the system that has all the data. We have to get from point a to point b in a timely manner, my suggestion was to interface the data through the integration engine. The interface analyst and hospital billing analyst just want to add another point to point interface.

It's just another interface

Interfaces are data feeds, and they are relatively straightforward. In some organizations, this is a strategic plan that is well thought through with consideration to architecture and interoperability. In others integration is actually an afterthought, an added service. One has the application, the data, and the integration. Sentiments like the above over a long period of time result in one largely unplanned environment that is basically uncontrollable and unmanageable. Especially with the view of that everyone needs all of the data elements from everyone else.