In part 1 of this blog, we covered at a high level the basics of T-SQL user defined functions. In part 2 we’ll explore the advantages of leveraging functions, and when to do so.

I recently had a student from one of our online training sessions email me a few days after class, asking why I recommend always using the SourceID column as part of every TSQL join. They sent two queries to demonstrate why they felt they shouldn’t use SourceID.

So, in plain English, what is a T-SQL user-defined function? A function is a SQL query saved as a database object, which leverages and outputs data to serve a focused purpose. Once a function is created it easily can be utilized at any time by anyone with proper access. Measured creation and execution of functions will greatly streamline data production from your T-SQL environment, allowing technology to do what it’s supposed to - make our lives easier! Let’s start with a simple example to better explain why we should be using functions and illustrate different types.

MEDITECH's Data Repository best practices recommend a dedicated drive for tempdb data files, that the space allocated to this drive be 25-30% of total data space for the SQL Instance, and that the allocated space be split between eight data files and one log file. Is it possible to modify tempdb to meet these standards? Read on to learn how.

In part 1 of this blog, we discussed how to build a URL that could navigate to various resources on the internet. We learned how to build the URL string based on a combination of the website path and the data within our results. In part 2, we’ll look at a similar example of how to navigate to another SSRS report on our local network that resides in a different project folder. Using the same technique, we can build the navigation link and pass parameters into another report instead of using the default SSRS method which only links to reports in the same folder.

Part 1 - Navigating to an Internet site

One of the most obvious features of SQL Server Reporting Services is that the reports are presented to the user with a web browser. This means that SSRS natively has access to many of the same functional principles as most web pages and web sites. Specifically, this would include the use of hyperlinks (URLs) to easily navigate to other reports, web pages, or other web-based resources. How do we do that? With the Action property.

Technically speaking, MEDITECH's Data Repository is a typical relational database, in that it stores data in tables, uses primary key columns for referential integrity, and is interacted with using off-the-shelf tools like Microsoft SQL Server Management Studio. But there are several aspects that make it unique – and challenging to work with – all of which are important to understand if you’re a data analyst or report writer at a MEDITECH hospital.

One of my favorite features of SQL Server Reporting Services is the ability to provide a parameter with multiple values. This is a great way to give your users flexibility at runtime by allowing them to select one, two, several, or all values from a given list. It even has a built-in way to easily select and de-select all the possible values. A common scenario that would benefit from this is when you are going to publish a single report that could potentially be used by multiple locations or departments.

How do I find data in the MEDITECH Data Repository?

Many new DR report writers have a challenging time locating data in the DR because they're unfamiliar with the database table and column naming conventions. Luckily for those users, MEDITECH has provided tools within each application as well as system level tables for both the NPR and M-AT platforms that help to identify table and column data.

Knowing which tables to use is one of the biggest challenges when writing DR reports. Understanding those tables, and the relationships between them is part of that challenge. Primary keys are a critical part of any database, but especially so in the DR, since they are the only definition we have for how to correctly and quickly get information.

As report writers and analysts, we sometimes have to ask: how do I go about optimizing data retrieval from the MEDITECH Data Repository? When is it appropriate to construct a data mart and what should I consider?  Before we explore the answer, let's first, define a few terms (thanks to Technopedia and Wikipedia):

Whether you’re GPSing around traffic, going through a drive-thru, or using ALT+TAB to switch between open windows, life demands shortcuts. You know the adage – work smarter, not harder. Working with SQL Server is no different - if you’re a busy developer or DBA, you likely spend part of your day writing TSQL code in Management Studio. Fortunately, Microsoft has built in features we can use to make our jobs a bit easier. Here are a few recommended shortcuts and other tips to help save you time and keystrokes.