SQL Server Reporting Service – Few Important Concepts and Overview

SQL Server Reporting Services, due to its robust but user friendly architecture, is an obvious choice for Enterprise or in-house reporting, for product management, sales, and human resource and finance departments. Its flexibility makes it an ideal for usage in applications as well (Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services – MSDN Magazine August 2004). Reporting Services offer various delivery methods, from ftp to email and it provides various rendering formats therefore it makes adds easiness to business to business reporting. Similarly because of its flexibility and industry standard security model, it makes extranet and secure internet reporting easily achievable.

The reporting system comprises for following main components.

Main Components of SSRS

Report Server & Report Server Database

Report Server is an integrated web service which controls report generation and management. Report Server database is a SQL server database which is used as data dictionary about reports (catalogue, groups) and caching. SQL server agent is used for scheduling the reports.

Report Manager
An ASP.NET based Web interface for managing the reports, setting security and user permissions.

Report Designer is considered as a part of reporting services in Microsoft documentation but because RDL (Report definition language) is an XML based open standard, any vendor can implement it and therefore a single tool can’t be categorized as report designer. However, Microsoft provides a graphical report authoring tool with Visual Studio.NET 2003 or later for creating reports which automatically creates RDL markup at the backend.

Reporting services were designed with concept of disparate data sources in mind. A single report can retrieve data from multiple heterogeneous databases and render it to make it look like it’s from a single source. It provides built-in windows authentication security however one can write custom security wrapper to fit particular enterprise needs.

With SQL Server reporting services, multiple delivery methods and formats work like a charm. You design a generic report and reporting server takes care of exporting it into HTML, Excel, PDF, WAR(web archive), TIFF, CSV and XML format. As always, one can write his own custom format writer wrapper class for any custom format.

Reporting Services Delivery Formats

Reporting Services Delivery Formats

Reporting Services provide four distinct formats of report delivery also known as subscription in Reporting Services arena; Individual subscription, data driven subscription, SMTP delivery and file share directory (FTP) based subscriptions.

Reporting Services Architecture

Reporting services architecture

Above diagram schematically explains Reporting services architecture

Beside the code segments which can be written within a report in VB.NET, these API provides makes it more programmable. The application program interface can be classified into following categories.

  1. Data processing extension application programming interface (API)
  2. Delivery extension API
  3. Rendering extension API
  4. Security extension API
  5. Web service API
  6. Windows Management Instrumentation (WMI) configuration API

Interactive interfaces are another salient feature of SQL Server Reporting Services. Reports designed in SQL server reporting services supports charts, document map, freeform, cross tab matrix, sub reports and tables. Reports can also be parameterized and event driven (supports actions). Management is one of the most important parts in any reporting system; SQL server reporting services has it all planned. It manages jobs from a user friendly console, provides personalization "my reports", tracks report history, manage shared data sources, provide search, subscription and snapshot features with shared subscription from one stop shop, the management console. Reporting Services supports report caching and stores reports execution data in execution logs,

Report Generation and Publishing

Report Generation and Publishing

As defined in the diagram above, the process of report generation and publishing consists of the following main steps.

  1. Reporting server engine (Report Processor) receives the request for a particular report. A request includes parameters and formatting instructions.
  2. Report Processor retrieves the report definition on the basis of request.
  3. For the corresponding RDL, the report processor then retrieves the report data for specified data sources.
  4. Report Processor performs transformation on reporting data and sends the document data along with schema to rendering engine (rendering extension).
  5. The extension publishes the final rendered report.
  6. The following steps are basics of how reporting services work. The extensions (data processing extensions, rendering extensions etc) can be custom built and wrap around the existing set of API to provide extended functionality.

SSRS Integration with SharePoint 2007

SSRS Integration with SharePoint 2007

Configuration:

  • SQL Server 2005 SP2 is installed on report server in Native mode along with WSS Object Model (farm install)
  • SSRS Configuration Tool creates a new Report Server database in “SharePoint Integration mode”
  • SSRS Add-In is installed on WSS 2007
  • WSS Central Admin web pages register SSRS web service and windows service with WSS farm

Database Integration Points

  • WSS Content Database stores the master copy of SSRS items
  • Schedules, caching, and subscriptions are stored in SSRS database only
Advertisements

Microsoft Announces Visual Studio 2010

Microsoft is offering a first look at the next version of its Visual Studio integrated development environment (IDE) and platform, which will be named Visual Studio 2010 and the .Net Framework 4.0.

http://msdn.microsoft.com/en-us/vstudio/products/cc948977.aspx

There’s a lot promised in the new release (expected to ship, duh, in 2010), from improved software testing tools to software engineering modeling capabilities to integrated development and database functions for the application lifecycle management (ALM).

