SSRS/IQA/Rise Training Series

[Pages:72]SSRS/IQA/Rise Training Series

Module 4 ? Using SSRS with iMIS

Hands-On Training Reference and Workbook

Business Systems Consultants, Inc. (312) 553-1253



Table of Contents Overview of SQL Server Reporting Services (SSRS) ..............................................................................5

What is SSRS? ................................................................................................................................5 Open Database Connectivity (ODBC)...............................................................................................5 Relational Databases......................................................................................................................6

About tables and records...................................................................................................................... 6 About fields ........................................................................................................................................... 7 About keys ............................................................................................................................................ 7 iMIS Tables ....................................................................................................................................7 Membership Tables .............................................................................................................................. 7 Meetings Tables .................................................................................................................................... 7 Dues and Subscription Tables ............................................................................................................... 8 AR/Cash and Orders Tables................................................................................................................... 8 Fundraising Tables ................................................................................................................................ 8 SSRS Integration with iMIS .............................................................................................................8 Creating a New Project.......................................................................................................................9 Section Goal...................................................................................................................................9 Understanding SSRS .......................................................................................................................9 Opening SSRS .................................................................................................................................9 Solution Explorer .............................................................................................................................10 Planning your report ........................................................................................................................11 Section Goal.................................................................................................................................11 Report Elements...........................................................................................................................11 Creating a Report in Five Steps .....................................................................................................12 Getting it Right.............................................................................................................................12 Creating a Data Source.....................................................................................................................12 Section Goal.................................................................................................................................12 Setting up a connection ................................................................................................................13 Lab 1................................................................................................................................................15 Creating a report definition file ........................................................................................................16 Section Goal ........................................................................................................................................ 16 Creating a Report Definition File......................................................................................................... 16

? 2014 Business Systems Consultants, Inc. ? All rights reserved

2 of 72

Your Report Window....................................................................................................................17 Creating a dataset............................................................................................................................19

Section Goal ........................................................................................................................................ 19 Datasets .............................................................................................................................................. 19 Creating a dataset ............................................................................................................................... 19 Query................................................................................................................................................... 20

Name:.............................................................................................................................................. 20 Data source: .................................................................................................................................... 20 Command type:............................................................................................................................... 20 Query string: ................................................................................................................................... 20 Timeout: .......................................................................................................................................... 20 Fields ................................................................................................................................................... 21 Data Options ....................................................................................................................................... 21 Parameters.......................................................................................................................................... 22 Filters................................................................................................................................................... 22 Building a query ...........................................................................................................................23 Using SQL ............................................................................................................................................ 23 Using the Query Builder ...................................................................................................................... 24 Saving your work................................................................................................................................. 28 Lab 2................................................................................................................................................29 Report Layout ..................................................................................................................................30 Section Goal.................................................................................................................................30 Layouts ........................................................................................................................................30 Adding Fields ...............................................................................................................................31 Lab 3................................................................................................................................................33 Layout.......................................................................................................................................... 34 Preview........................................................................................................................................ 34 Field Properties............................................................................................................................34 Appearance ............................................................................................................................. 35 Data ......................................................................................................................................... 35 Design...................................................................................................................................... 35

? 2014 Business Systems Consultants, Inc. ? All rights reserved

3 of 72

 International ........................................................................................................................... 35 Layout...................................................................................................................................... 35 Misc ......................................................................................................................................... 35 Lab 4................................................................................................................................................37 Finishing Touches.............................................................................................................................39 Section Goal.................................................................................................................................39 Moving Report Components .........................................................................................................39 Adding a Text Box ........................................................................................................................39 Adding a Header or Footer............................................................................................................40 Adding Report Parameters ...........................................................................................................40 Lab 5................................................................................................................................................42 Integrating Reports with iMIS...........................................................................................................43 Section Goal.................................................................................................................................43 SSRS within iMIS ..........................................................................................................................43 Importing an SSRS report into iMIS ...............................................................................................44 Adding Reports to your Navigation..................................................................................................... 46 Using SSRS to format your report .....................................................................................................50 Lab 6 ? Create a Pie Chart report using IQA and SSRS ........................................................................50 Appendix A ? Setting up SSRS in SQL Server 2008 R2 .........................................................................60 Appendix B ? Setting SQL/SSRS for automatic email subscriptions.....................................................64 Configure SQL 2005 ......................................................................................................................64 Configure the report in SSRS for Email Subscription.......................................................................66 Configure SSRS for File Share subscription.....................................................................................71

