One of my customers called me today with what seemed like a simple request. He wanted a list of all of his BO Enterprise users. Now I don’t do much with Enterprise but it seemed so simple that I told him I would do a bit of digging.

The first thing we found was that this data is in the BOE CMS InfoStore.  The CMS data is stored in such a way that the only way to access it is either through the SDK (serious programming) or the free “Query Builder” that comes with BOE.  The next thing we found was that this Query Builder comes with very little explanation or documentation.  Fortunately I was able to find a link Continue Reading »
Business Objects Query Builder

Last month I put out volume 4 of my Expert Techniques for Crystal Reports, adding another 30 articles to my growing collection of tips and tricks.  But writing up the summary for these articles is always a challenge, because many techniques can be used to solve several different problems.  My chosen description for an article may not sound applicable when a user is searching for a specific solution.  Google makes searching easy, but only if your search terms match my description.

So say you need to know how to make a dotted line that automatically fits  between two columns on a page – like you might see in a table of contents between the chapter and the page number.  You might search for dotted lines, or maybe “table of contents”, or maybe TOC, etc.  Up to now I couldn’t think of an easy way to list ALL of the options for an article’s subject.  Then one of my students asked me if I had an index for my course materials – and the light bulb went on.  So I have started a master index for all of my guides.  The beauty of the index is that I can list the same article under any number of headings or descriptions, making it more likely that a user will find what they search for.

So far the index includes all 4 volumes of my Expert Techniques, as well as my Expert’s Guide to Formulas.  Over the next few months I will add more guides until the index is comprehensive.   There is an HTML page with all entries (so the search engines can index it) as well as a PDF version that you can download to use off-line.

Creating Crystal Reports from PeachTree accounting data has become a specialty of mine.  But getting data out of PeachTree has always been something of a challenge because the only way to access the data has been using the proprietary Btrieve query enginge rather than the more standard SQL method.  But I just helped a customer upgrade their reports to PeachTree 2010 and was pleased to see that this latest version of PeachTree now accesses the data using OLEDB instead of Btrieve.

Since OLEDB is SQL based, this means Continue Reading »
PeachTree finally allows SQL (OLEDB)

Here is a simple solution to a challenge that came up today from one of my customers.  It has been kicking around in my head for a while but I finally had an excuse to play with it and test it.  I figured some of you might also find it helpful.

If you are like me, you find many uses for cross-tabs in your reports.  So say you a have a cross-tab by Customer (row) and by Month (column).  But you want each Customer row to start with three columns of identifying information rather than just the normal one column.  You could combine the three fields in a formula but then they don’t always align well as columns.  Today I realized that there is a simple way to get true columns, and it works in all versions at least back to v8.5. Continue Reading »
Cross-Tabs with multiple initial columns

I found some clever SQL that allows you to strip the Time off of a DateTime in a SQL Server Command.  This has several uses but it makes it simple to use normal Date parameters with DateTime fields and not having to worry about losing the last day due to the times being after midnight.    The SQL would look like this:

DateAdd( day, datediff( day , 0 , YourDateTimeValue) , 0)

Alternately you could use this:

Convert(date, YourDateTimeValue ))

This link describes several other methods to do the same thing.  Some might work better than others in your environment.

If you need some guidance when it comes to using SQL expressions or commands in your reports, you should get my Expert’t Guide to SQL Expressions, Options and Commands.