Microsoft is putting its attention on improving Visual Studio for the benefit of every one of its users—from the CIO to the software architect to the enterprise developer to the software testing team.

A key goal in VSTS 2010, says Microsoft, is to help democratize ALM by bringing all members of a development organization into the application development lifecycle, and remove many of the existing barriers to integration.

One way that Visual Studio 2010 will do this is to break down the ALM roles, from the business decision maker (who needs a project overview but doesn’t want to be bogged down in details) to the lead developer or system architect (who enables the software infrastructure and draws the blueprint), to the developer who writes the code and the database administrator (DBA) who integrates it with the company database to the testers (who make sure the software is of high quality).

For the IT manager or CIO, says Mendlen, VSTS will give clarity and visibility into the state of the project throughout the lifecycle, using Team Foundation Server-enabled dashboards customized for her role. The dashboard can answer high level questions such as ongoing project cost or project status.

Agile Tools, Built-In

Visual Studio 2010 also will sport features to integrate Agile methodologies into the tech stack using Team Foundation Server. Skinner explains, “We’ll include in the [VSTS] box an Excel workbook for teams that are leveraging, say, the Scrum process so they can get burndown from their project.” These features, he says, will let Agile teams track daily progress, see projects broken down into iterations and use sprints.

Putting Quality Earlier in the Development Lifecycle

One sometimes-stressful interaction in the application development lifecycle is the tension between developers and testers. Developers have to do a better job of testing their code before they send it off to the software testers. Developers don’t always know which unit tests they have to run, and often they don’t have the time or inclination (your own cynicism-meter can determine which) to run the tests anyway.

Merging of Developer, DBA Roles

Most of these changes are a ways off, though you can expect to see some of this functionality demonstrated at the upcoming Microsoft Professional Developers’ Conference. One item, however, takes effect immediately.

As Microsoft sees it, the roles of the database-centric developer and “regular” developer are less distinct than they once were, so the company is merging its VSTS database and development products. As of October 1, those who belong to the Microsoft Developer Network (MSDN) and currently own Visual Studio Team System 2008 Development Edition or Visual Studio Team System 2008 Database Edition will receive for free the Visual Studio Team System 2008 Development Edition, Visual Studio Team System 2008 Database Edition, Visual Studio 2005 Team System for Software Developers and Visual Studio 2005 Team System for Database Professionals.

Ten technology trends that will shape 2008

Grid computing

India is the fastest adopter of grid computing in the world, according to Oracle’s Grid Index IV. Oracle should know, since it has 70 customers in India using its grid solutions.

Grid computing is applying the resources of many computers in a network to a single problem at the same time. It is not a new phenomenon. Research organisations, government agencies and universities have been using this concept for years. What’s new is that Indian corporates are now latching on to the concept.

Some of the enterprises using grid computing in India include the Gujarat Electricity Board, Saraswat Bank, National Stock Exchange, Indian Railway Catering & Tourism Corporation, General Insurance Company, Syndicate Bank, Ashok Leyland, Maruti Suzuki India Ltd and Municipal Corporation of Hyderabad.

Last year saw two major developments that will prove important for the adoption and acceptance of grid computing in India. First was the Computational Research Laboratories (CRL) — a subsidiary of Tata Sons —developing Eka, the world’s fourth fastest computer and two the Centre for Development of Advanced Computing’s (CDAC’s) Garuda finally got off the ground.

For all those chief technology officers who are constantly looking at reducing their hardware costs Grid Computing may provide an answer, say analysts. Traditionally, companies bought hardware to support certain applications. However, these applications have peak processing hours and slack time. Grid computing takes advantage of the slack time and transfers the processing loads on systems that are being underutilised.

“Indian businesses are going global. As part of their growth strategy, they have to take risks and decisions – whether it means investing in people or technology. Organisations from sectors like telecom, financial services, travel, construction & engineering, media and utilities are convinced of the benefits of grid computing because it allows for superior scalability and better return on investment,” explains SPS Grover, vice-president, Technology Business Unit, Oracle India.

Sunny side up

Photovoltaics (PV) promise to remain a hot topic in 2008. PV is the creation of electricity from a light source – sunlight, for instance.

A basic photovoltaic, also known as a solar cell, is made of materials like silicon and thin-filaments, commonly used in the micro-electronics industry. These are capital-intensive projects. PV modules connect many solar cells together and mount them on a frame or platform. Their margins are better.

India is becoming an attractive solar market, and IT firms like Moser Baer, Signet Solar and Webel Solar are confident of the growth. The recent semiconductor policy sops and the government policy for “off-grid” electrification, are added incentives.

Electricity and social development go hand in hand. Rural areas of India are so far-flung that in some cases the small population and high cost of laying down power lines may not make it a viable proposition.

