SSRS report with groups in separate, named tabs

The 2015 fishing season is drawing to a close. Last weekend we closed my Grandpa’s cabin up in Northern Wisconsin for the year. I spent most of my non-working time doing some deer hunting, but I did get in about two hours of fishing after taking out the pier. As I reflected on my lack of success, I realized that I don’t really do too well after the lake turns over. Turnover is the process a lake goes through in the fall as the water temperature falls. Once the surface temperature gets colder than the deeper water (the water below the thermocline), it gets heavier and sinks to the bottom, which stirs up the entire lake. With water temperature and oxygen content evenly distributed over the entire lake, the fish can literally be anywhere. As I was fishing I marked pods of baitfish sitting on the bottom in 40 feet of water. It’s awfully tough to get down that down when using artificial bait. Another factor to consider is that cisco spawn around this time of year, so the muskies have no reason to be anywhere but near these clouds of fish. I did spend a bit of time trying to fish near these pods by vertical jigging a Fuzzy Duzzit, but got no action. I did manage a 21″ northern, but aside from that I saw nothing.

Cisco spawn in the late fall.

Cisco spawn in the late fall.

I got a request recently for a SSRS report that would have results grouped by one field, with page breaks between the different groupings. The user also wanted the report to download into Excel with each separate tab being named the group by field’s value. I’d never done this before, so I did a bit of research and built the report as specified. It was actually fairly easy to accomplish. Here are steps I used to create the report.
1. First build the report with a grouping. For this particular report, I grouped on a field called Vendor.
20151106 Report
2. Next go into the properties for the row group. Check the option to create a page break between each instance of the group.
20151106 Sorting
3. It usually also makes sense to sort on the group, so the page breaks are in some intelligible order.
20151106 Page Breaks
4. Lastly, select the group and expand the properties menu for the Group section. Change the PageName property so it is taken from the value you are grouping on.
20151106 Page Names
Now we just build and deploy the report, run it, and export it to Excel to see the results of our work.
20151106 Excel
Using this functionality can really make a report useful and polished. Your users will appreciate it!

Creating Drill Down Reports in SQL Server Reporting Services

The wait finally ended! The Okauchee boat launch, after being closed for remodeling for most of the year, finally opened back up. I had an opportunity to try it out last weekend, and I was pretty happy with it. They left the existing launch ramp from the previous configuration, and the pitch is absolutely perfect. They cleared out a bunch of trees and a disgusting bathroom, and replaced them with extra parking places, some grass, and a permanent port-a-john. I felt like they probably could have fit some extra parking places if they had made less of it grass. My main complaint is there is no way to stop and clear weeds after pulling out of the lake without blocking other parked trailers or rigs trying to drive out. In terms of fishing, I spent about three hours on the lake fishing for muskies with a buddy. We managed three fairly aggressive follows, but the fish were all pretty small. All in all, it was a great morning and it was awesome being back on Okauchee.

Okauchee boat launch
Today I had to learn to make a slightly more complex report in SQL Server Reporting Services (SSRS) than I’m used to. In order to mimic the functionality of an Excel pivot table, I needed to make use of the SSRS drilldown functionality. The first step is to create a dataset that you want to display. For my purposes, I am using two predefined business intelligence views, one that has invoice data and one that has product data:

SELECT  p.ProductClass ,
        p.ProductDepartment ,
        p.ProductNumber ,
        SUM(i.[Total Amount]) AS TotalAmount ,
        SUM(i.[Ship Qty]) AS ShippedQty
FROM    BI.dbo.FACT_Invoices i
        INNER JOIN BI.dbo.DIM_Products p ON p.[ITM Number] = i.[ITM Number]
                                            AND p.[SKU Code] = i.[SKU Code]
WHERE   i.[Inv Date] BETWEEN '2015-09-03'
                     AND     '2015-09-04'
GROUP BY p.ProductClass ,
        p.ProductDepartment ,
        p.ProductNumber
ORDER BY 4 DESC;

