Crystal Reports - An Introduction | Phillip Burton | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Crystal Reports - An Introduction

teacher avatar Phillip Burton

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

50 Lessons (5h 40m)
    • 1. Hello

    • 2. Curriculum

    • 3. Installing Crystal Reports

    • 4. Running Crystal Reports, and adding a Data Source

    • 5. Creating our first report

    • 6. Altering our report, and saving it.

    • 7. Opening and saving, and updating data source location

    • 8. Data sources - in detail

    • 9. Creating a report from scratch - adding fields

    • 10. Sorting results

    • 11. Grouping

    • 12. Manage groups

    • 13. Filtering (or Select Expert)

    • 14. Exercise - Creating a detailed, filtered, sorted, grouped report

    • 15. Formatting dialog box

    • 16. Formatting - colors, borders and fonts

    • 17. Common formatting, including suppress

    • 18. Formatting text (and boolean fields)

    • 19. Formatting numbers

    • 20. Formatting dates and times

    • 21. Adding objects

    • 22. Formatting graphics

    • 23. Aligning and resizing objects

    • 24. Adding additional sections

    • 25. Formatting sections

    • 26. The problem

    • 27. The solution - Introducing the parameter

    • 28. Parameter Options

    • 29. Text parameters

    • 30. Let's insert a chart

    • 31. Chart Options

    • 32. Different types of charts 1

    • 33. Different Types of Charts 2

    • 34. Context of charts

    • 35. Subreports - an introduction

    • 36. Drill down

    • 37. Designing a subreport using a Cross-tab

    • 38. Creating linked subreports

    • 39. Creating formulas

    • 40. Special fields

    • 41. If...

    • 42. Boolean logic and other Control Structures

    • 43. Text functions

    • 44. Mathematical functions

    • 45. Summary functions

    • 46. Date formulas

    • 47. Solving our subreport problem

    • 48. Using parameters for conditional formatting

    • 49. Advanced Filtering

    • 50. Congratulations

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Hello, and welcome to Crystal Reports 2008-2016 - An Introduction.

In this course we'll be looking at SAP Crystal Reports, and how to use it.

This course starts right at the beginning, and so is good for absolute beginners, but would also be useful for you if you have previously used it, perhaps viewing reports or making basic reports, and would like to improve your skills.

You don't need to have Crystal Reports on your computer, as first of all we will install a trial version of Crystal Reports for free, so that you can use it on your computer free for 30 days. This way, you can practice with your new skills, experiment with your own data and create your own reports.

This course is separated into 7 levels, each getting progressively more deeper into Crystal Reports. They are:

  1. Creating your first report,
  2. Adding grouping, sorting and filtering, also known as Selecting.
  3. Formatting,
  4. Adding user interaction through parameters,
  5. Graphs and charts,
  6. Subreports, and
  7. Formulas.

All of this will be taken at a fairly leisurely pace, so you can really get familiar with the menus and icons.

By the end, you will be able to develop your own reports, and start to explore other people's. And there's a Certificate of Completion available if you complete the course.

