Understanding Row Level Security (Before SQL Server 2016)

As I’ve posted before, writing this blog is much easier when I focus on things that interest me, which aren’t purely technical generally. That said, some technical questions get asked so many times I feel moved to write the answer.

And that’s the case with today’s post.

Row Level Security and SSRS

Say you have a company with multiple territories – Northwest, Northeast, Central, Southwest, and Southeast. (For this example, I’m using MS AdventureWorks 2012 sample database.)

You have three employees: Jim, Dan and Adam.

  • Jim should see data for all territories
  • Dan should see data for the Northwest territory
  • Adam should see data for the Southwest and Southeast territories.

Most ERP packages allow you to set up security so that each user can only see the data for his/her division when they use the application.

The problem: Generally, that security isn’t defined at the database level. So, if you use a tool like SSRS, if Dan has access to one region’s sales report, he has access to all region sales reports. Which isn’t want you want.

SQL Server 2016 has an advanced feature where you can establish row level security at the database level. But it’s newly released and has its issues. It also doesn’t apply to my customers.

Further, using it with an ERP would require modifying the underlying database of your ERP software, and I DON’T LIKE DOING THAT.

The Workaround Solution

Fortunately, there’s a workaround.

We start by creating a table that states which employees have access to which territories. It can be as simple as this:

Basically, we create this table so we can use it for different security scenarios, rather than just one report. The “ObjectName” field allows us to secure reports based on different values.

For example, we may want to secure one report by sales territories and another by cost center. So, we can create records with object name set to either “TERRITORY” or “COST CENTER.” In this example, we’ll use “TERRITORY.”

Then, we populate the table with data:

We can now use this with a sample query to show total sales by territory:

The last join takes “TERRITORY” and joins it to the ObjectsSecured table based on the name.

We run the query three times and copy the results into Excel. As you can see, each person only receives data for the assigned territories:

Now, let’s move to SSRS. Here’s the sample report:

We want to use the built-in field called “User ID.” Unfortunately, we can’t use it directly in our query as the system won’t recognize it. (Certainly, I haven’t gotten it to work.)

Instead, we create a hidden parameter that we’ll pass to the query. Here’s the basic definition (shown as hidden):

Now, we go to default values. The default value is set as an expression:

And now the expression:

You’ll note I’ve done something a little fancy here. “UserID” will deliver the domain name as well as the user ID. As domain names can change (and user names are always unique for my customers), I prefer to keep security on a name only basis.

Now that we have our parameter, we can use it in the query:

And when we run it, we get what we expect:

 

Building an SSRS Trial Balance for Dynamics GP

In this post, I’ll show you how to create a view in SSRS that allows Dynamics GP users to create a trial balance for any time period (or multiple periods) and get the ending balance for that period(s).

I was motivated to write this post after seeing a question on the GPUG forum about it. The question was referred to Victoria Yudin, a GP SQL guru, and she has a nice stored procedure to get a 12-month trial balance for the same year.

Also, I’ve been developing some generic design patterns for financial reporting in SSRS and as part of this project we’ve been asked to create trial balances for the last 12-month period—so the topic has been on my mind.

Why Views Instead of Stored Procedures?

While Victoria’s solution is good, I want to add to it by allowing users to return trial balances for any period, not just the last 12 months. Also, I wanted to use a view instead of a stored procedure for a couple reasons:

  1. From a users perspective, views work just like tables. So, for example, a user can use views in the SSRS Report Builder Report Wizard where they can’t use a stored procedure.
  2. Stored procedures tend to multiply and/or require lots of maintenance. If you add output fields to a stored procedure, you have to either create a new procedure or fix all your existing reports. But, if you add a few new fields to a view, your existing reports don’t change. (But this is really a topic for another blog post.)

So let’s start where Victoria started—with the balance table. I’m going to use the Account Summary view as it contains both open and historical data. This query takes the period balances that are stored in GP and totals them for each period. So, we get the ending balance for each period:

And here’s the output:

It’s not quite what we’re looking for. It’s great that each period “rolls forward” to include previous balances. But GP only stores data in the table when there was some activity in the period.

