Fun with SSRS

It seems an increasing amount of my time is spent building reports in Smartlist Builder and SQL Reporting Services. I thought it might be fun to share a couple of SSRS reports I’ve been working on lately.

The first is a Customer Statement. If you use GP customer statement forms, you are probably aware of the limitations in modifying the standard report format. Even the Word template format is cumbersome to modify, so using SSRS to print your statements is a great alternative. You can make an SSRS format as simple or complicated as you like.

I usually begin the process of any SSRS report by building a SQL view containing all the necessary information. In the case of customer statements, there is an additional level of complexity if you want to add apply detail, and most of us do! In my case, we needed a report showing all the open invoices along with all applied open payments and any payments applied during the selected date range.  My customer wanted to be able to enter a date range and select which of their customers would receive statements.  The final product is shown below.

This is a statement from my own GP environment. I’ve removed customer details to protect the innocent, but when you open the report in designer, you will see the Customer ID field and the address block.

As you can see, it’s a pretty simple format. Once you open the RDL in SQL report builder, you can change the logo and make any formatting modifications you like.  Just note at the invoice detail is in the main report and the APPLY detail is contained in the sub report. If you add columns in either place, you will need to adjust the formatting so that everything lines up.

The SQL view I used can be downloaded here.  (Remember to right-click and save as!) In order to use the RDL I  have provided here, you will need to deploy this SQL view to your environment.  You will also need the SQL view behind the apply detail sub report here (thank you, Victoria Yudin!) and the associated RDL for the sub report here.

If you are familiar with deploying SSRS reports and SQL views, you shouldn’t have any trouble with these.  You will need to point to your GP datasource and ensure that the appropriate security is set to the SQL views and the newly deployed reports. TEST, TEST, TEST to make sure the report shows accurate data for you!

The real power of this particular statement format is that you can print them for any date range. You can print a customer statement from the beginning of time until the last month’s cutoff, or you can print a partial month. You can print statements for all customers or for a select few. I’ve added a comment field as a parameter to accommodate one of my customers who needed to be able to enter a dynamic comment to print on all statements in a print run.

One challenge I have found in SSRS reports in general is how multiple pages are rendered, so if you have long statements, make sure you test with several different scenarios, as you may need to adjust the format to get it perfect.

 

 

---

 

The second SSRS report I want to share is a simple sales document.  This one uses the actual GP tables SOP10100, SOP10106 (header comments) SOP10200, and SOP10202 (line item comments). If you require more customer billing details, you could easily link the RM00101 (customer master) or RM00102 (customer address master) as well. 

Once again, I’ve made this pretty simple, but additional fields can be added easily. For end user ease of use, you could also write the necessary code to render SOPTYPE as a text field. For simplicity, I have left it as a number from 1-5 to represent QUOTE, ORDER, INVOICE, RETURN, and BACKORDER.

Also, additional parameters would make this more usable depending on a company’s business processes for printing or generating invoices. For example, you could add parameters for customer class, customer, city, state, priority code, etc.

Finally, for return document amounts and for fields such as markdown and trade discount, you may want to modify the expression to render these as negative numbers. This is very simple to do using the SSRS expression builder.

The RDL files for this basic SOP document are here (main doc) and here (line detail). Again – right-click and save as to download the RDLs. Otherwise, they will open as XML.

Again, deploying these is a matter of uploading them to your SQL Reporting Services environment and making the necessary changes to the datasources.

Once you’ve begun using SSRS for documents such as these, you begin to see the power of using the same format for historical documents. By simply making a change to the dataset, you could pull all historical invoices and define parameters to print by a date range, batch ID, etc. Posting a batch of invoices prior to printing is no longer a problem – simply use SSRS to print any and all based on your customer parameters.

Well, that’s all I have for now.  I hope you will have fun using these templates and share your own developments!

 

 

 

Advertisement