Meet Your Teacher

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and con... See full profile

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Hello: Hello and welcome to your crystal reports An introduction. My name's really person from far cat stuck, Or did you? Can in this coast will be looking at SNP Crystal Reports 2000 and 8 to 2016 Now to use it. This girl starts at the absolute beginning, and so is good for absolute beginners, but would also be good for you if you previously used it. Perhaps viewing reports or making basic reports. I would like to improve your skills. Now you don't actually need to how Crystal reports it's told in your computer, as first of all will install a trial version of crystal reports for free so you can use it on your computer for 30 days. This way you can practice your newfound skills, experiment with your own data and create your own reports. Now this causes separated into seven levels, each getting progressively more deeper into crystal reports. And they are correcting your first report, adding grouping, sorting and filtering, also known as selecting formatting. I didn user interaction through parameters, graphs and charts, said reports on formulas. Now all of this will be taken at a fairly leisurely pace, so you can really get familiar with the menus of that cons. By the end, you'll be able to develop your own reports and start to explore other people's on this, a certificate of completion available if you complete the costs. So what if I ever do? Let's see in detail what will be Ladic. 2. Curriculum: Hello under. Welcome to this Lettuces festival. Can you see this quite clearly? This costs waas recorded in high definition, which is 12 80 times 7 20 Now, if you can't see clearly or if you're not a little bit fuzzy, then there may be HD percent down on the right and corner. You might be a very faint or if you're looking at it in one of the new browsers, perhaps there might be a wheel Onda from that you may be seeing the quality is 360 p. If it is, could you protect it up to 720 p? That means you'll be able to see it crystal clear. Well, thank you. Let's start the cost. Right. Well, thank you for joining me in this culture are crystal reports. Right on. Thank you for joining me on this report. Right. And thank you for joining me on this course about crystal reports. So what we're going to do in this car? Well, there is an exam called C B lcr the team, which is by SFPD, the makers of crystal reports. And it says what they want you to know there's a free how Exam on their 80 questions. Andi. I thought it would be really good if we could cover a lot off things that on this course. So why don't we cover everything that's on the exam? Well, there's a huge amount aimed if you have a look you need to have. For instance, for this former Karesh, you need to have attended two courses to do that. So course PLC free 10 for example, The fundament fundamentals Off Report design. It's about $2000 and last two days. This course is not going to last two days. You'll be pleased to hear, so we won't be able to do absolutely everything that's on here. Our what I've done is I have tailored this so that I've gone. These are the things that I would want and everybody to know. So festival basic with procreation will be drink. I think a lot of that advanced reporting tightenings will be doing some of that formula creation. We'll be doing with us as well. Report customization. Onda formatting definitely report processing techniques. We won't be doing that, but there will be there isn't actually demo sets really of use. It's more of an academic think as far as I'm concerned, until you get to the more advanced stages off crystal reports parameter usage. Definitely. Andi Silver reports. We'll be doing some of us as well. So we're doing around half to 60% of what's here, So let's see what we've got. Well, I thought it might be good if we do it in a sort of level based process. So we start off really easy and level on is self contained. It's not reliant on you actually going on to level two level for you will get benefit if all you do is level one. But the more levels you do, the more confident shoot you will have on the more you will be able to do so, we start off with just your first steps there will dive in big, deeper. So first step is installing a free trial off crystal reports on your computer. So you that you could try for 30 days and will create a report using the what I call a wizard they're calling. Something else is you'll find out and will modify the report, save it and close it. So that's how very first step is our first report. Well, then, diving deeper will create a report by ourselves, will have grouping glad filters without sorting, and there will do an exercise that brings all of that together. Then we'll have a look at formatting, and that's an awful lot do for watching. By then, we have created to report that, quite frankly, looks know very nice, not very tidy, so not a formatting could weaken. Do, don't have look at the parameters will add some user interaction. So you created this report for overs to use. Let them use it as opposed to you. Just giving them the same report for everybody. With lots of graphs always useful to summarize. Day turned to see insights into lots of numbers. We look at suburb parts we look at to drill down and drill through reports on. Then we'll have look at formulas Onda, and you will see that throughout this course there'll be hints of what formulas can do. But then formulas will actually look at their building blocks and do so examples as to what formulas can actually do. So the start of the section will have a little list as to what we're going to do so. Each level will get progressively more and more challenging, but they will build on the previous level so you won't get lost. And if you do just, we want. That's the great thing about a video course, so let's start with Level one. 3. Installing Crystal Reports: right. The first thing we need to do is to download and install crystal reports. So if you open up, your favorite is in that browser Onda Research for crystal reports. I don't. Right. So we see questionable things, including W W to be dot crystal reports stopped called us UK in the British version. That's not actually why we want to go. Instead, we want to go to s a p dot com and you can see, browse and download free trials of S A P software for small and mid sized companies and crystal reports is by S a p. So let's click on that. So I would recommend going to the S a piece. I did not going to any of the others. You might try to get you additional advertising girl off the stuff on. The very first thing we've got is the S a p of business crystal reports free trial so you'll see further down. That's if you're developer. We've got developer editions free download on. We also got a viewer that's handy if you've got your trial has expired or you don't want to see the trunk in the first place. But you want to actually run a report, so its case on it. So we get this RAV a big website, but you don't actually need all of this. Instead, let's go back and click on free trial. Now let's see where we go. So I notice you are well at the top on. This is much better. So you have to register. You can see you can download SFP crystal reports. So I will put my details in here. No, I don't want to trap to N S AP representative. I am not going to take Please have a s a p sales representative. Contact me by phone, but I do need to take I have read the privacy statement it. Of course. You can actually read the privacy statement if you sell Wish. It's quite, um Thora. So I'll just talk my information and then I'll click These took me, but Alright, so I've done all that. I regret to say that this telephone numbers actually not existent, as you might guess from it. So let's click. Submit on. You can see it's thinking about something until you go. Thank you for downloading ASAP. Crystal reports your key cold is content in your bike. Um, email. Andi will be a quiet for installation activation. So now let's click. Install crystal reports 2013 on There we go. So depending on the browser you got, it's now I've downloading God's going to ask you to download so you can see I've got very quick Internet just three minutes. So you should also take advantage of this opportunity off checking your email. This is the email I've got. You can see that the computer thinks it's a bit suspicious, but it isn't so quick in a below. Andi, if I scroll down there is the installation key, so you will need that to activating. Stole your evaluation. Self lashing that the be the around, Don't you install it first and then activated. So let's go back and see how the downloads. Right so you can now see it's finished downloading. It's got a lovely found a 51050534 So let's open it. You may have got the option to save all the things, so whatever you do that's open it now. Okay, this doesn't look very promising. There's no e x e found that we can execute. There's nothing says Run this. So what's to It's a bit unusual is going to data units, the foul folder and then within that go into crystal reports on there. We will see this set up. They exceed that. We need to run. Specify an unusual set up to do, but never mind. So there is a set up. If I run it, you can see that know very little has happened. The computers were rowing like Mad Fan is going on enormously So what to do instead? Because obviously, this is not how you do it. Instead we go. Do you want allow this app to make changes to your PC? Yes. If you're having problems like I clearly have, one of the things that I would do is writing. Click on your zip there 4. Running Crystal Reports, and adding a Data Source: right. So now we're going to open crystal reports for the first time. So go to your start menu, go to if you're using Windows 10 or greater. All lapse of using an early version. All programs are just programs and scroll down all the way to the letter s. So we have s a P business intelligence and inside, that s a P. Crystal reports 2013. So let's kick on that. So you can see it's in a evaluation copy which will expand and 30 days Andi. There you can see everything, including verbal small text on my computer. So on my computer just had to reboot to get it looking good so we can see that we've got various options off which, quite frankly, there's only two. Start a new report and start from scratch or usual visit. You can also see that we can convert on trial for only $495. Um, that's it. On the all sorts of two bars appear she could have her over and find out what they do, but none of them I enabled. So let's start with a new report. So file new on not is that these are basically the same options you've got down here just in a slightly different order. So we'll start off if a standard report. So I think that you can see that this is the report wizard. So if I close us and open it up, you can see exactly the same thing. So the first thing that Christa report needs is data to report on Crystal reports is a report presenter. But to present a report, it needs data that goes behind the report. So we have two options. Create my connections and you can see I've got no connections whatsoever on. That's not surprising because I just installed it or I can create a new connection. So if lexicon that you can see the old manner off connection, So just in case you it's not clear, what do you mean by connection? I mean that Crystal reports itself does not store data itself. You don't create data in crystal reports requited, Xlu quoted an accessory created in SQL server in over ASAP products. You can write it in a lot of things, but not crystal report itself. So it needs to say hello to whatever is holding the data. So that's it could correct the reports. And what they've done in the more modern versions of Christa report is there put what they consider to be the most often used data sources at the top. All the rest they have put into more data, sources of the bottom. And you can see there are awful lots of, um, generally, you will only be working with one or two data sources. So once you've worked out what data sources you are working with, those are the ones we're probably gonna be sticking with now for me, we're going to be looking at Mac Soft access and Excel. So let's expand that, Andi here, you can see the first thing it wants is how am I talking about Microsoft access, or am I talking about Microsoft Excel? Now, if the numbers confuse you, wasn't Earth is Excel free 458 Don't be that confused. If you've got a modern version of Excel, you're going to be using Version eight. However, for our demonstration, we're going to be using an access database so you can click on this box to bring up all of the databases which are available so all of the data file so it couldn't says data files. We could change that. Just Mac soft access databases. Now, in older versions are crystal reports. I'm talking going up to 2000 and eight there waas a database that came with crystal reports that he could choose. But since that version, certainly for the more modern versions 11 4013 that database is now actually available online, so we got to find it. So we're just gonna polls this there and now we're going to find our source data. So let's open up. How favorites Internet browser on typing. And it's called Extremes as extreme Without letter here, the front extreme database for crystal reports on we start off with this Microsoft one. Ignore that completely. It is unhelpful. He tells you where it used to be on graphically I know where it used to be. So if it's cool down Sample database for crystal reports 2013 and you can see this is on the S, a p website. So click on that. It's so we've got this one person saying please let me know where to find the extreme mountain bike sample database report database itself, not including this trial version. Andi, you could see that. The answer is it's here so you can see what I have to find it. I can't really tell you 80 her because that's a really long here, and it might mean so let's kick on that on there. You can see it downloading, so I'll open it up, which is so we have some database. We also have some reports in there. So what we're going to do is we're going to extract this matter soft access database. So I'm going to copy it. Andi, I'll put it. That may be put it into my documents or not just cold documents. So we've been documents. I've created a folder called ASAP. Crystal reports on going to test it. So now we've got it. Let's go back into Chris reports. Find that folder which you can do but using goal of this lot. So there's my folder sfp crystal reports, and there's Extreme Docked NDB on, and I'm going to open it. So now I've got extreme dot mdb up there as the database I'm using Microsoft Access Click Finish, and you can now see that it's made the connection. So we have A S A P crystal reports extreme on we have It is things called tables, views and stored procedures. 5. Creating our first report: in the previous lecture. We answer the question. Where is the data? The data is in the extreme. In this lecture. We need to answer the question. What? What do you want to make a report about? Obviously, this is a bit difficult if you don't actually know what's data is contained in the extreme database. So let's have a look at the tables will expand the tables. We could see that we've got lots of financial stuff accounts, bills, customers, inventory, aunt employees. So let's have a look at the employees. So this is a date here table we want of Look at. So bring this across. So the selector table employees. So click next. And now it's saying off all of the tables that you selected in this case, just one. What do you actually want? Visible? Well, that's put in employee I d. Last name business home position, so that makes a good start to report. So let's click next and all the rest. I think we're going to bypass for now. Grouping class bypass record selection. That's food drink. That's bypass. No template. This just shows what sort off report we want to look at in terms of colors. End of such stuff. Andi haven't said no template. So let's finish on there. We have our report. Specifically, we have a preview of it. We can see that there are 15 records. Each record is an Employees are 15 in place when we have all of these last names. All of these first names Andi thes air the positions and we have the date in disguise in my British former. But it could be in your American for matter, your Japanese format or whatever date your four matches in hund There we have created our first report, so it can be a simple as that a simple saying file new standard report Select whatever you want. It's like whatever you want again, I'm going to take whole free. He feels across click, click and click on finish. I don't even have to go to the end, but you see some of these reports get a bit untidy. So let's close this report to concentrate on this report one and will not see how we can modify it in the next lecture 6. Altering our report, and saving it.: right in this final edge with this section or going to do is on another field to this report, and we're going to say the report in close crystal reports. So let's just examine where we are right now. We've done the where where is the data from? So what's the data cells we've done the what? What information do you want? The report. And now we're modifying the what. But first of all, what are you looking at? We're looking at the actual report. Natural reporters, 15 employees. What we're looking at is cold. A preview now in over reporting programs. This might be called running the report here. It's called a preview. Now, the best way to add on additional report additional field that's an additional piece of data is to move from the preview to the design mold. Celestri that by Cliff your design. So this is the design off the report. We have employee I D. Last name first name, one position on. We want to add another field. And to do that, we can go to view and field Explorer now, have a look of the icon. You'll see that the icon is replicated in the two balls, so you can choose which everyone you want so once. If you're not used to the icons, then going to the menus when you get used the account you might find dish using the icons on lot easier. So kick Field Explorer and have a look at the right hand side. We now have a field Explorer, so we've got a database fields and the last of the things that we'll have a look at later on, this course. So let's expand database feels. And inside we have the various tables are looking at in this case, just inside that we have all of the fields on notice. The icons, some of them are Chittick or checked. And it was They are being used so you can see that ticked off checked our employee i d. Last name, first name and position. Now, suppose we wanted to add another. We wanted to add Hi a date. It's very easy. We just drag it to the details section. So, like this, I'm literally just tracking Beacon. See where he's going to go on then. Oh, lease. Notice what happens. Hi. Date. Not only is in the details, but is also in the page. Header. So we've now had to this extra field. Let's see what she looks like. So we moved from design to preview and that we can see was he looks like So now we're going to save the support. So we'll go to file, save us on going to name this report my first report and click save, and we go from report one, which means it's not being saved to my first report. So I got it in the tab, who got it in the top and now to close file close. Or you could just press the exc, but on the top on exit file exit. So now we're back to desktop. We have closed crystal reports. So in just these very short lectures we have, how did the data sauce? We have created our first report. We've altered our first report and we saved and closed the report so that, in a nutshell, is how to do your very basic reports. And now you might just want to pulls this course and try all of this for yourself. Create basic reports using the wizard at extra fields using view fields. The feud Explorer. Credit reports save them in close. And then when you comfortable that well, now look at in the next section, Crystal reports in a bit more debt. 7. Opening and saving, and updating data source location: right. I just want to recap while we are at the moment, we've just created our first report in crystal reports. So that's our first report under created it using the database extreme, which is in this folder. So Crystal reports is now closed. I wasn't going to do. He's correcting new falter. You call it database, and I'm going to move the database into that folder. So enough words I'm Britain. Link Crystal reports. When I opened this report, he's going to expect the report to be in this folder, not in the database folder. So let's see what happens now if I just double click on this or I can go to crystal reports and open the report so either way would look so if I go to crystal reports, going to all programs s a p business Intelligence crystal reports still reminding me it's an evaluation. Copy. I know I can click on this. My first report. Now do you see what's happened? So I have moved the data souls, and yet I'm still seeing the dictum. Now let me just bring home the security ramifications that suppose I, Philip person, create a report using data to which I have access it and then I save it onto a network folder. Something else Let's call in. Mark, who doesn't have access to this data, opens a report and sees all the data. So that is a security problem. Because Monk isn't meant to see, say said this is payroll not meant to see how much people are Think on the I mean to see that. So how is that possible? Well, let's click on file servers again, and then you concede the reason by default, save data report is checked his ticket. So when I go to save as it is saving the data in the report, you notice it's 48 kilobytes. So if I now save it as my festival too, But our own check This saved data we've called First Click Save us now. Now it has been saved under its new name without the data. So if I close you can see this. My report, my first report to fractionally some smaller because there are only 15 records in the entire data set. But just imagine if there's 100 1000 a 1,000,000 a lot of 1,000,000 would be saved. Wave the report. It's a major security risk. So do be aware that so hopeful for my first report from here. So this is the version that was saved. We've the data and you can see yet the data. Is that so? Now, if I go to open or I click on the file open and I open up my first report to which has not been saved data your notice. There is no preview tab. So they're the preview tap for my first port. There is no preview tub. No way that I can switch into the report. It's still holding the metadata that there are 15 records, but it can't tell me what any of them are. So that is good from a security perspective, it may be bad from a testings perspective. Say you are trying to settle Peru polled. We've dated that over the Internet and the computer have to spend time locating that process and get downloading it. If you're using it for testing purposes, then yes, you would want save data, report it. So how do we associate the new folder path off this data? With this report, we can go two database and sent data source location, so click that you can see it is promptly set for my s a p Crystal Reports folder, which is exactly why it wants. That's the current data cells. Now this is where I can replace with. So if I click update with us help on here, we can see we can update the highlighted connection. So if I click on the doctor dot which gets me to the found manager, I can think of data basically on extreme in its new location. Click Finish, Andi. Nothing happens. So it's close this box because that's not very useful, is it? It's good clothes. Open it up again, see if it's done Anything No, you can't see is done. Nothing. I can try updating it again. So database click open and you can see it has updated it to this new database. But it doesn't look like anything's happened, and there's no preview instead doesn't know preview. We just have to design, so that's not helpful. But look up here we have the new location s a P crystal reports database, extreme MDP. So really, all of this is really unhelpful. But you can see it seems to put So let's just say this. It's already saved, so don't need to. So let's close it. Open it up again, and still you cannot see it very unhelpful in the extreme. So what I suggested you instead is still use this set data source location but creating new connections. So here's our new connection, or you can make a new correction by going into there, and you can see that it knows all about this new connection. It's got all of the folders, so I'm going to click that click that click update that's now dated close and still not hopeful. If I go back in, you can see it's what. But nothing's happening. But fear not Just because the tab is not there doesn't actually mean that it hasn't worked . If you expand your these database fields, you will see the latest fields employing ideas. Supervisor itself off. What we have to do if the preview tab is missing is got a view onda print preview or preview samples. Let's click print preview and there we have our preview. If you could, um, a print preview or you could preview a sample which says, I just want the first and I'll say eight records. Remember the 15 year total and that will give you a detailed version. So in this lesson, we've learned Festival. That saving data will report is a big security risk, but it's useful for testing purposes. We've learned that this update sent data source location is not as easy on obvious to use as it might initially seem. And lastly, if you don't see the preview tablet, which we can do by getting rid of this X, for instance, we don't now and a lot of the preview tab you can see it by going to view print preview. So some of you may be wondering, Well, what would happen with this? My first report it's saved. The data cannot go into design and then back into preview. The answer is yes, because it's not updating the data. If I now save it as my first report three. We've helped saving the date of the report. So that's that's no letup. You can see there is no preview tab. What if I go to view print preview? What will happen? Remember, it doesn't actually know where the data sources on this is when you will not have predicted that surely he'd be going handled. It doesn't know what the day sources, but actually it does, because what we've done in this set data cells location is not just updated. Data sells for one report with updated it for all the revolts, so be very careful when using that, it will update for all the revolts. So let's move this into this database into another folder. Second day to total. Let's open up our first report, which, remember, has the data saved so I can go into design into preview because it's not actually trying to draw the data. So now I'll save it without the data. Has my first report for now, close that open up my first report full. It has no idea what the data is. So what is going to happen? Let's find out. So I hope you're enjoying your Irish. In addition on, there's no print preview click print preview Onda. We get into where digital database exactly as we've seen before, so that shows what happens if you move your database connection. The computer will recognize it, and we'll try to help you, but sometimes it's just not immediately obvious how it does so 8. Data sources - in detail: right. We're going to now create a brand new report, So going to go file new standard report. Andi, you can see that we currently have our connections that we previously set up. But I'm not going to use even those. And as I said in the previous like show, this is failing. Helpful. This is actually in the sub folder called Data Base. And yet this appear is no cold that so very unhelpful. So instead, I'm going to create a new connection now, before creating the new connection. I just want to briefly run down this list. Andi, don't be surprised if sort of thes make absolutely no sense to you. You I just need to listen out for the data sources that you have the programs that you've got installed in your computer. So, first of all, we've got Microsoft Access, Microsoft Excel, onda de A or that's data access. Objects on what this means is basically anything that can operate using data access object methods such as Lauder's paradox, as it's very rude. Text in html, Microsoft access the old D base. So if you use Max of products, all some very old databases or spreadsheets then d A or might be the one that you want and you can see that you could if that's so, you can log in with passwords and user names, but you will necessary. You will need that. I think it all for Mac Soft access or Microsoft Excel. So the next one is a dio dot net. Just expand that so you can have a look at the dialogue books. Now, this is a highly technical one. If you develop something using ideal dot net, then this is why you do it. But if you don't want it is relax. You will never need to use it. If you do know what it is they just put in your file. Perfect class name. Next, our database files. Now, you've already seen Mac Soft Excel on Microsoft Access on. Generally, I use excess index, so why it is one of those instead of using this. But you have a few more other things like XML files, which include XML. Why the schemer is given to you as well, Onda again. It includes some very old type files so not often used but mostly used for the XML. I think if you've got a Java source, and then you got your java beans collectivity Onda. As you can see, you need to provide a job of being class, which I don't have, so moving streaks swiftly on the next one looks like it stands for four words, but it actually stands for Free Java database connectivity. So again, if you've got a Java database or a J a N D I, which is Java naming and directory interface, then this is why you can provide your information and you'll see that all these dollar box is actually much of a muchness. Just type in where you getting your information from and type in your credentials or D B C . It's another way to access database, so debates Exelon Max optics access files. Andi, I don't know nowadays that this is most in use for it, but he start if you need it. What there is definitely modern use for is all up. So where or L. T. P stands for trans turned transactions or L. A. P stands for anonymous it. So I knew altogether wrong analytical on essentially what? Oh hell, AP is It's cubes. So if you've used cubes in S s a s, for example, the SQL Server Analytical Services. And they came They got it the wrong way around his girl server analysis services. Then this is where you can get your credentials for your or like, cubes Now oily to be You can see that this is all has been in the past quite a popular, um, provider on day One of the major things used oily db for is this one this allows you to connect to and or l t p version of SQL servers are generally something that you can using S S M s SQL Server Management studios on. As I said before door wife, they sound like Jargon city. If you don't actually have these products, you'll never need to use this next one. Actually to look up salesforce dot com That is a c r m I A customer relationship management system. So I only to be used if you ask yourselves false, not come. So you know you are the next four. Rely on ASAP products Enough Get tickles. We're talking about a s a p crystal reports. So it's not surprising that they have special her interfaces for their own products. The 1st 1 his S A, P, B, W and D X Cleary and DX is the equivalent of SQL in cubes. What's one of the equivalents? DX isn't of one. So it's to be used in Cuba that you've created in the business warehouse or in Business Explorer using the query designer. So if you are heavily into S A P, then this will get you into both the cue ball. You'd version the analysis version and also into the quote designer. The next one is SMP in four sets. These are Barcelona's functional areas. So that gets you and it also gets you into a B A P queries. Then we have the SNP operational data. So this connects to a net weaver B w business White House on then this one the s a P table close to a function relaxes well, label as miscellaneous saw anything. That's no the above. Did you notice that the logging interfaces identical for all of them your logging first and then you get the query that you're looking for the cube or whatever the next one says universes. And I find this fascinating that it didn't actually say s a P universes, because it goes to business objects and assist objects is an s a P program. Eso If you're using webbing to face, that's what might create the reports. But also, I feel using the business objects enterprise server that will get you into. Then you can see there's a few of authentications that you can get into the Central management server that way. But yeah, this is another ASAP thing. You remember earlier we had XML. We've got another XML here, so this allows you to do a few more things for XML. It's a bit more XML specific will also allow you to get into Web services and, of course, the way, but uses a lot of XML back to S a p the repository that's another ASAP products. As you can see, Central Management Server the same logging required to get into business objects enterprise as thes universes did. And then there's a whole host off over data sources like, for instance, Microsoft Outlook, Microsoft Exchange. Generally, you probably won't be using these and you pop, you will be using more than half of these. Eva, So many gone through this lecture just to give you a brief overview Elves to what all of these are. Andi, If you don't have the product, that's it. Just starting your memory so that if you change jobs or your business gets one of these problems, you know, huh? I can put Crystal reports into this using that. So for what I'm going to do, I'm going to use the Access and Excel connection. I am going to going to my second database folder. There's my extreme. It is a Mac soft access that don't need to secure logging costs. Access didn't use a secure logging. There's my connection. So just to see what the properties, you can see that it's got the right location on. You could see it's got the right tables expand. It's also got fuses out. So we've now got our new connection to these current location the database. So in our next video will start quoting a report 9. Creating a report from scratch - adding fields: So it previously we've been using these wizards to create the reports on with it. Okay, in starting, but they're not useful for updating report. For instance, if you get into the middle of a report and realize hang on, I need X. Y said I need an additional grouping. I needed additional fota. Yes, all of these are possible in here. Um, if I next to go, I can do grouping. I can do voters, but it's not as useful for editing them. So what I want to do instead is actually start from Riel. Scratch I instead of creating a standard report, I'm going to create a blank report on theirs are blank reports Underwear with the database expert now is called an expert. It may look a little bit like a wizard may look a bit like a dialog box. Crystal report called these experts, So browse the data souls for the tables. You want Trent. So we have previously handed this connection, but we could have added it in this diamond box. So we have access to all of these tables. So what we're going to do is go into the orders table. So this looks remarked, will be like the wizard that we were just using. So now we're going to the oldest table and I'm going to select that table on news of sex. Let a lot more if I want to. I could get to the holders detail like that, and so far so previously we had a next but down here, which then takes us onto the slight. The field want to. But this expert doesn't do that. Now you notice that I've asked for two tables. Now, this is not actually calls on database all database design. But if you've got two tables, they must be joined together. For instance, I could have a list of customers as one table on a list of all of their orders. Has a second table, and they're joined together by a customer. I d. So, in really basic terms, I might have a customer name Philip Burton off United Kingdom and then in the orders I have that Philip Burton off United Kingdom is ordering X y zed and they're linked together using this Philibert in the United Kingdom. So how is are these two tables? The orders on the oldest detail linked together Well, if you see that on, do you recommend having look click on the links? But so you can see that we have thes fields and they are joined together using the olders i D. So both tables, orders, detail and orders have on order i d, which is, I think intensity needed your whole number. Andi So disorder, which is one order, might have just one detail, one product being ordered or might have 10 different products being ordered. I might have theoretically zero products being ordered. That would be a fairly stupid older. But it is possible. Now, just in case you are wondering what on earth all of these colors are. Why, so they have got more than one color. This is index legend. If you click on it, you can see that each of these colors with a two indices. And again, this isn't a cause on database and creation, but just to let you know that if there is an index than linking, makes the computer be able to draw on the tables together much more quickly. I mean, that's basically what they say is up here, but that is a product not of you in crystal reports. That's a product off your database manager. So if you see something that you're linked together that doesn't have one of these, then you might want to ask you if it's giving you today to excuse me, can you index this particular hull? And if you really want to get technical, you can click on an index and click link options, and you can see in adjoins left out of joins right out of joints. Andi, even the full drunk. And that's an example of a right out drawing, for instance, really on this particular one. If I was using an out drawing are Jews are left out of joint? If you've got no idea, that's I would use the right out of China. If you got no idea what I'm talking about, then I've got say course cold. 70-461 It's got six sessions. Session. One session to will give you all the gen about the basics on database creation. So if you do want to learn about left and right joints and inner joins, then have a look at those on their included If you're on you to me for business. But As I said, this is not a database cost. You got link types and you got enforcement off the joints and avoids whether it needs to be enforced so that there is something. So on the left side for the right side phone, since without enforcement you could have an order detail that doesn't have a corresponding Gorda. So you know, just gonna keep it to enter joint, do the safest one. So those links, if that one told the head the way the good thing is the computer auto links where possible , it may get it wrong. So if you start getting really complicated in terms off the Giants and self off, then you may wish along a bit more about database. Very. But if not just reassured that if you put in a couple of tables which are related, the computer will do all of this linking for you but national where it is, even if natural details are a bit esoteric, right? So now we've Kurt slash those tables will click. OK, we go to our field explorer. If you can't see the food explore, go to view Food Explorer or, alternatively, click that button Andi expand the date of his fields. Andre conceived, got two tables there. So now I can start just dragging things into the details section. So, for instance, I could drag all of these about kicking in the 1st 1 shift and click on the last one. Drag them a little notice what happens. It's a little plus sign on that on the old drug in as best as the computer can do. It thinks so. Let's have a look at different preview so that we can see have you think not at all well, formatted the door. I will be looking at formatting later. So that is the start of our report. Needs a favorite of work. But don't why we will be doing a fair beautiful are going to make it look half decent. 10. Sorting results: I just have a look at this report again? No. Is it actually in any particular older? What do you think on the answer is yes. If I go to next page, you can see the page lights up here. It says incidently to off two plus because it's not actually worked out. How many pages there are. If you want to see how many pages there are, you click show last page. Andi totally then works out. Everything did totality. Now the older that it's in. Is this one the older i D. If you had said the old date, you'd be sort of right as well, because the ordinance are being done in chronological order and so on. Actually, the old date will be done at the same time, but exactly being done in terms off the site title. That's I in terms of the old Dinty. But what if you didn't want it to be not older? Your report. You should be able to say I want it in a different order. I wanted to be an older off the order amount, so I'll have the biggest at the top, so I can immediately focus on those orders, which are the biggest. So what I could do is click that Andi, go to our records. Salt expert. Now, this is a fairly standard icon, so it's easy to recognize. But if you weren't able to recognize that, then go to the report and just go down and you see a record salt expert. So each of these items is called a record knuckled items A called records. So click on the record salt expert and we say, Okay, so what do you want to order by? Well, this case want will do it by the older amounts. Click on that order, amount field we take across. And we said we wanted to be ascending or descending on. Do you see that there is an eighth? Oh, a d Right. So descending means the largest amounts going to confessed. So cake. Ok, Andi, Instantly it changes. OK, but what if you wanted it to be in the audit date and then by older amount? Okay, take on the record sort expert, just going into the two different ways of doing that. And I wanted the older date No. Well, that give us what we want. I said, I wanted it to be sorted by the old date and then the older amount. Okay, Okay. And nothing seems to happen because it's sorting by the older amount, then the order date. So let's move this up by clicking on the order date and move it up by clicking on this arrow, which means that the date comes first in the sorting and then the order amount descending Golda, click OK, and you'll see that. Now we have all off these early orders First Andi, then the order amount is used as a second results. So here we have three items on the 16. So in the 19 and we have biggest to smallest thes actions on the 21st biggest to smallest. Now you can also remove those Move, move. Okay, we get back 12 which no salt and incidentally, that now allows us to see if I scroll across to a height of 1300 17 that it isn't in all today told because this fun on this one, this one have bean got the wrong year. So they should have put in 2000 and four, presumably because, says 2000 free. But all the other records, So 2000 and four. So his default is definitely sorted by the older I d 11. Grouping: now in our previous lecture, we sorted by order amount and then we started by all the date and then order amount. But what if we wanted to do so? The next stage? We don't just want to sort by older date. We want heading a clear delineation. We want all of the items, which are one particular date, to be in some way together, and then all of the next items out on a particular date. And in crystal reports, That's called Grouping so we can now group. Let's see how to do that. We've got to report on. Do we go to group expert? Alternatively, you can see the group expert is here. That's a couple of lines of a gap in the middle. So group expert. So it's very much like the salt expert. In some ways, it's asking you, what do you want to group buying in this case went to group by the order date, so click on the date. There we go. Now there is an options button down here. They're useful. It's sing. Okay, You want to use all the date as a group, but how often do you want a new group to be created Now you can see at the moment it say's for every week. So let's see what happens if I just say OK on diagnosed the options box and click OK, so you can see that there is a first group on the 16 for February, which contains all off the records between the 16th and the 22nd of February. And then there's another group for 23rd of February. So if you're just gone to this group by and just got well, I'm going to like we got with previous group, If you just said, Well, I'm going to group by the audit date There we go. Each particular order date is down its online. You'd be very disappointed, in fact, very surprised. So what you have to do especially dates, his views, this options and say I want it for each day. So not, Is there lots of options for every two weeks for every half month? Month? Freeman. 6 12 months and then smaller bits. So every half a day, every 24th of the day and stuff off. So usually you probably want for each day or for each month. So in this case I'm going for each day and here you can see how I want it in ascending order or descending Golda or whatever. There are a lot of options here. Most useful, I think, is you can force a new page. You could ask for group to be kept together, Son avoids. It won't split a group off similar records. So in this case, the day's worth of records on more than one page and you can repeat a group header on each page. It's lucky to those in turn, but for now we are grouping by all the date for each day. So it's cook OK and see what happens. So we can now see the 18th to the 19th 21st 22nd 24th 25th and then look at this 26 now also, look over here. What this is is called the group Edda and then we also have a group footer which correlates to the above had a So all we have for each group is a header, the detail and the footer. So if I just have over there seeking looked at everything in the bottom header details footer. So here we have a header on the detail is on the next page. There's the footer, so let's it might say that's not exactly what we want. And also also not is on the second page. It doesn't tell us what the date is. It doesn't say this is the 26th. I mean, it is over here, but presumably going to design how part of the reason for creating this a group is so that we can free up space so I can get rid off the older date field here. There we go, which just creates a bit of space, and I can move the's along now fitted better into our report. So now if ever looked it, we do not have the order. Data's a field, so there's no way in the world I could see that these on the 26th of February. So let's go back into our group expert going to the options and going to the options so I can say Repeat the group header on each page. So take that click, OK, click, OK, and now if you got a top, you can see that the date has been added on each and every page why was before but dislike that it was only being shown when there was a new group at the top of the page. So the over alternative, which I quite like one of the of alternative is to keep the group together. This is quite handy when you got smaller groups and you just want to keep them together without a page break. So if I now go back, you'll see that we have a bit of empty space of the bottle. Why was previously we had header by itself? But no, the header has been moved to the next page. Yeah, and then we will get to the middle of this page. There's a lot of blank space because everything to do with the 27th of February, unusual to the next plate and so on. So that makes decisions to keep everything about the group on one page where possible. And if it can't go on the existing page, it moves the Thai group down to a new page. But the never option is to just create a brand new page, a page break after, In this case, one visible group horned. Have a look out for these will come back to these much later on, but they're dotted everywhere. So here now we have a page break after every single new date. So which of these is best? Is entirely dependent on water? Report is going to be like I generally want to keep the group together andare generally repeat the group head on a new page on the Rarely Do I use a new page, this one. So generally keep this and this this if it's less convenient. Sometimes it's just kept looking. The report seeing saying, If this was I wanted from the report on going, Hey, no, we can also do additional groupings suffering, since we could group by the shape of via So if I do that, move that across. So now we're grouping together. Everything that his own say the 12th of December does been shipped by per later on doing shipped by ups and again you can do all these things keep you together. People head on each page, onda all that and additionally, you can also salt with the need section so I can also salt and say that I want insulted by the older amount descending. So now you can see it's assaulted by date by whoever shipping it Andi by just the grouping , and then it sort of by the actual amount in descending, Golda said. These two very powerful, definitely hope you to get the formatting. That said the order off what records are displayed, where right and in the next lecture will have a look at a novel way off saying What records ? What you want when we have look at food drink. 12. Manage groups: right. Well, just want to talk about managing the group's. If you remember, we've got two groups set up, so we've got one group which is based on the older date in ascending order on one which is based on the ship. Fear in ascending Gotta. Now we can monies that in a sense, off we can change it how we want. And this is a great way of quickly. Critic alternate reports based on the same information, Just a different view. So, for instance, we've got everything which is in date older. So we start off native February 19 February 21st of February on. Then we have the ship via But what if you want to do is look at all the shit veers first. So we wanted what of his first Loomis, or whatever his person alphabetical order, Andi. Then we have looked at all of the dates. So instead of here is a list of all the dates on this is when the ship, how the ship why not have here is how the shaped on within each one. This is the other dates. So I could do that quite easily by going back to our group expert, which is also this I can't hear. So here we have our two groups and you can see the hierarchy. This is why this 2nd 1 is indented. Now we can change the hierarchy just by going up or down. So I can never make this one or the day to go down, so it puts it below. What was immediately blood fork can make the order date go up. Hello. So now we vowed much. Do when you can. Now see that we've got the ship Fear at the top and all the day to the bottom says people can see what happened over here. So now we have the ship here at the top in the older date of the 2nd 1 And if a preview the report instantly, we've got all of the fed exes together. And then if we just flick through, it was an angle to the next one. Eventually, Loomis there altogether in date, older, and then we start with peas after a parcel pulse and that sort of thing altogether. Alternatively, you may say, Well, I now wanted so I want a group instead of by date. I want a group all them, all of them by month together. Specifically, options on earth, The orders or to date, You can say this sexual will be printed for each day, so we can change that to for each month. If I know, click OK, click preview. You can see that we have, Ah, whole number off orders, which were for February 2000 and four. And here we have February 2000 free all together. So it reduces the amount of headers. All you could say I want it for each week or each two weeks. So each half months. Oh, if you if this was a timed field, which it isn't, she gets a second minute, hour or half a day. So if I say I just want to show for each week, then there. Yeah, we have the 30 from November 7th December, which is a week later. 21st of descendants off now. We had previously deleted the actual order date in here, so you can't see exactly which date it is. So if you want a that, then you need to put it back in the result, a date. So pull up. Okay, so there you can see all of these, which are the 17 are grouped together, but not necessarily in the date order. So 24 27 26 26 itself off. So what we can then do is go back to our salt experts aerial record, salt experts and say I want him sorted in. And at the moment we assaulting in order amount. So in descending golden, that's the d. So that's why in this week we have all of these in descending golden. So you can say, actually, I want to change that. I wanted to be so that they are all in all the date. Andi. Okay. Okay. Tonight you can see there really not a date, but within that, what's the older £76 for 26 1061. So then you could say I don't want it to be in the amount descending Golda. So once you start setting up groups, you can then change which group calls first and create different reports from it very quickly, very easily. For instance, if I just wanted to go back to my previous area, we are now in the date older xxx Guardian week older. So I need to change that. If it so wished, Go back to the come in packs person say I wanted her day. It allows you to play with the data very quickly. Allows you to explore things new won't necessarily have found before Andi, it just allows you to be a better analyst. If you know what data is, then you can go. Well, I can show you that in a different view. There we go dumb. It allows you to just be more productive and effective and it's more efficient. So don't forget that there is more than one tab. So we have this options tab as well. But we've already explored all of that. But that is how you might 13. Filtering (or Select Expert): right. So in the previous, like show, we grouped by the dates. That's everything that's got the 18th off February 2000 free are all under the one heading and then the 19. And then we also grouped by the ship via so you can see we've got to full pickups now. It's an awful lot of days that we're seeing you forgot to the end of the report. You can see it's 167 pages, and that's a huge number. So what we might want to do is to say, I'm only interested in Onda name what you interested in? So, for instance, I could say I'm only interested in looking at the report where the older amount is grated and, let's say, 3000 dollars pounds, whatever your currency is. So most people call this a filter. If you go to excel, for instance on, do you create a list The Excel on quite a lot of other places? Call it a filter that isn't the case in Crystal reports, Chris reports. We call it select, so we select a group of records to show so just slugs your way that it's not called a photo . It's cold to select. So if you're looking for a thought A in all of this report, then you'd be looking in vain. But you will not, is it has the same icon as Exelixis funnel. So if you forget that it's called select, you can just look for the final. Now there's a drop down and you can see that the drop down gives you the same options as here. So we're going to snooze. The select expert over is known as a dialog box to look for records. So individual items which are okay, So what are we going to have a look on? So we could say we only want to see those where the older date is greater than so also or the ship date? Oh, you name it. So let's have a look at the older amount. Okay, so kick okay. And here we have orders. That's the name, The table too far. Just expand e database field to see that the table is called olders, where the orders amount is any value. So that's the default. So if I just click OK, now, nothing happens because we've not felted it. We've said the older amount can be any value. So it is filtered in a sense for the food in the sense of I want to show all of them regardless. So there's truth of better one. I want to order Saya Dennison order I d. I want to order it where the order amount is. Now here's a drop down list, so let's just quickly go through. These is equal to is not equal to So I wanted to be a weight equal to exactly 3479 70 for example. So I could do that. It's gonna be very short report So is equal to on here. I've got a list of all of the values up to. A certain amount you're not is at 3479 70 isn't there? So it's important that it only shows you the top several 100 so I'll just type in $3479. 70. Your notice. I'm not typing in the pound sign at the beginning. Click OK, I mean, I can do, but I'm not actually doing so. You can see early for selected one was dropped out. Things it would be that's a click. OK, use the same data on there. We can see all of the records 23 of them. In fact, where the older amount happens to be exactly freefall. 79 17. We want a deal that there were 23 records of that, but you can see different dates, different ship ing's. So let's go back in. So we currently got orders. The order amount is equal to on notice instantly, even though I'm using that the British pound we have over here, the American dollar just off interest. Also not as soon as I click in here. Everything that was here disappears because it thinks you're doing a manual adjustment. So don't do a manual adjustment. Had this level so, I want to say is greater than or equal to sort of definite stream Greater than on degraded are not equal to what, if I said is greater than or equal to 3/4 of the night. 17. Then that would include all of these free 47 night seventies. So if I click OK, that includes free 4 17 70 This report with 402 records. Brazil's includes overs like 81 9 55 for 5 to 1955. Why was, if I said, is just greater done? 3479 17. What? What happens to this first item on the 19th of February, 2000 free eight disappears because it's not. That figure is not greater than that figure. It's actually equal to that figure. So if you want to include that figure, say you home. As I was saying earlier, I want 3000 or more. Then I would say is greater than or equal if I just want to say more than 3000 which implies does not equal 3000. Then I would say Great it. So this is greater than or equal to 3000 and now we have 444 records and you can see them all here. So this keeps the grouping that we previously had that was set up in the previous Electra. But what it also does is now refines what we are displaying. So we've gone from everything on everything, if you remember, was a think 106 7 pages to 40 pages. So we have voted out everything that is under the £3000 or dollars or whatever county using level. So what's the advantage of this? While festival, it gives you a shorter report. We're down from 106 7 to 40. Secondly, it allows you all whoever's reading the report to focus on what's important on what's important is different for different people. So here I've said, what's important is that for all orders, over £3000 or dollars, that's what's important. So it allows you to then concentrate and not have to wade through everything. There was over 100 pages were admitted with small details that we just don't want to look at so equally. I could have said I want if I delete that and click OK and then that gets me all 2192 cycles, which are 106 7 pages, I could say I just want to concentrate on all of those where the sheep via is equal to pick up. So I'm only want to look at everything where the pickup is three options and you can see we are 353 records, which is 29 pages. So I could also if you want to start anyone has to say click, delete and click. OK is the quickest way to fresh data just to stop that dialogue. Boston Keep going up. I could also do that with dates. I could say I will only want see anything where the older date is between. So this is why we give to dates. So it's between the first, the 1st 2014 Andi the 20 for the first 2014 so you can see 95 records. You start on the first of the first and we go to 19 on their 20th. Well, there's a lot more that can be said about filtering. I mean, the select expert filtering, but does is to quickly have a look at is equal to is not equal to is one off. So if I just don't delete this, it's easy to show you this with a string. So the ship fear your text. So is one off. This means that I can say I want to see all of those which are FedEx. All pick up. Are all ups on. I didn't mean to say pick up. Let's remove that. So his FedEx or UPS that's one off all no one off, Conversely, So one off you can see we now just have ups and FedEx. If I said it is not a one off, then it shows everything but greater than or greater than or equal to. We've done less stand or less down, are equal to is the same thing. So you might want to focus on small amount. So why the value is under $100 you might want to look at is between. So if your mind in the strings in alphabetical order between Loomis on and perk you later. So that's another way of source of using an in Oh, it is one off starts with. So I could say I want to find although that starts with the letter p not just by the way, this is formulated to hear, but it doesn't actually update at the moment, it's only when I click. OK, that's it updates. So this is everything that starts with the letter P scenario seats. Update starts with P does not start with P. So that's everything. But on then we have is like or is not like so this is why use wild cards so wild cards we've you might using Windows or various other things, but basically I could say I want all of those which have start off I'll just remove This time it starts off with the letter P, which have, as many counters is, could be that has the letter k after it. That then has one more letter and then the letter p so big complicated. I'll click out. But that means it's got started. The letter p He's gotta have the letter k Somewhere in it followed two counters later by the letter P. Now, of course, there's only one thing that's actually is in that which is the world pickup. But you could say I want everything that has the letter p in it, but at some point, so includes percolator or parcel post. I want everything that has a P somewhere in it, but I don't want to count the beginning letter son of a Rhodes and must be at least one character and then as many counters you want, including zero Andi. Then let's opiates in front. So UPS works pick up works because of the p. Their parcel post works there, but not percolator, because perk lattices appear the beginning. So that's the is like so is like gives you a lot of flexibility. If you used a wild cards then, especially if you're using programming languages and this will be quite easy to Eve, not if it doesn't sound like something that you need. Just put it in your memory for now. You can forget about it on then, when the time comes where you go, I need to say everything which includes, say, there was a description with secured in brackets. I want to find everything which includes that at some point, then you can use that so often you just be using this stars rather than the question mark. We'll come back to the select expert later. There's more to san it. But for this level, what we wanted to do is to say, I want to just focus on all of those why the folder amount is greater than $3000. Okay, on day we go, So that is how you can filter out records that you don't want so you can focus on what's important to you. 14. Exercise - Creating a detailed, filtered, sorted, grouped report: right. We're now going to pull together everything we have learned to create a variant of this report. Now you may have not is currently we're using all of these fields from the table orders. But several lectures ago, I also included something, hold order details and have not used that a toll. Well, we're going to create a new report. So if I just say this, I'm going to say this as my third report and then 20. Clear out everything I've done. So just slight everything. And I'm going to going to the group by and I'm going to remove all the grouping. So we start off of a brand new slit. There we go. So what I'm going to do is I'm going to create a report that shows that's a look at the fields I've got available. And this is actually quite a good method of producing report. Put on a piece of paper. I'm going to use a spreadsheet in this case. What it is exactly you want to show in a report. So this is the date I've got available. What do I want to show? Well, what I want to show is for all of those orders. Waas. So where we have on items ordered why the unit price is greater than 100? Okay, so where the unit price is greater than 100 I want to show the details off those items. So I want to show, for instance, I want to show the older said the older idea, the total amount on the old date, let's say and then I also want to show the products i d your price quantity. So it's just for almost starting off with. This is the data adult. This is what I want to focus on. Let's food it. So I have deleted old off the fields from the report. Does that mean that none of the fields are being used right now? The answer is no. Some of the fields are being used because we got little tick marks next to some of them. So my question to you is how they're being used. They're not being used in the grouping because there are no groups. So how they're being used, they're being used in the salting, very are. So if you want to really blank report covered of those, we now can see the one neurotics. So let's go back to our requirements. We want the boulders, I d. We want the total order amount. We want the altered eight. We want the product. I d I don't need the old idea because we've all got it in this old table. You want the unit price on. We want the quantity. So if a girl has previewed, see what we got So we've got 3684 records and you can see immediately that we've not done something that we wanted to do. We said we want to show only way the I. There's an item ordered where the unit price is great enough 100. And today we can see 41 30 free and self off. But it's a good starting point to not put voted on so you can see the entirety of the data . So now let's put a foot one or the select expert, so I want where the quantity and what did he call a quantity, after all, previously gave us a diner box where we could choose what field. The answer is because we've got quantity highlighted. So if I on highlighted and go to, uh, select expert. Then it would ask us what field that if I select one first. So I want us like the unit price and go to our select expert that this assumes that we're talking about the unit price. So I want whether unit prices grace than or equal to 100. So it's cake OK, and see what happens to these first fuse. They disappear with down to 1774 records. Okay, so is this the finished product that would want? Now I know that there's not. There's not a lot of formatting and self off. We'd be doing that in late nature levels. I mean later levels, But in terms of the actual data, Is this what we want? You could say, Yes, it is well done, Pass in the back. But that's we look at these free. We have the same older i d for free, different items. The reason, of course, being that more than one item that was ordered in that particular order, which was more than $100. So what did it make more sense if these were grouped together? So that's going to a group expert Now let's have a look. What are we going to group by? Just have a look at the data and see if you can work out what we should group by. Well, you might have decided I want grew by the order date on Mike decided that you wanted to group by the older amount. But what I was saying is I won't all of these to be grouped together because they are the same order. So therefore, what I want to do is the primary key. The unique identify rare off the older. So I want to group by the older i d. So click. OK, now it's moved. Always copied the order ideal there. But what I want to do is actually we juice all of this duplication because each older has the same order, amount and the order date. So let's going to design Andi, I will move older Mount up, handle the date up. Andi, delete. That is order I d. I said I wanted to keep the heading if I could, but this time I try it Do setting now. Obviously they're always around that, but just be advised that they are drawing together at the moment. So when a press delete it will be deleted in the next level will be talking about formatting and all sorts of things. So we'll be inserting a text box. Better just be advised. That is not at the moment. So now let's overlooks our report. So that's looking a bit better. We now have each order not repeated in the detail. And then we have the holder products. Now what order is it as clean? Well, it's in the order off the older idea at the moment. Suppose we wanted to have it by the older amount. The total order amount. Where do we have the biggest olders first? Well, we could do that, obviously with that record sort expert. So I want to order it so that instead of it just being the older I d being first, which has bean automatic inserted by the group I wanted by the older amount Andi, you'll see that I can't actually do that because these sorting fields are this insulting within the group. So what I need to do that for is create another group. So we created never group for the older amount on because we want all of this report to be sorted by the older amount. I'll plot it higher up. So it's then amount. I can't after that holder. I d. Right. So we now have a group for each order amount. But how old is this? What we actually wanted? No, I said I wanted the biggest order amounts at the beginning. Okay, so let's go back into record expert and you can see that's this is ascending and I'll change it to the Andi. I can't change it to the descending or ascending in the records art expert because Caesar groups, so because he's the group's let's go back into the group expert. So now I will change the older amounts go to options. Two descending colder. That's better. And then you present that to you. Boston say, There it is. There's my report and he looks didn't says, Well, that's very nice, but all of these done by FedEx what you say? Whoa, I told you that I just wanted all the reports. All of the top value items that have been delivered by FedEx. You go. No, you didn't. If you did, and you get fight so you don't say that. So what you do say Okay, that's fine. So we'll go back to how sheet Onda se? Well, FedEx Okay, so which of these fields will tell us about FedEx? Is the ship via So we're going to our filter food transfer record. We can't. We have a photo saying is going to be ah, $100 over. So we add a second filter click new, or we could click new over here. So which field are we going to choose it up. And you go. Um, it's none of those because we haven't actually used it. That's right. But we can still go to our data set and say I want it. Why this ship be It is so I didn't don't actually have to use those fields which are currently in the report, but we're showing the data set equally. It could use the order details table if you want to. So I will shape via So what do I do? Which one of these is it? His equal to equally, if you choose is one off is equal to FedEx, so click OK. And now we have just pills orders which have been shipped by FedEx where the unit prices over $100. And it's in descending order of the total order on blubber. So you can see if you try to just visual to say, told it gets a bit complicated, Which is why one of these really does hope, he said, allows you to go. This is why I created this report. This is the quote, these other requirements Now you could say I don't know that it's FedEx. It's not there so you could had it. But the idea of what I was doing goes to show you that you can filter. That's it so you can select. I'm feels that's not actually in the report, I cannot say. Actually, I don't want this to show it all 17,000 because it's contained there. I don't want it as a natural headache. I just wanted you to the sort. Okay, well, let's go into our design on. We can delete not the group header, but the field. So the group had a rule main. It was just people like son. Now we still have this group header, but the information is not duplicated. That might be what you want. It might not be However, the reporters it stands, it's report that you condemn present You Boston Say, boss, here is the information you wanted. It's got the data set behind it. Andi, he's got the criteria that use wanted. You wanted it to just show why the unit prices over 100 way was FedEx. And it's nicely group nicely salted. There you go. Bus now is taking us a while to correct these reports. I appreciate that, but what we're doing is actually learning. We're going into detail in each of the boxes to create the report more quickly. The credit, a black report. You go into your database, you add whatever tables you want it. Okay, so that's the links. That's fine. You say I want, um let's do the grouping first. I want to group by older amounts. Now I know what I want. No, no. The boss ones want to do by the older amount. I want to do it by the older i d. Andi. I say I will tell you, although I d I want the total order amount I want. The date you notice is no headings. So to get the headings will put it into the details, and then we just move it up. It's a nice little trick. We then get the over details. We want product I d unit price quantity. We then do the filtering we want. Why? The unit price is greater than or equal to 100. Onda. We want where the ship via is equal to FedEx. Andi, let's look at the preview. So that was a minute. Maybe a minute, 15 seconds. So once you know what you want on, you know basically what these are. So you database expert group expert record, soul text. Vote on this. Let expert once you know where they are, it can't be that quick to produce a report. 15. Formatting dialog box: right. Well, I started this level. We are looking at the second report that we created for most off the previous level. Onda while the data is great, Andi, the grouping is great and the salting is great in the filtering is great. It looks dreadful. I mean, seriously, I would not give my boss that a Let's just have a look at some other problems. First of all, we've got overlapping field names in the pay cheddar order. I ds generally should not have a calmer in them. It's just a number. We've got this employee I d right next to the date. So it looks like it's the 401st date off March 2000 free. We don't need the time thes air just dates. They don't include a time element. We have a memo field that goes off the edge of the page. Yeah, there's a whole lot that's wrong with it, which we're going to fix in this level, going to make it look a lot better. Now I contact we teach you how to create a style. Yeah, you need to develop your whole styles. Have a look on the internet for various types of reports and say, or this particular standards grave me, her like group headings we've big backgrounds and all you might prefer more minimalist Look , all your company might have its own style that you have to follow. So I'm going to just do what's high feed looks right. But I'm going to give you the tools so you can do what you need. Now, the first thing is, how do you format any of this Onda looking at this? Help we have a format on most of this is great out because what we need to do, first of all, is select a particular object. So I've selected the employee i D number, and now we've got the former field assault. To me, it looks a bit like a calendar in his meant to be a great and one of the small bits is highlighted, but to me it looks like a calendar with the number for net on. We can see where it is here. So if I click that we get a dialog box, the former editor Onda we come up with the fourth tap in. We've got common. We got border, we got fund, got number Got height link. This one here this full farmers it's come up with is one which is unique to that particular type of data. So we have a number data, so it comes up number effect. Click on the date on Go to my format. It comes up with date and time formatting and got text, and it comes up with paragraph. So we'll be looking at each of these different permutations in later lectures. But for now, who kicked the wrong thing for now? Format. For my field, I'll find the one that looks a bit like a grid or calendar, and that's how we can get into this style of box. 16. Formatting - colors, borders and fonts: right First thing I want to do is Taddesse Blash of Color Black and white reports. Boring color can liven it up. Too much color can over well, So this is the style I was talking about in previous lecture, whether you're going for minimalist or full blown or whatever. So let's have as style colors own this particular field the day field. So we're going to our former field. Onda will have look at the border so we could have a border all round it so we could have a single boulder at the top and you can see at the bottom. This is looking like how it's going to look like it's a single border all around. Or he could have at the top of the bottom a double border. Or you could decide I'm not having a board on the left or the right on. I want this boulder to be read. Andi, I want a background. I want a background of a year long night to see that it's no put. The yellow in the border is put the yellow in the actual field, So in this respect, the fact that this is cold border is a bit of a Miss Norma because it also includes the background color to the entire field. So let's click OK and see what happens. So now you can see we've got this border a top of the bottom border. We've also got a fill. Background is not going across the page, so let's click on that and you can see these little handles are eight handles. Wanna teach caller and one in the middle of each side, So that's for re sizing. So I want to resize that all the way across the page. So if I see of the pages is not very clear, why each paid where each group starts and as part of the reason for formatting, it's a draw your attention to things. Now let's say I think that's a big Irish. OK, that's fine. We can just change it. We say we don't want a top border boo. Want a single bottom border? Onda. We don't want this background, so I can either make the white background or I can de select starts to say I could. UnTech can uncheck the background here, so now it's a bit more minimalist. If I go back into it going to the front. I can also change the four color in the front so I could say, I want the four color to be read. So we changed. We had previously change the background color like that. So now we're changing the color of the actual text. Personally, I think that the background color should be better in the front. It's really got nothing to do with the border is a Nike. Never mind. Here you can also change whether it's bold or italic or bold on diatonic, you can change the font to something a bit more striking, perhaps Broadway. You can change the size so it has perhaps a bigger impact on do you can see. We need to then change the height off it as well. Girl, just really just that have become again a big garish. But it's good to know that you have lots of options, and it could be that your particular company says you have to. You Gothic 14 bold but not italic for particular things. That's fun, that is, or it could be that you're saying that he needs to be underlined, but now with the border, and that that's different. So there is difference between underlining the bottle, which goes all the way across. So if I just cancer that you can see that that goes all the way across on defy, take that border off and replace it even underlined. You can see it's now just underlying the text, so lots of ways to manipulate the border. You can have it on the left right top bottom. You can have a drop shadow if you're going to be fancy. So that's what that looks like and a game that might be nice style. So what's that doing is It's during a white box with a red box underneath it and a bit to the bottle into the right. That's called a drop shadow, and you can change the front just like you can do in mike Self Word. So generally you will have bigger funds or more demonstrative stuff. So have its borders or background colors for their higher in the hierarchy. You are so group heading one will public of a big fund group, heading to maybe a bit of a smaller fund, so we can say that that has 12 size. Andi is in a more subtle color. Andi, I can't see the culo once I click more on that gives us the entire ity off all the colors. So I could say, and they're also like 16 million colors in this lot tick. You want that blue With that particular shade, you can see what is going to look like they're So there we go. Maybe, actually, I'll just make this a bit bigger. I'll use this in a leto. A lecture. Just make that's a bit bigger. Make this a bit because well, so it's really for playing about with things. No off course. You don't have to use that. The format painter. For all of this, you cannot use this formatting to bar on again. You will get pretty quick reactions. You can increase the signs, decrease it. But italics underlined all sorts of things and personally allergies that Robin going to format field each time. Unless there was some specific options that you don't have here that you want to use so you can see we have background colors, foreground colors. Here I'll go back to that blue that I do's don't you? We've got certain borders seeking new pots all borders or a limited number of borders. You can activate left border not or no borders. But it's not giving you absolutely everything that we have in this former field. So if you can't see what you want here, got to former former Field or click on the calendar stroke grid to get to the full light editor. 17. Common formatting, including suppress: There are a lot of formatting things you can do now. We've already looked at Borders and funds. There are things which, as I said before, different If you've got text field to competitive, you've got a date food compared if you've got a number field, but there are some which are common to all of them. On this letter is looking at this common tub and seeing there's a really good stuff here. Now the first thing is the object name. So by default, there would just be a default. Think that you probably won't need to alter it unless you start getting higher up in the levels. Andi, you want to more easily refer to particular objects, but I don't need to talk about that right now, But if you do need to change the object, name damages to tip text. So this is when you hover over fueled once he's going to say so. This is quiet date, So kick over that. And now when I have over the field, this is the required date. There we go. So that's informing the user what you've done in fact, falling. Perhaps you what, you don't read only that means that all the rest off these particular features up blocked. You don't want to be able to change everything out. And it also stops you from changing the border and the farm from the date and time itself off. How often you to do that? I don't. But as most if you're giving the report to somebody else and you want to stop them from doing certain things or at least she ever think about it before they do it, lock position, that size that. So that means you can't edit the position size. You've got it just what you want. I'm just going to omit these two just for moment. Text rotation. So whether you want it at 90 degrees or 270 degrees, very rarely used out of thought. How often do you want it side with? There are some specific examples are gonna think of, but not too many. Now, this horizontal alignment. You remember that part of the problem we had here? I'll go into the former fueled by writing, clicking and going on to it. Part of the problem was, this is write a line that was This isn't on this girl's right on top off this number. It's the state. So what I can do, They say. I don't want this to be default. The default being right, Aligned for numbers left, aligned for strings. I want this to be in the center and now what? You can see some daylight. This is not right on that. Go back to the fourth pain editor, you know, send using lots of different ways to got to sit down. Look box just so you know that it's there. Keep optic together that let's say you've got some text that goes on many different lines. It means sure that there isn't a page break in the middle of that object closed border on page break that you've got aboard around a object, whether it should be closed when there's a page break. It's if you start having an issue with boulders and page breaks, then try and de selecting this and seeing what happens. So that is most of this dialog box. But there is here can grow on this. I particularly want to look at here. It's for text at the moment. It is just on the one line. Now you notice can grow his only detect, and in fact, this is grown. But what happens if a D ticket if I d ticket so de selected d check it, then forces it to remain on one line. Why was if I check it, it can be spread onto more than one line. Now, at the moment I've got, it's going off the edge of the page, as you can see here. So if I just change, make it really small just for demonstration purposes, you can now see that he can go on to as many lines as it wants off course. You might want Mount say That's fine, but I got the Gettysburg Address in there, and I don't want all of those 600 words or solve on one particular field. I just want to limit it to just three lines. Well, you could do that as well. You can't say it can grow, but only up to a certain extent Now. Obviously, you want to give it a much space as possible, but that's the kind girl. What's left is quite important. Suppress and suppress. If duplicated, that means hiding the content of fields and you may go wine would you want to hide the content of fields? Well, if they're saying exactly the same information like, for instance, we have here 72 72 you might not want it all consecutive fields. I've got true, true, true, True. Well, I just want to have one truth kidding. So if I going to the former field and say I want to suppress this field if it's duplicated Leslie Okay, see what happens? So now we told, he comes up in the first, all that the records for that particular group. You see stuff. When their group we starts, it starts again. No, There's also a suppress the lovers not suppressive if duplicated, but just played surprise, and that will stop it from showing it all. And if you're going, hang gold, when is that ever going to be useful? If I put a field in, then I want to be able to see it. While you might just want to create an alternate view, which has fewer fields, perhaps you don't particularly want to see the older date in each particular one. There is, however, a much more useful purpose for this suppress, which will get into a letter level when we have a look a formula. But just remember that he's there. However, this oppressive duplicated is very useful when you're looking at more than one table inside the detail. So you remember that we used tohave the older amount down here the end the order date here and we also hard the order d. So this is going back to the previous level. We're looking at salting and filtering itself off. Well, if I just take the all of the groups off about and just remind you what we had to the beginning, if I were lost, have to reorder it. So that's it resulted by order 90. So the full particular orders. We have more than one item and we didn't want the same item. Same order to have the same older to have the same older if there was more than one product . So what I can do now is highlight. These three go to the format objects and say suppress if duplicated. Now look, what happens to my report is not more identifiable. You can now see quite clearly that these two items are in the same order I d, because it's suppressing it. But I also know something else. All of these three on the same all the date. So it's oppressing the older date here because it's for the same date, even though it's different older. So if I just, um, take that off and just to show you the role data, you can see that all of these are for the 19 food outside this. Look at these, the 15 for January. But we have two different orders, and we wouldn't necessarily want when the older changes full this to be suppressed because that just looks pale to me. So what to do about that Is you dead at a new group I want grew by, held his I D On guy, go get rid off the field. Onda. I will narrow down at the photo antenna, so it's just creating a bit of a gap between each order so you can see these noun usually group together. But much more importantly, the order date now repeats, but only when there's a new order I d sold. This oppressive duplicated runs through the detail for that one group. As soon as there's a new group, it's no longer suppressed because it's not duplicated is the very first thing you noticed earlier. Thes two were the same amount. £329 8 divide. So if I took this off again, you'll see that this is suppressed a swell, even though it's for different older. So this makes Grouping even more important because now you can suppress in an intelligent way. You can say I won't Teoh show the older date and the older amount, and I want to suppress it when it's duplicated, but only within the same order. Now suppression is so important that we actually do have a I fold here. We don't actually have a icon for suppressive duplicated, which is a bit of a shame, but we do have one for suppress and you can see we also have one for lot format we have for lock size, and we also have want to justify and write a line and all the rest of it. So we're getting closer. We're now seeing that we can I hide detail that we don't need. We can have GTO when it's Juvic ated. We can organize when it's going to be hidden Onda we can now left justify said. To justify, we can lock it in place, say that's fine. There's a few of esoteric things, so all of those are in the former field common. 18. Formatting text (and boolean fields): in the next few lectures will be looking at how to format a very specific types of fields number, fields, text fields, date, field. So we'll start off wave one of the easiest billions. Billions are awesome, honest toggles the driver, yes or no. And sometimes you have a freeway boolean, which is yes, no or I haven't a clue. Also known as No. But let's just avoid that for the moment. Let's assume yes and no. All true and false. And basically, that's what we've got. We can say, true or false, or we can say yes or no. You can say 10 or we can say tea or for a while, and obviously the shelter it is, the less time you need. So payment received? Yes, so a very simple section that that about being But keep your eye out will come back Twitter too much later. Level for anything that say's eggs to that's to do with formula. It's so just mark your brain. Actually, there's more to it than that. What, you see that? So now that's a look at text. So we've already had, in my view, one of the most important ones, which was the can grow on the number of lines seeking girl, which is generally related text. Let's be honest. So apart from that we have got quite a lot of things that you normally see in Mike southward. We have first line indention ERM indentations on. We have left and right so I could say the very first line. I wanted to be indented by one centimeter. So there you can see your indentured tab. Or you could say I wanted to be zero centimeters. But I want the left generally to be one centimeter. So that tells the first line as well. No. Can you say I want the first line two B minus one centimeter on the left of You want centimeter? Yes. So we don't know if that means Let's just go back a step where we had 01 The first line was not actually indented zero centimeters. It was indented zero plus one centimeters. So is all aligned, but a minus one centimeter in the first line means that the first line is actually being indented minus one plus one centimeters. So, in other words, it's not being indebted. It'll the first line in Microsoft word that is cold. Ah, hanging indent indentation. But you actually have to specify it more properly if I just show you how that works. And if you want to know what's enough? Talking about Microsoft Word? I do have a video word. I free them. In fact, in a moment you can say, I want the first line to be indented, that before you could say, I want it all to be invented that or I could say, I want everything but the first line to be indebted hanging by that full crystal reports. You actually have to specify it a bit more obscurely. If you want a hanging, you have to make the first line to be the complete reverse off the left, probably wanting to do that too often. But it's useful, for instance, where you've got, um, one adult onda reason. So, for instance, you could have a list of reasons, and they will indented one dark boulevard to dark blob about three Dublon. So, like you doing word if I have dissing word and I put one, don't then generally you don't want it so that it is. You don't want it so that it looks like this. You wanted to more look like that, in my view, but it is possible. It's just a bit awkward. Three can also in debt the rocks as well. Lines facing that's exactly the same as word, so you can have 1.251 point five two times normal. Oh, if you really must no 0.8 times normal really compresses it. Or you could say he's got to be exactly this money point, so points of the same as here. So the font size. So if I say it's 15 points, then the line spacing will be 15 point, irrespective of how big the text gets reading older, right to left or left to right. That doesn't make much of a difference, except for Arabic and some over countries on text interpretation. That's why your text field your actual raw data doesn't have to contain text, but it contains hitch team al cord or arty or, more likely, RTF called Andi. The advantage of using HTML and RTF called RTF, means rich text format. Html hypertext markup language. Is it Ables to say I want the following to be in red, and then the following to being blue. It allows you to use more than one color in a particular text field. So if I open up a world, go and said This is my text. Andi, I say I want this to be in Read on. I want that to be in blue. They can see that as a rich text vomit document for going to crystal reports. There's my document is rich text format on If I were to open that up in No pat, there it is. You can see it's vote differently, formatted so you can see there some sort of cold before this and then some sort of cold before my Generally you don't work with RTF itself. You work with the word document is off off and save it to RTF. So it's not about creating rich text formatting hates g M L court. It's about interpreting it. So if you got a text to which has RTF text or hate email text, that's how you would put it in. So that could be done for all word fields. Remember that we had in a previous lecture I blow this up to 13 but have reduced the header sides So, as I say, I think one of the big text to things is the can grow Andi that allows the computer to devote enough space that it needs for the textbook. If it doesn't need you for the text box. Fine. Well, which we will keep it at the amount that it you've said. But if he needs to go, then putting that girl will work. So that is basically everything that you could do with paragraphs on with billions. 19. Formatting numbers: right in this lecture, going to have look at numbers now. As I said previously, order I DS should not have comers in. Traditionally, they are just a number. So let's see what options we got. And as you can see, we've got quite a few. Now this is the default that your computer set up. And, of course, it's not just your computer. It's whoever's computer. You are sending the report to suffering since Paul's high in England, where we use commerce to separate thousands from hundreds. Send this to somebody in Germany, where they use full stops or periods to separate thousands from hundreds. Because it's in the system default number format. I would see a comma in between, and he would see a full stop period. So as soon as just don't change in that you're going away from the system defaults. So if I click on this one, for instance, both me and my colleague in Germany would see Commerz. At least that's how it looks. It's not actually the case. What we're looking at in terms of a comma is only what I can see. My German friend will see a full stop, so be advised that when you see it in your screen, you are looking at it how you would see it, but not necessarily how everybody would see it. So what I want is this What now it looks of his heart. There's a negative sign of the beginning and none of these older ideas got negative. But that's we're showing you how and negative would look. This is an alternate way off. How a negative would look Morton used. I think we've accounting graviton. A lot of Gloucester do things, so I use a minus sign. And if I click OK, there we go. We now have lost the car, which is exactly what I want. Now here we have a currency. Now is Clancy supposed to number? But you'll see that their share almost exactly the same dialog box. We have just a little difference in terms off the formatting and you notice that they are dollars whereas my view is pounds. And that's okay. This is just how it is represented in crystal reports. But when you see the actual reporting will be in your currency symbol and you notice that the currency symbol is the system default. So I would see pounds. My friend in America would see dollars my German friend would see year olds. Have you got the feeling that this might not be? What we actually want if we're doing it internationally and not, is that there are two separate things we've got system default and we got fixed viewing law cow. So what's the difference between the two? Well, the fixed viewing law cow is one that's fixed the report on the system. Default is one that's fixed to your computer. So basically, if you find you having problems, try the other one. But I city. If you trying Teoh, do an international report, you should be using formulas by then. So it's really a non issue. My advice. Just stick to the system default currency format. And if you start becoming an issue, if you start having international stuff, then you really have to get invested into formulas and actually some formulas in letter levels. Now there are customs styles as well for both numbers and currencies, and here we have a host of options. Andi alike Microsoft excel where if you wanting to change, but something in customs you have to use all of this symbology Here it's Lord spelt out What is your decimal separator? So in my case, it's the full stop. It could be the common perhaps. Are you wanting to use 1000 separated? If so, what is it so quickly? I'm getting towards the European style. Do you want leading zeros of what if it's 0.4? Do you want zero? Adult was all just the dot If it is ill, what wanted? You said you wanted to say zero more. You want to say dash or what? Oh, if it zero Do you actually wanted to say nothing? How many decimal places you want? Why'd you wanted to be around to which might not With same, you might want to show five decimal places that have been rounded to buy fewer or greater number. How do on decimals to be shown on you can see we got one more option very about one, in my view, then we had previously reverse sign, so that makes minus is plus and plus is minus on. We also have the accounting format that you can see that the currencies now separated out from the rest of it. previously it was close and now it's 50 way. So if I go back into it, you can see it's just changed it and doesn't allow me to gently back now currency. Remember this pearl from the tiles raising. If you do want to fix it to a particular currency, this is why you should use it. Enabled the council symbol and use this. So if I want us dollars, that's right. It is only one symbol per page. So you find just click. OK, OK, you can see it putting literally just at the 1st 1 in the page and then leaves it off the rest. So that might be clearer. For some people, it might not be for you. So go back to the simple way of its position. So, for instance, euros, especially for girls, it puts it right at the beginning. That right at the end, Sometimes you might want the negatives to be the other side. So here we have the negative next number, and here we have the negative on the other side. So you can see this seems to be a bit of a book with the use accounting format. When I wake up. Okay? It keeps it in the accounting format, which is the pound separated. The constable separated from the number. So if this book is on your version, then just click a different style so you can see now it changes it back on. Then you can customize it to house content. So then I can say I want this to be in pounds Sterling Andi. I wanted to be two decimal places and all the rest of it, though I wanted to be deep away bad on in terms of negative and positive you want after you stop lust, believe me, but you will use this. Allow field clipping and I'll show you why. Let's reduce the wait for this on. You can see hashes because the number is two big in terms of the number of digits it needs for the space that you made available is exactly the same as in Excel. If I type in a big number, Andi would use the color. Besides, you get all of these hashes that so it's comes from. So that's not saying That's the number that saying I can't display go back into former field into customize. We can allow for field clipping. Let's have a look. See what happens now. So now what it does is it puts as much of the number that it can. So previously the number has the pound sign. So I found me picked a bit less. It takes off the pound, sign a bit less, and it displays less and less. And you can see 620 is not 20. Not the right answer. So use with care. Andi used very right situations, I would say, but it is possible that if you have all of these hashes and there's no way to stop it, because you've got to have it in this personally, how to reduce the font size. But that is possible. So that is everything I think you need to know about numbers. You can do a standard number size here, a number format, or you can do a lot more Notice that all of these formula boxes who will come to those later 20. Formatting dates and times: right. So we've had to look at Boolean. We've had to look a text of her docket numbers and currency. Both left dates. Now you can see that currently the format is as per your computer. So let's just format the field and see what we can get. So there, er system default. Short format. So we also have system default long Former 01 March 1999. Um, quite often that you go. I see that's much better because it doesn't matter if you're British or American. You now know what 06 drug, zero free stroke zil free means. And let's face it, if you're Japanese, go back to this. If it wasn't for finally said 2000 that could mean that 2006 the third month of her date. If you're American, that would mean the six month the third date. If you're British, that means the safe date the third month. So quite often I do what one of these with the actual month in like that because it just stops any nationalised international problems. I could send this report to somebody in Japan, and they know what they're talking about. Assuming it's hard to know what I m a r means in Japan. So we've got all these formats. You notice that we start off with one's with date on time, then we have ones, which is just time we have ones which is just date. So all of our dates have a midnight time, so we don't actually need the time thes olders won't actually placed at midnight. It's just that nobody's actually we called it when they were placed. Yeah, there were probably place like 11. 42 AM So really said date, that's important. So if I change it to a date and I can use a two year digital four year, two digits or four digits in the year, just look how little space that now requires and save here will change that. And I could say, actually, I want a leading zero. Wanted to say zero what march instead of just 1 march. But I wanted to say, Do want March 19 9 Andi. None of these actually allow me to say that. Okay, well, we have to go into our customize. So first question, always saying the date and the time, or you just send the date or we just saying the time. I don't think I've ever seen a really report that has time followed by date. But at least it's an option, so I just want the date. Now. If I was doing the date, then the time then I could have a separator between two so I could have some dashes or act . Oh, something like that. Generally, you just use the space. So then we get into how you're going to former. So first of all we have, whether it's a custom or whether it's Windows default, generally, if you got into this customized dialog box with probably using the custom but the calendar type and my only options of Gregorian. But if I was in the Middle East out probably have a different type of calendar, at least is an option. How you want told. Right? So this is the Japanese former year month date. This is the British, and that's the American. And then what do you want for the dates? So when But I wanted 01 march on 99 you could have, um, era, but actually, it doesn't print. I was expecting to go on all Domine. I But no, it doesn't. I think those are more used, for instance, in Japanese calendars for the traditional date to serious why you go from the beginning of an emperor. So you have Year four off whatever, emperor. But a lot of Japanese just used the Westernized version now and then. We have separators on prefixes, so no, you can customize it to your heart's content. Similarly, with the dates. Sometimes, if using the times you can have use the system default, you can use a 24 hour or so Nona's military time, or you can use the 12 hour. You can also say how you're differentiating so you could say morning and afternoon or morning and evening. But haven P. M or whatever your language, your callous Andi, you can say how you want the hours and minutes and seconds to be displayed. If it all on what these air drop downs, you can also put in your own version so it doesn't have to be a call in here. You could have a dash, for instance. So now I've got it set up. How I want it so that would uses space tidies it up, remove stated that I wasn't going to use. I was never going to use the time and allows you to focus on what's important. And let's say I want also sent to justify it, especially this one. Now you might think, Oh my goodness, do I have to do that every single field? If I'm not using the standard If I create a new field, then going to format into forwarded to exactly the same thing again won't in a report option a big bottom in file options. We have a tab which allows us to format the fields it says field say, have. It's just a former. So if I going to number, I could say any future fields that I put in, I want to be formatted has falls 70 currency, any currency. Appleton. I want to be formatted or follows or data times all the rest of it. So my advice would be to actually set this up. Go to file options on field before you start praising your first report because you have a standard, undoubtedly, for how you what dates and currencies and everything girls to have here set it up first, then you don't have to warrior about having to go into every single field and set it up. How you like it. So that's everything we need to know about formatting. Good numbers, text currency, Boolean dates, times It's all there. It's all within that four for tab off the former field, and you've got to customize which you can then be able to customize it to your heart's content. So it's exactly what you want. 21. Adding objects: now part of good formatting is not just formatting. The objects, the numbers, dates, text. I'll call them objects in total all fields that you have. It's also adding objects that you don't have. You remember in the previous example, when we had older idea at the top in a header, we lost the very top it because it was tied to the field in the detail. So we need a way of actually restoring what we want. We need to add this header. This header is a text box. So if we're going to design, actually insert text boxes by going to insert text object now you notice that there is a cattle a at a small a notice he sold sold there as well. On that, the next one is group, which is there on. So we have the things in the insert menu in the insert to bar. So if we insert a text box, so now it's asking me to draw textbooks or just click, which gives me default size or a czar, saying, if I delete that text box, I could draw the boundaries of one whichever way I want to do it and then inside, I comport what I was missing. Korder, I d so diarrhea we have the missing textbooks. Now, in addition to these group headers and group footers, we also have page headers. Andi reports headed. So the report had two goals at the very beginning of a report. No matter how many pages it is, it only appears, wants a PE. Ted appears at the top of every single page and similar for the footers, except they're right at the bottom. So the report footer will only appear once and notice that the report for tirelessly appears before the page footer. So it appears at the end of the report. But before the page footer now we might be that we want a nice logo. Maybe you work for a company which has its all log on. You want that at the top of future part so we can insert a picture so it looks like a little house or teepee with a sudden it. So there you go. So insert picture, find the picture you want. Here's one I made earlier. Click often and then it says, Where do you want it? And I want it Here not is how long it is. It's very long, Andi. Then we hopefully will be able to resize it. So let's move it across. I can actually see this, um Onda go across. You can see I can resize like that. So I made it really long, but it is also a bit stretched. This cat logo isn't as good as a previous levels, which waas perfectly shaped. So we look at formatting these in the next dial up in the next lecture. But suffice to say that if you want to just insert lines, boxes, pictures or text objects so lines could be used, for instance, like in this group header here, we could do a line there so that will not nicely separate out the holders. Then they are all. And if I did 30 box, maybe a puzzle box in the page footer? No, it's like some I can't actually enter things in the box if I wanted to like words. If I wanted to do that, Donald, insert a text object. So I find such a text object right next to it here and say, these are this is Page and also copy that hope to hear and say you say this is work, less transit, getting in search of text object, then said this is then you will see in the preview for Got to the bottom. This is the page. Foot is that every single page, including the box on This is a report for two goals right at the end of the report. So that is how you insert additional text objects and pictures, boxes and lines. 22. Formatting graphics: right. Let's see how we can format all of those objects that were just inserted. So first of all, the text object such a match of guest. We have former text when we writing Click. Oh, well, we going to this. I can't, I think Andi, this looks very familiar, doesn't it? It's exactly like those text formats boxes that we had previously. So we wanted underlined and we wanted Well, quite frankly, we wanted to look exactly like this one. You can see that there is a difference. This is underlined. This one isn't so. If you've got an existing text Boggs this one that you want another text box to look like. For example, you could use the format painter, which is also in the format. So former painter works just like Microsoft Word. And if you haven't used the Microsoft word for word painter, it saves an awful lot of time. So what you do is you select the field that you want. I never feel to look like click the former painter. Click the field. You wanted to look like you. You want to change its done. So I select the one that you copying from the format on there, click former painter and click the one that's we're going to. So that's is a quick way off formatting. Of course, it needed just to be for text boxes, but you get the idea like the graphic. So let's see what happens when we go on to form a graphic. So obviously all of these have got Borders and that sort of thing. But who's our picture? Although specific options now we have scaling, which in terms, off percentages. So if you don't want even just to be stretched or in evil way cramped off straight still, whatever thes two percentages need to be the same. So that start off with 100% which means that the image is not stretch it all. But if I was to edited like a didn't last election, do you like that? Then you can see that it gets very walked. And if we're going to the former graphic picture, you can see that these percentages are quite different. So if I want to change the high to do so 6.4% as well, there you can see our cat is back up to where it should be now, though, of options it here you can explicitly say the signs. If you wanted to be definitely 20 centimeters, then you could just that. But then I would suggest catching the height to fit in terms of percentages. You can crop it so cropping. It is big, more convoluted than it is in Microsoft. Word in Microsoft Word. If you insert a graphic, Andi, I'll just you can crop it by going to the crop function. Andi, just visually cropping it like this. Join us again. Crop. Andi. There's better cropping it like that. I was here. You've got to provide all off the numbers yourself. Yes, your heart, but he still does the same thing. So I don't think that this much of additional interesting You can always press the reset button, but that's about it. You have the common, and you have border on dheim link with General Shown yet lines. Former lines. It's got a single or dashed or daunted on. How thick do you want it on? What color would you like? Onda When it's printed it. Do you want to move it to the bottom of the section? Sometimes? Well, actually, in I think it's the next lecture that sometimes the girl over when you grow old text boxes it might grow too much on DSO line could go past If I don't tell you right now. In fact, if I go to my second report on, you can see that Thescore website is just one line deep. If I put in line here, it looks like it's at the bottom of everything. But when I got to preview, you can see it's right in the middle. So former that line moves to the bottom of the section. Got to preview. Now it's in the right place, so that's quite a useful feature. Suppressed means hide. Repeating horrible horizontal pages is if it goes on, you're not just going down, but you're going across so you might have several pages going across on, then lock and read only. So that's what you got for life and for boxes that's going to for that box on very similar , except you've also got drop shadow, and you can also fill in the box in a color. So most of the off options we've already seen the common options on bond borders and that sort of thing, so I don't need to go through all this again. So that is all about formatting boxes and lines and wrinkles. There is this one other thing, which is the rounding. So you might not want box, which purely has straight edges, so you could have a bit of a curved box right at the edge, So that served doable. So always look out for these second tabs, whether available. But you can see more, says time. We're looking at common, and we're looking border hyperlink and then your very specific stuff. 23. Aligning and resizing objects: Right now, we are able to insert sel of thes objects. It's important that your report looks good. For example, you can see things are all over the place in this particular report. Let's say we wanted the peel box number to be directly underneath the employee i d. Number. Oh, we wanted thes were not aligned properly. Wanted these to be aligned. So there's a lot off small fiddling that needs to be done, So festival re sizing objects Now I really went with re sizing objects of it earlier. But just in case, if I zoom in, you'll see that all objects when you click on them have got eight handles. The's handles can be used to resize, so you just kick him one. So you press the mouse personal one and then hold and ragged to your new signs. So ones which are in the side just do the side. You can go open down on those, whereas ones in the corner you can go up, down, left, right, so resize the width and the height. So the one so if you only want to resize the height, just use the one in the top and bottom But if you want to do this the height and the width of the same time, they use both. And of course, you can. Once you click on it, drag by holding the mouse on anywhere over them handles, and you could drag the object to where you want to be. So that's I wanted it directly underneath the order date. There we go. But you see, it's not exactly underneath the order date, so what we can do is we can say, I want this object to be directly underneath the older dates, you know, write down there. It's just a fraction off. Let's make it even more so you can see it. So what we can do if I just zoom out slightly, is I can online thes two objects, so Lining means that they share the same left. All right, for example. So what I need to do is a need to click on this subject and then click on this or click on that on that click on that and I lose the focus on this. Okay, that didn't work. Let's try it again. Click on the 1st 1 on, then hold down control and click on the 2nd 1 and now you can see both, um, have bean selected. So no, I could go for month on the line, but that's the world I can't align him to be the same on. So you can see it's not quite as flexible as word of halfs because I would expect you to be able to align a graphic box on D A. A text box, for example. Fine. Except you can't How strange. So let's align these two together. So we are now lining two text boxes. So I click on the 1st 1 hold down shift or control, click on the second. And now, when I got for line, I could align the left, the right or the center. So let's line it to the left. They're gone. So now they both have the same left. If I lined it too, right? The gulf the same right? Never line. It's a center. Well, actually, the Niva have the lateral the right because it is now center formatted. I can also make the same size. You can see that they're different tights, for instance, if I can make it the same height or can make him same with or could do both. So going back to this sect report, I could say I want the play I d on the P o box to have the same left there because now the too far left because it's now overriding the customer i d. So now that they are both selected, I can just drag. And there we go. Let's make him the same size as well. So make same on bacon. Same height width on both Andi. That's too much for me. So I'm going to make it so that they're both smaller. There we go. But now they are aligned. They have the same thing. So are lining is the same position on then you can make in the same size is what if you want. Now, let's just have a look it to the other thing. So if acting on the's to, for instance, you can see that the options have now changed, I can now line the tops as well as the bottoms as well as the middle's. But then we have another option called baseline. What there is a small but subtle difference between bottoms and baseline bottoms is the bottom off natural field was baseline is the bottle with text, and there may be a little difference between the two. So basically, I would play with each one and see which one looks best in your circumstances. So that's all about lining. No, just in case you will be confused by my saying, we'll click the first object, then control and click the second object. And then later on, I said, click the first object and then shift and collect the second object. Let's see what the difference between the two is. If I click this customer, I d. And then press shift and click on this, a quite date it selects the 2nd 1 Why was if I press control? It selects the 2nd 1 so it doesn't seem too much of a difference on that. We've you on that in some things, like Windows Explorer. If you hold down control, that's what happens if you hold down shift. That's what's happened. Shift defines a start in the beginning, whereas control does individual things. But in crystal reports, holding down shift doesn't define the start. In the end, it just adds an extra one, so there is no major difference on family. You cannot resize objects in more mathematical terms if you go to formats in size and position, and then you can say I want this to be exactly X and Y coordinates down to the nearest centimeter in short of you working in. But this can't be used for multiple objects. If it's like two objects and Goldman size and position, there is no dialogue box comes up on that is a lining and sizing objects. 24. Adding additional sections: right. So now we know a lot of what we know. We can get this looking a lot better. First of all, we've got all of these field headings on top of each other, so I select one, move it up. So it's a ship fear. If I just put us up here at the moment, thes two should be higher up. I'll say, relate to waltz at the top. That led to these to check vehicles at the bottom. On line left. Have you go again? This one that goes up. In fact, I'll just online the top you go, employee I d. It's like that. They're going wrong around. Had a 50 50 chance of both The same sciences would resized of in the episode the Electra. So for this Down a Linus to the left. There you go. Quiet date there. So let's move. Payment. Let's put this up as well. So line that talk. Um move. Payment received all the way to the rights to here. Andi should date off here, right? Onda, we'll expand this Cory websites which isn't going to to money lines very go. I could also put these two these free up or 11 but I'm not going to now. I want to highlight all of these. Make sure that they are. You could see that slightly out. So I'm going to align the baselines. There we go on, similar for these. Bring to align those baselines Onda overall Got to put all of these fields in the middle. Middle aligned So but some looking up report. Okay, Andi, that's looking pretty good. But actually thes needs a bit more space on to conceive these A slightly wonky. But that's you. That's part of the paragraph. Think that we did tell you so let's reduce the amount of space in here. A report. Pure box. There. Have you go? That's keep producing the space. There we go. Andi, add the's too well, like this one. Anywhere up there. Here we go. Right now. Listen, looked it and you can see there's a problem. The career. What site is only can grow. So if I click on it on got form for my field, got commonly can see can grow on. You can grow up to three lines and it does. That goes up to two lines here. Effort. That means it's over writing what's on the second line. So we have. It looks like this close space which we call a website to payment received. But because this Korea website can grow to say that much, he goes into the middle off this payment received. Now I would actually suggest you do report like this That looks exactly like this. But there will be problems in your report that you will have a description which in grow and you might need to put something underneath fit. But you don't want to trust see clash now, one thing you could do of course, as expand this details down. We could move all of this down to free lines so that there's no way that it could cost us three lines is the maximum that this could go. So then there is absolutely no way that it can clash. But that's not obviously the best way. What's much better is if we could put some sort of separator between Korea website and payment received so we could say co website can go as deep as it wants, but only after it finishes can payment received gone and this is called sections. So these are to be distinguished from groups, but they are look similar. So it's not going to report. We have a section expert and you can see the icon just that right next to the filter. So Section experts mean that you have everything which is in one particular section together. So at the moment we have a report. Better pay Tedder to group headers, details and foot isn't all those over footers. Each of those as well as being groups and reports and page editors and all the rest of it are also sections. So what we can do is add a second details section. So let's click on details and insert. So now our details have split up into details A and details be that's Click OK and see what happens over here in Latin Side. So now with let's talk into details A and details be. What I'm going to do now is move the line Onda the payment received into details be that it works out once time. Just there we go. It's Luckett so sometimes these things would be so fiddly. So now they found me this shaped one town as well, just to make it clean and reduce that details A This everything in details Acre grow as much as it wants, but he want over, right? Wasn't details be details be will only start when detail a end. So there you are. You can now see we have details a Andi details be so there we go so that can grow and grow and grow. But until everything in this details a think has finished this section on the then can details be section start. 25. Formatting sections: Right now we've got these sections. Let's explore to see what we can do them. So let's go back into section expert. So if I click on, say, details A. The first option is surprised. So that means don't print it. Don't show it. Don't do anything again. This is really useful. If you created a massive report what I call a Level four report, which means you've got a header Group one you've got ahead of group to head of group free. And then you got all of the details at the detail section and then you want to create a level through you bought when you just take the first report, and then you just hide the details, you suppress them, and there's your level three report. Then you hide that says, suppress your group header free, and then you got level to report so it allows you to create it's orations very quickly. Print at the bottom of the page so very rare. You'd need that. But occasionally, like for, for instance, of footers, you might want to put them on the bottom of page, keep together. So that means literally keep it all together. So that it can't break a page unless it absolutely has to. So you probably get a new page if that's needed. Suppress, blank section sources. Nothing there. If you've got a text field, which is blank and nothing else in that section will. That then hides that section suit. Don't just don't like lines underlay following sections. Let's just think about that for a second. What's that saying is supposed? I put something in group had to to Andi instead of waiting until it's finished before going into details? A. It puts details. Hey, on top of it, or when on Earth are you going to use that? When are you going to put something in one header on Want text to go on top of it when you're doing a watermark, for example, so you could put a water mark in one of the details, sections or group header and then immediately afterwards, have the text fruit on top of that. That's usually when you use or delay following sections, paid breaks or what they called paging so new paid before. After and when we get into page numbers, you can reset the page numbers off in avoids If you want a page number started number one after every group. Photo number one. So because 12345 and then finishes a section in starts again, what five. Andi can also have you wanted portrait or landscape. Andi, then the background color. So, for instance, I could very easy way of having the background color for all of my heading ones. Yes, let's be super very quick way. I don't have to worry about borders shading, and that's what to think and adult away about how long the paid Len fears all the text fields or anything. Just make the entire section of background color. Now there is one that escaped, and that is hide drew down. Okay, on this is Charlie useful? However, we'll have a look at that as much later level women start looking at sub reports because it is John useful. Burti Saturday beat moron of advanced level than where we are at the moment. Now, finally, we can change where the sections are. Obviously, we don't conscious where the group headers are page of the report at a usual group expert dialog box. To do that instead, if you wanted details A to go blobby to details be Then you just click the up and down But it seems you that details a becomes details be and details be becomes details A because they swapped But once you click OK, you can see what's happened. In other words, what was a is now being What was b is now is just moved the position So it looks like when you click on the up that nothing has happened But actually what wasabi has gone above what was a and now be named a It's old Johnny confusing You'd think that there would be a way to show it more easily But basically, if you want a section to go down to click down And if you wanted to go you go up click OK and you see this has happened So all ways to refine what gets grouped with what So for instance, now all of details they will be kept together and then details be will be printed on the after details a finishes so it stops clashes before they can even start And so that is everything's to do We've section expert 26. The problem: right. Let's just take a step back. We've got a report which is better for mattered. There are some things that could still be taken care of. Like, for instance, this required data's of truncated. So I could do that. It's much better for Master don't. It used to be and is currently 239 pages. Lock Well, we solved that sort of problem. If that was a problem in one of our early elections, when we had a look at the record, we said we wanted to have a filter or select fields where the order amount that's gritted on 3000. That reduced the number of pages to 60. It was 40. But we don't know lots of formatting since then, and so allows us to focus on what's important now. What's important to you, of course, might not be as important to somebody else. Let's say the next person comes along and says Yes, a great with poll, Nigel. But I want to look at those which are between richer or for 2000. You go OK, it's OK as fine. A book called on changes to 2000 on jobs, a good un we now to say two on pages, and that's almost consider calls. Nigel. I needed to be 4000. The problem that you've got is that this filter where it say's is greater town. It was greater than or equal to is, Ah, hard coded figure. It is you that's typing in difficult. It's saying it must be this figure, not another figure brackets unless I change a letter. What is much better is if you can give the user the end use of the person who will be running this the choice of what figured to put in what's advantage of that? Well, firstly, it takes the onus off you to produce the report. It is time you might be unwell. Let's say you're having. And after you might have bean put into another side and somebody says, I need a report that says 4000. I need to wait till Nigel comes back. Yeah, I know how to run the report, but I don't know how to edit it, and I don't really want to touch it while *** is not here. So if you can allow the end user to say what level they want to look at that will make the report a lot more useful and allowed them to swat, ankle self serve. They can open up the report and go, Oh, he's asking me for the amount. I hadn't thought of that. How you 4000? So how to do that? That's when parameters or perimeters ankle parameters comes in. So we learn that next lecture, how to set up a parameter. So who placed this heart coated figure? 27. The solution - Introducing the parameter: a predator is a user defined value. So at the time of running a report, the end user can say, I want 9000 daughter. So first of all, we need to define a parameter. Now, everything else we've seen is it reports or this will be reported and there will be a parameter with said experts. Where? Ah, so it's a little bit now Why? Well, why enough would it be? Oh, silly me. That's a big problem has figured out here. Bottom left. So click on parameters on there's a plus, but it's actually the right inside. We have a parameters field in the Field Explorer. So if you don't have feud Explorer, this is what it's absolutely essential. Got if you Andi Field Explorer so primitive fields. Currently there's no plus sign next to it. We don't have any parameter fields. You could see the list like on. It's a question mark in some, uh, straightforward bracket. Crystal Report uses curly brackets for other things, but these seemed to be straightforward brackets, so writing click on Golden New right, so we create a new parameter because he created new parameter and a list of values. So we want the minimum or the How are you is going to be a number or currency. Let's keep the currency. Andi. It's going to be based on the order amount so we can give people a drop down list on the older amount. So they could just ticket because most people walk. Don't want have to type. They want to be able to use drop down lists. Okay, so how does that look? That looks pretty good to me. So let's click. OK? And there we have a minimal order. Value prompted field. So let's go back to design. Go back to preview. Andi, Um, it's no sir. So just because you've defined the parameters field doesn't mean that the computer's actually going to be using it. So remember what we're going to be using it for. We're going to be filtering on it. Oh, market Lee, we were going to use it in the slight expert record, so the orders are order amount is greater than or equal to 2000. So I click this on. Do you see that there are these first few 100 records? Well, right at the top. We have in curly brackets. A question mark. So you went with a question mark over there. It looks like it's not curry brackets, but it must be, and then minimum order via Let's click that. So we're now selecting only those records where the order amount is greater than or equal to the parameter minimal order value. So let's think. OK, so we now have a dialogue box. We have to enter the mineral order value on there's a drop down off other than looks like I do have to type it. So I would say 2000. Okay, okay. And there we are at 81 pictures on bond. You can see we have order values. There will be got one That's 2000 but we don't have any below that. And you can see the parameters that we are currently using. Enter minimum order value 2000 so I can change. That's over here. I can say 3000 Onda, um has it. Don't No, there's still a 2000 there, so just changing us over here doesn't actually do anything that's in the moment. Let's change it back to 3000 and you can see that we've got to his life under the top. Apply changes would make this change from 2000 to 3000 or revert all changes. I'm just going to revert it and you can see we're back to 2000 which is what the actual reporters. So we changed that now to 3000. We are take two on pages. This reminder click of light changes. We're now down to 60 pages on those smaller amounts which were 2000 to 3000 have now disappeared. So if I change it now to 8000 apply changes, but smaller, you can see we have a problem. They just need to expand that there. So not down take team pages. We have a little I could hear prompt for parameters that gets us this download box, which is a bit more of a formal way, perhaps of doing it so we can put it 12,000 on this automatically updates. Except I haven't applied the changes. So even if you go into this dialog box, click OK, it doesn't do it, but immediately it's a 2000 click. OK, we still have eight thousands. We still have to take this, apply changes on you could see, we have a grand total. Just eight records now. Very short report. Just two pages long. So this is how you can make it much more attractive by adding parameters, allowing the engines that to say this is what I want? 28. Parameter Options: Well, let's see what options we got with regard to primitive fields. So if we go into this parameter field and click edit, so what do we have here or festival? We have a re sizable dialogue box and do recommend reciting it caused quite a few things here, So it just gives a big mall height festival. Let's have a look at why there wasn't the drop down box. Well, there wasn't a drop down box because this huge area was empty. So even though we put in order amount, that didn't actually do anything, So what we need to do is populate this list with the list of static values that the drop down box will contain my static value, I mean on changing values. So, for instance, if you think off the ship via you had FedEx and UPS, there's a list of, say, six values, and you can say these are static values. They are not going to change. We're not going to introduce 1/7 carrier. We just like if you have a database which is classifications off under 18 years old between 18 65 and over 65 those are three categorizations. You can say these are not going to change. They are static. So let's say that this minimum hold of I was not going to change. Okay, so I had the Thursday to the first item I think was four point old one on the second tomb was for 55 on Deaf only got about several hundreds of the studio. Okay? No. Okay, that's just okay. We're just tells thankfully, we do have an alternative rather than just to clicking on new and adding an extra. If you're goingto actions, we can upend all of the database values. Now. Actually, I want to insert an existing word, upend all the existing database values. So if I click that, we'll see that we get all the values for $4.5 all the way down to $8000 defect thing that maybe somewhere in between no, I might be wrong on that. So that puts in all off those values. So if a now click OK, Andi going to here. You can see that there is now have dropped down download folks. It's a lot more complicated. Andi, it goes all the way from $4. All the way to 8000. No, it isn't 151. So apparently it is not useful when you've got hundreds off different options. So let's just go back into that. So realistically, there are several things which are wrong with this approach. The first is this is only a list of all of those values which are currently in the database . He founds to go back into database and add $4.10. That wouldn't be in this list because it is not account existing database for you. So personally, I wouldn't really think of are using drop down list for numbers. So if I clear this, there we go, that's fine. But let's just have a look at everything else that's you or do notice. By the way, the interactions you can import and export lists from elsewhere. So, um, we can't show it on the view a panel. So after we can show it or not, show it and we can edit it all. We could make it read only we can have our prompt text, which is that inter minimum or device so we can change that we can prompt with the description on Lee. Let's see what happens if I change that from true to false Andi show that does it look like anything's happened? In fact, there is something which happens to it, but we'll have a look at that in the next lecture because it's not really applicable to its inapplicable. When you have a list optional prompt, this is a bit of an advanced option. It's when you have parameters, which are optional the default value. Now this is good. This can say when the report is run. I want there to be a value in the parameter, so the injuries that doesn't have to type of property value they can just accept what I put in. So I could say $2000. That is the minimum order value, unless you tell me differently. So allow multiple values not really used in this particular example. While we're just talking monetary but be useful in the next lecture, allow discrete values on range values similar to devising, but the minimum value on maximum value. I want to say you can't putting lesson $1000 on you Compton more than 6000 and then the final thing, which is sure by your description, will have looking the next lecture. So for numbers, really, it's the prompt. It's the default value and it's the Mina max. So it's click. OK, let's go to the parameters. So please specify a currency value between 1000 and six fountains, so I'll keep 12 fouls and that's okay. So that's between one of 6000. Although it's out of range, right? Let's make it 2000 butt naked. 2000 500. There we go. I defy the changes Some day we go. So now if I go back in and save this report, close full plates up again. You see that it has kept the previous minimum order that I put in. It doesn't replace it with the default. However, when you publish it, then there is no previous minimum order value added so then it will keep to the default. So the default value is good, Andi, especially good. When you were in it for the first time, just be advised that when you open up the report there might be a non default firing. That so that is a quick look at what else you could do with regards to this particular parameter which happens to be a currency 29. Text parameters: right. Let's not another parameter, Andi. This time I want to be able to restrict the any orders that were in ship to buy a particular company or shipped via a particular company. So in the database fields in orders, we have that this ship fear. Now, are they going to be hundreds or thousands of records like we saw in the older amount? Well, that's just writing. Clicking it on. Go to browse data. No, there's six. Okay, so that's quite suitable for drop down list. Okay, so let's insert this parameter or course in your character. We call this ship to be a prophet, right? So the value field aboard the vie field is the one that the database is actually going to use A computer is going to use is the ship via right. So I'm going to append old database fires. So there we have our six. Now, I'm going to ask what my colleagues to put a description toe all of this. So I'm going to export this into a folder called, but this case has a peak was to report I'm going to save it as a text. Well, because that's the only option called parameter Don't text. Now, let's go to that. Andi, if you're used to text files, we can put a tap on each of these. But the description so Federal Express description could be actually identical to the value because okay, fellow never heard that one on United Postal Service. USPS. Right there we go. So I'll say that and I'll go back into here on. I will import what I've just done. So there we have two descriptions. So the values are what the computer uses internally. The descriptions all want the end user sees. So if instance, pick up customer pick up. But it might be even worse. It might be that you got values. 12341 being Federal Express to being Loomis. Well, you definitely want some sort of helpful using to face. That's what description is for. If you had the description in a field, then you could have added that field. Andi populistic. So there we go. So we've got all of these. So let's see what happens when we going to hear and absolutely nothing, because we're not using it. So let's use it. We're going into our photo Reckord or select expert record. We will had that the ship via must be equal to the ship via parameter usually is in curly brackets and click. OK, right. So we now have this drop down list and now you're not said it's got both the value on description. So I'm going to say, How do you want to see those which have customer pick up? Okay, so now it's just explain those which are customer, because now I can change this way. We have a drop down list, some changes to parcel post and do that. I can talk in any old rubbish. Andi, Click that, and there'll be nothing. Okay, I think we probably need to do a bit more formatting. So let's go and edit this on and see what that is. Well, the 1st 1 we've got is prompt. We've description on Lee, so if I change that true go back into this, you can see that it's now on. The asking is based on the description, so it's hiding value until years. You get to hear. So if a clinical parcel post via it's just putting in the computer term Parcel force, suddenly customer pick up. It's interpreting letters pick up. So there you go. So next we need to stop it from me being able to type in your rush. So we have allowed custom values currently untrue. I'm going to change that. False. So no, I can't literally type in anything. I've got to use the drop down list of that home type anything in, which is exactly what I wanted. So that's good. You can see that this changes into bold with a little staff to its after I've changed it. But I have not clicked. Apply changes. Okay, right. So let's see, what has it got? Allow multiple values currently false. OK, let's change that. True. Okay, we have a different dialogue box, so I know I want to go for Federal Express. Andi customers pick up. Well, I can do this. A kick. Okay, Andi, there we have got pick up full both people on Federal Express. Well, I could do more than one now, house, if you're surprised by that, do you mind? You're thinking this should say is one off, but no, it actually uses is equal to so if you're thinking it should say is one off the parameter solves the problem heat food users and or for each of these. So it doesn't say that it must be Federal Express and customer pick up the computer. No, that's impossible for one record, both picked up on DFO to be shipped by Federal Express. Instead, the computers intelligent enough to say I want both I want records where Eva Federal Express all customer pickup is used. And that's quite intelligent wit, right. Going back into the proto, we have allowed discrete values. What does that mean? Both these are going to hear. I can say I just want that one off. Federal Express. Andi, pick up one that's not ideal adjacent to it. There we go. But if I don't allow discreet than all allow our range values, notice that range values became true as soon as I put them onto false and vice versa. So now it won't allow me to go federal X one from the top of one for middle something like that. Instead, the ballot box looks like this so I can pick the start of a range on the end of a range so I can go from everything fall Loomis up to customer pick up and I can make it inclusive for by including the value of exclusive so I can hide that range. Click OK, That's everything from Loomis to customer. Pick up. Quite frankly, there's not too many times you'll be using this, but I still exists. And you can also say that there is no law by you and there is no offer valuing affords while you can't say them both at the same time. Because I would be absolutely everything. But you can say one of the office I want from the beginning, all the way up to poor little. If you wanted everything all the way out to ups, so had that arrange. Okay, there we go. And if you really want to, I really don't recommend this. But if you want, you can allow both. You could allow both discrete values. Andi range values on this is when the dam a box starts getting quite messy. So you could you could say I want everything from me. I won't do us. I want Federal Express and I want everything from percolator to ups. I don't see too many uses for long. Both birds. It makes it the parameter selection box a lot more complicated. Maybe you can think of a use. I haven't come across a real one yet. However it exists. We've also got Edit Mask. It was a big topic that is, actually that's when you allow custom values. Life reasons. You won't allow people sapping their zip code there on postal card or perhaps their own email address, but they have to be within a certain range of values. For instance, if you put in a email address, it must have a I am Pacific seven at sign, and he must have a full stop period after the at sign. Not too many uses unless you actually do allow. Obviously, you must allow custom values. If you do want to see the full list off the masking values on. I'm not going into it now, but it is on the screen. Just have a look at the crystal reports help on just type in edit mask and you can see it. There is a lot off possible functionality that you could put in how often you need to do it . Don't know, but I would recommend remembering goal of this. I'll just look. It'll be each and every time you need it because it allows that alphanumeric character requires it allows an outfitting the character doesn't require. It allows a letter require there's so many different options you can't possibly remember it'll Andi is no used anywhere else if I remember throughout crystal reports about from masks. So it's not like the wild cards that we were looking at earlier. The Asterix is in the question marks. So really, I would control help. Have a functioning goal, Eddie Mask. If you be done, the final one shall buy your description of not often use will look at that have in later. So here we have all of the various options that we can do to great text parameters allowed the user to type in one or multiple of them a range allow them to typing custom values or not on. You can see how flexible it is. We've just adding this fight this parameter. We can make the report much more interactive and say I am only interested It bills percolator shipped have I use with packages which have a total minimum order value of more than 6000 fresh report. Here we go that is such a boo for the end user to be able to hold down what they want to see. 30. Let's insert a chart: right. Until now, everything that we've done in crystal report has bean word or numbers based we're going to do is do something that is tells us picture very quickly. We're going to create a graph or chart. First of all, I'm going to get rid off the ship via So I'm going to going to the group expert on bond. Just get rid of it like that. Okay, so next we have less. So look where we are. We have each individual date going to change the parameters. Options. So that hub it allows multiple go true, so good to slept a lot. I'm going to say £2000 to pull $2000 is the minimum. Okay, they go got 620 records, but it's a very big document. 54 pages. So what I want is to have the amount off the order. Andi the ship via in a graph second, see, for each individual date how much has been sent out. And by whom. So have we doing lots and lots and lots of graphs if you want. But I think we'll also do a summary graph right at the beginning. So right at the beginning of the report will insert a graph, which tells me the total amount that's being shaped by all of these. So to insert a child, hopefully this will become clearer to go along. Go insert chart and you can see it's got these free little bars, which is replicated that. So we insert a charge, put it where we want it, and we have the chart with it. So it's interesting to me that it comes up with the data first rather than the type. If you're using Microsoft Excel, he comes up asking What type of chart do one. It's it, Yeah, you have slept the time even said the data first. So I want the total amount. So that's undervalues every time the ship via changes. Or, to put it another way, I want shipped via to be on the X axis and total amount to be on the Y axis. So what type of child? While the default is a side by side bar chart, I need to say See bars, display of I for each group and subgroup brackets optional, good for showing comparisons between group values, so that's just click. OK, let's see what we got, so it looks like what we want. Ship via going across Some off order amounts going across are going up. Very old ship. Veer's what Street Beers, I think like UPS Federal Express, not numbers. But let's really designed for you that's going to preview view preview more than see what we get on there. That's what we want. We have each shipped via and we have a total amount. So Federal Express, if I hover over it £567,000 or dollars parcel false. You can see this is very temperamental. This hype this tool Tip 511,000 pickup 523,000. So in just a minute we've got this analysis off the information. Of course, we can limit what we see. If I change the parameters to just a Federal Express on pick up, let's see what happens and apply the graph changes because it is reflective off the options that we have chosen. So if I wanted to see the value off those shipments, which are over £6000 for those, we now have 300,000 as opposed to 500,000 so It's very reflective off what deter is being footed or selected in the report. So let's go back into it. So I go into the chart expert. So writing click chart expert. You can see that there is a chart to think here, but let's go into the chart. Export a swell so we can check. We can have a look at all of these others. We'll spend some lectures just going through each of these and seeing what happened. Well, let's look at the options that we've got. We've got axes so we can have major or minor grid lines. So if opposing minor once, it would put in additional lines. If that was set up, right, So let's go back into the chart expert. We could also scale Oregon also range. So if you don't want auto rate, let's say I wanted all the values to start 400,000 and go up to 400,000. We'll assume it's OK. That's exactly what happens. So you can't select exactly where you want to start. You can start it negative format. You can have it in thousands or millions, so thousands. We have the cave 350 k You could have it in millions m or you could get the computer to auto. Range it on bottle, scale it. So eat, then Saiz. I am putting it into this particular scale, so if I don't also scale it, just look at the difference. It goes, Note 300,000. If I also scale, then it zooms in 250 to 310,000 which may be what you want. But beware. Look how small this pickup virus value appears to be, and yet it's over half a 1,000,000. So generally do be careful when doing bar charts, which don't start at zero. So just beware so options that you can have a color charter. You could have a black and white now showing labels and showing values. Let's just look what happens when we show label there you can see in the middle the label not that helpful at the moment, but we can also show a value on that shows how much the actual thing is 301,000 compared to 254,000. I would leave also range on Onda legend. That's this over here, which shows which colors which on you can have to the top aside the right, the left or the bottom. So some people like the possums and people like to the left or the right. It depends on your style, your background. You can never have a transparent background and what you see right through it, or choose not to, because markers were not using markets. But we are using bars so you can have big balls or small bars. This is the smallest on this'd biggest so average he's probably best on. This is why you can put in in the text tab. This is where you can put in the title that subtitle The Foot nor the Group title in the data title. So it's already done that automatically. But you might prefer to change that. It's something different, Andi. We can change the font as well for any off thes, I think. Okay, And this also much exchanges. So we have inserted our first chant, Onda. We started forwarding it 31. Chart Options: now there is so much customization you can do with this, that I can't possibly go for it old. But I will go through some of the more important stuff. So previously, we have had to look at the chart expert. Now there's also for my child, and you know, it is a little icon that looks a bit like this calendar. Think well, that gives us exactly those things that gives us common border and hyperlink. So not too much there, though you can edit the border if you so much. No. If you click on things like the Siri's Onda, we can see that we have a format Siri's riser. So we can change, for instance, the foreground color. And you can see that things automatically happen. This soon has I click on stuff and we've also got Borders that so there's so much customizing that you could do here. It's unbelievable. You can also form unto the Antrel balls themselves by writing, clicking and going to have got left, click on wall first and then go to Siri's options so we can have a rectangles. Or we could have a beveled box, which has a big, more of an outline that you can see. Oh, we can have a reversed bevel box, which looks like the rectangle is sunken in rather than raised up How often you need to use all of these girls. But it's nice stories there, so let's stick to rectangle. You can also use death. Make it free day, and you can see in here roughly. It looks like overlap on Gap weeks allow you to more easily customize some things that even if you just hover over it, change the overlap factor for bars. You see roughly what is talking about here. We connected the titles. We did that earlier, where we want to show the data labels on what we want to show the value or the label and value. I gently just go fire you personally. The legend. That's this to show below on the right inside of the left, inside, where the markers God, their God left in the moment. But lots of different options grid lines have to shell guidelines major minor. I don't need to show major guidelines for the why exist Data Access Onda whether you're grouping, so a lot of these are let's just play about with it and see what happens. So looking as an axis of a click on Alexis so I can have a format. An individual and the access label on bond make a much bigger than that, or I conform out to the natural access itself on. Say, I want to hide the maximum value or the minimum value, like you don't want to shell zero on there or can stagger the values. Look what happens over here when I stagger the values. So especially if you've got words supposed to figures that might be of use. You can have the access on the left or the right inside on both sides. If you so wish his where you can show your minimum or maximum values or like the computer calculated and your major and minor steps where the zeros always to be included. Like if you have arranged for months, that could be you saw the previous example. The computer got also scaled it between quarter of a 1,000,000 of 310,000. Well, if I always wanted it to include zero, that's how do it make it a descending access for girls the other way on. Don't even draw values, which are outside of this range. So if this range included, if this graph included 100,000 Andi, I said I wanted his shoulders between tune 15 free and and 10 then that 100,000 ball wouldn't even show right how to formats the number. A number of decimal places of its number. Data Cron zero. What's have you had worked a short in thousands or millions or billions on non and Web to show major or minor good lines? You can also, for my the legend, which just basically is colors on the border on Dwight, it is located. Now. If you're writing click, we have select more than we have. Zoom in. So what does that mean? Well, look at the curson in the moment it is selecting stuff, Eliza, if I change that, so I forgot. Here, zoom in. Then I consume in to individual elements. It's not working for me currently, but personally found will resume in just literally Zuman on. Finally, you can load templates and savers templates, so let's say you found the template that you want. You can then save it as a template, and then you can bring it back. Alternatively, you can have a look at all of the templates, which will be included and say, I want to use that one. So that is a quick look at some of the additional options, which were available with graphs, charts and the like. 32. Different types of charts 1: Let's have a look at some of the other child types you can use. What? We're starting with the bar chart. We can have a stack chart indeed. Scented bar chart. Now these aren't used when you have more than one on change off. So let's just say we're putting payment received in additional. Now you can see that we have received payment on £269,000 worth, but not on £238,000 left on. You could see that they are right next to each other. So if I change the promises so we are showing all of them so we can see that we've got Big Child, Big bar, little bar, big global little bar. What we can do instead is that builds up so that we have a small bar on big bar. But together they show the total. Now notice that the colors have changed so that the bottom bait reflects what hasn't been paid the false on the topic to reflects the what has been paid true. And of course, we can change the format entries so that there would be showing true yes or no or similar. So instead of saying false week of writing, click and go to edit access label on dissented, saying False A. Not on this one. The truth is taxes. Okay, thanks so much more intelligible. So this shows what's not being paid but also shows the totals. That's a bunch to the stacked. If you're going to the percentage vast act, then here. We're not saying we really want to see how much has Bean paid now how much natural dollar terms has been paid for each individual shipper, but we want to see the proportion for each. So you can see, for instance, that Purpura later, 10% has not being paid. Where was for Parcel Paul's? It's a much lower percentage, so stat percentage bar chance are for when you want to see proportions. As you see components proportionate. Part of the total right line charts very similar to bar charts. The best used, say full date rangers so you can see how solving changes through time so we could see in this version that we've got FedEx Loomis Parcel Force. But really what we want to see Instead, he's to show it according to the ship date or the date after the Runkle room on the fourth . We want the older amount, but we want to see, um, eight her months. So let's change the order date instead of on change off. We can say click on order. We wanted to change for each month. Of course they're each month. So now is saying on a change off the month off order date. Show me the order amounts now for that of all chalices. Quite usable. Now, a stacked bar chart is when we have more than one on change off. So if I put in the ship to see you again, that just looks hideous. No, of how would get rid of all of the, uh, data labels, but overall, that just looks hideous. So I'll get rid of the date the data limits by going into Char Tex. But and we have assured the data labels There we go. Even so, that's not really telling me much area I could just about for this green thing. But if I had a stat chart then that shows the overall value on you can also see each she was component towards it. And again, a percentage line charge. You can see in a particular month that this one had a huge This is UPS and that really big proportion of the shipping. And you can also have markers, which looks a bit of a nod option because we've already got Marcus. But, ERM, that's available as well area. So exactly what we've just been seeing. Except instead of lines that the linesman put it, they have stacked area like that. Or you can have a percentage area like that, exactly what we've seen except it's filled pie charts. Now pie Childs show out of 100% want your particular component leads towards. So, as you can see, it requires one. I'll change of field one show values. So have you got to show value. So if I get rid of that part, l put in, just should fear this shows which shit via had the most or what percentage of each hand also the type I. So if I had labels that would be quite useful this time. It also had the labels. There we go. So that's telling me now in one fell swoop that Federal Express 506 7000 accounted for 17.5%. Now you can get to call the next stage and have multiple pie charts. So let's and back in at the older date and say that for each month we're looking at the ship via for each month. So multiple pie charts. You can see that we have one particular she, um, a pie chart for each ship via So this one happens to be Federal Express. This one happens to be Loomis on within that we have all the months. So you need quite a lot of space for that. Maybe, um, they go well, that perhaps produces high city state of labels that looks massive. Take it down to fall both. Still, it's big and former those data levels. So if you're doing this, you need a lot of space, especially if you're showing the debt levels. But you might want to say well, for you, Supply. I just wanted to see what were the top for months. Okay, so let's go back into our chart Expert on order date. We are wanting the top end up, so I want the top three now. We're not used top end before, but we can see that We just want the top three in this case, off order amounts. So let's click OK on that instantly reduces its down to literally the top three months. Much easier to see, perhaps. Or perhaps we changed the older of this on that goes up. So we have for each month all the top three months the, uh shipped via. So let's change it so that it is all of the months on. I just want to see for each month the top two Karius and I want to see everybody else just grouped in once. So there we could see the top carrier WAAS FedEx for May Second was per later, and there was really small right. Finally, we can format the chart frame, so that's not what would perform natural pies. So if you don't click on anything and just click on white but outside chopped Option 30 a long photo belong data levels. We have show pie name so that will identify which month all of these Aaron. Obviously, there's a lot of formatting you can do. You can also do explosions so you can get said the over to move across like eso forgot back into charter options area. We can explore the pie so lots of work can be done and needs to be done. If you're using this sort of option, go back into child expert. We can also have proportional pie charts so that the pies on told the same size. If I delete all of the data labels, this is a very good visualization. As to which month Walls biggest he could seize. You know, that's so damn. And by far the smallest office, the biggest, like putting Minolta in this date option in terms of the 33. Different Types of Charts 2: Let's have a look at some of the over different types of charts. Three D rises. So we have botch outs, period chance optical charts and cut corner bar charts hand. It's basically looking like that so you can see that we have The percolator was a pure o. Later have I use going across there and the FedEx values there that wants going that way on the actual values going that way. The problem with three d child is that you can't actually see behind the big ones. So, for instance, the Pura later, because you can't see whether there is a bigger there or what their figures in here, so use with care is basically or something. But it is interesting. The sheer quantity off things that you could do this the pyramids on. We have opted guns. Look, families moved. To be honest, you will be able to tell that was a nautical. Knew what? That's more like cinder and as you cut corners one. But if you could find a use for them. But I suspect the less data you've got, the better for those surface Childs. It's again, good luck finding use of those, but they are available. So we have Sai Whether you have sides or whether you have honeycombs, you can read descriptions. So you see time is often plotted along the major axis sort of display relationship between the surface and they curve. So if I change this hope, so just drag it up like that so you can see various relationships whether they have any used to you or not. He knows X y scatter. Now country, we've got free axes. You can see it wont allow because I couldn't come. You got free access. It wants a lot because it's got two axes needs. So this plots relationships between an X axis and the Y axis. So if I returned to the chart X, but way needs one change off to shore values. So we have one change of to show value so we can sell the order amount on the I don't think we've lost. Never won. Maybe that's called in the older ideas. A surrogate here we can see that all of the order amounts applause it here a lot off the order ideas are plus it appear Onda. We have the charts show show the intersection off them where, what month each of those are. So because we got all of these months say, we've got 20 months. We got 20 different points chart shown. So here we've got looks like 2000 and 4 June 2004. On the long side, there was 100 and 50,000 order amount on 350 this access so short relationship usually used with two. No miracle that has to be used to numerical values. So if you want to pit to numerical values against you, Trevor, together with a no numerical value exploits caste class might be of interest radar charts and stacked original charts. Stacked just means that the two separate values are possible. Top of each other was radar. It means that they are just separate. I've only used the Red I'll Charles about twice, so bubble chance we'll be like the X Y scatter graph that displays free debt if I use woman . So the first date of a new groans along the X axis the river runs along the Y axis on the third sets the size of the bubble, so let's have a number by you so you can see that these sizes are no longer. These shapes are a longer off the same size or smaller. Some are bigger. I don't think I've ever used us. And I got to be honest stock. I can't really show you try. But it requires a very certain type of data. Let's just see if I can. Okay, so he shows for each neighbors serious the high and the law off a particular of your two for rangers. So he here we've got gold or amounted older i d. So once one is the top off the order amount in the event is the top of the order I d. So imagine you've got Although I d being this much on older mild being that much for this shows the middle. If you're doing the high, low, open, sharp, open close then this is really financial stuff you need, I think four daytime's yes, for data values one which off the high warmly shows the law. So never the minimum and maximum like we got here. But another to which where it starts in the day and one white short ends, how side of the financial stuff work out a way off needing this new work, numerical access. So this shows all of the figures going across an olive because going So this is really why you don't have so much descriptions but where you have numbers. So the numbers in this case on dates. So it all depends on the, um, stuff that you've got, whether any of these would be useful. So these is what Oh, really? Why you have numbers as opposed to date is opposed to descriptions gauges. There's a lot off formatting the least. We don't make these work right, But you see that there's a gauge which goes from, say, 0 to 100% or from 0 to 60,000 Onda Shoals. Your position on that gauge says, like the gauge in a driving car There goes from no to 100 miles an hour. Can't These are time Patriots even started and inundate and also descriptions. So if you use any program packages than you'll be familiar with God charts for no no, I've never used a fall to be honest. Yeah, but these are very for me esoteric things which I am a show all of your different Siri's on how much is in each wall, going up and down. The funnel on the last one is a history, Graham, which shows the occurrence of frequency off each particular things. So between 6000 records, you have this Really? When you start getting into these more esoteric, you really do need to look at your data to begin with and say, You know, what I really want is X y zed and then find out. Is that the child for it? In this particular case, Sister Grams, that can be quite useful. They can say, for this number range between There were 100 orders between which had a total order between 2000 and $4000. There was 50 olders that had a toss a lot of between $10,000. And I think that's actually what this is showing. So that can be useful if you just want to have a knock it all. You have data in groups in buckets, so we're putting all of your holders into these particular buckets between 6000. That's how maney holders there were. So that is a quick look at all of the different graphic 34. Context of charts: right. Well, now we've gone through all of the various child types. What's actually do we want to do? I'm entrance that question in this way. What we want to do is tow have a graph right in the beginning, off the reports on the graph for every day which chills how much was being shipped on by whom and how much in terms of the order. So let's lead to this craft. It's even started it. So we insert a new chart. So on every change off shit beer, boobs, every change of ship fear show the order amount. Now you can, of course, say I don't want the some of it. I want the count. I want the average Oh, I want the distinct count. Distinct counts means that if you've got let's take it the boy around. Let's say we were doing a distinct count off the ship via if you had five parcels that were delivered on a particular day and they were to ups, um, pick up ups, pick up on Federal Express. Then there'll be a count of five. But to be distinct, count off free based on the ship via because they would only be free Actuals servers. Now you can see lots of other things. Minimum maximum. But the rest of them just have a look. You probably will never need them. But if you know they are there, like medium and more details off to offer types of average. This averages the mean so yeah, definitely some average. And you can also show it as a percentage off something authorities presented to the grand total. So I wanted to the bar trout. So every time the's chip via changes, I want the total. So there we go on there is my summary march out. So I'm just going to change this amount off space. We don't need that much space there because so zooming so we can have labels. We can do all sorts of things. We've been through all of these options, so I want to show the value in I'm not okay. But then I want to show it for each day. So what I'm going to do is going back into designed to you on going to copy this and in the group header. Good to paste it right. Let's see what happens. So we have this overall and then because all of these values in this group are restricted by the 19 February 2000 free date, the graph is similarly restricted, so it's no longer she angers absolutely everything in the report, it showing us only that group. So that group happens to be everything that was shipped from the 19th off February 2000 free on here. We can see the detail of that. So if I move on to another bench, we can see that there were full orders on this day to 21st February and here we concede in that. So he really is worth 1000 words. You can instantly see what is on each day. And, of course you need to have this group home. He's day. If you're going to the group expert, we can have it per month. So now we see put for April 2000 and four. How much was delivered in a particular month? Going to that stage? There's May 2004 so what's important is not just that you can critic graph. Here's a graph. Hey, what's important is why you put it has a real impact on what data it uses so I've put it into a group, had a one and all that. The data it sees is everything that's contained in the group had one Imagine that there was an adult it after. Imagine that much. There's this big table which shows everything within this group had a one. This graph is a summary table, Andi. I think it might just be useful also just to include a second chart just here, which is a tie child showing exactly the same thing. So on each change option V initial some off orders. I don't need to Legend cuss. The legend is being shot because the legend is being shown in this chart Expert not is when I got a chart expert, it says, Do you want just this particular instance? I they may 2004 or all of them will. I want all of them so I don't need to shore labels for this. So I had uni strong labels. I don't need to show the legend. Here we go. Just formatted so that I can actually have it on the page. How about that? So there are lots of things you could do, but this is a perfectly reasonable example, wanting nicely for acid in all the rest of it off how you can use chart in the report to instantly summarized information that you're about to see. 35. Subreports - an introduction: My problem is this is getting into a really long report. How many pages do we have? 57. No one necessarily wants to read a 57 page report. Certainly no. Maybe your boss or your boss boss. They want his things. That a punchy. Tell me what this is. Give me a summary. And then if there's more detail, I don't want the detail. So I want the best of all votes. I want a punchy shelter report and then I want details, but only such things if I asked for them now. What you can do, of course, is create not two different reports. You can say, Hey, just summary fault, boss on Here's your detailed report and tears of abroad, but it's not very user friendly toe happen. Yeah, I suppose. I was looking at May 2000 and three. I didn't have to look at one report. The summer report for May 2000 and three and then I have to open another, were appalled to find more detail, and maybe 1/3 report find even more detail is not the best solution. It's only is a solution, but maybe not the best solution. So instead what we can use. Oh, soap reports. Oh, we can use drill through reports. So this action, what we're going to do is focus on off of those both the drill fruit on sub reports on See how we can make the end users experience so much better with us. 36. Drill down: right, let's have log it. Creating a drill through report on Let's see the amount of work involved in at creating a drew for report. But that was it. No, what's required? ELISA some. So we got a 57 page report. Let's say I wanted to create a report which was solely for May 2000 and four. If I double click on May 2000 for notice. We got two types here at third time he's created, which is solely my report for May 2000 forties. A free page report. So that's it. I could just talk about stuff. Now let's say I don't want the detail to be shown for the major would halt because some pages is a lot. I just want to show a lot of grass instead. So let's just work through this. I want to hide the detail for a summary polled, but when I do care about a month, I want to show the detail. So if I go into sections, do you remember I was talking about hide? Drill down. Okay, well, one you double click on the reports were drilling down to a new report. So if we hide a section, but to show it when we drew fruit. So whenever it's Heidi's in the main recall. But when we do a click wanted shown, that's what that means. So what we can do is we can hide, which is a sort of suppress hide details A and details be until we get to the main reports . Do the Drew for report. So we click on details A and we click up Hide. It's not working, and he looks like you can't do it except you can. What? You might not be able to click on details and details being hide them, but you can call me details. So if you divide details or divide group heading to pay and be say, you can't just hide one and not enough on that, for me is a bit of a shame. But you can hide the entire t of the details and you notice that it then shaded. So if I don't have that, let me show you another way of doing that. What we do is we go to design view and we see these details and details be don't you and writing people them and I go hide drew down okay? And you can see it's already done that for details. Be as well. Now, the opposite of high details okay, is show. So you can see what that something is hidden or not. Why you can't do it with here? No idea. But now if I have a look at the reports a little preview, it just consists off the drafts. So that might be your some report. It just tells you at a glance what you want. And then when you want the main thing DoubleClick it's now there for your detail. Isn't that an easy way to create your drool down report? You just hide what you don't want except when it's a fruit fruit vault. Brilliant. Now, just to show you how unfriendly STIs section expert might be have hidden these things, I go back into section expert who tells me I can't do this wave my preview tops so I can't change the drawdown if I moldy drilling down. So how close that time? So you very bizarre that account that so no user friendly, but never mind. Let's say we wanted the graphs to be shown in the drew down report, but not in the main report, so that would give really empty, uh, made report just at the dates. Nothing else. Well, you can't insert sections and then have sexual. Won't be being high drew down and sexual one A. Not because as soon as you do that eight hides sexual one A as well. So what you do to won't be happens to one a as well, so you can't do that way. What we can do instead so we delete that sexual will be is do a second grouping by holder date. So if I do a second grouping by order date, it's all to take that real Andi i group that is world for each visit me to make your each month. I think it's for each that one of these for each month on this one is for each month like that's exactly the same. So we have a second grouping. I can put the graphs in this second grouping, so maybe actually, I'll keep just the pie chart in his first grouping. Let's say and have this graph in a second grouping. Now I can hide group heading to while keeping group heading one. So now in my some report, I just have a pie chart. But when I double click now have a pie chart as well as my bar chart as well as the detail , which isn't that the reason the details and that is because that's also now hidden. So I've got to double click on this to get the detail, so eat double clicking to get you a new report on hides one level. So we start off with prove heading long. And then we have group heading one and two on the with the detail. So initially I hide these two. So make it black. Let's say so. Hold on. Seeing his group had a one when I double click on a group had one what I see. His group had a one that wants specific Group one and group headed to. I can then draw through Group added to a specific group at 22 to show the details. So there's a lot of drilling that you can do. You can go into so much detail going down and down and down. Whether you want to do that or not, is your choice foot. At least you know that you can generate these sub reports very quickly, could have a table with. For instance, let's say you had a report which shows every month all of the individual days, and for each day you saw it see the ship to sit via on. For that, you see the details so he show each level of the detail as you go down. So that is the essence of the Drew crew report. And it looks like you should be able to do it in here, the section expert. But you can't do it for the details on the comments sounded do for the details. Eight is being consistent, so you can do it on details. But you can't do it on details and details be just looks a bit old. So personally, I would just hide it here and show it here. So that is how to create drill down report. You create a master report, you hide stuff on, then people can double click on it 37. Designing a subreport using a Cross-tab: next. I want to talk about sub reports. Sucre polls are a sin name. Suggests reports within your main report, but we've already got lots of features. For instance, we got groupings. We got sorting. Got details behind er for Do you want to you some reports. Well, one possibility is to use a different data set than what you're currently using. For instance, we may have orders and ordering by month, but we might also want in this report to show number off employees paid during the month. So a completely different data sent. Oh, and this is what I want to use this for is you know how we are faltering. This we are used were saying minimum order 2000 itself off. Well, the data so set could be the entirety off the data for that month. Andi. Then we have before us on this small thing. So let's create a new report. So go file a new blank report going to use the oldest. Right? So that's the data set. Andi, what's it going to do? Is a good to create the critic with mint off the graph that we had, we had the graph. We've shape via running across on the numbers running up. But what I'm going to do instead is use what's called a cross tab. So it's not actually a graph. It is a table. Some people would call it a pivot table, for instance. So what? Create that pivot table? Uh, month. Let's say I see how just created for the grand total so we'll put it in the page. Header Doesn't matter where it is. Who so Thai Israel trance again? It's positive. Gets a bit more space for me to work. Also another painted, the report said in all the computers. Quite right. Constant reports header. So put it, groups. We put it into the report editor. There we go. So I'm hiding all of everything s so I want the ship fear going across Andi I want the old dates going down. Andi, I want the total off the shoulder mount. So let's see what that looks like. So we have grand totals and we have the grand total over here over here for each carrier for each date. How much wisdom? So I'm going to juice the size of these columns by clicking on one of these fields and reducing the size of the field do same here when it comes going to format this so that it is zero decimal places. So now let's look at it with thy holy word for that one field. So now I'm going to have to highlight bills free fields on. Do exactly the same. No, let's look that we got So we have a small too report which goes on and on and thereby big might highlight thes taught this total wrong. Andi shade it. Now I'll do it with the mentality off the pivot table. So we have a cross tab expert here which we can also look at, so I could see that its rules going down. So I sold a date. She were going across it. Older amount, lots of ways of doing these things. Onda. We can apply styles. So how will apply so really interesting styles? That seems to be the best way Onda. We can customize the style so we can say that we want the grand total to be in bluish color on and the main body of the text to be white so you can see that formats the table are quite nicely with the title of top. Obviously a lot more, for one thing you could do to this. But the idea is, here is the cross table, which, if you go to cross table expert, you can set up that way as well. And you can customize the total. The older dates columns as much as you want. But that's just, um, juices. Just with further now, one pages, No one page. So I'm going to save this Has a sub report so good. I call it my report. Right. So now we concert this sub report into my second report. 38. Creating linked subreports: right. Inserting a sub report is quite easy. Let's go to the report. Cider Onda. I'll insert it right here. So inset Subject Bolt, choose an existing report on Call it My This is my sub report. Now notice what this is On the demand side, reports similar to hyperlink will just explode That in a moment there was a second ballot box link. We're not going to use us in a moment, so they click. OK, there there's I saw a report doesn't look very attractive, but let's go to preview. So we start off with my self report, we start off with the graph, Then we have the subject bolt that if school life through that, then we get through the main body of the report. So that's how you insert a sub report. But what happens if I Well, that's festival. This is going back into that sub report and say I want to do that option, which is the on demands of report brackets. Quite like a link. And see what happens when I do that. Ah, so it's a soap report that's available, so if I click on that, then it opens it up so that is exactly like a hyperlink. It's not just like a hyperlink. It is either link. So let's close that and get back to that so you can use supper. Polls like hype clings to get to your main report, so you could, in fact, have report which contained nothing except sub report. So I never words. It is a switchboard. You could have 10 links to 10 sub reports and say, Where do you want to start? You're starting here with this suburb, all at the switchboard. Where do you want to go to now? What happens if I move this into the group header? Let's have a bit of space for that. It's trying to escape. Try not to click it. We come, right. So what happens? We have a link for each date for each month. We have a link less careful liquid December 2000 free. And it doesn't just felt on December 2003 a child, you the entirety and, um, what they're saying about some reports could have a different data set. Will this data societies every single thing? So if I just show you what I mean, if I change the holder value here 2 to 6000 on. If I say that the it's only for those parcels which were collected. I got the wrong thing. Cost my pickup. We now have a five o'clock changes. Thank you. We now have a very short with positives all off 10 pages on. Do you can see 200? Beautiful 1000. But this sub report, if I click on it, gives me the four million. It gives me everything so I can get this sub report to ignore the parameters in the main report. All I could get it to use the parameters in the main report. So let's go into this report and see how so you can see change suburb all links. So this is linking to the support so I can link this suburb fault too, on what we're good to link. So let's say the ship via product. Okay, so let's click. OK? Andi. Yeah, I have to close all existing sub reports. So now let's click on this and I only get to pick up. I certainly never words. I don't have to declare a parameter within my sub report the computers intelligent to work it out. So it's filtering the list is using a slight Let's change the subgroup link to get rid of that. And he said, Let's do the audit date So let's click on OK, so now when I click on this report, it just focuses on one DEET. So if I go to the next one, we have a range of three days, kick a lot and it just focuses on one date. So it's not focusing on the entire month, but it's focusing on just one, after all, states in a month. So how can we get it to show the entirety off the month? Okay, for that, we have to get to the next level. We have to start looking at formulas, but you can see from just what you've seen here that we can link all the date Andi shipped via. Let's say to the sub reports that we have we double click on this sub report. It will just look at the picture of the ship via that we were looking out and they will look at a date which is influenced by here. So it's looking at the 11 for February, and it's looking at shipped via now. forced to get the entirety of the month. We now have to look at formulas because we have to be able to find a formula that will give us the month. So if you're ready to go into the next level, we come back to this sub report later. But what we have seen is that we are now able Do you do drew downward falls on wearable to use sub reports. I've as a hyperlink Oh, as an actual report within the main report which can use a different data set. So there's a lot there that we've got potential for really. What you have to do is very careful. Design off your sub reports or where you want everything to floor through. 39. Creating formulas: right, four minutes now, creating a formula is as easy as crossing a parameter. It's just the detail that gets a bit complicated. So to create a formal writing click on Formula Fields, I think new. So we need a new name for former, so dependable against, too. So just call the test on DWA. What do we see? A. For Miller editor on This just looks Really Wow, There was so many things going up, but still too many things. Let's have a look what there is so lesson Know this scientist, For the moment here we have all of our report fields that are currently being used. These are all of the fields, regardless of what is being used or not. Here we have all of the functions, and you can see there's an awful lot off them. And here we have what are called operators. So some of which I will be introducing you to solve its you. Probably l saw beyond this level your adult to scare you, quite frankly, so to create a formula, let's see what we have to do. Let's see if you can create a formula that's very easy. I want the formula that say's the customer I d. I just double click on it Now I know that's what we got here. We have curly brackets around a field. Did you remember we had curly brackets around parameters? So let's see what helps it is Now there's a huge amount of stuff appear, most of it you're not going to need. Let's look it, undo and redo. This has a check mark or tick mark on. What that literally does is to check that the formula is correct. No, I was found my was he fellas to do something like that? It says, I've got no idea what this is about. If I was to, uh, put something like that, it says it doesn't appear to be part of the formula. So checking that your formula is correct is crucial. Now you can save the phone looks any time, and you can also save in clothes. But if you want a new formula, then we could do that. From this drop down and again, we get asked what the former is going to be about. We can also have a look at the range off data. So we confer owls. The data. There's also an icon here for that. So if you want to look at what data we're talking about again, it's not. The full range is only limits 200 or so right, and we could save in close. And now we have our new Fuller. It's not much for formal at the moment. If you just edited, it just gives me the customer Rieti exactly the same as the field. So let's just insert it into here. You could see uncertain to report is one of the options, or I can just drag it it. So there was your customer. Rieti notices. When I hover over it, he says at a test event is saying it's a 41 of the guys. So there we are. We've created our first very, very simplistic formula. 40. Special fields: Now what? We're inserting stuff. Let's just deviate just slightly from formers on going to Special Fields Special Field. You can see what there are. Who created the file who created when he was created. The file path of the name page end of M page number. The print date. The print time Record number. Reckord selection for Melo Report Title. Total page count. So this is very useful. So if I go on to design going to the page footer, I could drag page end off em across. So that tells me what tells Thief Footer that to display page wall of 2 to 2. I can also put into, say, the header, the print date, print time and I can put into the main body. I'm it. Just format this week. Better it said the record number. So let's look at the preview so we could see right to the top of page one. We have what for me is the current date and time on in the detail. We have the record numbers, So 12345 So these special field very useful for just finishing off your report. So records selection formula if you remember that selection is the word for filtering. Let's just put that in, see what it says. So we need to allow it to grow so we can see order. Amount is great down a week to minimum holder value. Andi, which is a parameter, and orders dot ship fear is equal to the ship via perimeter. Do you know what this looks like? This looks exactly what it says. It's a formula. You can ask. You read it. We can read that the orders table and the order amount field is greater than or equal to the mineral water value. Onda the orders dot shipped via is equal to the ship via parameter, so that is how complicated formulas can look. We're looking at much more complicated, but that's not too bad, is it? It's readable, So let's get a bit more deeply into Former's 41. If...: Now let's create a new formula on Good call. This is over 6000. Let's say Okay, click. OK, now what I want is a formula to say yes. Oh, no. If the total older value that's older amount is greater than 6000. So let's try and build up that formula. So it's something to do with God if I you and it's something to do with. If so, that's a conditional comparison so we can see is greater than or equal to. So let's blow this up to take on this so is greater than or equal to six 1000. Okay, let's take check that. No, I was found. Okay, so we save close. So let's insert that into here. So we have is over 6000 to insert that into there. Clue. This particular formula is Opto. This particular order is over 6000. If I had a little say true because I'm minimum order is currently 6000 business change that to 2000. There are true arid 6500 false weighs 5200. That's just edit this so that we could say something a bit over than true or false. Let's use a if statement now an if statement is a control structure. So let's hope, let's hope if this then that over wise this so I want. So currently it says if this is the implied bit, if the oldest amount is greater than I call to 6000 then so true it was safe. So that's what it currently says. Difficult backing you can still see it say is true or false. So hoping you understand this true or false. But first of all true, I'm using as a string, so I'm putting it into double quotation marks. So if this condition happens to be true, then put the word truth house support. The world falls now. It didn't actually need that for what I was doing. It was saying, true or false. But now I can change what's in the string over, um, 6000 Hills. The six. So let's check farmers Fine. Seven. Close. So now you can see that where it's over 6000 it says over weight under it says under. So all of that is just using a simple if if then else Andi, you can see that it's just here in construct structures If you have to be reminded off this Intacs, just look for it on and you'll find it so formulas. Hopefully, you can now see he, especially if you use a selfie like Microsoft Excel or if you used to visual basic Oh, even see shop is not that difficult. It's not so frightening. If, however, you've got no experience to me, be a then I think this is probably the most complex that I'll take it for now at this level . So don't be frightened. Andi, maybe review this. If this is unclear, so if this is unclear, then review this else. Let's move on to the next level. 42. Boolean logic and other Control Structures: No, we can do a bit more. We can say Let's say if the order amount is greater than 5000 or is under free 1000 then no , we want to say out of range. Over Wise will say in the range of 3 to 5000. So let's create a new formula and we call it in 3000 to 5000 range. This is version nimble one. So, uh, pulled amount is greater than or equal to three fast. And then what? We do a Boolean comparison, and the older amount is less than 5000 then in range off 3 to 5 k those outside range. So if the older amount is greater than or equal to 3000 and it's less than 5000 then it's actually in the right range. So let's check that fine. Saving close. Andi, insert it area so we can see that these are outside the range. But if you have a look at some of the ones there are, this one is inside the range. So can you see how powerful all of this is now? This can be expressed in another way. So if I create a new formula in 3000 to 5 or privacy set was is greater than one value. Andi is less than another, but I could say the older amount is less than 3000. The order amount. He is greater than the ankles to 5000 then outside your those 3 to 5 K, so it's exactly the same result. But the office it were around. If I just say that and go back to the here, the first version went inclusive. This one goes exclusive. Is it outside the range? The 1st 1 says, Is it inside the range and say It's inside the range for I was the 2nd 1 says, Is it outside the range? And it needs to be in all here because it's Ivor below 3000 0 it's about 5000. It's not going to be below 3000 on double 5000. That's not possible. Why was for this first range? Is it higher than 3000 or equal to Andi nor than 5000? So both of ills do exactly the same thing, but in different ways. On this is often are you going to find the longer formulas you can do exactly the same thing in multiple ways. Now just a quick look at all of these over a 1,000,000,000 stuff we could say equals two or not equals two less time. No greater done. We could say Excell and not not his. The reverse off. So here, we said, is greater than 6000. But I could said it is less 6000 wasn't no in front of it. So if the farming condition is no true, so is it less than 6000? So that would be true false. So let's say Figure 5000 is less than six fouls, and that's true, then we wasn't not at it. So if it's not less than 6000 now, it becomes false. So if the amount is not Lesson 6000 and say it's over 6000 it gets complicated. Basically, you can have full is a simple as you want it, and I would urge you to make him simple. So that is the entire list off the 1,000,000,000 functions that you've got. I just realized with not you have to use brackets are in some programming languages. You can say this, but when you click on that and you notice it's not working, so we're not need to use brackets used buckets with any of these functions, but not it looks like it's more compulsory. Let's put it that way before I leave this topic. There's also what's called program shortcuts. So programming show like the double I f. For this would be that if holders amount is great in the 3000 in less than 5000 comma in range 3000 5000 comma outside range. That's an alternate way of doing exactly that. You've also got to choose and switch. Choose Saiz. Give me a number. Say, from what? 10. So give me a number. Andi, I will give you on help book based on that. So if I say that, then the number is free. So how will give you the results off the 3rd 1 of this? So that would be able to see. Similarly Switch switch is a short cut for its so switch, Um, his equipment off. If this then give me that. If this thing give me that if this thing give me that. So if this is great, man, 6000 men say presents its 1000. If it is greater than 5000 it could be five is a anonymous number of eggs. So, personally, I don't stick with the this one unless you're more familiar with one of the over three, but just let you know it exists. 43. Text functions: right. Next. Want to look at the stream functions on? A lot of these will be quite familiar to you. If you have used so many. There's so many different languages. Excel and visual, basic and C sharp. So let's just have a look at some of the highlights festival. You have the cases, you have a lower case. Did you have over case? So that changes a string from, say, a law case. One changes it to, uh if use upper case. There's also a proper case. Know what? Sniff? If you haven't a clue what I'm talking about, it's all of this veld. Well, let's have a look at the Korea website. So if I am sure all the Koreas you can play. We have all of this in lower case. But what if we had a under this being the Korea website memo? What if you wanted it all in uppercase, so call this. Okay, we could say I wanted the other case off the Korea. What's the dough? It is Here we go. So test that it's fine saving close drug it in. You see, it is nothing more than the copper case of that. But that's exactly what we wanted. Alternatively, what if you wanted the proper case? So proper case is why every sward has a capital letter and all the office don't. Alternatively, there's a lower case as well. What if we just wanted the first five characters? While that's what left is all about, you can see left is the string. So I'm usually a field, for instance, on the Lender family characters who want always check to see if there's any errors, so that gives you, in this case, the 1st 5 characters. You can also have the mid So why you starting? So stop into the city character and go on for five characters, say, or you can have the right so you have the right, uh, five balls characters. So there's the right to five most colleges. Trim, trim left and trim right. REM use leading and lagging spaces. String reverse sounds great, but I never come off with the reason for needing to reverse the strings. So instead of a B, c D e d c B A in stra is used in Boolean. It's a Boolean operator, he says is one particular string contained inside and of a string. That's what instructor is in strength. Join con Katyn aids. But there's also overcome coordinations, which means joining a string. So, for instance, you can have order, value and all the value so that no value could. Website ankle your website. So if I close that you can see we have the website repeated, which you could also do with replicate string replace Um uh, some languages of in Colditz Substrate Inc. No, that's left replace replaces the occurrence off something with something else. For instance, if I wanted to replace in the Curry, what signs the haste continue to be to be docked? Just admit it that will search for instances of w to be to be dot and replace it with absolutely nothing. So they now you can see here city people. That's a flash ups dot com. It's found www dot andi got rid of it on, but I think those are the major ones that there are vile converts, a string into a number, a numeric text. Does he have a way in string? Reverse does the same as in string. Accept tickles From the end of the file end of the string going backwards length notices. No l e n with some languages used. Lend tells you how long a particular string is. Include length off website. So 20 counters long. 23 characters long on. That is basically eight. It's really a case of now going. Okay, this is what they can do. What do I want to do? The formulas will help you do it. Do you want to con Katyn? 82 fields together, for example, When you can do that in a formula. So I'm showing you all of these tools. But at the end of the day, you need to know what you want to do and then do it. You'll find out how to do it. So these are all of your weapons. What do you want to do? 44. Mathematical functions: right mathematical functions. Andi. These are very much like Excel, so you don't have to learn a whole new set if you know, excel. So let's just see what we've got. Well, first of all, you got the operators, plus divide by two, the power off inter to divide. So, for instance, five into divide by two is two model ISS five. Model two is one because five divided by 22 remainder one multiply negate, which is minus sign in front on subtract 1%. So what is X is the percentage of wine. You also have these comparisons Onda de eighth and self off for using with comparisons. So let's have a look at the functions. Ceiling floor and int are rounding functions as well as round, so seeming goes away from zero floor goes to world zero on Now you can also do it to multiples as well. This is getting a bit complex, but life, for instance, floor off 5.5 equals Phablet sequel. Fine, but floor 5 25 to the names multiple of two is for, because it yeah, it's whichever is the multiple. That's closest. So. 2468 So it's a little complicated floor. So normally floor goes toward zero ceilings. Go away from zero to the nearest integer, but sometimes you can make it to the nearest 10. All the name is 200 integer int. Just try Cates. Uh, while I see it doesn't quite feel great. It cuts. It goes down to the nearest integer, so it's very similar to floor except the negative numbers on truncate that deals just get rid of everything after their full stop. So, for instance, seeming of 5 to 5.5, let's make it five or six is six. Floor, 5.6 is fine. Inter fighting six is fine. Andi Trump eat, but six is five. If you look at those in negative terms with ceiling of minus five is minor. Sixth floor minus five, minus five. The paint off minus 5.6 is minus six, and the trunk kato minus 5.6, is minus five. They all react in certainly different ways, depending on whether it's positive or negative number, he feels on sure tissues into if you want to go down or truncate. If you want to go toward zero, A. B s gives you the absolute vice or the absolute avoids about the minus sign. So a Bs all frequent five is frequent. Five. The A BSL minus 3.5 is plus frequent. Five. There we go, things like 80 and calls sign to seem turn those are all to do. We've trigonometry. If you don't know much more, Trigon Ality, You never need to use them sign. That gives you the sign of a number so it returns minus 10 or one, depending on whether numbers below zero exactly zero in front of zero. Your positive number. So, really, there's not much in terms off mass. There's also financial stuff. If you need that good luck, because it's all to do with lots of stuff that I don't know much about. But then I'm not a financial person, never needed to use them. Apart from calculating and trust rate, there's also type conversions. So if you need to convert a number to a string, if you need to comfort towards a date or if you converting from a string to a number, then these will be very familiar to especially to nothing. C sharp users that how off? How often do you need to do that. Well, I suppose if you're saying the I d. Is does not the customer I d like exactly Don't need equal science and you can see a string is quiet here, so we need to convert that to a strict on. We need to open brackets around it. Very good. So these conversions are only needed if you are mixing two things okay in the same formula . So let's look at that. So the customer I d is 41. That's a string on. That's a number converted to his drink. Now, the greatest thing about all of these formulas is time. You need time to explore them so that you can see what there is. So all I can do is give you a summary of them, and then I can use functions in any later videos. 45. Summary functions: There is also a range off functions in the summary, which are particularly due mostly to numbers, but not always. For instance, some said this can some a field, so give me the some off the order amount on. Put that into a group heading, for instance. They'll give you the totality of the older amount in an entire group. Or give me the average or give me the count. And, of course, Count doesn't need to be based on a number can be based on a text field, but most of these are numbers. Give me the minimum maximum median world. That's average, of course, is the meat. Give me the second largest, the second smallest, second, most frequent. Give me percentages off average count, distinct count maximum minimum Salman itself off. So there's an awful lot here, but really, the only ones that you be using the most of time are some coverage on account. So give me Give me at the average off the old of that amount. So what is the average for all of the records giving the maximum of it the minimum off it? So all of those are contained in summary 46. Date formulas: now the final set that I want to cover at this level. Ah, the data time functions. There are an awful lot of them. And there's also something date Rangers. So let's just commonly have look at some of these day of week. So that returns a number based on what day of the week it is. And for all of these, by the way, if you're going, what does he return to Skip Commit Press F one and you get God might help so and also a few examples. Day tells me what day of the month this is. Date. I can convert, say that into a proper data sixth off every 2000 for and you may have noticed date being used internally by the computer as we went month is an extract turn off. The month has his mid minutes extraction a minute second and, yeah, date that. That's allows us to add a day or month for a year, several years to a particular date. What's the difference between a date or two dates in terms of days or months or years? Addictive on a date? Part again extracts part of a date. Dig cereal quite like, ERM and you can see it's quite similar to date, year, month, day converts into a day on. Then we have some more esoteric things, like convert the number off a months like 11 into his name. November check reversed. Ring on number is convertible into a date or not, that's actually converted. Just equivalent. It is. You're not just you remember that there are three conversions here in the type conversion convert to date. See, date on. We have similar things for times that you got for dates. Now date Rangers is a particular date within the last 30 days. Or is it for two days before that? Or is it for two days before that is if after today's it after tomorrow? Is it up to today? Is it up to yesterday? Is it in the first half of the year? Is it in the second quarter of the year? Is it in the last 28 days or roughly? The last four weeks of Sunday is it's in the last seven days. Is it a last four month, full week month to date, in the previous Yeah, year to date in the previous year? Is it over 90 days into the future. Is it 30 days into the future? And my goodness, there are so many. But that's good. We don't have to use them, but it gives us a shell cut. So, for instance, if we want to test whether a particular date is within the next 30 days, we have a formula for that. So I'm not asking you to memorize all of these on just asking you to see Yes, these are available. Good. Incidentally, there is an over 90 days which really should be aged 90 days on more so that they're all in the same section but decided to call it over 90 days. So I'm real soft. Current date and current time. How can we use this to solve our current problem? Our current problem, if you remember, was tying a subroutine so sub report into our main reports so that it gave us the entirety off the month. So have a look at so the maybe pulls it and see if you can work out how we can do that. Answer In the next lecture 47. Solving our subreport problem: So let's just review the problem that we've got far Subpart. The problem is, don't we are having this group heading in months, but it's passing on a single day into the sub report. So instead of seeing all off the values for December 2000 and three or February 2 files and free well, it's only passing on a very limited number there. We can see it's just one date on this Cleary more than just that. So there has to be is a coordination between this report and the Jufer report off the sub report. Darcy's being uses hyperlink. Incidentally, a Jewell down report is why you have hidden certain items and they expand Onda drill through report. Here's where we can go to a completely different report in case you're wondering what difference in technology. So as I say, we need a common factor, which is the entirety off the month. So what we can do is have a formula that has the first day of the month in both this report on the super part, and so they're linked together. So let's find the first ever month. So you called his first day. Okay, now it's going to be based on the order date, and we could see whole of date things here. So there are very clever way to do. But I like a not so clever way, perhaps a bit of a knock away. I use the date to cereal and Sadiq cereal bodes off the date until a year, month day. So I say, What is a year off? The altars date? What's It's a month. Give me the first day. So test to make sure that's fund. No, that's funny to find, so I'll copy that. So that's saved as first of month. No clothes this turn into a closet. Maybe not, but I need to get to my eyes my sub report preview. So I need to get to my sub before, so I probably need to open it. So let's open it up. There you go. There's my support, right? We'll have a formula here as well, so the new formula is first, and it's going to be exactly the same. Using gold to settle to date. That's fine as well. Same clothes. So if I had wanted to, I could no put this first of month into the cross tab if I want to do so. If I could just drag it into here so you can see first of February 2000 free, it's come out of all those university December. Let's come up of all those. So I'll say this and close this. Now I need to redefine the links between this report and support, so I don't want anything to do with the older date. Instead, I want to use the first day of month, and so that is going to go to the first day off a month. Let's just see if it works. Okay, I know it's not working, so you could see there was a problem that when I try to access it. So let's reimport the sub report because we've changed the subject port. So maybe there were problems there, so he just needs to be fresh. So let's now change the links again. And so take out first report and put it back in on. Now that's better. So can you see the problem? This was just said with Poor Fields is now saying at first of month at being the simple for a formerly formerly for a former lake field So you said changing data. So now, in fact, we could place. It'll take on this. I get everything which is in the first off. December, right? So with that, we get the correct man from this support. So that's how we do it. Using a formula that's common to both reports. 48. Using parameters for conditional formatting: Now we know how to do formulas. We can now have a locket. All of those fuller things. That's it. Eggs, too, that we didn't know what to do with. But now we do. If you don't want to talk about that, some look at an example we're writing. Click on this and go to former field, and we have a look at, say, the border, the background, which isn't part of the border. But, you know, says this ex to next to it, so that means we conform at it. So let's say we wanted it to be for mattered raid in a bread background if the amount was more than $10,000. So we take the background, but we don't use any of these. We got two x two, so that gives us our former editor. Now. There's a very hopeful thing down at the bottom. This conditional formatting formula must return one of the following color constraints. CIA for cola Black Maru green olive navy, purple teal simple right lying yellow, blue, fuchsia, Akwa white and no cola. Or you could say color and then number between north and 255 represents right now Institute 155 green and one for Blue Onda fruit. That she gets access to all the 16 million because on distantly, that is a remedy to comment to remark. It's not actually being processed safe to keep that in. And in fact, if you do want to have to comments into your formulas, that's how you can do it. So what I said is, if the total amount it's more than 10,000 we wanted to people it read. So so the older amount is greater than 10,000 sin. We look up red, which will be see All right, house. I see a no color is that you know that color is spoke. The American revolt, the letter you. So let's test that, See if it works. Fine. Saving close on notice that this has changed. It used to be a pencil lying on its side, doing nothing. Now it's a pencil active. The fact is, ready is completely coincidental. So click OK and see what happens. So now we have conditional formatting. We've used a formula to say anything that's more than 10,000 make red. Similarly, you know. Do you remember that too? We have a record number. And if you Ferguson, how would you not? That's remind you. We used the special field record number to just ascertain a record number. So I receive 15 16 17 18 19. So how can we use that? Well, let's go back to one of our simpler report. Let's go back to this report. If you just remind yourself of how this looks. Here we go on. Let's aunt the special field record number. So 12345 What if you wanted to say we want every even record number to be? Have a background color. Okay, so going to design view behind like the lot we got four months objects go to Boulder. Background tick x two and we do brought in something. Festival. We have to find out. How do we get the role number? The record number. So I just collapses. Why did you send that? Might be? Well, there were two possibilities. It's either in document properties, which it isn't because that's actually about the document. But it's a very useful things are going to or it's in the print estate, and here you could see things like the record number. So we start with beef. The record number. Okay, if it's even how we're gonna do that while there are several ways but one of the easiest, perhaps is to use the module ISS. So if that remember that model, it is what's left when you divided cellphone since five, divided by twos to remain the one so one of the modular. So the record number more to zero. So no words for when you divide the record number by two, you have no remainder. Then let's choose. Um maybe see on yellow. These are all of the clinical strength. That's a yellow No color. So no, I was found seven Close. Okay, going to preview. And now every second row. He's colored. Of course, that would look so much better if we didn't have this group head a group photo class cells . So now every single second row, the fields, that is his colored. But what about if you wanted the natural roll itself to the details? Well, if you go into the section expert, we have a background color and guess what does the next to their. So let's do exactly the same thing If and I'll do it a different way just to show you more than one way. If the Inter Jenna home record number, divided by two, is equal to the knowledge of division, it's indifference. If you find you out of a to, it will be 2.5 on into 2.5 is too. So too will not be equal to 2.5. But do the same with six divided about you and I'll be fine. So then and choose a different color gray else. Devi. No cola. Okay, so right Good seven. Close. Okay, let's see how that looks. It's just now after do not realize it didn't actually slept the details section. So let's just quickly do that again. Coughing based way go So seven close preview and you can see that it has gone yellow all the way across. So it says silver. But the details says yellow Oh, no color. Just so if I change that to Green, it's even close. So the fighters say it's over. Here is of no relevance. It's going to be green, always going to be white, but you can see that it's also yellow because we still have this formatting on the fields. So we can I take us off. So, uh, for much objects on de select background to go hold on to do it individually. Oh, we can make the background the same color yellow so it's easy just to do that problem Details changed back to VB Yellow way Go So you can see what happens if one conflicts with the Noah. If you got one saying that background color is gonna be green and another saying the background bill is going to be yellow where they can only be one. So try not to use this trick in more than one place at the same time. So we have several ways off finding out where we can change the background color. We can way change the background for several reasons. We have a chance of background color because it's exceeded a certain value, or simply because we wanted on every of a role every third row. Regardless, just have a look around now, and the sheer quantity off all of these X tubes so you can change the font based on what's the value off this field or indeed, any formula. There's so money. You could change the price, for example, so there's so money. If you just look around, you'll see that there are lots of ways you can use formulas without actually setting up a formal formula field. So whatever your mind says, I want to do X Y set, you probably can with formula. 49. Advanced Filtering: now, just before I leave the topic of formulas. And by the way, this is the quick button to get to the formula workshop, says FX. There is one thing I want to just touch on with regard to this liked expert record. Well, first of all, do you recognize this now? It's a formula, so now you can edit it, has a phone if you want, so you can do your photos based on formulas, and so you can recognize this is a parameter that's the parameter that is a field, and that's the field. But it's far from that. If I do a filter on, said ship date, we've got the usual things, the equal one off, less greater done between on formula. But we've also got in the period, so we've got the month today, the year to date last seven days. Last four weeks, a song last four week, last four months, all days to tomorrow. Does this look familiar? If not, it should, because it's identical to the list that we had in the Rangers, except it's differently. Organized this time. Over 90 days is included with the aged not to 13 31 16 so forth there. He's in the right, told, or at least what I think, right? Olders. So that's he's just a tippet that when you do dates, you can do it in a particular periods between now you are based on now, so you don't have to do a formula for now. Use like, for instance, whole days from today you could do a former to safe is greater than the current time. Or you could just say it's in the period. All dates from today. So that's just a little bit of advanced formula work, which, because in quite nicely with what we've learned now from formulas. 50. Congratulations: no congratulations minus to get through toe on what? It cheaper, that is. Let's just think about what you have accomplished. You've installed crystal reports on your machine for free. You created your first report and you've added grouping salting, filtering, formatting, permissions, graphs, charts of reports, Andi formulas. And I think by now you're getting very confident. We've where everything is your goal. Yes, it's skeptical icon. So what do you go from here? No festival. If you have completed all the lectures, you should be able to download a certificate half completed. Secondly, there is no better way to land that I actually do it. So why not take your version of crystal reports and stop creating reports that group and get started, get selecting and formulas. But some reports make them interesting. Got color at grass. Crystal reports is now available for you to conquer. What have you go from here? Thank you very much for joining me and enjoy crystal reports.