29
Fri, Nov
0 New Articles

TechTip: DB2 for i HTTP Functions, Part 3: Access SQL Server Reporting Services

Web Languages
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

The new DB2 for i HTTP functions give application developers integration opportunities with a whole realm of modern business applications.

 

The real power of the new DB2 for i HTTP functions comes from their ability to let the IBM i talk with any number of HTTP-based web services and applications. One such HTTP-based app is Microsoft's SQL Server Reporting Services (SSRS). SSRS has rich HTTP-based APIs (e.g., SOAP and REST) that can be accessed by the IBM i HTTP functions. Therefore "i" apps can interact with the SSRS report server, including the ability to request a report's execution (in any of the available output formats, such as HTML, Excel, or PDF).

 

If you're new to the HTTP functions, please see the references at the end of this tip.  To get a brief overview of how to use Reporting Services in the IBM i world, see Rev Up "i" Reporting with SQL Server 2008 Reporting Services. The sample shown in this tip requires SSRS 2008 or higher.

 

For the sake of this article, I'll hypothesize that the IBM i is responsible for running a large month-end batch job. The last step of the batch job is to send management an email of the sales and expenses for the month. A new SSRS report with all of the bells and whistles has just been created to access DB2 to provide a dashboard summary of the entire month. Management has requested that this fancy SSRS report be attached as a PDF and distributed with the current month-end email.

 

Because of SSRS' versatile HTTP-based API and the powerful new HTTP functions in DB2 for i, this task to generate a PDF of the report will be a snap.

 

To get started, the first step will be to create a local Windows user account on the SSRS box (assuming SSRS is running in native mode) and assign the user permissions to access the report server and report. The local user I built for this example is called RSUser. In this article, the new month-end report is simply named TestReport, and RSUser was granted execution permission. RSUser will be the login used by the IBM i to log in to SSRS.

 

By default, only Windows users (using Windows authentication) are allowed to access a reporting services app. In order to access reporting services from the IBM i, the SSRS instance should have "basic authentication" enabled. Recall that basic authentication is used to allow an HTTP client to pass a user name and password to an HTTP server in plain text (unless HTTPS is used). As a security measure, basic authentication is disabled by default in SSRS because it is easy to sniff passwords, and besides, Microsoft would like the world to use Windows Authentication for everything.

 

To enable basic authentication in SSRS, locate and edit the rsreportserver.config XML file. (Microsoft didn't create an easy way to turn this option on). This URL explains how to do this for SQL Server 2008 Reporting Services & Higher: http://msdn.microsoft.com/en-us/library/cc281309.aspx.

 

Now that SSRS has been configured to allow authentication via a simple HTTP-based login, the next step is to figure out how to create the report using a simple HTTP request. After viewing a Microsoft article on how to integrate Reporting Services with other applications, it is apparent that creating the report as a PDF is as simple as building a URL with the following format:

 

http://servername:port/ReportServer/Pages/ReportViewer.aspx?ReportName&rs:Command=Render&rs:Format=pdf.

 

Substituting the reporting server name with my SSRS installation's IP of 172.29.97.8, the port of 8080 and report name as TestReport, the URL becomes:

 

http://172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?TestReport&rs:Command=Render&rs:Format=pdf.

 

The remaining parameters and their constants are rs:Command=Render and rs:Format=pdf. These values instruct SSRS to render the report in PDF format. See the "Report Server Commands" section in the URL Access Parameter Reference documentation for more information on these commands.

 

Note that some parts of the URL may vary, depending on how the report server is configured and where the report is placed within the folder structure. In this example, TestReport is in the root folder. Further, although beyond the scope of this article, it is possible to pass values to the report parameters via the URL. For more info, see Pass a Report Parameter Within a URL.

 

For this example, the local Windows user account and password are RSUser / radical8!. The credentials can easily be added to the URL (as long as there are no escapable characters) so that the final URL is:

 

http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?TestReport&rs:Command=Render&rs:Format=pdf.

 

With the URL built, it's easy to test the report rendering by pasting the URL in a browser. Preferably test with something other than Internet Explorer so that Windows authentication isn't used; that way, you can tell whether the credentials are working correctly. When testing, the report server will either return an error when there is an invalid URL (or value in the URL) or return a PDF document. The URL can be tweaked and tested in the browser.

 

Once tested, the only thing left to do is insert the tested URL as a parameter to one of the DB2 HTTP functions as follows:

 

-- Table function usage -- return PDF as a BLOB
SELECT *
FROM TABLE(
SYSTOOLS.HTTPGETBLOBVERBOSE(
'http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?%2fTestReport&rs:Command=Render&rs:Format=pdf','')) WS;

-- Scalar function usage - return PDF as BLOB 
SELECT data FROM (VALUES(SYSTOOLS.HTTPGETBLOB(
'http://RSUser:radical8!@172.29.97.8:8080/ReportServer/Pages/ReportViewer.aspx?%2fTestReport&rs:Command=Render&rs:Format=pdf',''))) WS(data);

 

Once the data has been returned as a BLOB, it can be dumped directly to the IFS with the help of an embedded SQL program using the BLOB_FILE data type. Reusing the same code from the prior tip (with the URL and IFS file name changed), we have this:

 

D PDF              S                SQLTYPE(BLOB_FILE)
D WebURL           S      255   Varying      
/Free
   WebURL='http://RSUser:radical8!@172.29.97.8:8080/-
          ReportServer/Pages/ReportViewer.aspx?-
          %2fTestReport&rs:Command=Render&rs:Format=pdf';  
   PDF_Name='/tmp/MonthEndReport.pdf';
   PDF_NL=%Len(%TrimR(PDF_Name));
   PDF_FO=SQFOVR; // Overwrite

   Exec SQL
      SELECT SYSTOOLS.HTTPGETBLOB(:WebURL,'') PDF_DATA 
      INTO :PDF
      FROM SYSIBM.SYSDUMMY1;

       *InLR=*On;
/End-Free

 

The rendered report is placed on the IFS so it can be attached to an email. Including this last step in the month-end batch job is all that's required to get the new report in PDF format from SSRS.  Once on the IFS, it can be added as an email attachment and the work is done.

 

Don't forget, as I mentioned in Part 2 of the series, for safety it is better to pass the user name and password in the Authentication header field instead of placing it directly in the URL.

 

In summary, the new DB2 for i HTTP functions give application developers new integration opportunities with a whole realm of modern business applications—SSRS being just one of many. The process in this article gives an overview of how to integrate with other HTTP-based systems:

  1. Identify an HTTP-based API your IBM i needs  (web services, REST, etc.).
  2. Activate basic authentication, if necessary.
  3. Often, it's easy to run a request right in a browser so you can verify how it works on the fly.
  4. Incorporate the HTTP request into your application using one of the new HTTP functions.

Integrating with other applications is becoming increasingly easy to do using HTTP requests. These new functions can help tighten the links between disparate systems and reduce the cost of integration.

References

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: