How To Create SSRS Reports In Navision

tanmaykothari

Dear All
i want to create ssrs report in navision (claasic client).
please tell me the steps.

vijay_g

see this link..
http://msdn.microsoft.com/en-us/library/bb522672.aspx

tanmaykothari

Dear Vijay

Thank u for ur reply
it definetly help me lot

can u tell me please how can i call ssrs reprts in classic client
or how to intigrate ssrs in navision classic client

task is that crete ssrs report and call it from classic client

Pivotier

Use Pivotier (www.pivotier.com). Allows users to define, design and run reports in the classic client. Creates reports using RDL 2010 specifications in the Microsoft 2012 Report Viewer.

Output options are Excel, Word, PDF, Outlook and SSRS.

Eliminates the need to build reports in SSRS, but if you do, you can import the RDL file into a Classic form, then allow users to run the report from a list inside NAV.

Pivotier Express Plus provides over 75 new out-of-the-box reports which can be published to SSRS.

http://www.pivotier.com/Centerline/Asse ... eports.pdf

EnricosFolly

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.

Pivotier

Agreed that all tools are just that - tools which help a proficient user or developer. As NAV developers, we were disappointed with the difficulty of building RTC reports and the deficiencies of RDLC reporting which prevented pushing reports from NAV to SSRS. Our research determined that SSRS reporting requires true RDL - meaning that a dataset must be present within the RDL definition, Microsoft's tools like SSRS Report Builder and SSBIDS (aka Visual Studio Report Designer) were modern and impressive, but made more difficult to use because of proprietary data issues such as flowfields and fairly simplistic "wizards" which did not always allow a developer to learn and become immediately effective - let alone profitable.

So we did several things.

1) enable NAV users to generate Transact SQL and report models from within NAV, simply by selecting NAV tables and fields, joining tables then pressing a menu item to create a very complex SELECT statement or a Report Model. Many developers find that this predefined dataset makes report development for SSRS 50-90% faster. These SELECT statements automatically handle NAV issues like: Company Names, Flowfields, Option Fields, Boolean fields and SQL decimal precision casting. Problems which may be fun the first time you wrestle with them, but quickly become impediments to productivity.

So this is an example where a flowfield generates a nested SELECT statement and both option and boolean fields are automatically translated without any manual labor. This took 15 seconds to create and is also a useful tutorial for developers on how to handle NAV "stuff" in SQL.

// auto aliasing of field names to handle spaces, periods, etc.
SELECT Customer.[No_] AS CustomerNo_, Customer.[Name] AS CustomerName, 

// handling an Option field
(CASE Customer.[Blocked] WHEN 0 THEN ' ' WHEN 1 THEN 'Ship' WHEN 2 THEN 'Invoice' WHEN 3 THEN 'All' ELSE '' END)  AS CustomerBlocked, 

//handling a boolean field
(CASE Customer.[Tax Liable] WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE '' END)  AS CustomerTaxLiable, Salesperson_Purchaser.[Name] AS Salesperson_PurchaserName, 

// translating a flowfield into a nested SELECT by interpreting C/SIDE table properties
(SELECT Sum([Amount (LCY)]) FROM [CRONUS EXT USA, Inc_$Detailed Cust_ Ledg_ Entry] WHERE ([Customer No_] = [Customer].[No_])) AS CustomerNetChangeLCY 

// handling company name
FROM [CRONUS EXT USA, Inc_$Customer] AS Customer 


// joining a table
LEFT OUTER JOIN [CRONUS EXT USA, Inc_$Salesperson_Purchaser] AS Salesperson_Purchaser ON Salesperson_Purchaser.[Code]  =  Customer.[Salesperson Code]

We think most developers and customers would like this productivity aid so we fill the need to interpret NAV table properties to assist in generating useful datasets without hours of pain.

2) created over 75 new reports which use RDL 2010 specifications so that developers can use these working models to clone and learn. We learned most of our C/SIDE reporting skills by looking at existing NAV reports. Developers and customers can quickly learn how to apply advanced formatting, calculate variances, embed advanced charts, gauges and indicators, generate summary/detail toggles and so on.

3) Allow SSBIDS reports to be run from inside NAV, including entry of report options at run-time, to then be accepted as parameters within a Visual Studio report. Even more fun, we push language captions as datasets to the report so that any report can run in multiple languages based on the NAV application language.

4) enable NAV users to design and publish adhoc as well as financial reports directly to SSRS report folders (which they can create to sync. with NAV roles) from inside NAV.

This is why a top executive at Microsoft calls our work with SSRS integration "wicked" and "complementary to NAV 7" and why leading ISVs are finding that building better and faster reports this way helps their products sell better and their customers happier.

Why not get rid of the first 90% altogether and only have the second 90% to work on

A hammer does not tell me where to pound the nail - it just makes it easier

David_Singleton

EnricosFolly wrote:

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.

:thumbsup: :thumbsup: :thumbsup:
Enrico, this is a great post, you really hit the nail on the head.

And I love your quote ""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.""