Crystal Reports for SAP Business One Mastery Training Course

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Starting from the beginning. Creating a report or layout.

Index:

00:01:42 - Crystal Reports interface overview

00:02:08 - Starting a new blank report

00:02:28 - Adding a new SQL connection

00:06:15 - Adding an SQL table to your report

00:09:50 - General Crystal Reports interface overview

00:10:45 - More on the Field Explorer

00:12:45 - More on the Database Expert

00:14:48 - How to disable Automatic "Smart" Linking

00:16:32 - My practical advice for learning Crystal Reports

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Starting from the beginning. Creating a report or layout.

Index:

00:18:40 - Record Select Expert

00:19:18 - More on the Section Expert

00:28:30 - More on the Group Expert

00:29:40 - More on insert menu item

00:30:05 - SAP Business One save add-in integration

00:31:18 - More on the Preview mode

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Starting from the beginning. Creating a report or layout.

Index:

00:31:52 - Making and editing document layouts

00:32:52 - Editing a Sales Quote layout from SBO

00:34:51 - Token parameter example for layouts (DocKey@)

00:36:00 - Notes on saving an edited layout or report into SBO database

00:37:04 - Trick to finding a report if you accidentally saved it incorrectly

00:39:03 - My discussion on PLD converted Crystal Report layouts (Customer Statement)

00:44:10 - Lecture summary

A short and useful lecture to help you find the downloadable lecture content.

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Before you do anything you need to plan out your report. It will save you time and frustration and will help you to gather the needed table and field informaton. My Excel method will save you time and energy.

Index:

00:02:51 - My steps for Excel method

00:03:20 - Example plans

00:07:40 - Example #2

00:08:50 - Review of presentation

00:09:38 - More advanced example plans

00:09:55 - Cool quote of the day

00:10:14 - More about the structure of this course related to planning

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Various ways to find data in SAP Business One. Once you have a plan you need to start with a logical method to find the tables and fields you will need four your report.

Index:

00:01:17 - Reminder about HD video

00:01:50 - Reminder about posting questions to lecture sidebar

00:02:35 - Demo begins

00:03:40 - Review of Excel plan

00:04:05 - System information tool (CTRL + SHIFT + I)

00:05:09 - My anology of SAP tables as a spreadsheet

00:06:03 - List of fields needed for report

00:06:46 - Identifying fields needed for report using system information tool

00:07:21 - Introduction to the query generator tool

00:08:01 - Discussion about finding tables and fields with system information

00:08:38 - Detail of query generator tool

00:10:01 - OCRD (business partner) table field details

00:11:02 - Execute SELECT * FROM OCRD table to view all columns and rows from the business partner table

00:11:48 - Finding fields from query results

00:13:02 - Discussion of linked tables in SBO

00:14:55 - Easy way to identify linked tables with query generator

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Various ways to find data in SAP Business One. Once you have a plan you need to start with a logical method to find the tables and fields you will need four your report.

Index:

00:16:23 - Other ways to identify linked tables

00:19:03 - Manually browsing SQL tables

00:20:41 - Trick to identify the data you want from query generator

00:21:00 - Natural sorting of fields

00:21:38 - Where to find UDFs in tables

00:22:06 - Discussion of SAP object references and their importance

00:24:31 - SDK database reference

00:25:23 - Searching database reference

00:26:27 - Summary of finding data methods

00:26:52 - Discussion of SQL profiler tool

00:29:00 - Don't forget to post your questions to the sidebar to the right of the lecture

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Master the database expert. Continuing with making a connection to your database, adding tables and fields to your report.

Index:

00:04:08 - Beginning of demo

00:04:13 - Effects and discussion of automatic "smart" linking

00:06:10 - Disabling automatic "smart" linking

00:06:45 - Making a report from scratch and adding a new database connection

00:10:14 - Adding tables from our Excel plan

00:11:51 - Joining SQL tables

00:12:34 - Methods of finding which fields join tables

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Master the database expert. Continuing with making a connection to your database, adding tables and fields to your report.

Index:

00:16:17 - Discussion of "less is more" for database tables and report efficiency

00:17:13 - Field data types and linking

00:19:03 - Types of table joins (INNER and LEFT OUTER mostly)

00:21:50 - Discussion about SQL views and commands

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Now that we have our data, it's time to work with the section expert to setup our report structure and finally put some fields down.

Index:

00:04:58 - Beginning of demo

00:05:52 - Tutorials on the section expert

00:06:48 - Default report sections

00:10:56 - Tab option details for section expert

