NiUG Asia Pacific Discovery Conference

12-14 September 2016

GREAT THINGS YOU CAN DO WITH SQL REPORTING SERVICES (SSRS)

ASHLEY HUNT, PGA OF AUSTRALIA

ABOUT PGA

PGA of Australia is one of Australia’s oldest sporting bodies, forming in 1911 Consists of 2,700+ members made up of tournament players, golf trainees and vocational members working full time in the golfing industry. PGA runs over 600 professional golfing events per year for professionals. We offer accredited continuing education for members and have a nationally recognised Trainee Program. Recently we launched a formal training program called Community Instructor in conjunction with Golf Australia to allow teachers, Golf Club members or other people accreditation to assist in the delivery of golf programs. We have approx. 70 staff, mostly based in the National Office in Melbourne with offices in NSW, QLD, SA and WA Have been running imis since 2009 currently on 20.1 looking to upgrade to Q4 in December. We are self hosted so I can do what I like Using Rise, Informz, Webformz, IEmail, Meeting Closer, Task Centre, AutoLogin

1

NiUG Asia Pacific Discovery Conference

12-14 September 2016

AGENDA GREAT STUFF YOU CAN DO WITH SQL SERVER REPORTING (SSRS) ABOUT SSRS What is SSRS Some SSRS Report Examples

GETTING STARTED WITH SSRS What you need to know to use SSRS in IMIS What do you need to create / edit reports

SSRS & IMIS SSRS in Rise (SSRS & Report Display DCI) Communications suite & Process Automation

REAL WORLD EXAMPLES PGA of Australia PAMS (Professional Association Managers)

ABOUT SSRS WHAT IS IT

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services). While SSAS enables users to construct special databases for fast analysis of very large amounts of data, and while SSIS enables users to integrate data from many sources outside Microsoft SQL Server, SSRS enables users to quickly and easily generate reports from Microsoft SQL Server databases. -- Wikipedia

2

NiUG Asia Pacific Discovery Conference

12-14 September 2016

ABOUT SSRS WHAT IS IT & WHO IS USING IT

Some general examples:

GETTING STARTED WITH SSRS JUST STARTING OUT OR NEED SOME TRAINING

Learning Resources: • • • •

Microsoft Virtual Academy - https://mva.microsoft.com/ MSDN - https://msdn.microsoft.com/en-us/library/bb522859.aspx Paid Training Services like Lynda Good old YouTube

3

NiUG Asia Pacific Discovery Conference

12-14 September 2016

WHY USE SSRS WITH IMIS

• Incorporate SSRS into Rise • Self service reports for members; eg Activity Reports, Certificates, Invoices, Statements etc all exportable to PDF, CSV • Staff functions such as generating letters • Can be launched from IQA’s • Use as a BI tool • Generate data on the fly • Flexibility to show custom data not bound by IQA • Use in Communication Suite and Process Automation; eg Monthly Reports • Ability to execute stored procedures or use stored procedures as data

GETTING STARTED WITH SSRS PRE REQ KNOWLEDGE FOR IMIS

• • • • • •

SSRS Report structure Excel Formula SQL Code IQA Rise Imis Data Structure

4

NiUG Asia Pacific Discovery Conference

12-14 September 2016

GETTING STARTED WITH SSRS SOFTWARE REQUIRED

•

SQL Server Data Tools (SSDT) – Visual Studio 2015/2012 etc – SSDT can be installed as part of SQL Server however can also be installed standalone. Access to the SQL server needed.

• •

Report Builder (not recommended) – standalone report designer Using Visual Studio allows for Project creation to group reports

https://msdn.microsoft.com/en-us/library/mt204009.aspx (2015) https://www.microsoft.com/en-us/download/details.aspx?id=36843 (2012)

RUNNING SSRS REPORTS IN RISE

• In Rise there are 2 main ways to run an SSRS Reports using Dynamic Content Imems (DCI’s) 1. SSRS Report DCI – Bound to an IQA query. Mainly used for self service reports http://help.imis.com/20.2/Understanding_iMIS_Features/Continuum_-_Reporting/Quick_facts_SSRS_reports.htm

2. Report Display DCI – Not bound to an IQA query – enables direct run of the RDL file without an IQA query. Very flexible. Often used for staff reporting http://docs.imis.com/20.2/#!reportdisplay5.htm

5

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS SSRS REPORT IN RISE

Standalone SSRS Reports You have complete control over the datasets you create and can go directly to SQL. Changes to the report can be made directly, with fields easily added and removed without needing to go back and forth to iMIS. There are fewer steps and you can get reports up to your website very quickly:

Create Report

Import into Document System

Add to Rise

SSRS IN IMIS

• Open Visual Studio • Select Report Server Project

• Click Add New Data Source under Shared Datasource

6

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS

You can select either SQL Server or ODBC as Data source Select Server Name Connect to a database name Enter credentials if needed Click on Test connection