The next step is to set up a new report in SSRS and define the Data Source and your Data Set.
20151016 SSRS data defined Next add a tablix to the report. Put your metric data in the right columns and your highest level category in the left column.
20151016 SSRS tablix part 1 You need to add a child group for any fields that you want to drill into. I’ll start by adding the Product Department.
20151016 SSRS add child group20151016 SSRS add department This will automatically add the group to your tablix.
20151016 SSRS tablix part 2 Next pull up the properties for the row group you just added.
20151016 SSRS child row group properties Go to Visibility, and choose Hide. Toggle the checkbox to Display can be toggled by this report item:. Choose the row set of data that is the parent.
20151016 SSRS child group by visibility Next I add the Product Number data set and change the properties in the same way. This time I make the Department the toggled by report item. I format the report to make it look polished:
20151016 SSRS final design After deploying, you can now see there are plus signs before any unexpanded Classes and Departments, and minus signs after any that I have expanded.
20151016 SSRS final report I hope this has provided a simple example of how to create drilldown reports in SSRS

Run an SSIS job from SSRS

An important part of being a responsible boater is to prevent the spread of invasive species. When pulling your boat out of the water, plants and animals can “hitch a ride” by attaching themselves to your boat or trailer or sitting in your boat’s livewell or bilge area. Then when you move to the next lake they will get off or out of your boat and begin reproducing. This can introduce species of plants and animals to other lakes where they don’t exist natively. The ecosystem of each lake can be delicate, and introducing a new species can severely impact the species that already thrive in the lake. As an example, zebra muscles are an invasive species that are wrecking havoc on the lakes in Southeastern Wisconsin. Zebra muscles look like tiny clams with stripes, and they attach themselves to anything (rocks, sand, plants) and filter particulates out of the water. This makes the water much clearer which impacts the predators, who will now need to find more effective ways to ambush their prey.
I was surprised to find a local lake closed due to an invasive plant species, Starry Stonewort. From what I’ve read, this plant grows so thickly that poison is ineffective in trying to destroy it. It also hardens almost like coral, and reduces the amount of area that fish can use to spawn. It is very important to check your boat and trailer after pulling out of the lake but before leaving the launch to make sure you are not carrying any aquatic hitchhikers.
SSIS is a great tool for many tasks, especially moving data into and out of SQL Server. However, this tool is not easily accessible to the majority of the members of many organizations. One good way to let these types of users move data around is to set up the SSIS packages to be run in an SSRS report. Unfortunately there are a few steps to get this set up, but once you have it up and running you can cut yourself out of a lot of simple but time consuming tasks by letting users load or extract their own data to use in Excel or anywhere else.
The first step is to create an SSIS package to do the actual movement of data. The input or output will usually have to have a standard naming convention and standard field configuration.
20150810 SSIS from SSRS SSIS package
Next, create a SQL Agent job that will run that SSIS package.
20150810 SSIS from SSRS SQL Agent Job
The next step is to create a stored procedure that will run that SQL Agent job.

-- =============================================
-- Author:		Gugg
-- Create date: 2015-08-10
-- Description:	Runs the SQL Agent job that imports the IQR files
-- =============================================
CREATE PROCEDURE [dbo].[ImportIQRFiles]

AS
BEGIN

   SET NOCOUNT ON;

   EXEC msdb.dbo.sp_start_job  
    @job_name = N'AdHoc - DirectTech IQR Import'
       
END

GO

Lastly, create the SSRS report that will use that stored procedure as a data source.
20150810 SSIS from SSRS SSRS Report
Now when the user clicks to view the report, the data will be successfully imported or exported into SQL Server.
20150810 SSIS from SSRS Report Icon
It can a good idea to make the SSIS package as robust as possible with error emails sent to those who you expect to be running the report, that way if the import or export fails, the user can figure out why (often a misnamed file or missing field) without having to get you involved.

Stop Empty Reports from being Emailed in Reporting Services

