Crystal Reports Tutorial from zero to hero. Full Crystal Reports course in one lesson

lesson 1 crystal reports introduction part 1 creating database [Music] do create database statement creates a database with the car transaction's name the use keyword is used to select a [Music] database [Music] [Music] creating categories table [Music] [Music] we created a table called categories that contains two columns category identifier and category name category identifier is a primary key [Music] creating countries table [Music] we created a table called countries that contains two columns country identifier and country name country identifier is a primary key [Music] creating cities table [Music] [Music] we created a table called cities that contains three columns city identifier country identifier and city name city identifier is a primary key country identifier is a foreign key creating customers table [Music] do we created a table called customers that contains four columns customers identifier city identifier name and surname of customer city identifier is a foreign key creating salesman table [Music] we created a table called salesman that contains five columns salesman identifier name surname date of employment and boss identifier [Music] creating company table [Music] so [Music] we created a table called company that contains three columns company identifier city identifier and company name company identifier is a primary key city identifier is a foreign key creating cars table [Music] [Music] [Music] we created a table called cars that contains five columns car identifier category identifier company identifier car name and model name creating car transactions table [Music] we created a facts table called transactions facts table that contains three identifiers car identifier customer identifier salesman identifier three measures price amount value and column called sales date part two insert into data into tables inserting data into categories table [Music] inserting data into countries table [Music] inserting data into cities table [Music] [Music] inserting data into company table [Music] [Music] inserting data into cars table [Music] [Music] do [Music] inserting data into salesman table [Music] inserting data into customers table [Music] [Music] [Music] [Music] [Music] inserting data into transactions facts table [Music] [Music] do [Music] [Music] part 3 creating a user [Music] in object explorer right-click the logins folder and select new login type login name select sql server authentication option type and confirm password [Music] select default database click server roles select the appropriate role and click ok part 4 creating a first report open crystal reports in the file menu click new and then click standard report click the plus sign next to the create new connection click the plus sign next to the olay db [Music] select microsoft oladb provider for sql server and click next choose server type user name and password then choose car transactions database and click next [Music] click finish click the plus sign next to the car transactions click plus sign next to that bow then click sign next to the tables select car table and click greater than button click next [Music] select car and model columns click greater than button and then click next click next twice and then click [Music] finish [Music] [Music] double-click title and change names format objects using bold emphasis and delete an underline align text to the center [Music] drag print date object to the report header section [Music] insert box object right click box object and select format box select color check box choose any color and click ok [Music] [Music] double-click model label and correct the title click line object and draw three vertical lines and then draw a horizontal line [Music] [Music] [Music] [Music] [Music] insert text object and type the title of the report select first report text and change a few properties [Music] click preview tab first report is ready [Music] [Music] part five creating aodbc connection open control panel click start and then click control panel double click the administrative tools icon double click the data sources icon click system dsn tab and then click add button select sql server native client and click finish type name description select server name and click next [Music] [Music] select with sql server authentication option type username and password and click [Music] okay select car transactions database and click ok click finish and then click test data source click ok three times [Music] part six creating a second report in the file menu click new and then click standard report click the plus sign next to the create new connection click the plus sign next to the odbc select sqlodbc and click next type user name and password and click finish click the plus sign next to the car transactions click plus sign next to the bow then click sign next to the tables select car table and click greater than button click next select car and model columns click greater than button and then click next click next twice and then click finish [Music] [Music] build a report like the previous [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] save the report do you want to learn new skills in the fastest and most effective way visit learnwithvideotutorials.com lesson two sorting and grouping data [Music] part one creating a view open sql server management studio and create the view [Music] execute the query [Music] [Music] part two creating a report [Music] launch crystal reports from your computer's start menu click file then click new and select standard report in the available data sources pane click the plus sign to the left of the sql odbc type password and click ok click the plus sign to the left of the car transactions click the plus sign to the left of the debo and then click the plus sign to the left of the views add the v transactions view to the selected tables pane and click next add all fields to the fields to display pane and click next click next twice and then click finish switch to design view the spacing of the columns isn't the greatest but you can adjust them manually and do some other formatting press and hold down the shift key and select all fields [Music] you can change the format of the sales date field to eliminate the time information click preview tab to display report [Music] press and hold down the shift key and select all fields right click sales date field and click align and then click tops [Music] [Music] [Music] [Music] [Music] one way to make titles stand out is to enclose them in boxes [Music] [Music] draw a few vertical lines [Music] [Music] [Music] [Music] [Music] draw one horizontal line [Music] [Music] [Music] [Music] [Music] part three sorting report data [Music] click the record sort expert button in the available fields pane select surname and click the greater than button do the same for car field make sure that the sort direction is ascending and then click ok the reports records are sorted first by surname then by car [Music] part 4 grouping related items [Music] click the group expert button in the available fields pane select surname and click the greater than button then click okay [Music] insert box object part 5. inserting summary click the insert summary button summarize the price field use the sum summary operator and choose group 1 for the summary location do the same for amount field and then do the same for value field [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] summarize the price field use the sum summary operator and choose grant total for the summary [Music] location [Music] do the same for amount field and then do the same for value field [Music] [Music] [Music] part 6 inserting minimum maximum and average click the insert summary button summarize the value field use the maximum summary operator and choose group 1 for the summary location [Music] do the same for minimum and average [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] do you want to learn new skills in the fastest and most effective way visit learnwithvideotutorials.com learn with videotutorials.com interesting video tutorials free lessons competitive prices lesson three using formulas and parameters to retrieve data part one using formulas to retrieve data [Music] open first report click select expert and choose record option select car and click ok in the pull down list below the filter fields pane select is equal to and from the pull down list that pops up below it select bmw and click ok [Music] click refresh data [Music] you can create a second formula click new tab and create formula select v transactions model tab and click delete select is one of option and add a few cars click ok and then click refresh data [Music] delete formula part 2. using parameter fields to retrieve data at runtime [Music] if you can't see field explorer on the right edge of the screen choose view and then click field explorer right click the parameter fields option and choose the new enter a name for the parameter field and select the data type of the entry click ok click select expert and choose record option select car and click ok in the pull down list below the filter fields pane select is equal to and from the pull down list that pops up below it select car name and click ok type car name and click ok and then click refresh data press f5 or click refresh report data option choose prompt for new parameter values and click ok type car name click ok and then click refresh data part three using the parameters in the title of the report insert text object and type sales do drag car name and drop on title field format title object [Music] press f5 or click refresh report data option choose prompt for new parameter values and click ok type car name and click ok [Music] [Music] [Music] part 4 creating multiple values parameters right-click car name and select edit go to the allow multiple values and select true from settings drop down list and then click ok now you can type a few car names [Music] do you want to learn new skills in the fastest and most effective way visit learn with video tutorials com lesson 4 using subreports part 1 creating a view [Music] open sql server management studio and create the view [Music] execute the query [Music] part 2 creating report and subripot [Music] click file then click new and select standard report in the available data sources pane click the plus sign to the left of the sqlodbc type password and click ok click the plus sign to the left of the car transactions click the plus sign to the left of the debo and then click the plus sign to the left of the views add the vcars view to the selected tables pane and click next add all fields to the fields to display pane and click next click next twice and then click finish switch to design view format the report crystal reports features the can grow formatting option that allows a text object to grow vertically if necessary to show its entire contents press and hold down the shift key and select all titles [Music] [Music] insert box object draw a few vertical lines you can use the copy and paste to create new lines [Music] draw two horizontal lines [Music] [Music] [Music] [Music] [Music] on the insert tools toolbar click the insert subripot icon type subripot name and click report wizard button click the plus sign to the left of the car transactions click the plus sign to the left of the debo and then click the plus sign to the left of the views add the v transactions view to the selected tables pane and click next add a few fields to the fields to display pane and click finish then click okay drag the placement frame that appears at the cursor location into the details double click sub repot format the sub repot [Music] [Music] [Music] [Music] [Music] [Music] part 3 linking subrepot format the subreport [Music] right click the sub report and select change subripot links select car field in the available fields pane and click greater than button choose car field from drop down list do the same for the model field and click ok click preview tab and then click refresh data [Music] [Music] [Music] right click the report header section and click suppress to hide this section you can hide the other sections [Music] [Music] [Music] [Music] [Music] [Music] part 4 creating unlinked sub-reports click file then click new and select blank report click cancel hide report header page header report footer and page footer on the insert tools toolbar click the insert sub report icon type subripot name and click report wizard button click the plus sign to the left of the car transactions type password and click ok click the plus sign to the left of the bow and then click the plus sign to the left of the views add the v cars view to the selected tables pane and click next add a few fields to the fields to display pane and click finish then click ok drag the placement frame that appears at the cursor location into the details then double click subropot format the [Music] [Music] choose file and then click page setup from the drop down menus change default orientation of report pages click landscape radio button and then click ok [Music] [Music] create second subripot [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] an unlinked subrepot can be thought of as a completely separate report [Music] [Music] do you want to learn new skills in the fastest and most effective way visit learnwithvideotutorials.com lesson five using blank report and using templates part one using blank report click file then click new and select blank report in the available data sources pane click the plus sign to the left of the sqlodbc type password and click ok add the vcars view to the selected tables pane and click ok in field explorer click the plus sign to the left of database fields click the plus sign to the left of v cars to display its fields click the first field that you want to include in the report and then drag it to the left edge of the details section repeat previous step for any other fields that you want to include in the report [Music] [Music] click view menu and then click print preview to display report [Music] part two using templates click file then click new and select standard report in the available data sources pane click the plus sign to the left of the sql odbc type password and click ok add the v cars view to the selected tables pane and click next add all fields to the fields to display pane and click next click next twice in this dialog you can choose from several templates select the block blue template and click finish do do do you want to learn new skills in the fastest and most effective way visit learnwithvideotutorials.com lesson six using charts and maps part one creating a report with chart [Music] click file then click new and select standard report in the available data sources pane click the plus sign to the left of the sql odbc type password and click ok add the v transactions view to the selected tables pane and click next add a few fields to the fields to display pane and click next [Music] click next twice and then click finish switch to design view format the report [Music] [Music] so [Music] [Music] [Music] [Music] [Music] [Music] [Music] click the chart expert icon add the surname field to the on change of pane add value fields to the show values pane and click okay [Music] part 2 creating a view open sql server management studio and create the view [Music] part three adding the map create a standard report [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] so [Music] [Music] click the insert map icon drag country from the available fields pane to the geographic field box drag value field from the available fields pane to the map values pane and click okay part 4 using the map [Music] [Music] [Music] do you want to learn new skills in the fastest and most effective way visit learn with video tutorials com [Music] lesson 7. creating crosstab report part one creating first crosstab report [Music] click file then click new and select crosstab report in the available data sources pane click the plus sign to the left of the sql odbc type password and click ok click the plus sign to the left of the car transactions click the plus sign to the left of the bow and then click the plus sign to the left of the views add the v transactions view to the selected tables pane and click next in the available fields pane select surname and click the right facing arrow to the left of the columns pane select car and click the right facing arrow to the left of the rows pane then drag model to the rows pane drag price amount and value to the summary fields pane click next three times [Music] choose the grid style and click finish [Music] part two formatting a crosstab report [Music] [Music] [Music] [Music] do part three changing the appearance of crosstab report you can change the appearance of the [Music] report [Music] do you want to learn new skills in the fastest and most effective way visit learn with video tutorials com lesson 8 adding formulas to reports part 1 creating report with formula [Music] create the standard report [Music] [Music] [Music] [Music] do delete the car field and then delete the model field right click the formula fields and choose new type formula name and click ok expand the sql odbc and then expand the cars drag car and model field down to the formula entry area to build up a custom formula [Music] this formula concatenates the car field with a blank space and then concatenates the result with the model field click check button then click save button and close window [Music] drag the car and the model from the formula fields node of the field explorer to the details section format the report [Music] do do part 2 creating a custom function create the standard report delete fields surname name car and model format the report [Music] [Music] [Music] right click formula fields and click new type formula name and click ok right click the report custom functions branch on the workshop tree and choose new type function name and click the use editor the two parameters x and y represent the two names you want to concatenate both are declared as string variables click the check button and then click the save button click the concat with space formula build expression with concat [Music] function click the check button and then click the save and close button drag the can cart with space from the formula fields node of the field explorer to the details section [Music] part 3 using a standard functions create the second formula build the formula with a standard function like uppercase and lowercase click the check button and then click the save and close button drag the concat 2 from the formula fields node of the field explorer to the details section format the field do part 4 using a formatting formula and if expression double-click the formula expand the formatting formulas node in the workshop tree expand the details right click the price and choose new formatting formula [Music] select font color and then click the use editor button [Music] type the expression the if then control structure is useful when you want to do one thing if a condition is true click the check button and then click the save and close button part 5 if then else expression the if then else control structure is useful when you want to do one thing if a condition is true and another thing if the condition is false part 6 select case expression [Music] use the select case control structure when there are more than two alternatives to choose from and you want to do a different thing in each case part 7 for loop expression create the new formula the for loop is the best tool to use when you want to execute a section of code a predetermined number of times suppose you have a character field named car in a view named v cars and you want to know how many instances of the letter l it contains drag the loop function from the formula fields node of the field explorer to the details section [Music] [Music] [Music] [Music] part 8 creating the running totals fields [Music] right-click the running total fields and click the new in the running total name box replace the default name with price 300. in the available tables and fields pane select price from the report fields area then click the arrow button pointing to the field to summarize box select the count options click the user formula and type the formula click the save and close button and then click ok drag the price 300 from the running total fields node of the field explorer to the report footer section [Music] so do you want to learn new skills in the fastest and most effective way visit learnwithvideotutorials.com lesson 9 changing to a different table and using a crystal viewer part 1 viewing the sql query and changing to a different table because crystal reports works with sql databases it must eventually translate the tables fields links sorting and grouping that you've used to design your report into sql you can view the sql statements crystal reports creates by choosing database and then choose show sql query from the pull down menus choose database and then click set data source location from the pull down menus which will display the set data source dialog box if you wish to replace a single table with a new table select the particular table you wish to replace in the current data source list then select the new table and click update button part 2 exporting to different file formats to export a file choose file click export and then click export report you initially have two simple choices to make for your export the file format of the export and the destination of the export [Music] part 3 installing a crystal viewer download the crystal viewer double click cr viewer x choose language and click ok click next choose language pack and click next twice [Music] click finish to finish installation part 4 using a crystal viewer open crystal viewer and then open any report [Music] to export a file choose file then click export current section and choose the file format open another report and export to another format do you want to learn new skills in the fastest and most effective way visit learn with video tutorials com

Channel: Learn with video tutorials

Views: 9,306

Keywords: crystal reports, crystal reports for beginners, learn crystal reports, crystal reports introduction

Id: A8lK4P1xnaM

Length: 134min 19sec (8059 seconds)

Published: Mon Apr 26 2021