SQL Reporting Services and Dynamics GP and Report Models- Grrrr....

I love SQL Reporting Services, let me say that right away. I also love Dynamics GP.  And I love the standard SQL Reporting Services reports that come with Dynamics GP. However, and this is a BIG however, I always cringe just a little bit when I am asked to make a modification to one of them. And I should also put a big disclaimer that I am not a developer and am often humbled when it comes to my own technical skills.

Why you ask?  Well, I might be showing a bit of my non-technical self here by saying that when the report is based on a report model, I cringe a thousand cringes :)  Sometimes I luck out and the field I need is already part of the report and I just have to move it, or sum it, or something simple like that.  But a request that came to me last week was not that simple although it sounded innocent enough.  The client just wanted the employee name to the Employee Pay History report.  Easy right? And an obvious request since most folks don't memorize every employee's ID.

So, I open visual studio and the source project (yes, the source projects are available here on PartnerSource and CustomerSource).  And I open up the Employee Pay History report, and flip to the Data tab and the dsGPS10UPR dataset.  Looks simple enough right?

Now, I just need to drag the First Name and Last Name fields on to the dataset.  So I do that.

So I immediately notice the automatic grouping.  So I remove the fields, and try adding them in different locations, I attempt to deleting the grouping.  Nothing seems to work.  And if I generate the report, I get duplication of records due to the grouping.  Ugh.

Now I know that many of you probably already know the solution to this dilemma.  But I didn't at the time.  And I spent a couple hours spinning my wheels and trying a variety of solutions including a number of completely fruitless internet searches--where I concluded that Report Models are definitely underused :)

But then I started looking at the underlying XML--by clicking on the Edit Selected Dataset ellipsis next to the Dataset name dropdown list.

In looking at the XML, I found the node dealing with subtotal sets and specifically the subtotal by Employee ID and First Name.

And I remove every line between SubtotalSet and /SubtotalSet.  But when I flip back to view the Dataset, I still have a subtotal by Employee ID which I did not have on the original. 

So I edit the Dataset again, this time remove the entire section between SubtotalSet and /SubtotalSet.

That does the trick!  Now I have no more subtotals in my Dataset.

So then it is just a matter of adding the fields to the report itself. 

I even got superfancy and used an expression to concatenate the First Name and Last Name.

Thought I would share my experience with this, just because there doesn't seem to be a lot out there regarding using models and specifically working with the Dynamics GP source projects. 

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional.  She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

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...