Defaulting SSRS date parameters to first and last date of the prior month

Challenge

Irrespective of the date a report is run/accessed/loaded, how do you default the date selection parameters to first day of the prior month and the last date of the prior month?

Solution

Start Date Parameter, Default value expression

=DateSerial(Year(Now()), Month(Now()), “1”).AddMonths(-1)

End Date Parameter, Default value expression

=DateSerial(Year(Now()), Month(Now()), “1”).AddDays(-1)

Advertisements

SSRS Example Wrapping a long thin tablix into two columns on the page

I have a report table with only a few columns but lots of rows. When printed it runs to several pages with a lot of white space (and wasted paper). I came up with a solution I’ll probably end up using again so decided to write it up and share.

Challenge

Wrap the tablix into two columns on the page. Records should appear to flow across the page (left to right). As opposed to down the first column then down the second column.

Solution

Introduce a hidden column in my Tablix containing a row number.

=RowNumber(“Tablix1”)

Copy and paste the Tablix then line the two up, side by side.

Access the Group properties for the default Details1 Group (containing the invisible row number). This is the first tablix on the left half of the page.

Either Filter the group (or set the visibility) by dividing (mod) the invisible row number by 2 and check for the remainder.

=IIF(ReportItems!Tab1Row.Value MOD 2 = 0 , True, False)

Access the Group for the Details2 Group in the second tablix on the right side of the page and filter the group (or set visibility) the same way. Note the switched True/False args.

=IIF(ReportItems!Tab2Row.Value MOD 2 = 0 , False, True)

Result

Pain Dashboard

My result is even numbered record items (row numbers) in the left column and odd in the right column. Report data appears to run left to right.

Filter SSRS Report Content or Visibility based on User ID

Challenge

Using one standard report template (RDL file), restrict and customize the report content for individual users. Users in Location A can see their own data but not that of Location B. Accommodate exceptions such as managers that may be authorized to see data from both Location A and Location B. We don’t want to use local groups to administer SSRS report access and security permissions.

I have hundreds of Locations so hundreds of RDL files with their own ACLs is not an acceptable maintenance proposition.

Solution

Use the Windows User ID of the report viewer to determine visibility properties of report elements. Use the SSRS User!UserID object (which contains the visiting users Windows User ID) as both a report Variable and a Parameter. Each can be used for slightly different things.

Create a database table (called Vistors) containing their Windows User IDs and their authorized Location ID. Users like managers may have multiple entries if they are authorized to view multiple Locations. The easiest way is to create an Excel .csv and use the SQL Server data import tool (or bulk copy) to get it into the table.

In Report Builder setup Data Sources so you can access this Visitor table then setup a Visitor Data Set with

SELECT UserID, LocationID FROM Visitors WHERE UserID = (@Visitor)

Create a hidden Visitor parameter with a default value using the following expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

When the report is loaded, the Users!UserID object gets populated and passed to the Data Set query. The query then returns any LocationIDs for the Visitor.

From here you can use a nested parameter to select data by matching LocationIDs. You can also assign User!UserID to a global report variable called Visitor and use this variable to set the report item visibility propert.

Why the two different methods? You can’t use report Variables in a Dataset Query (in the TSQL). You can’t use Parameter values in the Visibility property of a report item such as a Tablix. It is also not possible to use Parameters!Users.Value as an attributes in a Lookup() expression from within a Tablix. Pretty stupid, huh?

To make the Variable in Report Builder, right click on the report background and select the Variables tab of the report properties dialog box and use the same expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

In the Report Builder set the report element Visibility property (of say a Tablix) with the following expression

=IIF(Variables!Visitor.Value = Lookup(Fields!Facility.Value, Fields!Facility.Value, Fields!UserID.Value, “Users”), False, True)

Run and test the report.

The Results

If the report viewer’s user ID is in the Visitors table, the tablix should be visible. If not, the tablix is  hidden.

For example, the Parameter value can be used in the TSQL query to filter the data set on page load using syntax like,

SELECT * FROM table WHERE some_field IN (@Visitor)

The global report variable provides a means to set the Visibility property of the report object (Tablix).