SQL Tip — Facility Prompt by User Access

One of the challenges of moving from inside MEDITECH-integrated reporting (NPR or RD) outside into the realm of DR (SQL + SQL Server Reporting Services or Crystal Reports)- based reporting is that you lose the benefits of being integrated.

A key capability lost is the ability to restrict a user to a specific facility's set of data when reporting. Whereas in NPR you might be prompted, for example, to pick a BAR or AD database, which was facility-specific, in the world of the DR you need to be mindful of multiple facilities and filter accordingly.

Which brings us to the question: which facility (or facilities) does the user running this SSRS report have access to? And then – how do we get the report to know which facility to look at? And how does the report know who we are?

Let’s start with which user is running the report first. Inside SSRS, you can declare a parameter (cUserID) and set that parameter to an expression (calculated value):

=User!UserID

Which gets us the currently logged-in Active Directory user on the workstation:

IATRIC\thomas.harlan

When you are setting up this SSRS parameter, you want to make it hidden:

And set the Available Values to code to get the current AD user. Click on each of the Expression buttons:

And set the code in each expression to:

=StrReverse(Left(StrReverse(User!UserID),InStr(StrReverse(User!UserID),"\")-1))

Then do the same on the Default Values screen:

We do this because the format of the AD user ID is not what is stored in MEDITECH. The AD username returned by SSRS looks like:

IATRIC\thomas.harlan

But all we have in MEDITECH is the part after the “\” character. So the expression code returns us everything after the “\” to the end of the string:

thomas.harlan

With that shortened value, we can now call a stored procedure from the next report parameter (cFacilityID) to get all of the facilities where that AD user has access.

In that stored procedure, we need to look at a variety of different tables, to see if (A) we find the ID, and (B) which facility they are attached to:

Livefdb.dbo.MisPerson_JobFacilities
Livefdb.dbo.MisPerson_Jobs
Livefdb.dbo.MisPerson_ProviderFacilityMain
Livendb.dbo.DMisUserAdmMriFacility

Attached to this tip is a stored procedure (IatricFacilityIDFromUserID) which will take that AD user ID as a parameter, then check this constellation of tables and return the list of facility ID(s), SourceID(s) and User ID/Name (for verification purposes).

After you’ve compiled that sp in your DR, you can create a new DataSet in your report for this new stored procedure:

Which will have three fields (we only need two, really, but the UserNameAndID is useful to making sure you’ve gotten it working properly):

…and feed it the cUserID value as a parameter:

Note that the Parameter Value field has the parameter name we created earlier
(cUserID) wrapped in brackets with a leading @. This chains our retrieved cUserID to the stored procedure to get a list of facilities. Now we connect that to the cFacilityID parameter:

We connect to the same stored procedure on the Default Values screen (which, if you have more than one facility assigned to you, will only use the first one in the list):

Now we can carry on with the rest of the report…

Bonus!

Attached to this tip is a template report which implements this code. It’s an excellent idea to start from a reusable template, so you don’t have to go through all of the setup for standard features over and over again…

The stored procedure to get the list of MIS Locations from the Facility ID is also attached, as it’s used in the template report.

Extra Credit

If you’re not at an MT6 site, you don’t need all of the code in the stored procedure. Just the last section where it looks at DMisUserAdmMriFacility will get you the clinical users. The Business/Financial users you’ll need to get from the Client/Server or MEDITECH versions of the Job Table(s).

(end)

Visit our report library at http://www.iatric.com/Information/NPRReportLibrarySearch.aspx to look them up.

You can find additional Report Writing Tips on our website at http://new.iatric.com/report-writing-tips, as well as information about our on-site Report Writer Training and Report Writing Services.

To subscribe for email notifications for new Report Writing classes, please follow this link:
http://new.iatric.com/schedule-of-classes.

For more information, please contact our NPR report writing team at reportwriting@iatric.com.