InDesign Data Merge with Images

In this Adobe InDesign tutorial you’ll learn how to merge multiple image files into Adobe InDesign to create form letters, postcards or other custom InDesign documents that use custom images for each page in the document. To learn more, visit our InDesign Training Course page to see our InDesign Training classes.

QlikView–Moving the “Unmovable” Object

QlikView is without a doubt one of my most favorite programs to teach.  The capabilities for modeling and visualizing data are just amazing.  But for all of QlikView’s shine and wonder, it’s not without its shortcomings.  I really shouldn’t say “shortcomings”, but there are a few areas that could use a bit of improvement.  Take, for instance, moving objects around the report space.

Normally, an object possesses a title bar (or “caption”, as QlikView likes to call it) and you can simply click and hold the caption bar and move the object.  The problem arises when a designer hides the caption bar (and to a lesser extent the border), leaving the report viewer with what appears to be no way to grab and move the object.

Instead of temporarily turning the caption and borders back on just to make a simple adjustment and then turning them back off again, you can place the mouse pointer over the object and hold down the ALT key on the keyboard.  This will activate the MOVE feature where you can now relocate the object quickly and effectively.

Gauge
(more…)

Microsoft Word – 10 Features that will Improve Your Workflow

With so many features packed inside each Microsoft application, it’s easy to overlook some that can really add value to your daily workflow.  Below is a list of some of the features you may have never noticed but will quickly become part of your daily routine.

Styles

image

Styles are not only a great time-saver, but they form the basis of a variety of other Word features.

Styles allow you to assign in bulk a variety of different formatting attributes, like font size, style, color, alignment, line spacing, borders, shades, indentation, etc…  The list is quite voluminous.  Once styles are applied to text, the text can then be bulk-updated simply by changing the style.  If you modify the style, Word will automatically apply the change to all text whose appearance was created from the style.  Imagine the time saved when making minor alterations like changing the font size of all headings.  Change the style and all of the headings are updated to match.

Once you have your styles in place, you can now also navigate more effectively through your document via the Navigation Pane.  Any text formatted with a heading style will appear as a list entry.  This will become one of the greatest time-savers when navigating large documents.

image (more…)

QlikView – Suppress Splash Screen

We spend so much time trying to make things run as quickly and efficiently as possible, but sometimes we just can’t help ourselves when it comes to self-promotion.

QlikView loves making things go fast; starting QlikView is another story. As if we were unaware that we had elected to launch QlikView, a several second “commercial” in the form of a splash screen has to tell us that we are one of the lucky ones.

Splash1

As much as I enjoy using QlikView, I find the startup splash screen to be a bit of a nuisance.

The good news is, there is a VERY easy way to prevent the splash screen from being displayed during program launch. (more…)

Excel – Hide Records with Zeroes in Pivot Table Calculations

One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?”  On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted.  There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated.  This is not an appealing prospect.

There is actually a very easy way to not display pivot table records that equal zero. (more…)

Crystal Reports – Add True Bullets to a List in a Text Object with the CHRW Function

Suppose you want to display a list of items in a text object and you want that list to appear as a bulleted list.  If your list is hard coded (i.e. USA, Canada, Mexico), then you could simply type the character that represents a bullet, like an asterisk, and have your list in no time flat.

Our example will have a bit of static text at the beginning followed by the bullet list.  The static text will read as follows:

“Last Year’s Sales and Suppliers for”

We will follow up the text with a carriage return to ensure the bullet list begins on a fresh row in the text object.  This is where you could type something like the following:

* USA

* Canada

* Mexico

The finished product would look like:

Bullet01

Suppose your list if items is the result of selections made within a parameter and you want to display that user-defined list with bullets.  The first thing you have to realize is that you can’t just place the parameter in the text box and get the list. (more…)

Microsoft Project – Display Estimated Durations with a Different Color in a Gantt Chart

When working with tasks in a project, it is common practice to display a duration as an estimate.  Displaying an estimated duration prepares the viewer for possible changes in scheduling.  An estimated duration takes the form of a question mark placed after the declared duration.

Estimated1

The issue is that some project viewers fail to notice the question mark; then when durations are updated, project viewers wonder why things have changed.  One way to ensure that people’s attention is drawn to the estimated durations is to change the color of the Gantt bars to reflect an estimated status.  There is no built-in state for displaying estimated durations in a separate color, but with a few short clicks this behavior can be achieved. (more…)

QlikView – Formatting Expressions in List Boxes

When adding an expression to a list box, one of the common complaints is that the numbers displayed are devoid of any number formatting.  This is especially frustrating when displaying large numbers without commas to ease readability.

QVExpress1

What most beginning report developers try is to format the values with the Number tools in the list box’s properties. (more…)

Excel Hidden Camera Tool – Great for Dashboards

If you are an Excel user who likes to create charts, design dashboards, or just likes to play with neat toys in Excel, this tutorial is going to be right up your alley.

Excel contains (in a super-secret place) a hidden camera.  “But why would I need a hidden camera in a spreadsheet program?”  I’m glad you asked.  If you have ever created a chart on one sheet, but you need the chard displayed simultaneously on a different sheet, and you don’t want to make two of the same thing, the camera tool will solve this problem.

First thing’s first; we have to find the camera before we can put it to creative use.

(more…)

12 Excel Keyboard Shortcuts for Every User

Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application.  It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all.  But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.)

The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts.  Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at all.

So let’s get this show on the road!

  1. CTRL+SHIFT+L – Turn On/Off Filter Controls

Filters are of tremendous use when analyzing large numbers of records in a table, but you are only interested in a select set of records that met a specific criteria.  Activating your filters is just a CTRL-SHIFT-L away.  This keyboard can also be used to turn off all of the filters and display the entire list.  (Filters are on by default when you convert a straight table to a Data Table, and not always desired.)  Finally, if you hit the “L” key twice (CTRL-SHIFT-L & L) you can effectively clear the current filters to start fresh with a new filter query. (more…)

QlikView – Limiting Record Loads with the FIRST Prefix

While developing a report, you may be loading thousands if not millions of records during the execution of your script.  If you are making edits to your script, and require a reload of the data to test your script changes, you may find yourself spending more time waiting than actually developing.

If a sample of the data is sufficient for testing purposes, you can limit the number of records returned by using the FIRST prefix.

The FIRST prefix is placed directly before the LOAD statement in the script followed by the number of records you wish to load.  For example:

[Main Table]:
FIRST 10000
LOAD * FROM
[1000000 Records.xlsx]
ooxml, embedded labels, table is Sheet1);

In the above example, reloading the script will only load at most 10,000 records from any given table.  If a table is encountered that has less than 10,000 records, then the full table will be loaded.

When your development period is complete, you can either comment out the FIRST prefix or remove it entirely from the script.

QlikView – Export All Tables as QVD Files

Because QVD files load SO MUCH FASTER than the original data sources (i.e. Excel, Access, TXT, etc…), you may wish to save all of your tables to QVD files.  There are a couple compelling reasons to do this:

  • You wish to develop your report in an offline state while retaining access to a relatively recent copy of the data.
  • You wish to save all of your dimensional tables for reuse by other reports, and you want those tables to be optimized for fast reloads.

To accomplish this task, perform the follow steps:

1. Add a sheet to your script and give it a name of your choosing (ex. Save to QVD Format)

2. Add the following code to the newly created sheet

SaveQVDs1

(Here is a version you can Copy/Paste)

FOR vCount = 0 to NoOfTables()-1
LET vTableName = TableName($(vCount));
STORE '$(vTableName)' INTO '$(vTableName).qvd' (qvd);
NEXT vCount

3. Reload your script, and marvel at all of the newly created QVD files.  NOTE:  These files will be stored in the same folder as the source report.

QlikView – Dynamic Chart Titles

It’s every chart maker’s dream to have dynamically changing titles (well, that and a coffee table that looks like a giant floppy disk.  Seriously; Google that one if you haven’t seen it.  It’s pretty cool.)  Any-hoo…  To have a chart’s title change based on data you are, or are not, filtering by is a real boon to understanding the story that the chart is trying to tell.  Take the following example:QVDCT1

The reader of the chart can look at survey data for four categories all at once as a combined assessment, or they can filter by a specific category and evaluate the ratings individually.  If the chart were to be printed, the reader would not have much of an indication as to whether the chart was displaying all categories or focusing in on a single category.

Enter the DYNAMIC CHART TITLE!!!!!

(more…)

Crystal Reports – Missing Template Preview

When applying a template to a report, it’s often preferred to “peek” inside the template to see what it has to offer prior to applying it to your report; to “take it for a test drive”, as it were.

If you have a vast template library, and many templates are similarly named, it can be of great benefit to see a preview of what you will receive if applied.  The Template Expert provides just such a preview.

Preview01

For those who prefer to create their own templates, and possibly add them to the library, the preview feature may not display if certain prerequisites have not been satisfied. (more…)

Crystal Reports – Importing Values and Descriptions into Parameters

If you are creating a list of choices for a parameter drop-down list, you can acquire the choices from several sources:

  • Manually enter the choices in the list (static and least exciting of all of the options)
  • Append a unique list of choices derived from a field in the database (static)
  • Point to a database field for available existing choices (dynamic)
  • Import items from a text file (static)

Each of the options has their pros and cons.  If you are trying to generate a list that is unlikely to change (like a list of states in the USA) and you need to ensure all options are made available from Day 1, the first option of manually entering all of the choices is the typical approach.

But what if all of those choices exist in another report or file?  In this case, the ability to import list selections has great appeal. (more…)

QlikView – Scheduled Update of QlikView Reports

Suppose you want your QlikView report to automatically reload its data based on a schedule.  The manual process would be for you to open the report, click the RELOAD button, and then close the file.  If you team up the QlikView report with the Windows Scheduler, you can have this reload take place without human intervention.

Step 1 – Create a Batch File

