While I’ve been rather quiet online lately, I can assure you it’s not due to a lack of things to share! My recent travels and the beautiful spring weather have lured me away from my computer.

However, SQL Reporting Services has been on my mind, particularly with recent client projects and an upcoming training session I’m delivering on using it with Microsoft Dynamics GP 10.0. Revisiting the material has reminded me of some useful features I tend to overlook. Inspired by resources like the fantastic SSW Rules to better SQL Reporting Services, I’ve compiled my own top five underused SQL Reporting Services tips:

Me. Value

• This function provides a quick way to reference the current field within formulas, conditional formatting, visibility rules, and more. For example: =IIF(Me.Value>0, false, true)
• It’s a great time-saver and helps prevent spelling errors.

Report Items

• Need to include data fields in page headers or footers? The Report Items collection lets you reference text box names.
• For instance, if you have a document number field in your invoice report body, right-click the field and check its properties to find its name (let’s say it’s “TextBoxName”). Then, in your page header or footer, add a text box and use the expression =ReportItems!TextBoxName.Value to display the document number.

Cascading Parameters

• This feature is incredibly helpful for creating reports with dependent filters. Let’s say you have a report that users can filter by sales territory and salesperson, but you want the salesperson list to only show those belonging to the selected territory. Cascading parameters are the solution!

Here’s how to set it up:

1. On the Data tab, create two new datasets:

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

2. Ensure your main dataset includes the @TERRITORY and @SALESPERSON parameters in its WHERE clause, spelled consistently.

3. Define the parameters: go to Reports >> Report Parameters.

4. List the parameters in dependency order, starting with @TERRITORY.

5. For both parameters, set “Available Values” to “From Query” and select the corresponding dataset, value field, and label field.

Now, when you run the report and select a sales territory, the salesperson list will dynamically update to show only relevant salespeople. For a deeper dive into cascading parameters, check out this resource: http://technet.microsoft.com/en-us/library/aa337169.aspx

Download the Dynamics GP SQL Report Source Files

• Did you know you can download the Visual Studio project files for Dynamics GP SQL Reports? This allows for custom modifications and serves as an excellent learning tool.
• Download them from PartnerSource: https://mbs.microsoft.com/partnersource/downloads/releases/msdgp10_srsdownload.htm?printpage=false

Creating Perspectives for Report Models

• Report models provide end-users with a simplified way to perform ad-hoc reporting by presenting a logical view of database tables, relationships, and calculated fields.
• When a single report model needs to cater to different audiences, perspectives can help tailor the view for each group.

Let’s illustrate this with an example: imagine a report model containing purchasing data like purchase orders, payable checks, vendor details, item information, and distribution information. The purchasing team might primarily focus on purchase orders, vendor details, and item information, while the finance team might be more interested in purchase orders, payable checks, and distribution information. You can create separate perspectives to display only the relevant subsets of data for each team, simplifying their interaction with the report model.

Here’s how to create perspectives:

1. Open your report model in Visual Studio.

2. In the pane on the left of the entity list, select “Model.”

3. Right-click and choose New >> Perspective.

4. By default, all entities and attributes are included. Unmark those that should be excluded from the perspective.

5. Click OK to save.

6. Repeat for additional perspectives.

When users select a report model in Report Builder, they can then expand it to see any defined perspectives.

These are just a few of my favorite underutilized SQL Reporting Services tips. What are yours? Please share your own tips and tricks! In the meantime, happy reporting!