? 2014 Business Systems Consultants, Inc. ? All rights reserved

4 of 72

Overview of SQL Server Reporting Services (SSRS)

Welcome to iMIS - Introductory SQL Server Reporting Services (SSRS). This guide will provide you with a basic understanding of Microsoft's reporting system and how it integrates with iMIS.

The exercises in this guide walk you through creating a report, selecting records, organizing records, inserting special fields, and adding SSRS to the iMIS Report menu.

This guide includes the following sections: 1. Overview of SSRS 2. Creating a New Project 3. Creating a Data Source 4. Planning Your Report 5. Creating a Report Definition File 6. Creating a Dataset 7. Report Layout 8. Finishing Touches

What is SSRS?

Microsoft SQL Server Reporting Service is a professional report writer that has the ability to communicate with any SQL database, including iMIS. SSRS is included with your purchase of SQL server and is run through Visual Studio. SSRS and iMIS communicate with each other through a database interface called Open Database Connectivity (ODBC). SSRS allows you to create presentationquality reports, share reports online, or email people reports on a scheduled basis as a subscription.

iMIS provides a query tool Intelligent Query Architect (IQA) that can be used as the basis of an SSRS report, allowing people comfortable with IQA to use it to query the data, and then use SSRS to do the layout and any charts and graphs.

Open Database Connectivity (ODBC)

Client programs, such as database tools and business applications, communicate with the database engine or network database server through database interfaces. The ODBC is the database interface that points the SSRS software to the iMIS database. The user runs an ODBC compliant application (for example, SSRS, Crystal Reports, iMIS, Access) that reads an ODBC data source. The ODBC data source points to a database that resides on the network or on the workstation. With this connectivity, the client application can access the information from the database and then use this information to create reports or work with the data in other ways.

The following diagram illustrates how the ODBC interface communicates with the database and the client applications.

? 2014 Business Systems Consultants, Inc. ? All rights reserved

5 of 72

Relational Databases

A relational database consists of tables, fields (columns), and rows. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. Relational databases store data in the form of related tables. For example, the iMIS database is a relational database.

Here, the Name table contains the ID, LAST_FIRST, TITLE, and WORK_PHONE fields and has five data rows.

About tables and records The tables consist of fields, or columns, and rows of records. A table consists of records that are a collection of individual fields. Tables are linked through relationships. The Name table, for example, is a table in iMIS.

? 2014 Business Systems Consultants, Inc. ? All rights reserved

6 of 72

About fields A field is used to store data. The data type to be stored in a field is pre-defined, for example, as a money field, as an integer field, or as a picture field. The ID and LAST_NAME fields, for example, are fields in the Name table.

About keys A key is a field that defines a relationship between two or more tables. The main key types are primary and secondary. A primary key uniquely identifies a row, for example, Name.ID. More than one row may have the same secondary key.

iMIS Tables

The iMIS database comprises many tables and groups of tables. Each iMIS module contains a group of tables such as the Membership, Meetings, Dues/Subscriptions, AR/Cash, Orders, and Fundraising tables. When you link your SSRS report to the iMIS database, you can set up your report to access data from these or any other iMIS tables. Tip: For help identifying which tables to include in your report, visit or find an existing report or IQA query with similar fields.

Membership Tables

For example, if you were creating a Roster report, you would include the Name and Name_Address tables in your report.

Note: The Name_XXX is the name of your customized demographic tables where XXX represents the name of the tables. Name_Demo is used for the conversion of the iMIS LAN-based demographic fields.

Meetings Tables

For example, if you were creating a Badge report, you would include the Order_Meet, Order_Badge, and possibly, the Meet_Master tables in your report.

? 2014 Business Systems Consultants, Inc. ? All rights reserved

7 of 72

Dues and Subscription Tables

For example, to create a Dues history report, you would include the Activity and Subscriptions tables in your report. AR/Cash and Orders Tables

For example, to create an Invoice report, you would include the Invoice and Orders tables in your report. Fundraising Tables

For example, if you were creating a mailing label report, you would include the Activity table in your report.

SSRS Integration with iMIS

ASI is in the process of rewriting several Omnis and Crystal reports in SSRS. The iMIS installation and upgrade will automatically copy any such SSRS reports into the iMIS directory. The SSRS reports will display with the Cyrstal reports under Generate Reports. Until this process is complete, you will have to create and upload your own reports into iMIS.

? 2014 Business Systems Consultants, Inc. ? All rights reserved

8 of 72

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download