Open Notepad and enter the following text:

— For users running the 64bit edition of QlikView —

“C:\Program Files\QlikView\QV.EXE” /R “C:\MyQVData\MyQVFile.qvw”

— For users running the 32bit edition of QlikView —

“C:\Program Files (x86)\QlikView\QV.EXE” /R “C:\MyQVData\MyQVFile.qvw”

(Change “C:\MyQVData\MyQVFile.qvw” to the location and name of your QlikView document)

For an explanation of the QlikView command line switches, see the end of this tutorial

(more…)

Crystal Reports Multi-Pass Processing Engine

Crystal Reports uses a three-pass reporting method to generate reports.  A pass is a process that Crystal Reports uses each time the data is read and manipulated.  Depending on the complexity of the report Crystal Reports may make 1, 2, or 3 passes over the data.  This feature allows for complex reporting and formula manipulation.

CRMultiPassModel (more…)

QlikView  – Popular Hotkeys / Shortcuts List

Script Editor

  • <F5> or <Ctrl> + R
    • Runs load script
  • <CTRL> + <Shift> + R
    • Partial reload
  • <Ctrl> + T
    • Shows model diagram
  • <Ctrl> + E
    • Opens table editor for LOAD INLINE statements (cursor should be placed inside statement)
  • <Ctrl> + Q + Q
    • Inserts script that generates several dummy tables
  • <Ctrl> + K + C
    • Comments blocks of script
  • <Ctrl> + K + U
    • Uncomments blocks of script
  • <Ctrl> + F
    • Allows search within the script in the debugger window

(more…)

Crystal Reports – Adding a Watermark to a Report

If you have ever looked for the watermark feature in Crystal Reports, you no doubt walked away smothered in soul-crushing despair wondering how Life could be so cruel.

True, there is no official tool in Crystal Reports that adds a watermark to a report, but that doesn’t mean it can’t be done.  Adding a watermark just takes a bit of creativity and a handful of mouse click and keystrokes.

The trick is to add a subsection and underlay the following sections in the report.  The steps are as follows: (more…)

Excel – Conditional Formatting with Subtotals

If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel.

If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline.

Before Subtotals

CondFmt1

After Subtotals

CondFmt2

But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit of pizazz. (more…)

QlikView – Mandatory Include Statement to Prevent Data Inconsistencies

The goal of every QlikView developer is to do as little work as possible.  When you notice that you are writing the same instructions in multiple app scripts, the notion of re-use is a red flag that flies with great fervor.  QlikView’s Include statement provide just such an ability.

The advantages of re-using scripts between multiple apps has several advantages:

  • Complex scripts can be used by multiple people with superficial knowledge of the script’s logic.
  • Include files ensure business process consistency when performing similar actions across multiple apps.
  • Include files expedite script maintenance because the script need only be updated once (in the Include file) and all scripts pointing to the file will automatically incorporate the updates the next time a Reload is executed.

As with anything in life, every “pro” must be accompanied by a commensurate “con”.  In this case, the risk of using Include files to re-use script code lies in the fact that the file containing the script must remain in the same location and the file’s name must remain unchanged.  If either of these two events occurs, the portion of the script pointed to by the Include statement will not execute. (more…)

QlikView – Always View Chart Bars

One of my pet peeves (wouldn’t that be a great name for a dog; “Peeve”?) when working with charts in QlikView is that I have a tendency to click on bars accidentally when showing data to colleagues.  When I do, all of the bars disappear except for the bar I mistakenly clicked on.  And for that millisecond in time, everyone receives a mental jolt that I wish would rather not have happened.

ChartBa

Since I was trying to direct their attention to a particular month, I think a better behavior would have been for the bar to change color to visually separate it from the herd.

I know what you’re thinking; “But how can a QlikView designer, like myself, enable such a behavior?”  I’m glad you asked.  Here’s the trick. (more…)

Using Excel MODE Function to Return a Text Response

Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers.  But what if you want to return the most frequently occurring word in a list of words?

MODE with Numbers

Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often.

Mode1

In this list, the number “4” appears more often than any other number.

MODE with Words

As you can see, the MODE function does not work very well when pointing to a list of words.

Mode2

The function returns a “#N/A” error message.

Not to fear; MODE can be made to return words, but it take the combined efforts of SEVERAL functions, none of which are MODE!  (How odd does THAT sound?) (more…)

QlikView Preserving Dates in Crosstables

The data transformation tools in QlikView are amazing.  When I think of the hours I have spent working with data that was output by some system trying to get that data into a format that I could then START modeling.  Oh, the wasted man hours.

One of the QlikView transformation tools has the ability to take a crosstable (like a pivot table in Excel) and convert it into a straight table; a traditional flat table where data typically starts.

CTDates1

The only problem with this tool is that it converts all of the data in the crosstable into straight text.  This means that even if you see data that looks like a date (ex: 11/25/2015), it is really just a collection of numbers and slashes.  QlikView does not understand what these numbers represent.  This makes modeling the data a bit problematic. (more…)