Microsoft SQL Server Reporting Services (SSRS) is a great tool for writing custom reports for Microsoft Dynamics GP users. One issue that caused a little frustration was having to create multiple reports to accommodate different databases in GP. When a customer would request a change to a report, I would have to modify the report and then redeploy the report three or four times. Finally, I decided, “Why not make the data source dynamic?”
After a little searching on the web, I found a way to base the data source connection on a parameter. My first step was to create a parameter to allow the user to select the company they would like to run the report against. This is easy enough to accomplish by creating a dataset based on the following query:
Select CMPANYID,INTERID,CMPNYNAM from DYNAMICS..SY01500 and order by CMPNYNAM.
Next, I set the values for the company parameter @COMPANY.
Finally, I create a data source based on my parameter. For my data source, I setup a Microsoft SQL Server connection using the following expression for my connection string:
=”Data Source=servername” & “;initial catalog=” & Parameters!COMPANY.Value
Just change the placeholder servername to the SQL server GP resides on. When a user goes to run the report, the first parameter they select is to pick a company. Once they select the company, the remaining parameters and report data will be from the company they selected.
From there on, we rely on SQL security to control whether or not a user can run data from the company selected. If they do not have proper SQL permissions, they will receive an error trying to generate the report for a company they do not have access to.
Now, when a change is requested by the client, I have a single report to modify and deploy.
For additional tips and tricks regarding Dynamics GP and ERP products, subscribe to our blog.