List all Account/Fiscal Period Combinations

A Dynamics GP Consultant can fix this by starting with a list of all account/fiscal period combinations that exist, instead of starting with the amount data. Here’s the query:

And here’s the sample output:

This is much better.

By the way, in financial reporting we often want to see all accounts, whether or not they had activity. Or if they had budgeted activity but not actual activity. Or actual activity but not budget. The solution is to start with a list of what we want to see, rather than start with the amount information.

Now that I have a list of possible accounts and periods, I use a left outer join to check for actual activity:

When I run the above query, I get the output I want—with a value for every period:

Next, I’m going to run the above query into a view. Note that when doing this, I remove the “order by” clause as well as the check for fiscal year, which I used for testing.

With the above view, I can create an SSRS report which groups fiscal year and periods as columns and account descriptions as rows:

Filtering Time Periods

Now, if I want to choose only a few periods, I can do so by changing the parameters. But remember, I must filter the time periods in SSRS, not in the SQL query. Why? Because to calculate balances correctly, I need to select periods 1-6 in order to get period 7.

For example, let’s say I want to select the last 12 months. I tell the query to select two year’s worth of data:

I’ve purposely broken the filter process into multiple steps. I like to segment the process because multi-part formulas are harder to debug when you return to your reports months (or years) later.

In each case, I’m combining fiscal year and period into a six-digit number. For example:

  • 2015 period 10 becomes 201510
  • 2015 period 6 becomes 201506.

I do this three times: (1) for the fiscal year and period from the data returned from the query, (2) for the fiscal year and period from the query result and, (3) for the starting fiscal year and period, which requires a little twist in the logic.

All these fields can be created from Dataset->Properties->Fields:

Which I define as:

Now, I’m going to establish the ending period of the report, which I get directly from my parameters:

The logic for the starting period is a little more complex. Basically, I have two situations: period 12 and all other periods. For anything but 12, the logic is simple:

  • Subtract one from the year
  • Add one to the period.

So, 2015 period 10 becomes 2014 11.

For period 12, I want to start at period 1 for the same year.

So, the year remains the same. The period becomes 1.

Here’s how the logic looks in SSRS:

Now that I have the three date fields, I can easily create the filter.

Choose Dataset->Properties->Filters:

I state that “fiscal year and period” must be between “start fiscal year and period” and “end fiscal year and period.” I also exclude period 0, which represents the balance brought forward.

Here’s the output:

A Word of Warning

I acknowledge that using filters in SSRS degrades performance. I’m only using filters here because I’m crossing years. If I simply wanted all months for the current year, I wouldn’t need a filter because I could incorporate the record selection into the query.

If you’re looking for a Dynamics GP Consultant or would like a copy of the sample report, let us know.

 

Listing Your SSRS Reports With a SSRS Report—Part 2

A few posts ago, I showed you how to create a nice hierarchical listing of all your SSRS reports within SSRS. While the hierarchical listing is nice, I think it’s even better to have each folder in a separate column (which makes it really easy to dump to Excel and then filter by column).

I’ll show you how to do it in this post.

At the end of the post, I’ve included a link to download the source code. Also note, I’ve created a function in this example, so you’ll need to be able to create the function on your database.

Let’s start with our basic query. Again, I’m selecting only report objects from the catalog table:

Now that we have the basic query, we want to parse the path into its individual pieces. We use the CHARINDEX function to find the characters in the path string and separate them into pieces. The code looks like this:

Basically, this loops through the path string, looking for / marks and placing each folder into a different variable.

I’ve set this one up to work for nine levels as the number of columns returned from a function must be fixed. This was certainly one of those times I wanted to clear an array. But I digress.

So, we have our query and function. We can then combine them into one query:

And the result has each folder in a separate column:

Once you have your query working, you can easily paste it into your SSRS report.

Rather than cut and paste the values used above, you can download the source code here.

* * *

For more of this kind of content, sign up for our newsletter.

 

Finding Stored Procedures Used by SSRS Reports