SSRS IN IMIS KEY TERMS

DataSet: •

Is the data that the report will pull information from –

You can build these in 3 ways; 1. 2. 3.

• • •

SQL code Creating a generic query builder Using a IQA saved as a report in iMIS.

Its up to you whether or not you use the query builder option or if you enter the sql code directly into the query For example select * from vBoCsContact or; Select id, full_name, phone, email, address from vBoCsContact where Member_type=‘M’ and Status=‘a’

Parameters: •

Are the same as filters in an IQA – a way to condition the data you show

7

NiUG Asia Pacific Discovery Conference

12-14 September 2016

GETTING STARTED WITH SSRS SOFTWARE REQUIRED

Gotcha! Imis SSRS Reports needs to run with the 2008 RDL Schema (when you view the XML Code)

If you create a new report within VS then it defaults to 2010 / 2016 when you look at code

GETTING STARTED WITH SSRS SOFTWARE REQUIRED

To get past this you need to change the Target version. Right click on the project name and select properties Change the TargetServerVersion to SQL Server 2008 and use the file from the bin\Debug folder (Output Path)

8

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS IQA BASED REPORTS (SSRS REPORT DCI)

• • • •

•

•

Uses IQA as report source IQA is familiar no knowledge of SQL code is required but handy Many IQA reports come standard with iMIS IQA based report usually require more planning upfront as it can be time consuming to modify if you forgot a field in the original query or need to make changes to the data source once the report layout exists. More maintenance is required as every report you create needs both the report template and the query. Data is controlled by the IQA – no parameters in the SSRS report

Create IQA Query Edit Save Report definition Modify Layout Import back into Imis Create a Reporting Services Report Add to Rise

SSRS IN IMIS Viewing an SSRS report from an IQA query

https://youtu.be/2hWn58YFG_s

9

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS USING SSRS IN COMMUNICATION SUITE & PROCESS AUTOMATION

• You can use SSRS in the communications and process automation • SSRS reports get attached as a PDF in Process Automation • If sending in communications suite be sure to use only the parameter of @ID in SSRS to personalise – eg Welcome letter or Invoice

SSRS IN IMIS USING SSRS IN COMMUNICATION SUITE & PROCESS AUTOMATION

• Setup your SSRS in your communication template

10

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS A NICE TIP

When presenting address details in an SSRS you can conditionally show address fields if they are blank or not use the following in your expression (assuming field names are correct) =Fields!FULL_NAME.Value & vbCRLF & Fields!ADDRESS_1.Value & vbCRLF & iif(Fields!ADDRESS_2.Value "", Fields!ADDRESS_2.Value & vbCRLF & Fields!CITY.Value & ", " & Fields!STATE_PROVINCE.Value & " " & Fields!ZIP.Value & iif(Fields!COUNTRY.Value "",Fields!COUNTRY.Value,""), Fields!CITY.Value & ", " & Fields!STATE_PROVINCE.Value & " " & Fields!ZIP.Value & iif(Fields!COUNTRY.Value "",Fields!COUNTRY.Value,""))

SSRS IN IMIS REAL WORLD EXAMPLES – PGA OF AUSTRALIA

CDP status report

11

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS REAL WORLD EXAMPLES – PGA OF AUSTRALIA

CDP Certificates

SSRS IN IMIS REAL WORLD EXAMPLES – PGA OF AUSTRALIA

Membership Invoices

12

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS REAL WORLD EXAMPLES – PAMS ASSOCIATION MANAGERS

• PAMS use SSRS reports for Invoices, Statements, Internal Financial Reports, Monthly Association reports to members, Event Badges

SSRS IN IMIS REAL WORLD EXAMPLES – PAMS ASSOCIATION MANAGERS

• Pams has setup a great system for dealing with invalid email addresses from bounce backs from a 3rd Part email marketing system • If an email address has become invalid (no longer exists) – and they are unable to contact the member a letter is sent. SSRS handles the letter and the flag to say that the letter has been sent. • A stored procedure is executed from the letter SSRS to update the “LetterSent” field in a UD table. The IQA then displays a tick to say the letter has been sent and an Activity is written

13

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS REAL WORLD EXAMPLES – PAMS ASSOCIATION MANAGERS

• In the dataset an EXEC sp is run before the select query

• From the staff site the letter SSRS report is displayed and exported/printed via an IQA

SSRS IN IMIS REAL WORLD EXAMPLES – PAMS ASSOCIATION MANAGERS

• The letter opens in a tab window where it is exported to PDF or printed

14

NiUG Asia Pacific Discovery Conference

12-14 September 2016

SSRS IN IMIS REAL WORLD EXAMPLES – PAMS ASSOCIATION MANAGERS

• The IQA query then gets updated with a tick marking that the letter has been sent and a activity is recorded

WITH THANKS TO OUR SPONSORS NIUG DISCOVERY CONFERENCE

15