Report Builder Report using data from SQL Anywhere 16

Report Builder 3.0 is the present version shipped at the same time as SQL Server 2012. It is a one-stop report authoring tool which can even be launched from Report Manager or from a Reporting services Integrated SharePoint Site. Of course you can download and launch it after installing on your desktop.

The most viewed Report Builder tutorials are here:
Report Builder is described in great detail here:

http://jayaramkrishnaswamy.sys-con.com/node/982742
Creating reports using Report Builder 2.0 is described here

http://jayaramkrishnaswamy.sys-con.com/node/1227111

 

You can master Report Builder 3.0, just follow the step-by-step instructions

Report Builder 3.0 is described exhaustively in my latest book

Out of the box it can connect to a variety of vendor products and the inclusion of ODBC and OLE DB makes it extremely convenient to connect to many other (not out of the box supported) products

Case in point is SQL Anywhere Server 16 for which you can set up an ODBC connection

This post shows you some of the steps that you can follow to turn out a report from SQL Anywhere 16. The following assumes that you have already started the SQL Anywhere Personal server. The procedure has still some unanswered questions, please read the last section

The following screen shows how you connect to an ODBC source for a connection embedded with the report

The next slide shows an ODBC System DSN 'demo' created using the SQL Anywhere 16. In reality this ODBC DSN gets registered when you install SQL Anywhere 16

 The next slide shows that connectivity is OK with 'dba' as username and 'sql' as password

The final connectivity screen after testing the authentication is shown in the next image

 The Credentials for this connection are shown in the next image

In order to create a dataset for your report use of Query Designer is not possible as it is not supported. You need to have this information on your hand to insert into the Report Builder's data set page.

The next image shows the InteractiveSQL tool in which a SQL Select statement is used to choose a all the fields from the Contacts table.

The dataset will be created using this embedded connection as shown.

For this, the query contacts.sql created in Sybase's InteractiveSQL is used and persisted to the desktop. This is imported into Report Builder's data set interface as shown using the import button

.

The next image shows the report designer interface taking in a few columns from the data set shown on the left.

This last image shows the report being displayed in Report Builder 3.0

Caution

While the above procedure is correct you may find problems while repeating this procedure and this will be mainly due to the odbc32 and odbc64 problems as I understand it

However, take a look at my bug report to Microsoft connect here

https://connect.microsoft.com/SQLServer/feedback/details/799460/report-displays-correctly-in-ssdt-and-deploys-without-errors-the-same-report-is-not-displayed-in-report-manager-or-report-server

First step in report authoring: Connecting to a source of data

Report authoring is perhaps one of the most frequently required activities in an enterprise. Knowledge of report authoring is an invaluable asset when one is looking for a job in an enterprise. Before you can write a report you will have to know where your data is and how to get connected to it from your authoring software. Report authors who are not very comfortable with using data on a back end server could use the more powerful and highly interactive Power View reports, but this requires a developer in your organization to create a model for you to work with. Once the model in place it is super easy to create stunning reports. This is only possible on SQL Server 2012 with the reporting services installed in SharePoint integrated mode.

The following is specific to SQL Server Reporting Services, but this is important for any authoring tool. In the following Visual Studio 2012 is presumed and a web project is to be created to demonstrate the use of Report Viewer Control.

Presently the Server Explorer has the following items:

 

If you do not see Server Explorer you click View in the main menu and click Server Explorer as shown:

Presently there is a connection established with the Adventure Works 2012 database on the SQL Server 2012 named Kailua.

Click Connect to Server item on Server Explorer tool bar shown.

The Add Server window is displayed as shown.

The name of the computer is provided (you provide the name of your computer). Click OK.

Adding a Data Connection

Click Connect to Database toolbar item as shown.

Or you can right click Data Connection and start as well.

Assume that we clicked Connect to Database.

The Add Connection window is displayed as shown.

This is because before getting to the database you must first get connected to the server. Notice that the Microsoft SQL Server (SqlClient) is the default. It can be changed to others by hitting the Change... ellipsis button and following the wizard.

