I was looking for a list of the tables and fields in SAP B1 and found a great site:

http://www.saptables.net/

The person that maintains this site also maintains a similar site for the JD Edwards application

http://www.jdetables.com/

So while I was at it I thought I would see if anyone had done something similar for Great Plains. I found a few sites.

http://dyndeveloper.com/DynModule.aspx (some info members only)
https://victoriayudin.com/gp-tables/
http://gptables.azurecurve.co.uk/

This type of information is usually available directly from the vendor, but often those resources are restricted to licensed users with a support account. The sites above are open to the public. If you know of has a similar site for another application, let me know and I will add it here.

I do lots of work using GoToMeeting and often the screen I see is reduced in size, making things hard to read. It can be frustrating when trying to highlight a word without including characters on either side. So I have developed the habit of using double-clicks to select words. I find that not everyone knows this trick.

When selecting normal text, like in MS Word or in a browser, you can double click on a word to select the entire word. In many applications the word selected also includes an extra space on the end. This allows you to move the word to another part of the sentence without having to add and delete spaces.  (In a few applications you can even use a triple-click, which selects an entire paragraph. This works in most browsers and MS Word and Wordpad. )

I also use double-clicks when working in the Crystal Reports formula editor. If you double-click on literal word (within quotes), Crystal will select that word (but no trailing spaces). If you double click inside a field name Crystal will select either the table name (before the period) or the field name (after the period).  If you double-click on the name of a variable or function, Crystal will select that name with no spaces.
I find this very helpful when copying and pasting values.

I also use Notepad++ to write both Crystal formulas and SQL commands. When you double click a word in Notepad++ it not only selects that word and highlights it; it also highlights every other instance of that word in the document. This makes it easy to see where a specific table, field or value is being used.

I have written before about copying objects, both within a single report or from one report to another. Below are some new tips for copying values instead of objects.

Copying literal values from preview mode:
Often when validating or troubleshooting a report I want to take a specific item from the preview screen (an invoice number, customer name, etc) and look it up within the application. I have learned that you can copy a value directly from the preview screen and paste that value into another application. Or, I may need a specific value from preview to use as a literal value in a formula. All you have to do is select the value you want and then copy it and paste it into the formula. This can be especially helpful when the value is long or difficult to type correctly.  All three copy methods work (Ctrl-C, right-click or the edit menu).

The value that gets copied to the clipboard will match what you see in preview. Numbers and date values will be pasted in the same format as they appear in preview. Fields that are not large enough to display the entire field value will still copy the entire value of that field. So truncated text or large numbers that show as pound signs will both paste the full value.

Copying object names from design mode:
Often when writing a formula I find that I need to insert a database field or formula field that is sitting on the report. Rather than trying to remember the name of the correct field, it is possible to copy the name of the object while in design mode. Just select the field you want and copy it using one of the three methods mentioned above.  When you copy a field in design mode you are copying the name of the field.

You can then paste the name into the formula editor and put curly brackets around it.  Crystal should recognize the field. If it is a formula field your pasted value will include the @ symbol. I have found that the most efficient method is to first type the open curly bracket. This opens the auto-complete list with names of all available fields. Then you past in the copied field name and Crystal selects the correct field from the list. When you hit enter it adds the closing bracket automatically.

This is especially helpful in complex reports when you have many tables and fields, or when the field names are particularly long and hard to remember.

I write many formulas, and the more formulas there are in a report the more import it is to name them well. About 10 years ago I wrote an article with tips for naming formulas. I will often come back and rename formulas when I find that the original names aren’t clear enough. One of the main points of that old article was “don’t be afraid to rename formulas”.

But there is one thing I didn’t mention in that article. You get a slightly different behavior if you rename formulas while you are inside the formula editor as opposed to if you rename formulas in the Field Explorer. The difference has to do with the formulas being listed in alphabetical order. When you rename a formula in the Field Explorer that field gets repositioned immediately based on the new name. But, when you rename a field in the Formula Editor the field doesn’t get repositioned immediately. The formulas won’t be resorted until the next time you modify and save a formula’s contents.

Say I have 50 formulas in my report and I want to rename 12 of them from “Payables 01”, “Payables 02”, etc. to “AP01”, “AP02”, etc. If I am in the Field Explorer and rename “Payables 01” to “AP01” that formula (and my cursor) would be immediately moved to the “A” section of the formula list. To rename “Payables 02” I would have to pick up the mouse and scroll back to the “P” section of the list to find it. That formula would also move immediately to the top of the list, and so on. But, by renaming these fields in the Formula Editor I can rename the first formula, hit “Enter” and simply use the down arrow to move to the next field. All 12 fields stay together during the renaming process and they don’t get alphabetized right away. When I am ready to alphabetize the list again I select any formula and add a space to any line and then hit “Save”. The list gets alphabetized and the 12 renamed fields move to their new positions.

Note – you can’t just click save. You have to modify a formula and then save it. That is why I typically add a space and click save.

So next time you want to rename a group of formulas you can use this trick to save a bit of scrolling.

In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.

My solution was to create a “cheat sheet” grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):

• Microsoft SQL Server
• Oracle
• MySQL
• Pervasive
• Progress
• PostGre SQL
• MS Access

I have empty columns for Providex and DB2. They are empty because I haven’t had any recent projects on those platforms and don’t have an environment where I can experiment.

I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated.  And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.

I wrote a long time ago about the “Stealth” subreport. This is an invisible subreport that runs in the background and provides a value to the main report as a shared variable. But if you have a few of these subreports in the same container report it can be difficult to do troubleshooting. To see what the subreports are returning, you have to go into each of the subreport’s key sections to unhide or unsuppress them.

But I received a suggestion from Gordon Portanier that makes it simple to activate and deactivate “stealth” mode in all of the subreports in one place. First you write a formula like this and place it in the report header of the main report:

WhilePrintingRecords;
Shared BooleanVar Stealth;
Stealth := True;

Then you go to each of the sections in the subreport(s) that you want to turn on and off when troubleshooting. Instead of hiding or suppressing those sections, you put in a suppress condition that says:

WhilePrintingRecords;
Shared BooleanVar Stealth;

As long as the first formula isn’t modified, those sections should stay suppressed and the subreport should stay invisible. When you want them to appear you comment out the bottom line in the first formula, the one that assigns the value TRUE to the variable. Because Boolean variables are FALSE by default the variable will revert to FALSE and all the sections where you used that condition will now appear.
So, thanks again to Gordon Portanier of Crystalize in Canada for sharing this suggestion.

There is one thing you have to watch out for when you use the “Select Distinct Records” setting to eliminate duplicate records. There is often a difference between what you consider a duplicate and what SQL considers a duplicate.

When you activate this feature (Database > Select Distinct Records) Crystal changes the first line of the SQL query from:

Select

to

Select Distinct

This causes the SQL engine to look for duplicates within your raw results.  To the SQL engine, duplicates are any records that have the exact same value for every column in the results or every field listed in the SELECT clause of the query. This will be every field used by the report in any way.  You can see which fields these are by looking for the check marks next to the field names in the field explorer. If the database finds multiple records with the exact same values for every field, it will eliminate the extras and return only one of the duplicate records.

So here is the risk. Pretend that you have three records in the results that were mostly identical but had one field that was different, say 3 different timestamps. As long as the report doesn’t use the timestamp field then the “Select Distinct” will return only 1 of the 3 records. But as soon as someone decides to use that timestamp field, even if they just place it on a section, the SQL will see three unique records. Your report will then start to show all three records, even though you might consider them duplicates.

So if you are using “Select Distinct”, make sure you test any changes carefully, especially when they involve adding new fields to the report.

After my newsletter went out I heard from a user who had the “cascading” failure problem and received a solution from SAP. She was told to go into each subreport and open “File > Report Options”.  There she was told to take out the check mark for the feature “Show Preview Panel”.  She started doing this with all of her subreports and she never saw the problem again.

I suggested this fix to two other users that had experienced the same problem.  It solved the problem for one user but not the other.  So it may not be a sure fix for everyone, but it certainly is worth trying. Please let me know if this works for you or if it doesn’t.

That is also something that I didn’t know, that subreports can have “report options” that are set different than the main report.  I had never checked but just assumed that the properties in “File > Report Options” applied to the entire container.  Always good to learn something new.

Years before I started my blog I was creating online content in forums like Tek-Tips.com. I am still an occasional contributor there but years ago I was spending an hour or more every day answering questions and generating thousands of posts. It isn’t unusual for me to do a web search and find my own answers from long ago in the results.

Recently, I was trying to see if I had ever posted my formula for taking a date value and finding the last day of that month. I found one example in a TT post from 2001 and reading that thread made me smile. The thread is all about finding the last day of the month and the answers are pretty complex. They were even discussing the 100 and 400 year exceptions for leap year.

I was late to the party, but my formula only had 4 lines. This is the same formula in 2 lines:

Local DateVar Last := Date(DateAdd( 'm', 1 , {Orders.Order Date} ));
Last - Day(Last)

You put any date value (database field, parameter, CurrentDate function, etc) in place of the field {Orders.OrderDate} and the result is the last day in that month. The comments below show two alternate formulas submitted by some of my colleagues that take different approaches.

This tip was included in Vol. 4 of my Expert Techniques series. Each volume in the set has 30 similar tips and they are all free. The full list of topics is here.

I haven’t written many posts on SQL topics. But recently it seems that much of my work involves writing reports based on complex SQL queries. Part of this involves converting Crystal formula logic into SQL syntax. The fun part is that the syntax varies from one flavor of SQL to another, especially for date calculations.

So today’s post is specific to SQL Server syntax, one of the most common flavors. I often need date calculations that are relative to today’s date. In a Crystal formula I would use CurrentDate. In SQL Server syntax the closest equivalent function is GetDate(). I have used it for quite a while assuming that it is the same as CurrentDate. But I just recently discovered that GetDate() includes both the date and the time, which changes things. If you run a report at 2pm on March 8th and the WHERE clause says:

WHERE orders.Date >= GetDate() -2

you might expect to get all the records on March 6th, but you probably wouldn’t. If your Orders.Date field doesn’t store times you would not get any records from the 6th. If that field does have times you would get records, but only those after 2pm on the 6th. So if you want the calculation above to behave like the CurrentDate function in Crystal you have to remove the time from GetDate().

I found two ways to strip off the time off any DateTime value. The one I see listed most often is this one:

DateDiff(d, 0, GetDate())

or this one submitted by Ralph Wahlert:

Cast(GetDate() as Date)

These works great in a WHERE or ON clause, but have one flaw. If you include them in your SELECT clause so that you can show the date on the report the first will appear as a number in Crystal and the second will appear as a string. To use them in the SELECT and have them as dates, you have to convert them to DateTimes:

cast(DateDiff(d, 0, GetDate()) as DateTime)
cast(cast(GetDate() as Date) as DateTime)

These two work in the WHERE/ON clauses as well as the SELECT. In each case they will return a DateTime value but with the time portion set to 12:00am.