Recently, I was asked which stored procedures power which Dynamics SSRS reports. If you want to go report by report, you can download any report from your SSRS website and open it in ReportBuilder.

But, as I was on a roll with my recent SSRS catalog query, I decided there had to be a way to get this information through a query. And I figured it out.

(I have to say, after working through this problem, I have a burning desire to get away from T-SQL for a while (even though it was fun because I learned a bunch of stuff). My brain hurts. It’s time to get back to some strategic IT consulting.)

Below is the query that generates the information. Thanks to Sankar Reddy on MSSQLTips.com whose article was instrumental in helping me figure this out.

Note: You can download the query from the link provided at the end of this blog post.

Here’s some sample output:

You can adapt this query to run over your root report folder. You can also opt to see all queries, not just stored procedures, by commenting this out:

Keep in mind that query strings can be much longer than calls to stored procedures—and the data won’t dump to Excel very well.

If this query makes your brain hurt too, I’ve dumped the results from the Dynamics GP sample company into an Excel spreadsheet. Download the query and spreadsheet here.

If you’d like similar content delivered to your inbox, sign up for our newsletter. Simply enter your email into the sign up box located on the top right of this page.

HubSpot Call-to-Action Code end HubSpot Call-to-Action Code

 

List All Your SSRS Reports—With a SSRS Report

Reports have a habit of multiplying like crazy, no matter what reporting tool you use. You’re soon left to wonder, “Where did I put that report?” or, if you’re working with Dynamics GP, “What reports can I get out of this thing?” To help you answer these questions, I’m going to do three things in this post:

  • Show a basic query of the Catalog table to get report and folder information right from SSRS.
  • Use the data in the SSRS report to build a nice list of all stored reports, starting with a beginning base folder.
  • Provide a link to an Excel sheet for Dynamics GP folks who just want a listing of the reports.

Please note: This isn’t an ideal solution—at least not yet. In the report, I can only select at the top of the hierarchy. I have to perform more coding in T-SQL to select lower levels. A more elegant solution would use a recursive query with T-SQL, and I’m working on that.

Querying the SSRS Catalog Table

The Catalog table is stored in the ReportServer database. On many systems, the database will be named ReportServer (or something similar), but you’ll have to look to find out. Two things to note:

  • Microsoft says you should use the Web Services APIs for SSRS to access this information. They don’t guarantee that queries working over the database directly will work. (You’ve been warned.)
  • There are many solid posts available online about the SSRS ReportServer database. Scott Murray’s post on MSSQLTips.com is one of the most comprehensible.

That said, here’s the basic query I’m using:

I’ve only selected folders (type 1) and reports (type 2) because this listing is for end users.

I’m running this for a particular root folder, in this case “TWO” (the sample company for Dynamics GP). Of course, you can choose the folder you want or run the output for the entire catalog table.

Here’s the sample output dumped to Excel:

This is hierarchical data, so seeing only “child” and “parent” isn’t that useful, even if you sorted it. As mentioned above, you can create a recursive SQL statement to show the full hierarchy, but I haven’t done it yet. (You can do it easily in SSRS.)

Creating a Hierarchical Report for this Listing in SSRS

I’m not going to explain all the details of a hierarchical report here. If you’re interested, Ahmad Ahmad on Code Project provides detailed instructions. While I’m using different data, I am using the same colors as Ahmad. (Thank you!)

Here’s a sample of the output:

See how the child names are indented to reflect the different hierarchical levels. The level is displayed in column 1. I’ve also created a drop down for the root folder at the top.

Dynamics GP Standard SSRS Reports

If you want all these materials, follow this link on listing SSRS reports to a zip folder. I’ve included:

  • An Excel Sheet with a list of all the SSRS reports I found on GP 2013.
  • The query used above.
  • The SSRS report. The report uses an embedded data source, which I only do when sending out samples. Data sources always need to be changed anyway.
  • Instructions for changing the data source. I didn’t create the instructions specific to this example, but if you haven’t changed the data source before it should still help.

