Analytics Tips and Tricks

General Tips

• When using a saved worksheet or crosstab, associate searches with it to speed up the process and avoid mistakes. To do this, open a worksheet or crosstab and click on Add Searches to Object under the Search menu. From there, select any number of searches. When opening a worksheet or crosstab, these searches will now show up first under Open Search. To remove a search, go to the Search menu and select Delete Searches from Object.

• To remove blank values from fields, edit the search to exclude NULL values. In the edit search page, add the desired field to the search (if it isn't included already). Select the is not operator and set the criteria to be NULL. This will work for all field types. If this doesn't remove the blank fields, click And from the And/Or column. Select <> as the operator and put a space in the criteria field. This will not work for numeric fields. In that case, select the numeric field and set the operator to be greater than or equal to. Put 0 into the criteria field and this will remove blank values.

Crosstabs

• When using an expression to show one column as the percentage of another, change the aggregate type from Sum to Expression to allow the Totals line to be calculated as a percentage.

• When first creating a crosstab, drag all of potentially desired sorting fields into Slice Dimensions. When the crosstab is run, this will query all of the fields and any desired sorting won't be delayed by needing to re-run the crosstab. This also lowers the number of server requests and it may be noticeably quicker in some environments.

Worksheets

• Use the Count function to determine the number of rows returned before running a worksheet. This will cut down on server queries and can be used to determine how a change to a search will affect results.

• Distinct Rows is a tool that will help distinguish the actual number of records related to a search. There are limitations to Distinct Rows; please be aware of them. Distinct Rows will cut down on the number of rows as long as all of the data in a row is the same as another row. If patient-fullname is the only worksheet field, Distinct Rows will act somewhat like a Nested search. It will only list the unique patients who fit that search. However, patients with the same last and first names will be combined into a single row; adding DateofBirth will typically fix that, and patient-id will definitely fix it. If problem-problem or medication-medication is added, the same patients will show up but with multiple rows due to the additional fields. Distinct Rows cannot determine what should be distinct, so it only operates on the fields entered. A Nested search may be more useful in some situations.

Custom Reports

• To group by date rather than datetime (dttm fields), an expression can be used to strip off the time portion. Create a new expression and enter the following, substituting the appropriate field:

(SELECT (DATEADD(dd, 0, DATEDIFF(dd, 0, [fieldname]))))

Use this new expression in the Group section, and it will group by day rather than day and time. This is very useful for viewing data about appointments where it's best to only see the day groupings rather than each individual appointment.

Links

Back to Allscripts Analytics