For now accept the default. Click on the handle for Server Name after hitting the Refresh button. You see three servers, a SQL Server 2012 (Kailua); a SQL Server 2008 R2 (Maui) and a SharePoint Server.

Click HODENTEKWIN7\KAILUA to choose the server. The Add Connection window is updated as shown. We accept the default authentication, Windows Authentication.

Click on the handle for the Select or enter a name for the database field to display the drop-down list as shown.

Click Northwind. It could be another database in your case. Click the Test Connection button and verify that the connection was successful as shown.

Click OK on the above message window and click OK on the Add Connection window. The new database connection will be added to the Visual Studio 2012’s Server Explorer as shown.

That is all for now.

Mahalo

My new book uses images / screenshots such as the above to guide you through the learning process. The instructions are very simple and most of the steps are described.

http://www.packtpub.com/learning-sql-server-2008-reporting-services/book

http://www.packtpub.com/learning-sql-server-reporting-services-2012/book

Do you want to learn SQL Server Reporting Services 2012 in record short time?

Learning SQL Server 2012 Reporting Services was released today by Packt publishers. This is my second book on Microsoft SQL Server Reporting Services (which may be called a second edition). It is enlarged to include Reporting Services Integration with SharePoint 2010. With 566 pages it is somewhat thicker than the first edition (Learning SQL Server 2008 Reporting Services). However, the book has a large number of screen shots like my first book and is somewhat thinner than most other books in this genre. The reviewers and technical editors have put in a lot of effort and time to make this happen.

I take this opportunity to thank the reviewers (Satya Shyam Jayanty and Ritesh Shah) for their meticulous reading of the manuscript; the technical editors, Azharuddin Sheik, Mausam Kothari, Varun Pius Rodrigues, and Lubna Shaikh and many others at Pakct Publishing for their excellent editing and Microsoft Forums' (MSDN, Technet and SharePoint) help for answering so many of my questions.

Summary of Hands-on tasks you learn:

• Install SQL Server 2012, SharePoint Server 2010, and configure Reporting Services both native and SharePoint Integrated on Windows 7
• Install SSDT and author reports, both local and remote, and deploy them to report servers
• Develop desktop and web applications by using Report Viewer controls
• Author different kinds of reports with the latest gadgets by working with Report Builder
• Manage access to the report server, report scheduling, report delivery, report uploads, and downloads with Report Manager
• Create Tabular Models with SQL Server Analysis Services and deploy them to SSAS Server using SSDT
• Author Power View Reports from your SharePoint Site and export them as Power Point files
• Learn to create Data Driven alerts in SharePoint and monitor them on SQL Agent on SSMS
• Work with Windows Azure Reporting Services using Windows Azure Portal and SSMS
• Write applications in SSIS and WPF to access reports and access native server reports from SharePoint Site

I hope this book is appreciated as much as the earlier one.

This book can now be bought from the publishers as well as other online book sellers:
http://www.amazon.com/Learning-Server-Reporting-Services-ebook/dp/B00DM23X5M/ref=sr_1_2?s=books&ie=UTF8&qid=1372453186&sr=1-2

If you are using SQL Server 2008 then this book is for you,

http://www.amazon.com/Learning-Server-2008-Reporting-Services/dp/1847196187/ref=sr_1_1?s=books&ie=UTF8&qid=1372453239&sr=1-1

 

The links in the book are available here:

http://hodentekmsss.blogspot.com/2013/06/links-in-book-learn-sql-server.html

 

Links in the book, Learn SQL Server Reporting Services 2012

My new book on SQL Server Reporting Services 2012 is about to be released and this post provides a list of links referenced in the book, sorted chapter wise.







Hyperlinks provide a gateway to the extensive literature that can be accessed on the Internet. They provide information (collective knowledgebase) above and beyond what one finds in a single article,book,blog or other media formats. However links in a printed book are useful to only those readers who would go to any lengths to find information but irksome, frustrating and almost useless to the others. They are of course useful in online formats such as eBooks.

