SQL Server Report Service (SSRS) reports exist entirely outside of the Navision environment, but are accessible to Navision via their URL. The steps are as follows:
- Create a T-SQL query that produces the data set you need
- Construct the SSRS report layout in the IDE
- Publish your report to the ReportServer
- Set all configurations and security in the SSRS Report Manager
- Construct a call to the SSRS report URL including the report name and all parameters
- Call the report via the URL from within C/AL
- Enjoy a cup of coffee for a job well-done
Unfortunately, the SSRS writer consuming Navision data must overcome several data obstacles not usually found during past or routine C/AL programming. This complicates the steps outlined above.
The first of these is the T-SQL Query hurdle. A query for all the sales order
data from the Sales Header may look like this:
Select * from [CRONUS Canada, Inc_$Sales Header] where [Document Type] = 1
I don't know about you but I can spot several difference between data access syntax in T-SQL and C/AL.
Without identifying the solutions to these issues, it is sufficient to point out
- the presence of the company name in the table name
- the difference in the spelling of the company name
- the absence of meaningful option identifiers (like Document Type)
These issues must all be mastered even before a single SSRS report can be written. They are easy enough to overcome but few expect the problems. This is fair warning.
Working in the SSRS Business Intelligence Development Studio (BIDS) is very similar to the BIDS when working with RTC reports in 2009. The difference here are that the SSRS BIDS allows multiple queries, the use of parameters, binding of parameter values to other queries, and a much, much more control over the data while in the BIDS. The same kinds of issues of working with data-set items in headers and footers exist and the same kinds of tricks of the trade for dealing with these problems exist.
Once these reports are produced, they can be published to the SSRS Report Manager. Inside the report manager, access to reports is controlled by the same kinds of security and credentials found in most other web-based solutions. Active Directory security is most often used so A/D roles should be assigned to the users for whom access should be granted, they should be granted those roles and the rights must be granted for the role in the appropriate reports.
After all is accomplished, calling a report from Navision is as easy as
HTTP://localhost<or whatever server name>/Reports/Pages/<name of report>?Parameter=<parametervalue>
This is a gross simplification of both the complexity of SSRS reporting from NAV data and of the capabilities of SSRS reporting, once you have the NAV data you want. Running SSRS reports from the same server as Navision services or applications run will add a certain amount of processing burden on the server. If there are already many users on the server, the burden may be noticeable. If so, it may be advisable to move the SSRS reporting to another server. Be aware of the A/D and security issues with this additional topological complexity.
SSRS reports can be distributed via SSRS subscriptions, can be distributed in multiple forms, and can be integrated with SharePoint. There are many capabilities but do not assume they are easily implemented in every architecture. SSRS may provide tremendous additional capabilities but it comes with a very high burden of authorship and management.
I disagree, somewhat, with the blithe and repetitious chant toward Pivotier. I know a bit of Pivotier and like it personally. Along with its many touted benefits, I will add my personal observation about all such tools. The easier it is to gain entré to a technology using an encapsulated layer (e.g.: putting wizard-like front ends on the interface), the more frustrated will the clients become when they reach the limits of the helpful automation. Tools can only go so far. After that, real knowledge is necessary and the energy necessary to mount that boundary (after coming so far with no effort) is often very frustrating.
One of my favorite quotes goes something like this. "The first 90 percent of the job took the first 90 percent of the effort... the next 10 percent of the job took the other 90 percent of the effort."
Such are reporting efforts. Nothing comes for free in these technological terrors we have constructed.One final comment that is not directly involved in the question... Using SSAS to construct amazing data cubes and then consuming those cubes as data sources in SSRS ad-hoc reporting can be amazing. But that's just an example of the new worlds open to the C/AL programmer who is unafraid of venturing into the SQL Server world.