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

Training Course in Honolulu: SQL Server Reporting Services 2012

This hands-on course will provide you with the ability to create cutting edge reports using Microsoft's latest reporting services product, SQL Server 2012.

The course is offered by the Pacific center for Advanced technology Training: http://pcatt.hawaii.edu/home

Report writing is one of the most important IT related jobs in an enterprise. This course if for you if you would like to master report writing/authoring using SQL Server Reporting Services. The course begins with a presentation of essential technical background, after which you will work through guided hands-on exercises. At the end of the course you will have gained:
• A solid understanding of how to interact with SQL Servers
• Knowledge and experience installing and configuring reporting Services
• A working knowledge of using Report Builder to author reports and deploy them to report server
• A working knowledge of Report Manager's ability to carry out administrative tasks related to all aspects of reports; creating shared data sources, folder and folder hierarchies on the report server, and report models; managing security and subscriptions; and configuring data driven subscriptions
• An understanding of how to embed report viewer controls in windows and web projects.

Course code: 134F1TSQL003A

Venue: Honolulu Community College Honolulu Community College
874 Dillingham Blvd Honolulu, HI 96817-4598 Phone: (808) 845-9211
Official Website: honolulu.hawaii.edu/

Section 1 - Nov 6 - Dec 16; MW; 5:30 pm - 8:30 pm; 12 mtgs.

I will be using my latest book on SQL Server Reporting Services for this course:

Can you access SQL Server 2012 using OpenOffice 4.0?

Yes, you can. You can even connect to SQL Azure.

This is how you connect to SQL Server 2012:

It just takes 4 easy steps as you will see presently.

It's assumed that you have set up an ODBC DSN to connect to SQL Server 2012. If you want to know how you can set up this file access the following link:

http://hodentekmsss.blogspot.com/2013/08/how-do-you-create-odbc-dsn-to-sql.html

Click open OpenOffice 4.0 from its shortcut on desktop to display the following:

Click on Database in the displayed list.

The Database wizard is displayed as shown with four steps and it is already in Step 1 -Select Database.

Click Connect to an existing database and click the drop-down list to view how you want to connect.

Choose the last one, ODBC. Click Next. You Step into step 2 - Set up an ODBC Connection.

The accessible ODBC sources are displayed as shown.

Click on sql11 and Click OK. Click Next. You move into Step 3 - Set up user authentication. Enter credentials to the SQL Server. The windows authentication is used.

Place check mark for Password required. Click Test Connection.

Enter Password field and click OK. You should see a positive response as shown.

Click OK and click Next to move to Step 4 - Save and proceed as shown.

Read instructions. Save the OpenOffice database with a name as shown, Aug 25.odf.

The file opens in the OpenOffice 4.0 user interface as shown.

Now you can start working with Northwind database in OpenOffice 4.0

Perhaps, if you find connecting to SQL Server 2012 using SSMS is a bit taxing and somewhat intimidating try Apache OpenOffice 4.0. 

How do you create a ODBC DSN to SQL Server 2012?

You can use the ODBC Data Source Administrator interface on your computer to create the DSN. You can display this interface from Control Panel | System and Security | Administrative Tools | Data Source (ODBC) as shown.

We will now create a System DSN.

Click System DSN and click Add. The Create New Data Source window is displayed as shown.

Click SQL Server Native Client 11.0 and click Finish.

The Create a New Data Source to SQL Server window is displayed as shown. You are supposed
to provide all the details. The Name is user's choice as well as the Description. The
drop-down for 'Which SQL Server do you want to connect to' should bring up the list of
servers accessible on your computer as shown.

Highlight HodentekWin7\NJ and click Next. HodentekWin7\NJ is a SQL Server 2012 named instance on this computer with 'mysorian' as administrator. Click Next to open the window for authentication details.

Accept the default authentication shown. Click Next to display the following.

Accept defaults and click Next.

Again accept the defaults on this page as well.

Click Finish. The ODBC Microsoft SQL Server Setup file is displayed as shown.

Click Test Data Source and verify connectivity as shown.

Click OK and Click OK. The new DSN is added to the list as shown.

Click OK and close out.

This completes the steps to create an ODBC DSN to SQL Server 2012.

Training Event Honolulu: Introduction to Structured Query Language (SQL)

You are welcome to attend my course at the Pacific Center for Advanced Technology Training (PACTT).

Summary:
Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.

Dates: (Section 1) Nov 5 - Dec 10; Tuesdays and Thursdays; 5:30 pm - 7:30 pm; 10 meetings.

Venue:  Honolulu Community College
874 Dillingham Blvd Honolulu, HI 96817-4598 Phone: (808) 845-9211
Official Website: honolulu.hawaii.edu/Venue: Honolulu Community College;

Course code: 134F1TSQL001A
Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

SQL Azure- Open source access

In my 2011 book, I have described the procedure for connecting to SQL Azure using OpenOffice Version 3.2.1. Basically OpenOffice supports this connectivity using ODBC. Follow this link:

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

Download Microsoft ODBC Driver 11 for SQL Server (windows) here:
http://www.microsoft.com/en-us/download/details.aspx?id=36434

This should let you create an ODBC connection which allows connection to SQL Servers.

Once you have this you can follow the procedure in "Microsoft SQL Azure: Enterprise Application Development,2011, ISBN:978-1-849680-80-6, Chapter 10: Recent Developments #OpenOffice access to SQL Azure"

Please note that I have not tested this with new version of Apache OpenOffice and the new ODBC driver.

The above example shows running reports using SQL Azure Data.