00:13:15 - Keep together checkbox

00:13:44 - Tip of the day - Make each page stand alone if needed

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Now that we have our data, it's time to work with the section expert to setup our report structure and finally put some fields down.

Index:

00:17:48 - Suppress blank section

00:21:14 - Underlay following sections

00:25:43 - Read only checkbox

00:26:11 - Paging tab

00:28:27 - Color tab

00:29:06 - Conditionally suppressing sections

00:30:29 - Fields and field explorer

00:31:10 - Special fields

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Now that we have our data, it's time to work with the section expert to setup our report structure and finally put some fields down.

Index:

00:31:37 - Excel report plan review

00:32:04 - Start pulling fields into the report

00:36:22 - Doing a Crystal Reports preview (push F5)

00:39:20 - Adding a summary field

00:41:02 - Review Excel plan

00:42:12 - Remember to put comments and questions in the sidebar!

Hello! If you are enjoying the course so far, please leave me a fair review. This lecture will show you how to leave course reviews. It really helps me to develop this course and it really helps me to focus on this course more. If there is anything I can improve for you before you leave your review or if you have any questions, please let me know through the Udemy message system. Thank you!

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

With our sections and fields in place, it's time to make our report pretty. Formatting, alignment, tips and tricks.

Index:

00:01:03 - Beginning of demo

00:01:35 - My hatred of rulers

00:02:53 - Report page setup

00:04:53 - Basic report elements

00:07:21 - Manual size and position

00:08:18 - Adding a line and various options

00:12:40 - Tip: getting a colour from any image to use in your report from Photoshop

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

With our sections and fields in place, it's time to make our report pretty. Formatting, alignment, tips and tricks.

Index:

00:17:40 - Adding a box with various options and formatting

00:21:07 - Continuing with more basic report elements that all reports should have

00:23:31 - Inserting a text object and formatting

00:28:03 - Quick alignment of elements

00:33:34 - More on quick alignment and quick sizing

00:35:46 - Adding special field page numbers

00:37:00 - Zoom in, zoom out mouse trick

00:38:50 - Formatting page header

00:40:06 - Setting default fonts and formats

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

With our sections and fields in place, it's time to make our report pretty. Formatting, alignment, tips and tricks.

Index:

00:41:00 - Format painter

00:44:42 - Fine tuning your sections

00:52:00 - Dynamic section sizing trick from previw

00:52:47 - Formatting for numbers and totals

00:54:49 - Review of report and Excel plan

00:55:25 - Summary of best practices

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Data and format are all sorted out, now it's time to do our selection criteria to make our reports more specific.

Index:

00:02:23 - Beginning of demo

00:03:59 - How to determine valid field constraints for record selections

00:05:02 - Using record select expert to limit data in our report

00:07:05 - Suppress section vs record select discussion

00:10:04 - Making a new parameter

00:12:23 - Using a parameter with record selection

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Data and format are all sorted out, now it's time to do our selection criteria to make our reports more specific.

Index:

00:14:46 - Adding invoice fields to the report

00:22:10 - Using record sort expert

00:25:00 - Parameter for date range

00:26:32 - Assigning a date parameter to record select

00:29:20 - Adding summary and formattings for invoice total

00:30:18 - Setting parameter display order

00:30:58 - Discuss SAP token system for parameters

00:33:45 - Rant with tips to help you get more out of this course

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Data and format are all sorted out, now it's time to do our selection criteria to make our reports more specific.

Index:

00:02:15 - Beginning of demo, usefulness of grouping

00:05:20 - Review of Excel report plan

00:05:37 - Using group expert

00:07:05 - Formatting the group header

00:12:43 - Cleaning up the details section and adding more columns

00:17:50 - Orphaned records and group "keep together" option

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Data and format are all sorted out, now it's time to do our selection criteria to make our reports more specific.

Index:

00:18:45 - More details on common group settings and sorting

00:24:05 - Discussion on my method for making reports

00:25:55 - More details on group options

00:27:22 - Discussing "keep group together" details

00:29:18 - Reformatting remarks fields and some tips on handling multi-row fields

00:32:28 - Summary fields by group with group footers

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Data and format are all sorted out, now it's time to do our selection criteria to make our reports more specific.

Index:

00:38:03 - Nested grouping with invoice rows, adding table to report

00:40:40 - Adding invoice row fields to report and formatting

00:48:38 - Makeing nested group header column labels

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Enhance your report with calculations and conditional formulas.

Index:

00:02:56 - Beginning of demo

