Common Date Calculations for Parameters and SQL variables

I’m commonly asked to make the parameters for the date start with specific months. I’m typically asked for its First Day Last Month or Last Day Previous Month. Here are the T-SQL and SSRS Expressions to create the dates:

Using T-SQL:

--First Day of Current Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

--Last Day of Current Month

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

 --First Day of Previous Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

--Last Day of Previous Month

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

 

When you need to create these directly in the SSRS Report. Use the following:

 

--First date of Current Month

=Today.AddDays(1-Today.Day)

 --last date of Current Month

=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

 --First date of Current Year

=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

 --Last date of Current Year

=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)

 --Previous Month Last Date

=DateAdd("d",-(Day(today)), Today)

--Previous Month First Date

=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))

Creating a Azure SQL Server Reporting Portal for Public Usage

I love using Windows Azure.

This technology saves you a lot of time when it comes to deploying Microsoft server technology. No longer do we have get a physical server, install all the appropriate hardware, install the all the software, configure the software, and not to mention the security, change control, governance, etc. and all that is required to get just the server up and running.  With Windows Azure, you simply need to just open up the Microsoft Azure portal and create the appropriate virtual machine and pick the required template. Tasks that used to take day can now be done in an hour.

Making your SQL Azure server available for Internet users essentially consists of the following two tasks:

1. Opening HTTP/HTTPS endpoint for your VM in Azure management portal (or during the installation of the VM)
2. Establish a firewall port on the VM (After the VM has been installed)

The process is as follows...

First we have to create a Virtual Machine for the SQL Server. You can use any SQL Server version as all have the SQL Reporting Services components installed.

 

Next, when creating your VM, machine sure you add the option to connect via HTTP. This is done by adding the HTTP endpoint. You can specify a Private and Public port (80 is the default for HTTP).

 

Continue until the provision completes (i.e. Creating Virtual Machine,). Depending on the size of the VM (in this example I’m creating a SQL Server Web Server), it should take about 20 minutes to an hour. You have to establish the SQL Reporting Services as by default, the URL, Manager, and the Reporting database is not installed (version SQL 2014).

Open Windows PowerShell on your Virtual Machine and open a port so this VM is available for Internet users.

                 i.          Open a command windows (cmd.exe)

                ii.          When the command widows opens, run PowerShell (Powershell.exe)
               iii.          Run the following command via PowerShell on the Virtual Machine:

New-NetFirewallRule -DisplayName “Report Server Name”-Direction Inbound –Protocol TCP –LocalPort 80

Open the SQL Services Reporting Configuration Manager to now configure the SQL Reporting Service. First open up the tool and Connect to the existing SQL Server. Next establish the SSRS Web Service URL…

 

Next establish the SSRS Web Service URL…

 

Make sure you create the database next. Follow the wizard and input the required information. This is a straightforward process that will either create a new database or use an existing one. I would suggest creating a new one as it is a lot easier.

Next create the actually SSRS Reporting Portal that you will typically use to upload your reports.

 

Once you have enabled / installed the SQL Reporting Database, the Report Server websites, you can now connect directly via a browser to the server. You will have to use the fully qualified domain name (FQDN) as follows:

<Servername>.cloudapp.net/reports  (*.cloudapp.net is the default Azure DNS)

You can now have users connect to the reports, develop reports and use other SQL Reporting tasks that you normally accomplish via a SQL Server on your premises.

 

Using Alternating Row Colors for SSRS

Outside of the making sure that reports reflect the accuracy of the data, it’s also important to make sure the reports are easy to read. One of the most effective ways to make reports easy to ready is to ensure that the visibility of data aligns with the natural way people read data – left to right and top to bottom.

Using alternating row colors not only can make the report more visually pleasing, but also simply easier to read. There are several ways to accomplish this but the most methods would working using to common SSRS expressions: RunningValue and RowNumber. RowNumber is a bit simpler and requires less values but RunningValue is more reliable.

You can use RowNumber when you are using a simply Tablix (Table) where there is no Tablix Grouping defined. Tables are a popular method for displaying data. When you have rows with many columns (8 or more) you can make the tables easier to read by adding alternating color to the rows. Even without a lot of columns, alternative row colors can still make an impactful look on columns. For example, when we look at the following column…

This example using a column where there is no grouping. Therefore, we could simply using a RowNumber SSRS Expression. This requires specifying the textbox background using the following expression to generate alternative row colors… =iif(ROWNUMBER(nothing) MOD 2,"White","Gainsboro").

Again, this method works great when we are using a Tablix that does not use group or aggregations. When using grouping, I suggest you instead try RunningValue to accomplish alternating row colors because the row number are not often sequential when using grouping. In this case I have grouped the items by Conference and then allow drill down to the Institution category.

I would suggest then using RunningValue as the method to establish the alternative columns. You would then use the following method instead

=IIF(RunningValue(Fields!Resource_No_Name.Value,CountDistinct,Nothing) Mod 2,"Silver ", "White")

Whatever type of Tablix configuration that is being used, either RunningValue or RowNumber should be part of the SSRS expression use setting alternate row colors.

Creating YTD Calculation in SSRS

