I really enjoy using SQL Reporting Services and Dynamics GP, especially the standard reports included with Dynamics GP. However, I feel a slight dread when asked to modify one, and I’m not even a developer!

Why the apprehension? I might be revealing my non-technical side, but reports based on report models make me particularly nervous. Sometimes, I’m lucky and the necessary field is already there, requiring just minor adjustments. However, a recent request wasn’t so straightforward. The client wanted the employee name added to the Employee Pay History report. Sounds simple, right? It’s a reasonable request since most people don’t memorize employee IDs.

So, I opened Visual Studio and the source project, found the Employee Pay History report, and navigated to the ‘dsGPS10UPR’ dataset within the Data tab. Seemed simple enough.

My next step was to drag the First Name and Last Name fields onto the dataset.

Immediately, I noticed automatic grouping. I tried removing and re-adding the fields in different places, even deleting the grouping, but nothing worked. Generating the report resulted in duplicated records because of the grouping. Frustrating!

Many of you probably already know the solution, but I didn’t at the time. I spent hours going in circles, trying various solutions and even fruitless internet searches—leading me to believe that Report Models are not used enough.

Then, I decided to examine the underlying XML by clicking the “Edit Selected Dataset” option next to the Dataset dropdown.

Within the XML, I discovered a node related to subtotal sets, specifically a subtotal by Employee ID and First Name.

I removed all the lines between “SubtotalSet” and “/SubtotalSet”. However, returning to the Dataset view still showed a subtotal by Employee ID, which wasn’t present initially.

I edited the Dataset once more, this time removing the entire section between “SubtotalSet” and “/SubtotalSet”.

That worked! All subtotals in my Dataset were gone.

Adding the fields to the report itself was then simple.

I even used an expression to combine the First Name and Last Name.

I wanted to share this experience because information about using report models, particularly with Dynamics GP source projects, seems limited.

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.