My grandpa owns a cabin on a medium sized lake up in Oneida County. This lake is very clear (often described as gin-clear), and so fishing it presents certain challenges. Since the water is clear, you need to make sure your presentation looks natural. Lures with bright colors like fire-tiger are usually not the way to go. You also often have to fish deeper, since the sunlight penetrates further and weeds can grow in deeper depths. Since I grew up fishing this lake, I got used to clear lake fishing. I feel like I’m a better clear-lake fisherman. Maybe that’s why I prefer fishing Okauchee over Pewaukee here in southeast Wisconsin. Pewaukee and Okauchee are the only two lakes in SE WI that are rated A class muskie lakes by the Wisconsin DNR (Department of Natural Resources). Pewaukee gets all the publicity, but Okauchee is a great lake too. Pewaukee has fairly cloudy water, and is a shallower lake while Okauchee is a clear, deep lake. I’ve had much better success on Okauchee, and that’s why it’ll be the first lake I get on when fishing opens on May 3rd.
SQL Server Reporting Services SSRS has the ability to send reports out on a regular basis. This is a very helpful tool because we can put the data right in the users’ inboxes, making it easy for them to get and use the data. The scheduling functionality in SSRS is pretty flexible, but one important piece thats missing is the ability to not send empty reports. If the SQL statements used to send the report produce zero rows, an empty report gets emailed out to the recipients. I decided to look for a way to prevent this from happening. To demonstrate, let’s create a bit of database structure. First I created a table with a few records:

CREATE TABLE dbo.FavoriteLakes
    (
      id INT IDENTITY(1, 1) ,
      LakeName VARCHAR(50)
    )
INSERT  INTO dbo.FavoriteLakes
        ( LakeName )
VALUES  ( 'Two Sisters' ),
        ( 'Little Arbor Vitae' ),
        ( 'Okauchee' ),
        ( 'Pewaukee' ),
        ( 'Little Green Lake' ),
        ( 'Lake Tomahawk' )

Next I created a stored procedure that will return the results of this table. The stored procedure will be the data source for our SSRS report.

CREATE PROCEDURE [dbo].DisplayFavoriteLakes

AS
BEGIN
   SELECT LakeName
   FROM dbo.FavoriteLakes
END

Next I created a very simple SSRS report using BIDS. The report used an embedded data set with the stored procedure as the source, and had a textbox title and a table with a single row for the data.
20150318 SSRS Scheduler Mod
After deploying the report I can see it on the intranet site.
20150318 SSRS Scheduler Mod intranet
Next I set up a subscription for this job, but I stop before actually pressing OK. It will be sending me an email every five minutes.
20150318 SSRS Scheduler Mod Schedule Before actually sending the job I needed to take a screenshot of the top of my job scheduler in SQL Server Agent. Those jobs where the name looks like a GUID are the jobs associated with scheduled subscriptions in SSRS.
20150318 SSRS Scheduler Before Job Scheduled
Next I hit OK in Reporting Services to create the subscription. Then I refreshed the SQL Server Agent jobs and took another look. Now I see one extra job, so I know this is the one that is associated with the subscription I just created.

20150318 SSRS Scheduler AFter Job Scheduled

Upon going into the job the first thing I see is a warning not to modify the job. Doing so can result in database incompatabilities. I had to change the owner from the service account that runs SQL Agent to sa. It is important to note that any changes to the job or to delete the job would require me to change the owner back.
20150318 SSRS Scheduler Job Summary
When I go into the single job step for this job, I see a single line of SQL:

 [LW_ReportDB].dbo.AddEvent @EventType = 'TimedSubscription',
    @EventData = 'e20a1691-792b-4f8f-938e-ac4b5ed25b69'

20150318 SSRS Scheduler Mod Job Step
I’m going to change that SQL to the following:

SELECT TOP 1 1
FROM DBMaint.dbo.FavoriteLakes

IF @@ROWCOUNT > 0
BEGIN
   EXEC [LW_ReportDB].dbo.AddEvent @EventType = 'TimedSubscription',
       @EventData = 'e20a1691-792b-4f8f-938e-ac4b5ed25b69'
END

This takes the existing code and only call it if there is at least one record in the dbo.FavoriteLakes table.
Before and after this change I am receiving the following email:
20150318 SSRS Scheduler Mod Email
However, after clearing out the table:

TRUNCATE TABLE DBMaint.dbo.FavoriteLakes

I am no longer receiving the email. I’ve now made the subscription dependant upon having a result set.