BLOGS
EASY SUBSCRIBE


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.

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

SSRS: The Report Issues Link to Report Issues with a Report

Despite all our best efforts, occasionally – rarely – perhaps inconceivably – there are problems with reports or extracts we create for our end users.

Read More

SQL: Finding the Smallest or Largest of a Fixed Set of Values

Recently while working a regulatory report I found myself faced with needing to identify the first (or earliest) of a set of dates. Each date came from a different possible order related to the patient visit, and I needed to chunk through 10 different JOIN-ed tables and get the one with the first / earliest date.

Read More

SQL Tip: To ISNULL() or not to ISNULL()

When we’re working on other people’s code – for troubleshooting or optimization purposes – we see a fair amount of usage of the ISNULL() function. Generally speaking we replace those ISNULL() references with COALESCE().

But why?

Read More

SSIS Tip: Managing SQL Server Reporting Services Versions

Once you start working with Microsoft’s reporting solution, you will find there are multiple versions of SSRS, and some of the versions have different specifications for the RDL files which hold your report definitions.

In addition, there are different versions of Report Manager (and the rendering engine) which have different capabilities. And you can also publish RDL’s through SharePoint.

Read More

SSIS Tip: Using #Temp Tables with Integration Services Packages

SQL Server Integration Services is a powerful tool for building a single DTSX package, which can generate a single output file — or dozens of them — and let that set of work be scheduled to run automatically. And it’s included in your MEDITECH Data Repository server license for SQL Server! Instead of a dozen NPR reports, each with its own schedule (or the same setup using SQL and bcp’ing out files), you can have one .DTSX package that calls a stored procedure per file you need.

Which is all fantastic, until you find that you need a #temp table for some kind of multi-step processing inside your stored procedure…  

Read More

Comments

0 COMMENTS