Things I love about SQL Reporting Services

So while Steve has been prolific in his posts over the past month, I have to admit I have been more than a little deficient. I could blame travel (this makes week 5 in a row, albeit small trips) or workload (highly unlikely), but if I were honest I would blame the Spring sunshine for keeping me outside instead of at the computer :)



I have had SQL Reporting Services on the brain lately, as I have been working on a few client projects, and have also been prepping to teach "Using Microsoft SQL Server Reporting Services with Microsoft Dynamics GP 10.0" for partners next week in Seattle. Although I taught the first run of the class last May, enough time has passed that I wanted to work through the labs again and refamiliarize myself with the layout of the class.



Of course, every time I do this I am reminded of things that I do not use regularly in SQL Reporting Services, but should. There are some fabulous SQL Reporting Services blogs/sites out there including one of my favorites...


SSW Rules to better SQL Reporting Services

So, I thought I would add to the myriad of sources out there with my top five SQL Reporting Services tips (underused by me, of course):

Me. Value

• Used to quickly reference the current field in a formula, for conditional formatting, visibility, etc for example: =IIF (Me.Value>0, false, true)
• Saves so much time, as well as spelling mistakes!

Report Items

• To put data fields in to page headers or page footers, you can use the Report Items collection to reference the text box name.
• For example, you put document number in to the body of an invoice report. Right-click on the field, and choose properties. Note the name (for our example, "TextBoxName"), then on your page header/footer drag on a text box and enter the following: =ReportItems!TextBoxName.Value

Cascading Parameters

• So, you have a report that you want to be able to restrict by sales territory and salesperson. But you want to make sure that the salesperson parameter list only displays the salespeople for the sales territory parameter selected. What to do? Set up cascading parameters!

Create two new datasets on the Data tab:

• SELECT * FROM RM00303 (Sales Territory Master)
• SELECT * FROM RM00301 WHERE SALSTERR=@TERRITORY (Salesperson Master, with parameter)

You original dataset should have the parameters of @TERRITORY and @SALESPERSON defined in the WHERE clause. Make sure they are spelled the same way.

Now, you need to define the parameters, Reports>>Report Parameters

• Make sure that the parameters are listed in dependent order, @TERRITORY should be first.
• For each parameter, set Available Values to From Query and select the corresponding data set, value field, and label field.

Run your report, select the first parameter and notice the second parameter is now a restricted list. For more information on cascading parameters, click here.

Download the Dynamics GP SQL Report Source Files

• You can download the visual studio project files for the Dynamics GP SQL Reports to make your own modifications, they are also a great learning resource.
• Download from PartnerSource

Creating Perspectives for Report Models

• Report models allow you to enable end users to perform ad-hoc reporting by providing them with a logical interface to the database tables. Report models can include multiple tables, relationships, and calculated attributes (fields).
• To be efficient, you may find that you build one report model that actually serves a number of audiences that all work with a subset of the model. This is where perspectives can be helpful. You can define multiple perspectives within a report model, that present a subset of the report model to the end user.
• For example, let's say that I have a report model that includes purchasing information including purchase orders, payables checks, vendor information, item information, and distribution information. The purchasing team may only be interested in purchase orders, vendor information, and item information while the finance team may be most interested in purchase orders, payables checks, and distribution information. You can set up two different perspectives to present these subsets of information to the end-users, making it easier for them to work with model because they do not have to ignore the additional items that do not apply to them.

To create perspectives:

• Open your report model in Visual Studio
• Select Model in the pane to the left of the entity list
• Right click and choose New>>Perspective
• By default all entities and attributes will be marked to be included in the perspective, unmark the items that should be excluded
• Click OK to save
• Repeat as necessary

When users select a Report Model in Report Builder, then can expand the report model to view any perspectives that have been defined for the model.

So, that is my wonderful list. How about yours? Feel free to share your tips and tricks, and I will update the post. In the meantime, happy reporting...