If you’re interested in this kind of content, sign up for our email newsletter. The sign up box is located to the top right of this page.

 

Seven Keys to Cost Effective Business Intelligence: Intro

There’s No Such Thing as a Data Consultant

Lately, I’ve been presenting on the topic “7 Keys to Cost Effective Financial Business Intelligence.” I’ll be covering this topic here in a series of blog posts too. But before I start, I want to use this post to discuss the title. What is “financial business intelligence” anyway? How does it differ from “regular” business intelligence? What do we mean at Red Three when we say our focus is data for finance and accounting?

To explain, let me start with a key belief (and something that has taken me years to acknowledge): There’s no such thing as a generic data consultant. Sure, it sounds cool to call yourself a data consultant, especially when “big data” regularly makes the front page of the newspaper. Suddenly, people who have no idea what you do, think they do know.

But understanding data in general isn’t the same as understanding a particular kind of data. Yes, people may know you’re probably not the person to ask about graphic design. Or the latest fashions. But knowing about consolidations and valuations and gross profit is very different from knowing about Facebook posts or marketing data. Because end users won’t (and often can’t) explain what all this means. If you’re going to work in finance, you have to know finance. And the reverse holds true. If you’ve decided to work in finance, you need to acknowledge that you’re not the person to help with marketing.

Now let me get into the term “financial business intelligence.” Two things differentiate financial business intelligence from regular business intelligence: focus and audience.

Focus: Financial business intelligence is entirely focused on understanding how your company makes money. To reach this understanding, we might build complex financial reports or calculate your contribution margin by project, product or customer. But no matter what we do, the focus is always dollars.

At Red Three, we know how to look at data to make sure EVERYONE in your organization has a consistent view of how different activities contribute to the bottom line. We understand the basic processes and mechanics underlying your business as well as the complex accounting rules that sometimes confuse end users and keep different sets of numbers out of place.

Audience: The audience for financial business intelligence is “number guys”—CFOs, directors, controllers and the people they serve. Numbers count for these people. If we can use their existing tools to produce and deliver numbers consistently, then they’re happy. They aren’t wowed by the promise of expensive software. That’s why we focus on the Microsoft Business Intelligence Stack—including Excel, SQL Server and SSRS. After all, if you’re like most mid-size companies, you probably already own SQL server and the reporting tools that go with it. And if those can get the job done (which they do), then there’s little reason to spend more money.

In the next series of posts, I’ll discuss how you can get financial business intelligence cost effectively.

 

SSRS Tabbed Reports

Everybody who’s online these days uses tabs (even Internet Explorer users!). Yet SQL Server Reporting Services doesn’t natively support tabs for its reports. It does, however, support all the building blocks necessary to create tabs yourself. In this post, we’ll post show you one way to do it.

So when would you want to use tabs? Let’s say you have multiple companies within your organization. Perhaps you’d like the first tab to show consolidated results for the company and then subsequent tabs to show results for each subsequent company. Or perhaps you’d like different tabs for each line of business or sales person?

In our example, we use data from Lawson GL where company is a legal entity and Acct Unit is a profit center.

Let’s use a simple report created by the SSRS report wizard to illustrate:

The idea is simple: Create an additional tablix (a.k.a. table) without data to hold our “tabs” in the header row. Add a hidden parameter for the report, which our tabs drive. The hidden parameter, in turn, drives our dataset. Finally, add some conditional formatting to each tab, so we know which tab is selected.

Here’s how to do it: Insert a table and delete the data row. This table will be the only place where we add/modify/change our tabs.

Add some descriptive names to each column. In this case, let’s called them Company 1, 3, 5, etc., based on the company we wish to see. We also add “All Companies” which is our default tab:

Next, add a hidden parameter to drive our selections:

The default value is zero. This will make sense in a moment if it doesn’t already:

Our dataset query (which started off as “Select * from GLNAMES” in the wizard) will now select companies based on this parameter, or all companies if the parameter is 0.

You can apply this “or @Parameter = 0” technique to any parameters. Use blanks or nulls if the values aren’t numeric.

