Report Writing

Interoperability and EHR Optimization

Privacy and Security

Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

Thomas Harlan has been a report writing consultant with iatricSystems® since 2012. Before joining Iatric Systems, his hospital IT positions ranged from programmer to manager to Chief Technology Officer. He has more than 20 years of SQL experience (including Microsoft, Oracle, Informix, and MySQL), as well as 15 years of clinical and business reporting experience, extracting data from a variety of HIS systems including Epic, Lawson, MEDITECH, PeopleSoft, and Siemens Invision.

T-SQL- Guaranteeing the Database Context in Dynamic SQL

It is rare that this will come up, but in certain circumstances you may need to change (or guarantee) the database in which a query executes.

This can really plague you when you’re working with multiple databases on multiple servers. You don’t want to forget which database you’re in… and execute code in the wrong place.

Read More

T-SQL- Error: “Cannot resolve the collation conflict between…”

One day you write some code on a new system, or using tables in the database you’ve never used before, and BOOM! A perfectly ordinary query blows up… what is this collation business??!

Read More

Don’t Forget lowly SourceID!

Ignore SourceID at your peril!

When creating joins in a SQL query, best performance is often attained by including as many primary key columns in the JOIN predicates as possible. In Meditech’s Data Repository, the SourceID column is often overlooked, especially by SQL newbies. SourceID is the first primary key column in virtually every table in Data Repository databases and simply adding this column to your joins can improve performance dramatically.

Read More

SSRS: Limit the number of columns on a matrix

For today's blog post, we want to help solve this common problem: how to limit the number of columns in a matrix to prevent it from exceeding the page width, when there may be any number of columns.

The following information is based on Limit no of columns in SSRS matrix report By Challen Fu, Microsoft.

Read More

T-SQL- Reformatting bulk text into a table of lines

MEDITECH stores a lot of bulk text in the DR; usually as a sequenced table of lines, with some line length. That text may or may not be formatted with extra characters to control fonts and bold/italic/etc.

Read More

SSRS Tip: Using wildcards in Report parameters

Searching and filtering free text data can be a problem due to human error. Typos, transposed characters and even data entry into the wrong field present a challenge when trying to report on the data. In a SQL query we can use LIKE and wildcards to try to work around these issues.

Read More

SQL Tip: Improving the Speed of ALL

We recently undertook a project for a customer where we reviewed the top X reports (in terms of usage as logged in SSRS) for performance optimization. Some of their heavy hitters were tying up a lot of DR resources, and slowing things down for everyone.

Read More

SQL Tip: Using SourceID in #Temp tables

Because SourceID is part of the primary key in virtually all MEDITECH Data Repository tables, it is good practice to include SourceID as the first column all indexes, include it in all joins and to identify SourceID in the WHERE clause of the query. In most cases this will cause SQL Server to use an index seek instead of an index scan and greatly improve performance.

Read More

SQL Tip: Returning Significant Digits

From time to time in your work in SQL from the DR, you’ll wind up CAST-ing or CONVERT-ing a number into a string. If that number starts as a DECIMAL or NUMERIC with a scale (the number of places of accuracy to the right of the decimal point) then you will wind up with something like this:

Read More

SSRS Tip: Adding A Tool to Clear the SSRS Data Cache

  • Ever change the logic in your query, confirm new data in SQL, run the report from SSRS… and get the old data?
  • Ever change the columns in your query, run the report from SSRS… and get a weird error – or just not get any data in the new column in the report, but you can see it in SQL?
  • Even if you click the Refresh button????
Read More