In order to conform to page count limit set by the publishers (as you might have noticed computer books have become heftier and heftier) and yet provide guidance to the readers there is no better way than publishing a list of links that is chapter-wise sorted and placed in a location that is easy to access. This post is an attempt to do just that. However some essential information from Microsoft Documentation is used as is in the book in some of the chapters.

Chapter 1: Overview and Installation – SQL Server Reporting Services 2012

Operating System Requirements

http://msdn.microsoft.com/en-us/library/ms144275.aspx.

Software requirements (Power Shell)

http://msdn.microsoft.com/en-us/library/ff637750(v=azure.10).aspx

Finding processor cores of the computer

http://hodentekhelp.blogspot.com/2013/01/how-do-i-find-number-of-cores-in.html

Download link for SQL Server2012 Enterprise Evaluation x64 bit

http://www.microsoft.com/en-us/download/details.aspx?id=29066

Options for SharePoint Integration

http://technet.microsoft.com/en-us/library/hh213532.aspx

Distributed replay controller

http://msdn.microsoft.com/en-us/library/ff878183(v=SQL.110).aspx

Configuring reporting services 2012

http://msdn.microsoft.com/en-us/library/cc281311.aspx

SQL Server 2012 Security related link

http://msdn.microsoft.com/en-us/library/ms144228.aspx

Setting up IE browser to start with Administrator Privileges

http://hodentekhelp.blogspot.com/2013/01/how-to-start-ie-browser-with.html

SQL Server 2000 database files

http://www.microsoft.com/en-us/download/details.aspx?id=23654

Attaching and detaching databases

http://msftdbprodsamples.codeplex.com/workitem/19203

Attaching/Detaching step-by-step

http://hodentek.blogspot.com/search?q=Sample+databases

Running script files, step-by-step

http://hodentekmsss.blogspot.com/2013/01/how-do-i-install-sample-database-using.html

Choosing SharePoint Server for SQL Server 2012 Integration

http://technet.microsoft.com/en-us/library/dc6a3372-db26-43f0-b7aa-f725acc635c2

Installing SharePoint 2010 on Windows 7(x64)

http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx

SharePoint 2010 hardware and software requirements

http://technet.microsoft.com/en-us/library/cc288751(v=office.14).aspx

Download link SharePoint2010 Trial

http://www.microsoft.com/en-us/download/details.aspx?id=16631

Installing Microsoft Sync Frame work 1.0(x64)

http://go.microsoft.com/fwlink/?LinkID=141237

Get Microsoft SQL Server 2008 Native Client MSI

http://go.microsoft.com/fwlink/?LinkId=123718

Download and Install Windows 6.1-KB974405-x64.msu

http://www.microsoft.com/en-us/download/details.aspx?id=17331

Get Microsoft Chart Controls for Microsoft .NET Framework 3.5 (KB2500170).

http://support.microsoft.com/kb/2500170

SharePoint2010 and SQL Server 2012 choice

http://msdn.microsoft.com/en-us/library/dc6a3372-db26-43f0-b7aa-f725acc635c2

Reporting Services 2012 Add-in for SharePoint 2010

http://www.microsoft.com/en-us/download/details.aspx?id=29068

Making changes to application pool identity

http://hodentekhelp.blogspot.com/2013/04/how-do-you-make-changes-to-application.html

Chapter 2: SQL Server Reporting Services 2012 Projects with Visual Studio 2012

SSDT and SQL Server 2012

http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

SSDT first time problems

http://www.sqlservercentral.com/blogs/jamesserra/2012/04/13/ssdt-installation-confusion/print/

http://blogs.msdn.com/b/ssdt/archive/2012/06/06/getting-started-with-localdb-debugging-using-ssdt.aspx?CommentPosted=true#commentmessage

SSDT and SSRS 2012

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/1f005f31-82a6-4e1c-b221-cb2c798c4caa

SSDT December 2012 update

hodentekmsss.blogspot.com/2012/12/december-update-to-ssdt.html

http://msdn.microsoft.com/en-us/data/hh297027

Report Designer

http://technet.microsoft.com/en-us/library/ms173745.aspx