To tie this all together, we need to add actual tab functionality so that when we click on a tab, our data is updated automatically.

To do this, go back to the tabs and give each one an action:

Do this for all four tabs (or however many you’ve created) changing the parameter under “Use these parameters to run the report” accordingly. Company 1 gets parameter value 1, etc.

Finally, give the tabs custom styling so users can easily see which tab is currently selected, even when it’s not evident from the data (e.g. “All Companies” and “Company 1” look very similar.)

Here, we’ve created custom “BackgroundColor” and “Color” properties for these tabs. You can also use images (selected and not selected) if you prefer.

BackgroundColor: =IIF(Parameters!Company.Value = 0,”SteelBlue”,Nothing)

Color: =IIF(Parameters!Company.Value = 0,”White”,”Black”)

Do the same for each tab, again changing the parameter value you’re expecting inside the “IIF” clause. You can also use the value “Nothing” if “Transparent” or “White” gives you warnings.

Here’s what it looks like when deployed:

Here we used a combination of “go to” report action and a hidden parameter, but there are other ways to achieve this functionality. For example, multiple tablixes can be hidden or shown instead.

If you’re looking for a Microsoft SSRS Consultant, interested in more information or want to request the code, feel free to contact us.

 

Creating SSRS Parameters—From Simple to Complex

As you know, adding parameters to a SSRS report gives users the ability to filter and format data. In this post, we’ll show you an easy way to add parameters in SSRS, how to add drop-downs and how to allow users to select multiple parameters.

While there are a number of ways to add parameters in SSRS, one of the quickest is directly from Query Designer.

Use the form @variable_name. This will automatically create parameter objects in your report:

Your newly created parameter has no default value and no drop-downs for value selection. It’s simply a bare-bones textbox:

User-friendly parameters

To make parameters more user-friendly, you can add drop-down menus (or drop-downs). Start by creating a dataset for the parameter that includes all possible parameter values.

(Note: If your SSRS supports shared datasets, and if this parameter is to appear on multiple reports, then you can create a single shared dataset for greater efficiency.)

Once you’ve set up the dataset, the next step is to assign available values:

The values you assign will be displayed in the drop-down. In this example, the default values are all the available values. (Note: Set up of default values is optional.) Of course, you can customize this using the “Specify Values” option or by using a “where” clause in the query.

When we put all these pieces together, we get a much more functional and friendly prompt:

Multiple value parameters

If you want, you can go one step further and allow users to select multiple values using the prompt. (Note: This can be problematic with some sets of data but for most types of values it’s straightforward.)

First, check “Allow multiple values” for the parameter:

Second, update the “where” clause to use the “IN” operator. (The parentheses around the @variable are important, so be sure to include them.)

The result is an even more useful prompt:

In some older versions of SSRS/SQL Server or for certain data, you may run into problems when implementing multiple value parameters. Unfortunately, the solutions to these types are problems are often complex and may require extra parameter processing via stored procedures. If you run into these difficulties, feel free to contact us for more information.

 

SSRS – Adding Date to Exported Filenames

When you export a report from SSRS (to PDF or Excel, for example) the filename defaults to the name of your report. But recently, one of our clients wanted to have the current system date automatically added to the report name. For example, if the report is called “Task List” and it was run on January 1, the client wanted the file saved as “Task List 2013_01_01.” (Yes, you can do this manually but generating it automatically makes it more user proof.)

Unfortunately, there’s no easy way to do this. As a Microsoft SSRS Consultant, we did come up a few methods, two of which we’ll describe below. Please note: These solutions are NOT supported by Microsoft – if your team is caught, the Secretary will disavow all knowledge of your actions. (We’re joking, of course.)

The ReportServer.dbo.Catalog Method

SSRS stores its metadata in a SQL Server database called ReportServer. In that database the directory structure of the SSRS installation is stored in a table called Catalog. This table contains the root, the folders, all of the connection files, reports, etc.

Of interest here are the Path and Name columns. They are one part of the pathway that drives the default export filename. Conceivably, a SQL procedure could change these to meet your requirements and be scheduled to run on a regular basis.

However, because of the clunky nature of this method (exporting a report is client-side, so the solution should ideally be client-side as well) and its various side effects (this method would change the report name, not just the default export filename) we moved to the next method.

Free SSRS Reports for Dynamics NAV

If you’re transitioning to Dynamics NAV from other systems (such as Lawson), check out our free SSRS reports.

The Javascript Hook Method

Like most web apps, SSRS executes the ECMAscript standard – aka Javascript – on the browser. By inspecting the web app that SSRS delivers, we tracked down the logic that exports reports. It’s a piece of SSRS called ReportViewer. However, SSRS delivers this piece to the browser indirectly, which makes it difficult to customize.

Along the way, we found that SSRS does serve at least one Javascript file directly: it’s called ReportingServices.js, a collection of odd helper functions such as PadIntWithZero() and confirmDelete(). This file is typically located in <SQL Server Folder> \Reporting Services\ReportManager\js, and that’s how we got into the fortress. By exploiting the flexibility of the Document Object Model, we could hook into the ReportViewer object by modifying the seemingly unrelated ReportingServices.js – as long as it was all part of SSRS.

The actual code customization was trivial. In fact, we found a user modification online for MS Dynamics CRM that essentially accomplishes the same goal. And since Dynamics CRM uses the ReportViewer module as well, we only had to adapt that code slightly to meet our needs.

The concept is simple: ReportViewer has a variable called ExportUrlBase, which stores the default export filename (among other things). So, all we had to do was modify ExportUrlBase and change the default filename.

In the above code, the try-catch block as well as the if(r == null) block are set up so that if the function is called before the ReportViewer module is fully loaded, it won’t modify ExportUrlBase, but will instead keep checking every second to see if ReportViewer has finished loading.

The else block runs once ReportViewer has finished loading. It was there we modified ReportUrlBase, finding FileName and tacking prefixFileName onto it. To save space, we added only a simple piece of text to the filename, but you could modify this to almost anything, including dates, server-processed data via AJAX, etc. The only drawback with this method is that when SSRS is updated, the Javascript mod will have to be reapplied.

So there you have it. This is the method we used to modify our default export filename, and it does the job well.

 

Repeating Header Rows with SQL Server Reporting Services

I spend a lot of time with a large variety of software. Some of it is really specialized stuff most people have never heard of, and the complexity required to use it borders on elaborate voodoo rituals. So it’s a rare breath of fresh air to discover an app that Just Works™, such as SQL Server Reporting Services.

Except when it doesn’t. Sometimes, SSRS refuses to do what you want it to do. Thankfully, it does have “under the hood” access for the adventurous software spelunker. And, to be fair, I’ve only had one cause to descend into its cavernous depths: repeating a header on every page (instead of just the first page).

Here’s how you do it:

At first it seems obvious and simple: go to Tablix Properties and check the box that labeled “Repeat header rows on each page.” But when you do that, it has no effect on repeating header rows on each page. This is because header rows aren’t actually header rows in SSRS. (Don’t ask. Okay, if you must ask, take a look at Microsoft’s attempt at an answer. Basically, the row headers in the above screenshot are presumed by the dialog window to be group row headers when the tablix is in matrix mode.)

In any case, in order to repeat headers on every page we must first unlock the voodoo. The keys are well hidden, of course, behind a tiny, unlabeled button with a down arrow. I’ll give you ten seconds to find it:

Once you click the button, you can then select “Advanced Mode” which gives you access to automatically created static members which specify how header rows behave:

From there, it’s smooth sailing — provided you know where to look and what setting to set, of course.

1) Select the relevant “Static” groups under “Row Groups” (in my case it’s the top two above “(task_status)” since those light up the top two rows that are my actual header rows)

2) In the properties pane (right column of report builder) for each static member:

  1. RepeatOnNewPage = True
  2. KeepWithGroup = After


Et voilà! The header rows will now show up on every page, like header rows are supposed to. Check out our SSRS services for more information, or to hire us as your SSRS Consultant.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult