Wednesday, June 29, 2011

Crystal Reports : Displaying two summaries in a Cross Tab

I often find is helpful to display both a SUM and a COUNT within the rows of a Crosstab object. A recent client needed to see both the SUM of {INVOICES.AMOUNT} and the COUNT of invoices based on {INVOICES.DATE}.

After adding your Crosstab as normal, go into the "Crosstab" tab under "Format Crosstab"

1. Add a row to the crosstab for a SUM of {INVOICES.AMOUNT}
2. Add a row to the crosstab for a COUNT of {INVOICES.INVOICENUM}
a. What we want to count here is the uniqueID field in the INVOICES table
3. Hit OK
4. In order to make the values stand apart in the Crosstab cells, I like to change the text alignment (left and right) of each summarized field.

Crystal Reports : Automatically derive dates based on "Last Week"

This is a nice trick for those of you that want to cut down on parameter input for your end users.

Suppose a report needs to only print records with dates from last Monday through Friday. You could of course provide a date range parameter and let the user choose for each printing of the report.

You could however, use the nifty LastFullWeek function in crystal to return these values automatically.

The first thing to keep in mind here is that LastFullWeek will actually return a Date Range, NOT a Date Value. In order to see what dates LastFullWeek is actually returning, we will need to create two formula fields. {@DateStart} and {@DateEnd}.

The code for {@DateStart} : MINIMUM(LastFullWeek)
The code for {@DateEnd} : MAXIUMUM(LastFullWeek)

Drag those two fields onto your report. You'll see that in the Crystal Universe, the LastFullWeek starts on a SUNDAY and ends on a SATURDAY. So if we just want Monday through Friday, we need to do some tweaking.

The code for {@DateStart} : MINIMUM(LastFullWeek) + 1
The code for {@DateEnd} : MAXIUMUM(LastFullWeek) - 1

Now the formulas are returning Monday to Friday and we can use them in our Selection Criteria thusly (assuming we are looking at a field called {HISTORY.ONDATE})

{HISTORY.ONDATE} IN {@DateStart} TO {@DateEnd}.

Try it!

Crystal Reports : Inserting the Print Date

When printing the same report frequently, it helps to have the Print Date somewhere on the report. That way, you can always be confident of which report version you are looking at.
Within your Field Explorer, find the Special Fields group. Within that group, find and insert the Print Date field into your report. This field is automatically updated every time you refresh the report. Another popular Special Field is Page N of M, which can be inserted into the report footer to easily provide a page count.

Crystal Reports : Format multiple objects

You may select multiple fields in the Design View by Control – Clicking multiple objects. To easily apply formatting to all selected fields, right-click on one and select Format Objects.

Crystal Reports : Using a specified Group Sort Order

When selecting sort options for your Group, select Specified Order. Notice that a Specified Order tab appears when you do so. Simply choose in which order you’d like your Group printed. This is helpful in situations (for example) when the Group named "Thirty Days" must come before "Sixty Days", yet "Ninety Days" must be the last. Note that neither Ascending nor Descending order will solve this problem.

HEAT – Catalog File Not rebuilding

Firstly, what is a HEAT Catlog File? The HEAT catalog file is essentially an encrypted file that stores HEAT screen designs. The catalog file is rebuilt each time a new edit set is committed. HEAT references this file for the new screen layout and only passes data back to the SQL server.

Whenever applying changes to your HEAT system – i.e. adding fields/ deleting fields, changing form colors, etc. the catalog file needs to be rebuilt on the HEAT Servers and also on end user workstations. From time to time the catalog file may generate a message indicating “unable to build catalog file” Typically, this is due to the fact that something (an application) is using the file.

Unable to rebuild catalog file on Server:

In most cases the services running either one of the following or all of the following may be locking the file – Heat Business Rules, Heat Messaging Center, Heat Survey, Heat Self Service, Heat WebUi, Heat LDAP Connector.

Navigate to Control Panel > Services and look for any services that start with the word HEAT. Stop all services.

Also close down any applications on the server – eg call logging (if you were logged in). This should release the file and allow the catalog file to be rebuilt.

Unable to rebuild catalog file on workstation

The end-user in most cases has HEAT CallLogging or Heat Alert open. The user must click on “file and exit” and then launch Call Logging again – this will rebuild the catalog file.

HEAT Self Service – Configure Call Groups in Heat Self Service

Call Groups can be created to manage and organize issues in HSS. You can create Global Call Groups,  Customer Type Call Groups, or Personal Call Groups for individual users.

Launch Heat Self Service Web Server Configuration Tool, Navigate to the Customer Type (in the left pane)

  1. Click the audience for whom you want to create a new Call Group:
  2. Click Add
  3. In the Table Name drop-down list, select the table containing the field you want to use in your expression. When you select one of the tables, the available fields for that specific table appear in the Field list.
  4. In the Field list, double-click a field to add it to the Boolean expression (for example, CallStatus) in the Expression Text field.
  5. In the Operators list, double-click an operator to add it to the Boolean Expression (for example, =).
  6. Click Browse Field Data. The Browse data dialog box opens for the field you selected. All available options are listed in grid view. Select a value, then click OK (if needed you can filter the list by typing a value in the field next to the Filter button, then click Filter).    Continue to add expressions as needed to define this group:
  7. Click AND to add another statement. Using AND to join expressions means results must meet the criteria indicated by all expressions. For example, CallLog.CallStatus='Open' AND CallLog. CallType='Hardware'.
  8. Click OR to add a varying statement. Using OR to join expressions means results must meet any of the criteria indicated by the expressions. For example, CallLog.CallStatus= 'Reopened' OR CallLog. CallStatus='Open'.
  9. To define a sorting order for the Call Group, select the Table, then select the field(s) by which you want to sort the group; for example, if you select CallID, all records in the group are listed in ascending or descending order (you specify) by their Call ID numbers.
  10. Click the Check button (at the bottom of the page) to verify that your expression text is correct (if correct, a number indicating the number of Call Records found will display).

Click Save (at the bottom of the page).

Note: If you want to move the group to a different audience category (from Personal to Global, for example), click the Move group button. The Create new Group dialog box opens, enabling you to move the group.

HEAT – Adding a Live Image to a form


  1. Begin by creating a Binary field in the table.
  2. In the Table Editor, click the New Field Wizard button:

    - Type a name for the field, such as “ScreenCapture”. Add a Description if desired

    - Select the Binary Type

    - Click Next. Accept all subsequent defaults and continue to click Next. In the last wizard screen,
       click Finish.

    - Click Add Form to Field

  3. In the Table Editor, click the New Field Wizard button:
  4. Click the Edit Set tab.
  5. On the Form, right-click the field, in this example - “ScreenCapture” and select Attributes. The Attributes dialog box opens.
  6. Click the Image tab, then select the Live Image check box.
  7. In the Field drop-down list, select the binary field you created.
  8. Click the Advanced button to specify limitations for uploaded images. In Upload Options dialog box, you can limit the image size and image file size to optimize performance, and specify if an uploaded image will also become an attachment to the record:

    Maximum image dimensions - The maximum image size. The default maximum for optimal system performance is 640 x 480.

    • Compression Level - The higher the compression level, the lower the image quality, but higher compression levels conserve space and optimize performance. The default is 50%.

    • Maximum image file size - The maximum file size in bytes. The default maximum for optimal system performance is 1 MB (1000 KB).

    • Allow Original as Attachment - If the image is uploaded (rather than pasted), the image will also become an attachment to the record.

  9. Click OK to close the Upload Options dialog box.
  10. Close the Attributes dialog box.
  11. Save and Commit the Edit Set. HEAT users will be able to upload or paste an employee photo when creating or editing a call Record.

5 Things I Don't Understand About The Deficit

Why, Frodo, why?  My new Huffington Post piece.

HEAT – Create a Custom Toolbar

Call Logging allows you to create custom toolbars and add buttons to the toolbar as you need them. An individual user's toolbar is associated with the user and is available at any network workstation the user logs onto.

To Create a Toolbar:

  1. From the main Call Logging window, select Edit>Toolbar>Customize. The Customize dialog box opens.
  2. Click the Toolbars tab.
  3. Click New. The New Toolbar dialog box opens.
  4. In the Toolbar Name field, type a name for your toolbar.
  5. Click OK. The new toolbar is listed in the Toolbars list.
  6. Drag the new toolbar to the desired location to dock it.
  7. In the Customize dialog box, click the Commands tab.
  8. From the Categories list, select the category containing the button you want to add. The associated buttons appear in the Commands list.
  9. Drag and drop the desired buttons onto your new toolbar.
  10. Click Close.

To Remove a Toolbar Button:

  1. Select Edit>Toolbar>Customize. The Customize dialog box opens.
  2. Click the Commands tab.
  3. Drag and drop unwanted toolbar buttons off of the toolbar.
  4. Click Close.
To Delete a Toolbar:

Note: You can only delete toolbars you created.

  1. From the main Call Logging window, select Edit>Toolbar>Customize. The Customize dialog box opens.
  2. Click the Toolbars tab.
  3. Select the toolbar you want to delete, then click Delete.
  4. Click OK to confirm the deletion.
  5. Click Close.

GoldMine : Change the "default" search

Did you know that you can set the default search field when bringing up the Contact Search Center?

1. Select Tools | Options | Lookup tab
2. Change the "Default Lookup Field" to either Company, Contact, or to Remember what you last used

You will need to restart GoldMine for the changes to take effect.

GoldMine : Customizing search columns

Did you know you can add columns to your Search Center in GoldMine?

1. Bring up your Search Center.
2. Click on the "Columns" button, then on the "Column Selection" tab.
3. To add a column, simply double click it from the left-hand list to add it to the right-hand list.
4. Ok your way back out.

Your newly added columns should be immediately viewable within the Search Center.

GoldMine : Attaching a file to a Knowledgebase entry

Many of us use the GoldMine knowledgebase to track business related information. Employee handbooks, driving directions, you name it.

Did you know that you can attach any file to a Knowledgebase entry?

1. Select Go To | Knowledgebase from the top level GoldMine menu
2. Find the Knowledgebase entry you wish to attach a file to
3. Right click directly on the Knowledgebase entry within the left-hand pane
4. Select "Attach File"
5. Then simply browse out to the file in question, then Ok your way back to GoldMine

Pro Tip : You can launch the attachment by clicking on the "Paperclip" button on the top toolbar within the Knowledgebase.

GoldMine : "Blanking" unused Detail record fields

Some of you have created your own "Detail" records. These can be useful when tracking "many to one" items for clients.

But if you're not using all of the "extended fields" within the Detail, you can remove those fields from the Detail record itself.

1. Within the Details tab, right-click and select "New"
2. Select the "type" of Detail you wish to modify
3. Click on the Setup tab
4. For each field you'd like to remove from the detail record, simply enter in a double asterisk (**) into each Fieldname label
5. Ok your way out, and answer "Yes" when prompted by GoldMine to save your new field labels

GoldMine : Creating a userdefined Phone field

This actually came up during our live GoldMine presentation at Villanova university.

If you create a new userdefined field and use the word "PHONE" in the FIELDNAME, the field will automatically format any inputted phone numbers.

1. Create a new userdefined field called PHONE4
2. Rebuild your database
3. Now, entering 5556667777 into the PHONE4 field results in (555)666-7777

Pro Tip : Always omit the first "1" in a long distance number in GoldMine, else it won't format it correctly.

Monday, June 27, 2011

Efficiency Tip: Customize Your QuickBooks Icon Bar

If you’re like me, you use as many shortcuts as possible. You’re always a quick click away from the window you need.

To similarly speed up your access to common QuickBooks functions, you can easily customize your QuickBooks icon bar.

Just right-click on the icon bar (located right below the menu for File / Edit / View…) and click Customize Icon Bar…

You can add, edit, or delete from a lot of potential icon bar shortcuts. In QuickBooks Pro 2011, there are seventy shortcuts to pick from!

You can reposition the active icons by clicking and dragging the little diamonds to the left of the choices. You can create visual groups by adding or moving separators. Great!

Set it up so that your most commonly used functions are visible on the icon bar all the time. Delete ones you rarely or never use (you can always re-add them later if you want to.)

If QuickBooks has the capacity to make it more convenient for you, why not take advantage of that?

QuickBooks: Undeposited Funds

A new client that just started her business wanted to know why I discussing the Undeposited Funds account. She said that she does not yet have many clients, and therefore very few payments. So, what is the harm in having the payment go directly into the bank instead of Undeposited Funds?

My answer is to think about the future! Believe that there is soon going to be a time that multiple checks are being made on one deposit ticket. Currently, if there is only one check/payment deposited in the bank by itself, then it is fine to have it go directly to the bank vs. using the undeposited funds account.

However, when there are more than one check/payment for a deposit and they all get recorded directly to the bank register, you’ll have a hard time reconciling your bank account.

The goal is to get your deposits in QuickBooks to exactly match up to the deposits on your bank statement. That’s why undeposited funds is such an important feature.

Let’s say you deposited three checks together – one for $100, one for $200 and one for $300. The bank is going to show one deposit for $600. You don’t want to show three separate deposits in QuickBooks, you want to show one deposit for $600 too. Selecting the three deposits from undeposited funds lets you do that.

QuickBooks: Paying Sales Commissions with Payroll

Add the employee to the Sales Rep list. When entering the employee, click the Type drop-down list and choose Regular for this employee.

On the Payroll Info tab, add a commission payroll item in the area for Earnings. Enter the commission rate as a percentage in the Hourly/Annual Rate field.
- Go to the Lists menu and then click Payroll Item List.
- Click the Payroll Item button and then click New.
- Choose either EZ Setup (for most users) or Custom Setup (for advanced users), then click Next.
- Select the type of payroll item you want to set up and then click Next.
- Follow the onscreen instructions.
- Click Finish when you're done.

Generate a sales by rep summary or by detail report to see the sales amount for each commissioned sales representative.
- Set this to cash basis and select the appropriate date range.
- Memorizing the report will have it ready for you the next time.

Filter by items, item types, or accounts to only show the commissionable items (eliminate shipping, for example),

Pay the commission with a paycheck.
- In the Earnings section of the Preview Paycheck window, enter the sales amount in the Quantity field.

For the information in this report to be accurate:
- Create sales representatives in QuickBooks.
The Sales Rep list is separate from your employee list and doesn't link to your employee information, so you can add in-house reps (employees) and outside sales reps (non-employees) to the list.
- Assign a sales rep to every invoice in QuickBooks. To do this, enter the name of the sales rep into the Rep field on the invoice.

Generate a draft of the report.
- Before calculating the commission owed to each sales rep, look at the bottom of the report. If you see "No sales rep" listed in place of a sales rep name then some invoices were not assigned a sales rep.
- Double-click each invoice line to QuickZoom to it and assign a sales rep to each invoice before calculating commissions. If you don't see "No sales rep" listed, you are ready to calculate your sales commissions.

The amount you owe the sales rep is not calculated on this report, however you can export this report to Excel, enter your sales commission percentage, and let Excel do the calculations.

QuickBooks: Tracking Employee Advances or Loans

Tracking employee advances or loans in QuickBooks on future paychecks is a must for companies with a policy that allows giving employees advances/loans for personal reasons.

There are three ways in which to record an employee advance or loan:
1. Including it with the employees regular paycheck
2. Writing a regular check
3. Giving the employee cash from the company Petty Cash account

Step 1 – Create an Other Current Asset Account to track employee advances/loans
The first thing that you need to do – or have in place – is an Other Current Asset type account in your QuickBooks Chart of Accounts to track the money that is given to the employee.

If you need to create the account:
1. From the Lists menu -> choose Chart of Accounts
2. Click the Account button at the lower left -> choose New
3. Click the radio button next to Other Account Types -> and from the drop down menu choose Other Current Asset -> click the Continue button
4. Complete the details for the account -> Account Name = Employee Advances/Loans -> Account Description = To record employee advances or loans and repayments on future earnings.

Important Note: If your company frequently provides employees with advances on future earnings, create sub-accounts for each employee advance or loan; including the employee name and loan date in the account name.

Issuing an Advance or Loan to the employee as part of his or her regular paycheck.

If you want to provide the advance or loan money with the employee’s regular paycheck you will need to have in place or create an “Addition” type payroll item to record the money given to the employee.

If you currently don’t have an item in place, you will need to create one.
1. From the Lists menu -> choose Payroll Item List
2. Click the Payroll Item button at the lower left -> and choose New
3. Choose Custom Setup -> and click Next
4. Click the radio button next to Addition -> click the Next button
5. In the Name field, use a generic title such as Employee Advance or Loan. Click the Next button.
6. On the Expense Account window, using the drop-down menu, choose the appropriate “Advance” account (the Other Current Asset) -> click the Next button
7. On the Tax Tracking Type window, select None and click the Next button.
8. On the Taxes window, there should be no check marks -> click the Next button
9. On the Calculate based on quantity window, select Neither -> click the Next button
10. On the Gross vs. Net window, select net pay -> click the Next button
11. On the Default rate and Limit window, you can either leave both fields blank OR you can enter the full amount of the advance or loan in the first box -> click finish.

When you create the employee’s regular paycheck, from the Other Payroll Item box, select the “Addition” payroll item and enter the dollar amount.

Providing an advance or loan by writing a check.

Create an entry for the employee in the Vendor Center or the Other Names List, use this newly created name when completing the QuickBooks Write Checks window and from the Expenses tab, select the appropriate Employee Advance/Loan account from your Chart of Accounts.

Giving the employee cash from the company Petty Cash Account.

You’ll want to make sure that the employee’s name exists in either your Vendor Center or in the Other Names List. Open your Petty Cash Account register, select the Vendor or Other Name entry, enter the dollar amount given to the employee and select the appropriate “Advance/Loan” listing in your Chart of Accounts.

QuickBooks Timesaving Tip: Quick Report on Transactions

Question: Using QuickBooks, how do I get a report of all invoices? How can I see the Sales Receipts (checks, bills, estimates, etc.) that I have entered into QuickBooks?

Answer: You can get a report on transactions several ways, the fastest and easiest way to do it is to use the Transactions tab in the different centers.

In the customer (or vendor or employee) center in QuickBooks, click on the Transactions tab as shown. Then, you can select which type of transaction (Invoices in this example), filter and select the date desired. You can sort by clicking on the column heading (by Num in this example) and you will see a total too. Then, you can print or export the report if needed.

You can also click the column headings and sort by ascending or descending order by whichever column you choose. You can also right click to get a mini menu with several choices. You can select Customize Columns to add/remove columns or change the order of the columns or create a quick report.

MSCRM 2011: Filtering Data

In Microsoft Dynamics CRM 2011, there is new functionality provided that allows filtering of views the same way as filtering in Microsoft Excel.

Following is an example of the steps to filter a view:

* Open the “All Accounts” view
* Click on the Filter button in the ribbon. This enables filtering for each column in the view.
* Click on the dropdown arrow on the Address 1:City column. The following options for filtering are displayed: Sort A toZ, Sort Z to A, Contains Data, Contains No Data and Custom Filter
* Click on Custom Filter and specify that the filter should show Cities that begin with “New”.
* Now records will be shown only for those Accounts whose City begins with “New”.
*Go to the View tab and choose “Save As” to save the filtered view for future reference.

MSCRM 4.0: Hide Entity Navigation Bar Items

A client recently asked: How can I hide left navigation bar items within a Contact or Account?:

You have two options to be able to hide left navigation bar items inside a form:

1. Remove all privileges linked to these items in the security roles

2. Add onLoad javascript to force the items not to display

The security role method is very straightforward, however if you have dozens of security roles, this can be time-consuming.

To use javascript, see the below example:

Example: Hide the 'Sub Contacts' menu option inside the Contact entity

* Open a Contact in Microsoft CRM
* With the Contact open, press Ctrl + N to open in a new window
* In Internet Explorer Click View > Source

When the HTML opens in Notepad, search for Sub-Contacts. When it is located, notice that just before the highlighted text you will see: id="navSubConts", write this down.

* Click Settings > Customization > Customize Entities
* Double-click the Contact entity > click Forms & Views
* Double-click Form (Main application form)
* In the right-hand pane, Click Form Properties
* Click OnLoad and Click Edit
* Paste the following Javascript in the box:

document.getElementById("navSubConts").style.display = "none";

* Check the Enable Event checkbox
* Click Save & Close > click Publish

Open a Contact in CRM and the Sub-Contacts menu option should be gone. This same method can be used to hide other entity menu options.

MSCRM 4.0: Custom Entity Owner Fields

A client recently asked: We are using CRM 4.0 and when we create custom entities they have no 'Owner' field. We've tried creating it manually but this doesn't allow us to assign the record at any stage. What are we missing?

There are two types of ownership for custom entities and how you create ownership could be your problem. When a custom entity is created, one of the required fields is 'ownership'.

Your options are 'User' or 'Organization'. User is the default, and if
you choose it then each record will have an owner and you can do assignments.

If you choose 'Organization' as the ownership type, then the record does not have an owner. Once the entity is created, you cannot change this option.

To change the ownership, then you will need to recreate the entities. If this is the case, use the following method to create a custom entity with a User ownership type:

* Select Settings > Customization > Customize Entities
* Click New > Provide a name for the Entity
* Under the Ownership drop-down > select User
* Save the entity > Go to Forms and Views
* You should see the form with Name and Owner fields.

You can add fields, forms and relationships to the new entity. When you are finished, click Publish Customizations and the new entity should appear (with an Owner field).

Friday, June 24, 2011

Invest in Facebook? Not Me.

I explain in this week's Forbes blog.

Monday, June 13, 2011

Wednesday, June 8, 2011

This Week in Small Business: Is the Sky Falling?

My weekly New York Times wrap up.

What My Dad Taught Me About The Economy

Because things aren’t as bad as they seem. Even in this economy. My problems are my responsibility.  See more in my recent Forbes blog.