Problem with report preview in VS Designer

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/41d46c69-5a18-4915-8b5e-fc3c19da8db9

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/38ff8d54-06aa-40c3-8916-378d5185320e

First edition

Learning SQL Server Reporting Services 2008, ISBN: 9781847196187, Packt Publishing, 2009

Export problem to MS Access

http://connect.microsoft.com/SQLServer/feedback/details/735375/business-intelligence-bids-ssdt-missing-referenced-assembly

Visual Studio 2012 Ultimate download site

http://www.microsoft.com/visualstudio/eng/downloads

Report Viewer Controls

http://msdn.microsoft.com/en-us/library/ms251671.aspx

Visual Studio 2010 download site

http://www.microsoft.com/en-us/download/details.aspx?id=12187

Detailed description of Report Builder 2.0

http://dotnet.sys-con.com/node/982742 Part 1

http://jayaramkrishnaswamy.sys-con.com/node/1227111 Part 2

RDL and RDLC Schemas

http://msdn.microsoft.com/en-us/library/ms252109.aspx

Report Viewer Runtime 2008 version

http://www.microsoft.com/en-us/download/details.aspx?id=6576

Report Viewer Runtime 2010 package

http://www.microsoft.com/en-us/download/details.aspx?id=6442

Report Viewer Runtime 2010 SP1

http://www.microsoft.com/en-us/download/details.aspx?id=6610

Report Viewer Runtime 2012

http://www.microsoft.com/en-us/download/details.aspx?id=35747

Chapter 3: Overview of SQL Server Reporting Services 2012 Architecture, Features, and Tools

Major Components of Native Mode installation

http://msdn.microsoft.com/en-us/library/ms157231.aspx

SharePoint Standalone Deployment for SQL Server 2012 Integration

http://msdn.microsoft.com/en-us/library/bb510781(v=sql.105).aspx

Reporting Services 2012 Configuration details

http://msdn.microsoft.com/en-us/library/ms155866.aspx

Regarding Report Parts and their usage

http://msdn.microsoft.com/en-us/library/ee635721.aspx

ESRI Shape Files

http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf

Regarding Report scheduling

http://go.microsoft.com/fwlink/?linkid=232473

Creating, modifying and deleting Schedules

http://msdn.microsoft.com/en-us/library/ms155897.aspx

Power View Features

http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx

Authentication – Report Servers

http://msdn.microsoft.com/en-us/library/bb283249.aspx

URL access parameters

http://msdn.microsoft.com/en-us/library/1c3e680a-83ea-4979-8e79-fa2337ae12a3

Reporting Services Default Extensions

http://msdn.microsoft.com/en-us/library/ms157231.aspx

Security, Data Processing, Rendering, Report Processing and Delivery Extensions

http://msdn.microsoft.com/en-us/library/hh213576.aspx

Chapter 4: Working with Report Manager

Report Manager 2012 - Overview

http://msdn.microsoft.com/en-us/library/ms157147.aspx

Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008

http://msdn.microsoft.com/en-us/library/bb630430.aspx

Tasks and permissions on Native mode report server

http://msdn.microsoft.com/en-us/library/ms159840.aspx

Launch error from while launching Report Builder from Report Manager

http://social.msdn.microsoft.com/Forums/enUS/sqlreportingservices/thread/ec0a6b15-1816-4d6c-87c0-b2f1f28f3d04

Report Manager Site Settings

http://msdn.microsoft.com/en-us/library/ms181194.aspx

Report Manager F1 Help

http://msdn.microsoft.com/en-us/library/ms189690.aspx

System Role assignments (New/Edit)

http://msdn.microsoft.com/en-us/library/ms186541.aspx

Clickthrough Reports (SSRS)

http://technet.microsoft.com/en-us/library/ms345252.aspx

Exporting Reports (Report Builder and SSRS)

http://msdn.microsoft.com/en-us/library/dd239307.aspx

Caching Reports (SSRS)

http://msdn.microsoft.com/en-us/library/ms155927.aspx

Chapter 5: Working with Report Manager

Learning SQL Server 2008 Reporting Services, Jayaram Krishnaswamy, Packt publishing, ISBN: 9781847196187, 1st Edition, March 2009


