Wednesday, April 30, 2014

The SSRS Document Map


So, you need to create a large report with multiple objects, tables, and graphs.  Most likely the business user will want this output to Excel, PDF, or Word.  It will be very annoying for the user to have to scroll to page x in order to see that certain graph or chart.  It would be nice to have a table of contents or some other type of bookmarks for a user to navigate to the section they desire.

Tuesday, April 22, 2014

Create C# Classes from JSON


So you're working with JSON data and you need to create C# classes to hold the deserialized data.  Naturally you're going to use the JSON.Net library to handle the deserialization, but you would like something to give you a jump start on the C# classes.  You found this json2csharp page that allows you to paste in some JSON and have it generate your C# classes for you.  Now with just a little bit of cleanup you have your working classes!

Wednesday, April 16, 2014

Function to Only Display Last Word in SSRS


So you have an MDX query that returns back months like "FY 2013 - 2014 January", but you only want to show "January" on the report.

Here is the function to accomplish this:

Tuesday, April 15, 2014

SSRS Lookup Function Solves MDX Issue


So you have an MDX query that you are using in an SSRS report. You need to show subtotals and totals, but there are a couple of issues:
  1. The totals and subtotals are on averages which are difficult to total
  2. The objects being counted could span multiple groups so that a sum on the report would not accurately match what's coming from the cube
The SSRS Lookup function can help solve this issue!

Monday, April 14, 2014

Auditing MS SQL Dependencies


So one of the concepts discussed at the Kimball training was around audit metadata and tracking the objects and actions that get a Dimension or Fact to its final result. This definitely seems like it could be valuable but also could require a lot of effort.  Unless it is a firm business requirement, it isn't likely to be implemented due to the large amount of time and effort to track and document the lineage.

However, some of the tracking might be already done for you!

Friday, April 11, 2014

Generate C# Properties from SQL Table


So, you like to design your models in the database but need them in C# as well. To make the recreation in C# quicker, you created a SQL script to convert the table columns into C# Properties.

Thursday, April 10, 2014

Reviewing SQL Schema Dates


So you're wanting to know some basic dates around a schema in SQL Server.  Unfortunately the "sys.schemas" view doesn't have anything to indicate create or modify dates.  Furthermore if the schema creation isn't logged, then the only way to review this is to relate the schema to the "sys.objects" view.

Monday, April 7, 2014

Passing Multi-Valued Parameters to a Linked Report

Dear Josh,

This one has been bothering you for sometime and you finally figured it a couple weeks ago.  The scenario is you have a summary report, like a map, and a detail report.  You want to allow a user to filter certain things on the summary and be able to drill down into the detail of an element.

The issue is if there are parameters with multiple values selected, how do you pass all of the selected values to the linked report?

Friday, April 4, 2014

Row Level Security in MS SQL

Dear Josh,

You just got back from the excellent Kimball DW/BI Lifecycle in Depth in NYC, and one of the concepts briefly mentioned was row level security.  You made a note to look into this and came up with this demo script: