Monday, February 28, 2011

Crystal Reports : Displaying two Summaries in a Crosstab

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 : Displaying Minutes as Hours

Let us suppose we would like to summarize the amount of minutes in a phone call report, but express the grand total in hours and minutes. This is more easily accomplished using the "Display String" of the field in question. This allows us to create the summary field as normal.

1. Create a summary field to SUM your {CALLS.MINUTES} field
2. Right click on the summary field itself
3. Click on the "X-2"button to the right of "Display String"
4. Paste in the following formula :

local numbervar Hr := Truncate(CurrentFieldValue/60);

local numbervar Mn := Remainder(CurrentFieldValue,60);

Totext(Hr,0) + "Hours " + ToText(Mn,0) + "Minutes"

Pro Tip : The CurrentFieldValue function returns the current field value of the field being formatted. This allows easy code reuse.

Crystal Reports : Calculating the Week Number

Most reports use some kind of date math or date grouping (i.e dollars per month, week, etc).
Let us suppose we need to return the week "number" within any given year based upon a simple date. (Our fieldname will be {SALES.SALEDATE})

The DATEPART() function is what we need here:


1. The "ww" specifies that we want the "week number" returned.
2. {SALES.SALEDATE} is the date field to evaluate.
3. The final parameter, crSunday, specifies on what day the week starts.

Crystal Reports : Displaying the Day "name" of the Week

A client asked: I would like to display the day of the week on the detail line along with the date. How do I do this?

Using the example of CREATEON in Goldmine Contact1, you would do the following:

Create a formula using the DayOfWeek function called DayPosition as follows. This will create the numerical position of the date (e.g. 1=Sun, 2=Mon, etc)

DayOfWeek ({Contact1.CREATEON})

Then create a formula called DayName. This will add the name to the report (Mon, Tue, Wed). Use the function WeekDayName and add the DayPosition as the function detail

WeekdayName ({@DatePosition})

Drag the DayName formula to the detail row next to the CREATEON date. It will list the weekday name of the CREATEON date.

Crystal Reports : Calculating Age

One of the many things you will want to do with a bona-fide date field is calculate age.
Crystal makes it easy for us here; as long as we're comparing two date type fields, we can perform simple math.
Let us suppose that the date field in question is {CONTACTS.SOURCEDATE}, which represents when a particular prospect was imported into our database.

1. We can see how many records were imported in the last seven days

{CONTACTS.SOURCEDATE} <= CurrentDate() - 7

2. Or how "old" any particular record is, expressed in days


3. To calculate the age in years, we must use the Crystal DateDiff function:

DateDiff("yyyy", {CONTACTS.SOURCEDATE}, CurrentDate())

The "yyyy" specifies we want the answer in years; "m" and "d" are also options (months and days, correspondingly)

GoldMine : Setting a password policy

Another new security measure in GoldMine is the ability to set a password policy. This is done system-wide and applies to all users.

To do this, go to Tools | Configure | System Settings.
Click on the "Password Policy" tab.
You can set a minimum length, whether or not to block after three unsuccessful attempts, and also save a password history that cannot be repeated.

To set up password expiration, this is done on a per-user basis.

Go to Tools | Users Settings
Change the "Valid for Days" value.
You'll notice the "Next Change" date becomes activated after you choose x number of days.

Caveat; I once worked in a place that forced password changes every month. And as you walked around the office, on virtually every desk (usually on a stickynote) was each users password. So I guess the moral of the story is to use with care.

GoldMine : Managing User Security

I can't tell you how many times I've gotten the following phone call:

"One of my users deleted something. Can we get it back?"

The answer to which, of course, is yes, provided you have a good backup.

But it is better to shortstop the problem entirely with a little user security. Take away the Delete permission, for instance.