http://www.packtpub.com/learning-sql-server-2008-reporting-services/book/mid/010409ofvkyp

Chapter 5: Working with Report Builder 3.0

Report Builder: ENU\x86\ReportBuilder3.msi (24.8 MB) file download link

http://www.microsoft.com/en-us/download/details.aspx?id=29072

Report Builder 2.0 Interface description

http://dotnet.sys-con.com/node/982742 Part 1

Authoring Reports with Report Builder 2

http://jayaramkrishnaswamy.sys-con.com/node/1227111 Part 2

Data source properties - credentials

http://msdn.microsoft.com/en-us/library/ms178308.aspx

Chapter 7: Report Authoring with Report Builder 2.0

Learning SQL Server 2008 Reporting Services, Jayaram Krishnaswamy, Packt publishing, ISBN: 9781847196187, 1st Edition, March 2009

Report Parts in Report Designer (SSRS)

http://technet.microsoft.com/en-us/library/ee635721.aspx

Chapter 6: Power View and Reporting Services

Business Intelligence Semantic Model

http://blogs.msdn.com/b/analysisservices/archive/2012/03/09/xvelocity-and-analysis-services.aspx

What’s new in Power View

http://office.microsoft.com/en-us/excel-help/whats-new-in-power-view-in-excel-2013-and-in-sharepoint-server-HA102901475.aspx

Comparison of Multidimensional and Tabular Model

http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/

Overview of Power View

http://social.technet.microsoft.com/wiki/contents/articles/3726.power-view-overview.aspx#Useful_links_for_Project_Crescent_and_SQL_Server_Denali

Power View Visualization

http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx

Practicing Power View online

http://blogs.msdn.com/b/oneclickbi/archive/2011/12/27/more-demos-of-power-view-available.aspx

News about Windows Azure HDInsight; and Power View/Power Pivot

http://redmondmag.com/blogs/the-schwartz-report/2013/03/big-data-fray.aspx

Tabular Model and default field set

http://msdn.microsoft.com/en-us/library/hh479569.aspx

Measures: Tabular Data Model

http://msdn.microsoft.com/en-us/library/hh230824.aspx

Power View with Multidimensional model

http://blogs.msdn.com/b/analysisservices/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

Chapter 7: Self-service Data Alerts in SSRS 2012

Data Alerts details

http://msdn.microsoft.com/en-us/library/gg492252.aspx

Setting up content types in SharePoint site

http://msdn.microsoft.com/en-us/library/bb326289.aspx

SharePoint Integrated Reporting Services proxy site

http://msdn.microsoft.com/en-us/library/gg492284.aspxhttp://msdn.microsoft.com/en-us/library/gg492284.aspx

About alert Rules and Alert Scheduling

http://technet.microsoft.com/en-us/library/gg492254.aspx

Chapter 8: Reporting Services and Programming

URL Access

http://msdn.microsoft.com/en-us/library/1c3e680a-83ea-4979-8e79-fa2337ae12a3

Deprecated prefixes (DSU and DSP) in SQL Server Reporting Services 2012

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/72f83019-9d2b-4ede-a526-e096a0cabc5b/

URL Access syntax for SharePoint Integrated Reporting Services server

http://msdn.microsoft.com/en-us/library/ms153586.aspx

Report Viewer: CodePlex Site link

http://reportviewer.codeplex.com/

Stream callback and Report Viewer

http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.createstreamcallback.aspx

Report Server Web Services Management Endpoints

http://msdn.microsoft.com/en-us/library/ms155398.aspx

ReportExecution2005 end point

http://msdn.microsoft.com/en-us/library/ms154052.aspx

SharePoint proxy endpoints

http://msdn.microsoft.com/en-us/library/ms155398.aspx

SharePoint Integrated mode ReportService2006

http://technet.microsoft.com/en-us/library/reportservice2006.reportingservice2006

Windows Management Framework 3.0 (PowerShell) download

http://www.microsoft.com/en-us/download/details.aspx?id=34595

Power Shell quick reference (Cheat sheets)