Recently I wrote on a method to create YTD calculations using MDX without making modification to the SSAS cube. However, there will often be scenarios where you need an YTD calculation and you are not using a cube as a data source. In these cases you are at the mercy of making an YTD calculation using SQL. For those that have done this before, you know how difficult that can be and how much code you have to write to get that working.
You can creating this using a combination of a Tablix, Matrix, or a Chart object. The required SSRS Expression needed is RUNNINGVALUE. The example of this expression is as follows…

= RunningValue(Fields!Total_Cost.Value,Sum,"Chart3")

Here is an example of using RunningValue to accumulate YTD values…

 

  Using this example, I specific the following formula in the Tablix for the YTD…

 

 The same could be done for SSRS Matrix and Chart objects as well.

YTD Calculation in Query Designer

One of the most common requested aggregations in MDX is YTD. There are many sources that highlight how to do this in the Cube development environment, but sometimes you don’t have access to the SQL Data Tools / BIDs.

I find using the native YTD calculation problematic when attempting to make YTD calculation outside of the Cube development environment. The YTD function can be extremely slow to process outside of the Cube development environment.  Also, if you’re like me many times you only have access to read the cube but not develop against it. You should then you use the Query Builder to create an YTD calculation.

Basically use the Calculated Member Builder in the Query Designer (whether in Report Builder or Visual Studio).

In my example, I use the following data to create the YTD calculation against the Date Hierarchy PostingPeriod.Fiscal and the Measure.LaborCost.

Aggregate

 (
 PeriodsToDate
 (
[Posting Period].[Fiscal].[Fiscal Year]
 , [Posting Period].[Fiscal].CurrentMember
 )
 ,[Measures].[Labor Cost]
 )

 This how looks in the Calculated Member in Query Designer…

  This is one of the most useful features of the Calculated Member tool – allowing you to create dynamic aggregations without cube authoring..

 

Using SSRS to match colors

There are times when you have to match RGB colors in SSRS to Hex values that are defined by the business requirements. I save myself a lot of time by using websites that do this conversion for you. Here is one site to convert Hex to RGB and vice versa.

http://www.rgbtohex.net/hextorgb/ OR http://www.rgbtohex.net/

Simple but effective.

Getting the exact color

Sometimes you need the exact color codes (RGB or Hex) to match incoming graphics with SSRS colors. I would suggest that if you are not provided with company colors schemes already in this format (RGB/Hex), use the standard colors in SSRS in then work from there.

1.      Select the color you want. Do this by picking any object (text, rectangle, background, etc.) and Select properties. Choose the appropriate object to needs a specific color and then expression. In this example, I’m using Khaki. Select Expression…

2.      Under the Expression Dialog box, you will see the option More Colors in the Value Box. Select More Colors. This will bring you to the Select Color…

 

3.      Select the Color Circle Bar, move it to the top position. And this will give you the Color Sample “Khaki” and another split color for you to manipulate. We can record the RED, GREEN,BLUE color code for Khaki and use that to color object that need to match SSRS objects.

 

NOTE: Where trying to match graphics from incoming applications into SSRS, try using the PNG graphic format instead of JPG. PNG is a good choice for storing iconic graphics in SSRS and often has zero loss of quality regardless of the sizing (which should be small in SSRS). I use this all the time and am never disappointed when importing graphics of this type into SSRS.

Fun with SSRS Rectangle objects.

One of the most misunderstood features of SSRS is the use of the Rectangles. At first glance, it may appear that rectangles are simply shapes you can add to your reports in the same way you would add circles, lines, and other shapes. That can’t be further from the truth. In fact, rectangles are one of the more powerful objects in SSRS. The main purpose of rectangles is not only to display a box, but instead group all other SSRS objects (tablix, matrix, graphs, text box, etc.) in to a defined area on the report body to allow for “grouped” configurations. Here is an example of a use a rectangles. Let’s say we have three SSRS objects - a chart, a tablix, and a text box.

When we run the report, the spacing changes so that the first tablix is displayed and unused space is added unnecessarily. Without using rectangles as containers, we are at the mercy of where the report rendering process displays the object.

 

 If I added the chart and the second tablix in the rectangle (i.e. green box) in the design mode I force the SSRS to display the tablix at a certain location.

When we run it the second time, the two sections are not generating the excessive space between to two charts but instead keeping the two objects within the rectangle.

As we see in these examples, If we place these on the report body, and display the reports, SSRS will display the objects right to left in processing the display, and once that item is complete, it will show the next object. This can produce unexpected object locations.  Again, to force SSRS to display report objects together, we include the report objects inside the rectangle. This keeps all these items grouped together.

 

Another example of rectangle object usefulness is to allow you to move around sections of report objects when editing. This can be really a time saver when you have spent a lot of time lining up report objects in different sections through spacing and you need to move that entire grouping of report objects without have to drag individual sections and repositioning the reports. If you always start with grouping you report layout inside rectangles, you an easily move these reports object simply by moving the rectangles. That approach saves me hours across report projects.

I always felt if Microsoft would have named the rectangle object the “Item Grouping” object that would make it clear to the report author that this object is not simply to draw a rectangle, but as a container grouping feature to define how objects are displayed on the report.