To manage User Security, go to Tools | Users Settings.
This is you GoldMine User List.
Simply double-click on a username to edit it.
Go to the "Access" tab. (You'll notice there is plenty more to play with on the other tabs as well)
Uncheck the "Delete" checkbox in the top left hand corner.

Also keep in mind that if the user has "Master Rights", any settings you make are "trumped" by their Master Rights status.

You may also wish to further limit access, and that is fine. Setting up the perfect mix of Accessibility and Functionality can take some trial and error. And while some security is a good thing, it can definitely cut the other way; too much security can take the "Group" out of "WorkGroup".

GoldMine : Adding Columns to your Search Center

One of the nicest things about the new GoldMines (keep in mind I've been around since 3.2) is the ability to add and remove columns from your Search Center. This is done on a per-user basis and is handled thusly;

Open your Search Center (i.e. start searching for a contact)
Look to the upper bar of the Search Center and click on the "Columns" button.
Then click the "Column Selection" tab.
Use the little arrow button to move columns to the right-hand side (which will now appear in your Search Center)
Ok your way out.

This is also a handy way to easily create lists; add the columns you need to the Search Center and then use the right-click Output To...Excel functionality.

GoldMine : Closing a Tab Easily

I love the new tabbed interface of GoldMine, I really do.
But there is just one thing that tarnishes my otherwise perfect multi-tasking experience; the size of the little red "X" you must click to close a tab.
This was actually brought up by a user at our last live training.
The solution? Use the CTRL-F4 keyboard shortcut. This will close the currently open tab in GoldMine, and indeed many other windows applications. (It's actually a spin off the good old Windows ALT-F4 shortcut, the one that closes the current Windows application)

GoldMine : Editing the "Canned" Dashboards in 9.0

A lot of folks lately are talking about Dashboards, something included in the newer versions of GoldMine Premium Edition. But for those do-it-yourself-ers out there who want to learn how to build their own dashboards, doing this from scratch can be frustrating.
A good place to start is to Copy one of the canned Dashboards that come standard. (You can't edit them the way they are)

To do this, simply open your Dashboards view in GoldMine.
Right click on the Dashboard in question.
Select Copy.
GoldMine will prompt you if you'd like to duplicate the underlying Data Sources.
Click Yes, as this will ensure the original Dashboards are "untouched" by any modifications you make.
Start poking around and have fun!

MSCRM: Creating a Personal View

The Advanced Find feature in Microsoft Dynamics CRM is very powerful. You can search on almost any field in any entity to get information that you might need. In the following example we will return all of the contacts that have had an Account created in the last 10 days so that a welcome mail can be sent.

1. Open Advanced Find.
2. By default, Advanced Find automatically selects the Entity that you are working with as its default Look for. If you were working with Contacts when you started the Advanced Find, change the look for to Contacts.
3. Because you are going to be sending a piece of mail, you want to ensure that the columns you need to do this are displayed on your newly created view. Choose edit columns from the top toolbar and the Edit Columns dialog box is displayed.
4. Select Add Columns located on the right navigation pane of the dialog box. Add all of the columns that you would like to display in the view and click OK to continue.
5. Configure the sort order that you’d like to have by selecting Configure Sorting. Click OK twice to continue.
6. You need to add the criteria now to your Find because you have selected only the Contacts and the fields you want returned. If you were to run the Query now, you would return every Contact and not just those that are associated with an Account created in the last 10 days. To add this logic, mouse over the Select option, it changes to a drop-down. Scrolling down the drop-down, you will notice that the available fields to apply a filter to are broken down into two sections:
a. Fields
b. Related

Fields are the base fields for the entity you are working with – in this case, the fields for the Contact entity. Notice that every field applicable to the Contact entity is listed there, along with any custom fields you might have added.

Related enables you to select related entities to the primary entity you are working with – in this case, the entities related to the Contact entity. Every relationship to the Contact Entity should be listed there along with any custom ones added.

Select the Parent Customer (Account) from the Related section.

7. Now that you have the relation established, you need to add the last bit of logic, accounts added within the last 10 days. Mouse over the Select under Parent Customer (Account) and select Created On. Notice that the fields displayed are of the base entity Account – this is because you are now working and able to query on the fields for the related entity. Change the option to last x days and enter 10 as the value.
8. Selecting Find returns all Contacts related to Accounts added within the last 10 days. With the results, you can now perform a mail merge, export them or even create a quick campaign.
9. To save this view that you just created, go back to the Advanced Find screen and Choose Save As and give the view a relevant name. This view will now show up in the drop-down of views in the Contacts screen under My views.

MSCRM: Scheduling Reports

The report scheduling feature In Microsoft Dynamics CRM has the ability to generate snapshots of CRM reports. The snapshot types can be either an ‘On Demand Snapshot’ or the ‘Scheduled Snapshot’. You can schedule reports in CRM to retrieve data and deliver reports at specific times or during off-peak hours. Schedules can run once or on a continuous basis at intervals of hours, days, weeks, or months.
You need to have the CRM data connector installed on the SQL Reporting Services server. After the CRM data connector is installed on the SRS server, you can start the Report scheduling wizard as follows:

1. Click on the “Workplace” area and “Reports” section
2. Drop down on “More Actions” and choose Schedule Report.
3. Specify whether the report should be created (On-demand or On a schedule). Choose On a schedule.
4. On the next page you can specify the schedule which will be used to generate the snapshots. You can specify interval and time of the report snapshot.
5. On the next page of the wizard, you can specify start and end day for the schedule.
6. Snapshots are generated on the Reporting Services Report Server without any user input, so anything that you would normally set when viewing a report (parameters, default filter, etc.) need to be set before the snapshot is generated. With that said, the next page of the wizard allows you to set the report parameters. You can also set the default filter of the report by clicking on the “Edit Filter” button.
7. The final wizard page gives you a summary of the options you have selected over the previous pages. We can make a note of the “Snapshot Definition Name” is the name of the new report that will be created when you click “Create”. The new report is an exact copy of the original except that we set it to snapshot-only execution and save all of your preferences to the report. Also observe the footnote of the page- that we only list the most recent eight snapshots that have been generated, and because of this we set the report to only keep the most recent eight snapshots.
8. After clicking Create, you’ll get a progress button processing the request and eventually finishes up with a success page. After closing the wizard you’ll see the new report copy in the grid. If you are generating snapshots on a schedule, chances are you won’t have any snapshots at first, but once you get snapshots, you can see them by clicking the triangle to the left of the report icon. Then the snapshots will be listed by the time they were generated. Clicking on the snapshot time will open the snapshot in the CRM report viewer

MSCRM Online 2011: Connection Roles

Microsoft Dynamics CRM 4.0 provides the ability to define relationships between entities using the Relationship feature. However it is only available for defining relations between Account, Contact and Opportunity. In Microsoft Dynamics CRM 2011 this concept has been expanded to Connections Roles which are relationships between any two entities including custom entities. (Note: In CRM 2011 - use Connection Roles in lieu of Relationship roles. Relationship roles will be going away in future releases of Microsoft Dynamics CRM.)

Connections can basically be used to define the relation or the association between any two entities. To give an example you can have an account with multiple contacts. With the help of connection roles you can define that one of the contacts is actually the partner in the company and another an employee.

You can define a myriad of relationships:

Family relationships (father, sister, brother, spouse/partner, cousin)
Social relationships (tennis partner, golf partner, club member, friend)
Sales relationships (champion, influencer, stakeholder, referrer, gatekeeper)
Business relationships (supplier, partner, consultant, contractor, competitor, former employer)

When you create a Connection Role, you select the CRM entities for which this connection role can be used. The system administrator can create a new Connection Role by performing the following steps:
1. Go to Settings, Business Management, Connection Roles
2. For example create a new role called Former Employee. Choose a Connection Role category.
3. Select the record type that this role describes. In this case it would be the Contact record type.
4. You are given the option of choosing matching Connection Roles. In this case, set one up for Former Employer that is associated with the Account record.
5. Save and close the Connection roles. It is now ready to be used.

Now… to create the connection for the related records:

1. Open an Account and go to Connections in the Common area
2. Click on the Connect To Another.
3. In the window, choose the Contact that you wish to associate as a Former Employee.
4. When you click on the lookup for the As this role, all of the roles associated with that entity type will be displayed. Choose “Former Employee” and then OK.
5. Click save and close.
6. The connection will be saved. In this case, a relationship for Former Employer will be set up automatically on the Contact record.

Friday, February 25, 2011

QuickBooks Time Saving Tip

Add Filters Before a Report Generates

When you generate a report in QuickBooks, default information is used and the report is displayed on the screen. How often do you change the information and then generate the report again? In QuickBooks, you can have the Modify Report screen come up before the report is generated.

Choose Edit Preferences from the menu and then select the Reports & Graph option. On the My Preferences tab, check the checkbox entitled Prompt me to modify report options before opening a report. This will tell QuickBooks to open the Modify Report screen before generating a report, allowing you to put in your specific report criteria the first time the report is generated.

QuickBooks: The History Button

I am often asked about quick ways to find related transactions in QuickBooks. Clients know that reports can be generated, but wonder if there are other methods.

QuickBooks has made that easy for us with a History button in transactions screens. The History button shows you which transactions are related or "linked" to a specific transaction you selected. For example, a transaction history for a customer payment shows the invoices to which you applied the payment (and the bank deposit, if you deposited the payment).

You can double-click on a bill to view the bill details. This works for all related transactions in QuickBooks! If you don't see the History button, use Ctrl H on your keyboard, or Edit / Transaction History.

In QuickBooks, you can display transaction histories for:
• Invoices
• Customer payments
• Deposits
• Credit memos
• Sales receipts
• Bills
• Payments to vendors
• Credits with vendors
• Item receipts
• Journal entries
• Purchase orders
• Progress invoices
• Estimates

QuickBooks: Moving Columns on Reports

Some users don't realize they can control the order of the columns on a customer report. It's important to make your reports appealing for the reader, so it's nice to be able to customize reports in a way that makes sense.
Here are the steps for doing that.
• Open a new transaction report (Reports Custom Transaction Detail Report).
• Move the Name column to the left side of the Num column by placing your mouse over the word Name in the name column. Notice your cursor has changed to a hand.
• Hold the left mouse key down and drag the column to the left. As you get to the diamond between the Date and Num columns, you'll see a red arrow appear.
• Let go of your mouse and the Name column will be on the left side of the Num column.
• You can also resize a column by holding your mouse over the diamond between a column until it turns into a cross. Then press your left mouse key and move the diamond to the left or right to either shrink the column or make it larger.
• Don't forget to memorize your report when you have it just the way you want it.

QuickBooks: Moving an Applied Credit to a Different Invoice

While reviewing the data file at the end of the year, a client noticed that a credit had been applied to a wrong invoice and wondered how it could be moved to the correct invoice.

You can change the invoice to which a credit was applied. This task requires several steps, but if you follow the instructions a step at a time, it's not as complicated as it seems.

1. Open the invoice to which you applied the credit.
2. Click the History button at the top of the transaction window to display the list of all transactions linked to this invoice.
3. Click the listing for the credit you want to change, and click Go To, to open the original credit transaction.
4. Change the customer name to another customer. It doesn't matter which customer you choose because you're going to change it back.
5. Click Yes in any warning dialogs QuickBooks displays about the results of making this change. Now you've destroyed the link between the credit and the invoice to which it was originally applied. QuickBooks returns you to the original invoice window, where the credit no longer appears in the History dialog.
6. Open the credit transaction you just moved to another customer.
7. In the Create Credit Memos transaction window, change the customer name back to the original customer name.
8. When you save the transaction, QuickBooks offers the original choices for applying this credit. Choose the correct invoice.

QuickBooks: Templates: Duplicate…Then Design

My new client was excited about getting started with QuickBooks and invoicing her customers. Then she looked at the plain black and white default invoices. As a creative and colorful person, she wanted to know how to design her own invoices and other forms.

Designing template forms can be fun and allow for creativity since there are so many options available, for the “design challenged” to the “design maven”. The first thing to remember is that the starting point is with the existing template, which is duplicated and then altered.

Follow the guide to begin designing your customized templates.
1. Go to the Lists menu and click Templates.
2. Select the name of the template you want to duplicate – recreate to make it your own.
3. Click the Templates button at the bottom of the list and then click Duplicate or download more designs found online.
4. Click the template type (form) to create. It can be a different type from the original template.
5. Click OK. The new template appears at the top of the list. It ill have the same name as the original template with "Copy of:" in front of it. For example, if you duplicate the Intuit Product Invoice, the new template will be called "Copy of: Intuit Product Invoice."
6. In the Templates list, double-click the duplicate template to open the Basic Customization window and then click Manage Templates.
7. In the Template Name field, enter a new name for the template.
8. Click OK to save the new template and return to the Basic Customization window where you can begin customizing the form.

HEAT globally replacing a value

Global Replace lets you replace a value in a selected database field with another value on a global (database-wide) basis. For example, you can change a Call Type from HWARE to Hardware

Note: You are making a permanent change in the Database which cannot be undone. It is suggested you make a SQL backup prior to performing a global replace.

To Globally Replace a Value:

  • Log into the Heat Administrator Dashboard, click the Global Replace link in the Maintain Database section (or, in the Administrator main window, select Database > Global Replace from the menu bar). The Global Replace dialog box opens.
  • In the Table Name drop-down list, select the table you want to include in the global replace (the table that contains the field value you want to globally replace). Fields for the selected table appear in the Field Name list.
  • Select the field you want to include in the global replacement. This is the field whose value you want to replace.
  • In the To field of the Change Value section, type a new value or click Browse to locate a new value. This is the new value to be inserted.
  • In the Where text box of the Change Value section, click Boolean to open the Search dialog box and define a Boolean expression for locating the existing value.
  • Click OK.

HEAT - Configure Unified Login

The Unified Login feature allows users to log on to HEAT using their Windows logon information. This eliminates the need to type an additional User ID and password to log on to HEAT.

Log into the Heat Administrator Module, select Security > Unified Login from the menu bar. The Configure Unified Login dialog box opens.

Select the Enable Unified Login check box.

In the Specify Field Containing Network User ID drop-down list, select the HEAT field you want to use to validate the domain Login ID. HEAT matches the user's domain login information against the value in this field to validate the HEAT login information. You can choose from the following HEAT fields:

• eMailName

• FullName

• LoginID

Note: This option is available to HEAT SelfService users over the intranet only. The Unified Login feature is not available to users over the internet.

Heat Self Service - Filtering Call Types

Heat Self Service call types can be filtered based on which calls you want to make available to users when creating a new ticket.

  • Log into Heat Web Server Configuration - You will need administration rights.
  • Navigate to HSS menu option (this is displayed in the left pane)
  • Click on Call Types - Notice the "Not Published" and Published panes?
  • The published Pane displays the call types that will be vizible to users
  • To move the call type from Not Published to Published, click on the call type in the Not Published Pane then click on the arrow pointing right.
  • Save your settings after making the change

HEAT Self Service - Create a Banner

A quick and easy way to notify users of high profile issues is to create a Banner in Heat Self Service. The information typed here will display on the Home Page of Heat Self Service.

  • Log into Heat Web Server Configuration - You will need administration rights.
  • Navigate to HSS menu option (this is displayed in the left pane)
  • Click on Banner
  • Type in the message to be displayed
  • Use the Source Edit button to switch to source mode and view the source code.
  • Save your settings after making the change