Conventional generator sets too may not be feasible due to recurring maintenance problems. The best solution under the circumstances is solar PV-based systems to generate power, run irrigation sets, heat water and lighting up homes and streetlights.

India offers 100 per cent subsidy on solar PV systems for remote village electrification; and for villages with electricity, the government offers 60 per cent subsidy. Moser Baer’s $880 million (around Rs 3,500 crore) 8-year sourcing tie-up with Norway based, REC Group furthers this line of thinking.

The deal could get Moser Baer Photo Voltaic (MBPV) around $1 billion in revenues over the period of the contract. The global photovoltaic market is expected to grow over six times to $40 billion by 2010.

Software on demand

Software-as-a-service or (Saas) is a trend that will see considerable uptake among business users, say analysts. Saas is a software application delivery model, wherein the user pays according to the usage rather than for owning the software (license fee).

With close to eight million small and medium enterprises (SMEs) in India, Saas providers can start counting cash in 2008. Ravi Shekhar Pandey, research manager, Springboard Research, feels that the year 2007 was of creating awareness among the users and 2008 will finally see Saas’ adoption increasing.

“Earlier Salesforce.com was the only vendor that was propagating the concept in India. But now even traditional software vendors like Microsoft, Oracle and SAP have introduced Saas in their suite of offerings,” he adds.

“In 2006-07, 79 per cent of the companies that we spoke to were aware of the concept but in 2007 it has increased to 90 per cent,” Pandey adds.

Enterprise mobility

The concept of anywhere-anytime connectivity has changed the way enterprises do business today. Connectivity is not just restricted to the globetrotting top executives but also to the sales team on the field. IDC projects that by 2009, there will be around 880 million mobile workers worldwide demonstrating that mobility is going mainstream.

Companies like Sun Pharmaceuticals, Dr Reddy’s and Parryware, a division of EID Parry (India) are some examples where handhelds have been used to automate the sales force for real-time information.

Rajiv Sehgal, head (Value Added Services), Airtel Enterprise Services in a seminar on enterprise mobility said, “Enterprises are implementing mobile solutions to realise benefits in terms of product enhancement.” Analysts predict that banking and financial segment is one segment that has a huge opportunity in going mobile.

Go mobile and entertain

Entertainment devices will abound this year. They will be with you on the move (your laptops, PDAs and cellphones), at airports, your workplace, home and more importantly, in your pockets. TVs (both LCDs and Plasmas) will becomes larger and cheaper, and so will high-end mobile screens, offering services like TV on mobile, gaming, social networking, etc.

Mobile TV — which enables TV services on handhelds such as mobiles, handheld TVs, car TV, GPS terminals, game devices, laptop PCs, and other portable devices — is also closer to seeing the light of day in India. It’s an important development given that there are almost 225 million mobiles in the country, of which around 30-40 per cent are TV-capable. Besides, it will help increase the average realisation per user (ARPU) for telecom operators.

The Telecom Regulatory Authority of India (Trai) has already initiated the process, and given its recommendations on January 3 based on the feedback it received from around 30 telecom players, broadcasters, technology providers and direct-to-home (DTH) players in September 2007.

The world over, a number of mobile operators have conducted successful mobile TV field trials. Mobile broadcasting commercial services have been introduced in countries like Korea, the US, Finland, and Germany.

In India too, Doordarshan has launched a Mobile TV pilot project, and is in the process of introducing commercial Mobile TV services. UK-based mobile applications developer ROK Entertainment Group too has launched its streamed mobile TV system (called Tiny TV) in India through BSNL.

Entertainment will also become increasingly interactive. IOL Broadband already started the trend by launching India’s first IP-based on-demand television service (IPTV) a year ago. With IPTV offering from players like MTNL, Bharti and Reliance, the ‘on-demand’ entertainment is music to the ears of most people. It will also help increase the revenue for broadcasters and mobile operators.

Virtual nightmares ride high

Cybercrime is estimated to be a $105 billion market and looks set to grow this year as the complexity of cybercrimes intensifies.

The year 2008 is expected to be a year of an exponential increase in the activities of cyber criminals. Phishing — an attempt to criminally and fraudulently acquire sensitive information, such as usernames, passwords and credit card details continues to be a major concern during the year, claim security vendors.

Theft of intellectual property is expected to be another grave issue which needs to be tackled in the new year. “India is in the process of acquiring special technologies for cyber and digital frauds, international vendors of tools to trace cyber criminals, are now also showing interest in the Indain market,” says Computer Forensic Expert, Samir Datt.

Minister for Communication and Information Technology, A Raja, recently announced grants to enable the CBI install the latest technology enabling the investigative agency train officials in complex cybercrimes and also help in the mutual exchange of information with the Interpol.

The government is also in the process of amending the IT Act 2000 to address problems of data protection, data theft, e-commerce frauds and child pornography etc.

‘Open source’ makes inroads

‘Linux’— a free operating system (OS) as opposed to Microsoft Windows or Vista — is fast making inroads in the country.

Novell along with the Electronics Corporation of Tamil Nadu (ELCOT) is installing Suse Linux in around 40,000 desktops in the state.

This is the second-largest implementation of Linux on the desktop – the biggest one being that of around 60,000 desktops in Life Insurance Corporation (LIC) of India by Red Hat, which is estimated to have implemented over 200,000 desktop OS installations. Canara Bank too has around 10,000 Linux OS desktops.

Major firms and institutions like the LIC, Industrial Development Bank of India (IDBI), IndiaBulls and HDFC use Linux on the servers, for “mission-critical” applications too.

Linux has gained the support of corporations such as IBM, Sun Microsystems, Dell, Hewlett-Packard, and Novell, and is used as an operating system for a wide variety of computer hardware, including desktop computers, supercomputers, and embedded devices such as mobile phones and routers.

In India, besides major firms, state governments in Tamil Nadu, Karnataka, Kerala, Madhya Pradesh, Delhi and Maharashtra too have started using Linux in a bid to promote “open-source” environments.

SMEs are picking up the cue since you can install Linux on your desktop or server and reduce your operation costs. Microsoft counters this line of thinking by saying that the total cost of ownership (TCO) of its OS is lower than that of Linux.

The open source community that promotes Linux pooh-poohs this argument. One fact, though, remains: Linux is robust, steady, virus-free to a great extent and most importantly, FREE. Which means you can download it from the Internet or get companies like Red Hat and Novell (in India) to install it on your machines and provide maintenance services for a nominal fee.

Linux on the desktop is not as popular as Linux on the server. The reason is that Linux as an OS is a little difficult to use, especially if you’ve been used to a Windows environment. For new users, it would hardly matter.

Moreover, the argument that Linux does not have adequate support and lacks drivers for audio, video and gaming hardly holds water any longer.

Face to face

If corporate security is the buzzword in 2008, then biometrics will be the most probable answer to all such concerns. Biometric solutions for industrial applications surged to Rs 150 crore as an industry, according to a Frost and Sullivan report.

Although 80 per cent of the biometrics business still comes from the corporate sector, but the adoption of biometric systems among residential complexes is rising steadily and is expected to bring in the next phase of growth in 2008.

Also gaining traction are biometric segments like iris scan, middleware, multi-modality, voice recognition and signature verification.

The Indian biometrics market, which mainly consists of access control applications, is used extensively by the Defence and Security industry and fingerprint identification systems by the police administration, is now rapidly moving towards biometric regime.

The finance ministry too has set up an internal group to finalise norms for introducing iris-based biometric Permanent Account Numbers (PANs) for all income tax payers.

Similarly, the Ministry of Home Affairs is toying with the idea of introducing biometrics-based citizen identity cards and even biometric passports. This could be the turning point for the industry which is targeting an over Rs 1,800 crore turnover this year.

Arguably, the cheaper biometric solutions like fingerprint readers are now common on consumers devices like mobiles and laptops. This biometric application contributed more than 70 per cent in revenues to the domestic biometrics market last year.

PCs are safe no longer

Back in 1983, when virus researcher Fred Cohen coined the term ‘computer virus’ – referring to a programme code that can explicitly copy itself and has the ability to affect other programmes by modifying them or their environment – a lot has happened. Viruses of yore have turned into malware, broadly defined as software designed to infiltrate or damage a computer system.

The nightmare does not end here. Mutations of e-worms like Trojan horses, spyware, rootkits, dishonest adware, and other malicious software have crowded the cyber world.

Security vendors like McAfee, F-Secure, Symantec, MessageLabs and others agree that there will be a rise in the number of attacks seeking to capture a user’s ID and password by displaying fake sign-in pages.

Analysts have also painted a grim picture about instant messaging (IM). Most web-based IM clients have become quite popular among the Web 2.0 generation. IM features like file transfers and webcam support, make it prone to the virus attacks and 2008 might just be the year for IM attacks in offices or home.

Flash memory

Mainstreaming of flash with several fabs being put into production happened early last year, with a vast majority of these fabs producing flash chips. We saw major technology companies introducing computers without disk drives, with flash being considerably faster and more durable.

With 64GB in flash memory now available, affordable, smaller solid-state disks will be hitting the mainstream in a big way, leading to more crash-resistant and faster laptops. The year 2008 will see flash-based storage making a move towards the datacentre both as a green and a faster access option.

Flash’s main contribution in India would be in making the handheld devices more competitive than the laptop PCs.