http://www.microsoft.com/en-us/download/details.aspx?id=30002

PowerShell script for getting all Prerequisites to install SharePoint server 2010

http://gallery.technet.microsoft.com/scriptcenter/bcf3332d-f726-4ac7-b01a-eeda4b7ece8e

Making changes to Application Pool

http://hodentekhelp.blogspot.com/2013/04/how-do-you-make-changes-to-application.html

Power Shell – In depth link

http://msdn.microsoft.com/en-us/library/gg492249.aspx

System.Management namespace details

http://msdn.microsoft.com/en-us/library/aa719480.aspx

RSS disambiguation

http://en.wikipedia.org/wiki/RSS_(disambiguation))

Configuring Reporting Services URL

http://msdn.microsoft.com/en-us/library/bb630447.aspx

Writing custom code for reports

http://msdn.microsoft.com/en-us/library/ms156028.aspx

Chapter 9: Windows Azure SQL Reporting

Windows Azure SQL Reporting Services limitations

http://msdn.microsoft.com/en-us/library/windowsazure/gg430132.aspx

Pricing for hosting reports on Windows Azure SQL Reporting

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/fecb288a-d1a8-4eda-b6f7-85f6dc1e4b4e

Main link for pricing

http://www.windowsazure.com/en-us/pricing/details/

MSDN Forums

http://social.msdn.microsoft.com/Forums/en-US/category/windowsazureplatform

Technet forums

http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/threads

Windows Azure Portal- link to old portal

http://www.windowsazure.com/en-us/home/features/portals/

Windows Azure Service - known issues

http://msdn.microsoft.com/en-us/library/windowsazure/hh667464

Comprehensive reference to SQL Azure with hands-on examples

Microsoft SQL Azure: Enterprise Application Development, ISBN:9781849680806, Packt Publishers, 2010

https://www.packtpub.com/microsoft-sql-azure-enterprise-application-development/book

Chapter 10: Applications Accessing Report Servers

Windows Presentation Foundation in Visual Studio

http://msdn.microsoft.com/en-us/library/bb546194(v=vs.90).aspx

Web Browser Class (.NET Framework 4.5)

http://msdn.microsoft.com/en-us/library/system.windows.controls.webbrowser.aspx

Browser WebBrowser.Navigate () method

http://msdn.microsoft.com/en-us/library/system.windows.forms.webbrowser.navigate.aspx

View and Explore Native Mode Reports Using SharePoint Web Parts (SSRS)

http://msdn.microsoft.com/en-us/library/ms159772.aspx

SQL Server Integration Services

http://msdn.microsoft.com/en-us/library/ms141026.aspx

Chapter 14: Web Service task to convert miles to kilometres

SQL Server Integration Services Using Visual Studio 2005, ISBN: 9781847193315, Packt Publishers, 2007

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

Regarding the SQL Server 2012 Developer Training Kit

If the answer is yes, I recommed that you download immediately the SQL Server 2012 Developer Training Kit. This can be installed from here:
http://www.microsoft.com/en-us/download/details.aspx?id=27721



Before you install the KIT you need to install the Web Platform Installer 4.0. You can get it from the same link. Read the previous post here for installing Web PI 4.0: http://hodentek.blogspot.com/2012/12/you-can-get-lots-of-stuff-from-web.html

Note that the KIT's executable may not necessariloy show up in WEB PI4.0!!

When you run the Kit related exe (http://download.microsoft.com/download/D/0/9/D098DCF2-1888-4624-920B-B2DE71B2728F/SQL2012DevTrainingKit.WebInstaller.exe) file you will download the kit.

On this computer it was installed in the default folder "C:\Program Files\Microsoft\Web Platform Installer\WebPlatformInstaller.exe". Note that you have to allow Activex on your IE and you may get this warning, better pay attention to it and enable it.

When you run this program you will find the default.htm, the starting point of your journey.
C:\SQL2012UpdateForDevsTrainingKit\Default.htm

Default.htm opens out like this. You can learn a lot, not just DBA, but also development and of course BI.

Good luck.

Aloha from Honolulu