Despite all our best efforts, occasionally – rarely – perhaps inconceivably – there are problems with reports or extracts we create for our end users.
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.
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().
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.
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…
A very useful tool to have in your Data Repository toolbox is a Date Dimension table — which is a physical database table with one row per date from, say, 1900 through the end of 2499, and key elements pre-calculated about that date.
If you’ve been working with clinical documentation in the new SUR module in MEDITECH 6.1, you may have found that surgical interventions, assessments, query responses, and some standard fields are captured in this table:
We’re pleased to offer several MEDITECH Report Writing webinars this summer, free to hospital employees. Register to attend any or all these presentations from the comfort of your own office on the dates noted below. Scroll down for registration links and a description of each webinar:
- NPR and Report Designer Tips & Tricks
- 6.1 Reports Migration
- Make Data Repository Reports More Attractive & Functional
- DR Resource CenterTM Demonstration
It may not be obvious when working in SQL Server Data Tools or Business Intelligence Development Studio, but when you add datasets to a SQL Server Reporting Services (SSRS) Report, they get an implied order of execution. Say you have a MEDITECH EDM dashboard, and it has some datasets that control parameter lists, and then two datasets that call stored procedures to get patient data and calculate median timings:
Most, if not all of us involved in US healthcare are familiar with the National Provider Identifier (NPI), which is a 10-digit number starting with a 1 or a 2. Each NPI uniquely identifies a specific provider. The provider can be a person or organization. NPIs are used and embedded in billing and claims workflows.