00:05:14 - Creating a new formula field

00:06:21 - Formula functions list

00:07:38 - Writing an IF statement formula

00:14:20 - Discuss ISNULL and NULL values

00:17:17 - Replacing doc type field with new formula

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Enhance your report with calculations and conditional formulas.

Index:

00:20:44 - Making a new boolean (true / false) parameter to hide report details

00:22:32 - Writing a conditional suppress formula with a parameter

00:25:58 - Best practices for report design, design your reports to be more versatile to reduce the amount of maintenance required

00:31:09 - Conditional font colour example: highlight overdue dates in red

00:37:12 - Record select formula to filter out closed invoices

00:38:11 - Using a conditional suppress formula and a parameter to open / closed or both status invoices depending on user input

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Enhance your report with calculations and conditional formulas.

Index:

00:42:51 - Discussion of record select versus supress to filter out records

00:44:15 - Discussion of conditional grouping

00:47:29 - More ISNULL and NULL discussion

00:49:08 - Best practices for adding UDF values to avoid NULLs

00:50:56 - Recap of lecture topics

00:53:29 - Trying to improve sound in this lecture and questions

00:54:36 - BONUS: Invoice total calculation fix to match grouped rows

00:57:41 - Formatting the new formula field over the old field

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Make your report more interactive with SAP Business One specific syntax to drilldown on marketing documents and master data within the SBO client from Crystal Reports.

PS. I will attach the golden arrow image file for your reference

Index:

00:01:22 - Beginning of demo

00:02:20 - Create golden arrow hyperlink formula for master data

00:03:22 - Finding the primary key for master data and marketing documents

00:04:41 - Adding golden arrow syntax to marketing document

00:06:04 - Demo in SBO of how to use golden arrow links

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Reports within reports that have an independent data structure but some link to the main report (or not). Either traditional inline in the main body of the report or "on-demand" as a separate drilldown.

Index:

00:02:33 - Discussion about subreports and structure

00:04:35 - Inserting a subreport and creating the data structure

00:05:02 - Adding tables and fields to subreport using "Report Wizard"

00:05:41 - Formatting subreport once inserted into body of report

00:06:07 - Editing subreport

00:07:02 - Preview our new subreport

00:07:46 - Change subreport link to main report

00:08:22 - More report formatting

00:10:15 - Add a label for the subreport

00:12:05 - Creating an on-demand subreport

00:12:38 - Adding a dynamic formula for on-demand subreport caption

00:13:07 - Formatting default caption for on-demand subreport caption

00:14:40 - Using an icon as a link for on-demand subreports

00:15:02 - Clicking on on-demand subreport link

00:15:08 - Formatting new on-demand subreport

00:16:29 - Examples of other on-demand subreports and testing our subreport

00:17:30 - Recap of lecture

00:18:23 - Limitations of subreports and example

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Working with layouts for different marketing documents and forms in SAP. Reasons to use the default SAP layouts and how to work with SAP token system for making your own layouts.

Index:

00:01:01 - Discussion of SAP Business One's default layouts

00:01:47 - PLD converted layouts

00:03:47 - Start of demo, review of documents in SBO

00:05:10 - Review of default Crystal Reports Sales Quote

00:07:20 - My modified default Sales Quote layout

00:08:25 - How to edit a layout directly from SBO

00:10:48 - Previewing a layout and finding the DocKey and ObjectID with my easy query

00:12:57 - Finding the DocKey and ObjectID manually

00:18:17 - Review and editing default layout

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Working with layouts for different marketing documents and forms in SAP. Reasons to use the default SAP layouts and how to work with SAP token system for making your own layouts.

Index:

00:21:46 - A note about editing default layouts, "Non-Destructive Editing"

00:31:12 - My address fix for layouts

00:35:20 - Saving a new layout back to the database and "Save As" warning

00:35:50 - SAP Add-In and where to find it

00:36:32 - Why you should upgrade to Crystal Reports 2011

00:37:40 - I finally save back to the database

00:38:31 - How to find a temp file you accidently saved but didn't save to the database

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Working with layouts for different marketing documents and forms in SAP. Reasons to use the default SAP layouts and how to work with SAP token system for making your own layouts.

Index:

00:40:14 - Making a layout from scratch

00:41:20 - Finding the basic tables for our new service call layout

00:42:11 - Overview of tokens in the Working with CR 9.0 guide

00:43:20 - Making your DocKey@ and ObjectId@ token parameters

00:45:51 - Record selection using our tokens

