Wednesday, September 30, 2015

Crystal Reports : Use Fornulas to Group

One of the most effective techniques I've learned is how to group on a formula.
Think of it this way: the logic of the formula can be whatever you want it to be. So, any "high-level" corporate logic can be expressed here.
Take for example a table called HISTORY in which are listed history items per user.
Let us create a formula that will "kick-out" a value based on HISTORY.DATE.

IF {HISTORY.DATE} > CURRENTDATE - 30 THEN "History within 30 Days" ELSE
IF {HISTORY.DATE} < CURRENTDATE - 30 THEN "History beyond 30 Days" ELSE

Now this formula can be used as a Group.

You will notice that the last line is an ELSE {HISTORY.DATE}. This is because if a {HISTORY.DATE} does NOT fall into our formula criteria, then it will be "skipped". The final ELSE works as a "catch-all".

Crystal Reports : NULL vs Zero

Depending on your usage, the returning of NULL (from a formula or running total) can be more stubborn at times.

First, check what NULL conversion options the report is using:

1. Select from the top level menu File | Report Options.
2. Look for the "Convert NULL field value to default" checkbox.

If this box is checked, NULL is returned as a special "automagical" DEFAULT value.
This typically will convert NULL to zero in formulas and running totals.

Folks who are using newer versions of Crystal will notice an additional checkbox in Report Options called "Convert Other NULL Values to Default".

This checkbox refers to the NULLs being returned by objects other than database rows (i.e. formulas and running totals). Typically a combination of both of these checkboxes is required to properly fix a zero totaling problem. (Depending on back-end database and usage)

As always, try it out.

Crystal Reports : Calculating Hours

You may need to at some point or other calculate the number of hours between two dates. Keep in mind that if you are using a robust back-end database like SQL, a data type also holds the time precision down to the second. So this could be applicable for calculating billing hours, time clock hours, etc.

To make this happen we will be using the magic of the DATEDIFF() function.
Simply put, DATEDIFF calculates the length between two dates using a specified interval.

Let us suppose that our table has two values, {TIMECLOCK.START} and [TIMECLOCK.END}.

Create a formula called {@DAYLENGTH} and insert the following text:


The 1st parameter ("n"), specifies we want an interval of Minutes. Other valid intervals are "YYYY" for Years or "m" for Months.
So the code returns the number of Minutes between the two dates and then divides that value by 60, giving the hours.

Thursday, September 24, 2015

ZOHO CRM - Restrict Access to Reports

Sometimes we have situations where we don't want all users to have access to certain reports.  The secret to restricting access to reports is through permissions on report folders.

To change the permissions on a folder, click the Edit link to the right of the folder in the Reports module:
To bring up the folder permissions setting form.
You can choose to allow all users, restrict access to only you, or define a set of users that have access to the report.

Zoho CRM - Matrix Reports Explained

I have many clients that are confused about matrix reports, so I thought an example of how they can be used would be a useful tip.

Example: Sum of potentials in each stage broken out by month

  • Reports-> Create Report ->
  • Choose "Potentials" as the module - no related data is needed for this report
  • Under Groupings Tab:
    • Choose "Stage" as row headings
    • Choose Closing Date as Column Header (specify sort and date grouping)
  • Under Columns to Total - choose Potential Amount - Sum
  • Add criteria if desired (e.g. only open potentials - pipeline), and add date filter at top (e.g. current fiscal quarter)
  • The result looks something like this (with your stages of course!)

ZOHO CRM - Schedule Reports

With Zoho CRM you can schedule reports to be delivered right to your inbox!  Here's how

  • Click on Reports in the top Navigation bar to get to the reports module.
  • Click the Report Scheduler Button near the top
  • The Scheduler Configuration form will open:
    • Name the Schedule
    • Choose the report that will be sent
    • enter start date and time
    • select a repeat cycle (none, weekly, monthly,etc...)
    • Add Recipients - these can be Users (must be active/confirmed) selected individually, by group, or by role - or non-users (simply enter email address in box provided.
    • Click Save
Recipients will receive the report as an Excel spreadsheet.

MSCRM 2015: Failed System Job View

As a CRM administrator, you need to make sure your workflows and system jobs are functioning correctly. It is a good idea to regularly review the system jobs to determine if anything is failing. An easy way to make sure you do this is to create a Failed System Job View.

Navigate to Settings > System Jobs. In the view drop-down, choose "All System Jobs." Activate the view filters and choose "Failed" from the Status Reason options.
Then, under the "More Actions" button, choose to "Save Filters to New View." Then you can give your view a name.
When you refresh the page, you will now see your new view in the view drop-down. You could also add this view to a dashboard to see it more easily and regularly.

MSCRM 2015: Goal Rollup Job Not Updating Goals

Have you noticed that your goals are not being updated by the goal rollup job that runs regularly? The first thing you should do is check that the job is not failing. You can do this by navigating to Settings > System Jobs. Then choose "All System Jobs" from the View drop-down and filter to see jobs that have failed. If it is failing, investigate further to determine why.

If you're like me, then you're still scratching your head because the job has not failed yet the goals aren't being updated. I had created several (288 actually!) monthly goals beginning in January 2015 and clicking the "Recalculate" button to update them manually was not appealing at all.

I checked my System Settings to find that the rollup was set to stop after 30 days from the end date of the goal. Aha! This is why all of my goals from earlier in the year were not updating.

I changed the number of days to 365, rescheduled my goal rollup job to run now, and then all of my goals were updated. Yay! After I knew all of my historical goals were updated, I set the expiration date back to 30 days because my old goals will only need to be updated once.

MSCRM 2015: Global Search Results & Quick Find View

Ever wonder how to change the fields that are displayed when you do a global search in Microsoft Dynamics CRM? In the screenshot below, it looks like the fields being displayed are Full Name, Account, and possibly Owner.
It turns out that the search results will display the fields associated with the first three columns of the Quick Find View. If I check out this view for Contacts, I see that I'm correct. We have Full Name, Account, and Owner.
Instead of Owner, I want the search results to display the contact's email address. With a quick change to the Quick Find View, I can make this possible.

Tuesday, September 22, 2015

GoldMine : Create a Contact while Scheduling

Did you know that you can perform a few handy contact-related actions right from the "Schedule" window in GoldMine? One of the most useful is the ability to create a new Contact.

1. In GoldMine, select Schedule | Call.

2. From within the Schedule window, click the little "person" button to the right of the "Link to Selected Contact" line. Then, select "Create a new Contact".

3. GoldMine will bring up the "New Contact" window. Fill this out normally, then hit OK.

4. You'll notice that your phone call is now being scheduled against the newly created contact.

Try it!

GoldMine : Turning on Auto-Fill

Did you know that fields can be set up to auto-fill when a user starts to type in the field?

1. Go to the field you want to have auto filled.

2. Click on the arrow at the end of the field box to give you the pop-up box with the drop
down list.

3. Click on Setup.

4. Check the box next to Auto Fill and click Okay.

5. Anytime anyone starts to type in that field it will automatically fill the field from the list.

GoldMine : Showing Summaries in Lists

As always, the most useful functions are the best kept secrets. Did you know that you can see list counts in almost any GoldMine list?

1. Right-click within the list.

2. Select "Summary" so that it is checked.

You can now see the number of rows in the bottom left hand corner of your list. This works in the Search Center, History Tab, Pending Tab, etc.

Thursday, September 3, 2015

QB Tips: Apps That Work With QuickBooks

QB Tips: Memorizing Reports

Is there a report that you're constantly using?  Did you customize a report and would like to repeatedly generate it without having to customize it over and over again?  Well, you can!  Memorize the report and all you need to do is click on it ... with minor adjustments.  Here's how!

  1. Generate a report:

      2.  Click on the "Memorize" button  

     3.  Give the report whatever name you wish   

     4.  Save your report in a specific group  

You have now saved your report and can access it, easily, whenever you need it.  When you generate the report, make sure to change the dates for the time frame desired.

Wednesday, September 2, 2015

Crystal Reports : Crosstab Summaries

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 : Dealing with Notes Fields

When inserting "notes" fields into a Crystal Report, some things must be considered:

- After inserting the field, you may specify whether or not it "can grow" (meaning vertically) by going to the fields' Formatting page and selecting "Can Grow". You may also specify how many lines it "can grow" by.

- Certain databases keep their notes in HTML format, in which case the output will look garbled. You may, again, go to the fields' Formatting page and go to the Paragraph Formatting tab, then select a "Text Interpretation". Choices include HTML and RTF (Rich Text Format).

Crystal Reports : The Specified 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.