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.