00:46:45 - Manually importing the new layout into SBO

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Expansion on the token system in SBO. How to use tokens to run SQL queries for parameters and to pull information for custom document layouts.

Index:

00:00:11 - What is a token?

00:03:09 - Start of demo

00:04:25 - How to Work with Crystal Reports SAP guide

00:05:22 - Making a query for use with a token

00:07:23 - Adding query to parameter token in Crystal Reports

00:10:30 - Exploring other types of tokens

00:14:07 - Referencing tokens in other tokens to make dynamic cascading parameters

00:16:06 - Using tokens for selection criteria

00:17:20 - Tokens for making layouts

00:19:29 - Example token and preview in SBO

Bonus lecture with more detail on SQL views. Description of what they are, why they are useful and an example you can download and use today.

**REMEMBER TO DOWNLOAD THE FILES FROM THIS LECTURE**

New token I found to be very useful.

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Overview of using CR on the SBO mobile app.

Index:

00:01:36 - Limitations of mobile Crystal Reports

00:02:26 - Start of demo and setup of mobile size reports

00:03:31 - Discussion of general uses of mobile reports

00:05:41 - Importing a mobile report

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Deploying your Crystal Reports reports and layouts. Importing, exporting, managing and printing from SBO.

Index:

00:01:52 - Starting with Report and Layout Manager

00:08:16 - Doing an import directly from Crystal Reports

00:09:39 - Where to get the CR Add-In installation and some notes

00:11:45 - Get rid of CR 2008 immediately

00:12:33 - Saving manually with CR add-in from Crystal Reports Designer

00:12:55 - Best practices on report naming conventions, backups and versioning

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

Deploying your Crystal Reports reports and layouts. Importing, exporting, managing and printing from SBO.

Index:

00:18:14 - Finding your report after importing and more work with Report and Layout Manager

00:20:23 - Importing Crystal Reports from SAP Business One

00:21:50 - Modifying menu location and making your own menu folders

00:22:55 - Importing a layout and finding unique form key

00:26:31 - Exporting a package of layouts / reports from SBO

00:28:35 - Printing and print sequences

00:29:43 - Print sequencing

00:31:54 - Recap of lecture and topics

00:36:16 - Troubleshooting Question: Removing custom folders from menu structure

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

User control, authorizations and best practices for deployment.

Index:

00:00:52 - Start of demo

00:01:29 - Best practices for deploying a report

00:02:57 - User authorizations for reports

00:05:32 - User defaults for layouts

After taking my course you should have all the skills needed to create your own reports and layouts from scratch. If you have any questions you can post them on the lecture or message me directly. Thank you for purchasing and good luck!!

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

If you are out of practice and want a place to start, this course will get you back into the swing of things with a lightning speed review of basic concepts.

Index:

00:01:42 - Start of refresher

00:02:37 - History and overview of Crystal Reports

00:06:30 - Starting a report. Build a PLAN for your report

00:07:14 - Finding information in SBO, data structure and database expert

00:10:10 - Reference codes and linking tables

00:12:52 - Using the database reference (included in additional resources)

00:14:43 - Adding a new connection

00:15:38 - Linking tables in the Database Expert

00:18:45 - Turn OFF automatic dumb linking

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

If you are out of practice and want a place to start, this course will get you back into the swing of things with a lightning speed review of basic concepts.

Index:

00:19:14 - Previewing a report in Crystal Reports using my DocKey and ObjectId finder query

00:22:30 - Object ID reference list

00:23:26 - Sections and Section Expert

00:24:07 - Warning about conditional supressing and report totals

00:25:09 - Overview of grouping

00:27:18 - Stacking, database fields and sections

00:28:49 - Field explorer and formatting

00:32:28 - Parameters, supression and record selection

**TURN ON HD IN VIDEO PLAYER FOR BETTER QUALITY**

If you are out of practice and want a place to start, this course will get you back into the swing of things with a lightning speed review of basic concepts.

Index:

00:37:01 - Layout specific parameters

00:37:39 - Editing reports & layouts, importing them and setting them as default

00:40:07 - User authorizations

00:41:50 - Editing reports & layouts out of the database

00:44:04 - Overview of additional resources

All files used in the various lectures for download

If you don't know, I have an SAP Business One YouTube Channel as well.  Check out some cool videos at http://youtube.battleshipcobra.com/

This video is an introduction to SQL Queries.  I'm designing a course on this topic, but many people have expressed an interest in getting better at SAP Business One SQL Queries.  Well this video is a start.