crystal reports - using excel as a datasource - how to use Excel data in Crystal Reports tutorial

next I'd like to show you how to make a report from an Excel data source so once again we're going to make a blank report or you can just do file new blank report either way is okay now uh in this case if the Excel spreadsheet is already on this list over here where it says my connections then you can just reuse that as long as the data set hasn't moved but really I want to show you how to connect to an Excel spreadsheet that you haven't used yet uh in the crystal report so in this case I'm going to say create new connection and then you can see there's an access Excel category I'm going to pick on that and then we're going to say make new connection I'm going to double click on that for your database type notice how there's different versions of excel the reason for that is over the years the file format has changed with the different versions of excel you have to worry about the bottom one the bottom one is going to go from Excel 97 to Excel 2003 notice how I did not say Excel 2007 for whatever reason this even the newest version of Crystal Reports which is what I'm using um doesn't work with Excel 2007 files so if you have Excel 2007 you want to go into Excel open up that spreadsheet and save it as an Excel 97s 2003 type of file because Crystal Reports will not uh even find the ones that are from Excel 2007 so uh I'm going to pick on Excel 8 here and then we're going to do a browse then we're going to scroll down and you're going to find your actual Excel spreadsheet so I have one that's called um Europe expenses actually before I go into Europe expenses here let me give you a quick look as to what that looks like in Excel so I'm going to go into uh Excel here and I'm just going to pick on file open and I'm going to find one that's called uh Europe expenses uh so here it is down here Europe expenses uh let's go into that okay now notice this spreadsheet notice how the first row contain your field names so if you are going to use Excel in your uh as your data source for Crystal Reports I would recommend in fact really the best way to do it is to have the first row having your field names and then each row uh after that is a different record each column is a different field uh now um we can we can get information from the different sheets uh during the crystal report process I'll show you how to point to specific sheets but when you use that data it's very important that the first Strow contains your field names like this one I'm going to close out of excel here by the way um when you look at our uh skull caddy product there'll be many many other videos about Excel but here we're trying to use an Excel file in a crystal report so let's go ahead and pick on Europe expenses and we'll pick on open by the way many of these files should be available in the sample files folder uh or the sample file section of uh skull caty so now uh sometimes an Excel spreadsheet requires a username and password this one does not but if it did we would click on that box and fill in the information over here I'm going to pick on finish now let's see what happened here's the Excel spreadsheet so each different sheet comes up over here as a potential data source and we can use any one of those uh actually in the report another thing that would show up on this list would be Excel range names so uh if you had range names they would show up here as well as potential data sources if you're going to use the workbook excuse me if you're going to use the worksheet as your data Source then the first row of the sheet has to have your field names if you're just going to use a range name for your report then the first row of the range has to have your field names uh in this case I'm going to use the sheet I'm going to pick on that and now you can see it's over here actually I double clicked on that now um later on we'll have videos that I'll show you how to include more than one table but this report is just going to have the one table so I'm going to click on okay now we're back to design view uh so in this case I'm just going to expand my field Explorer there's the the Excel sheet I just used I'm going to expand that and then there's the field names where is it getting the field names from from the first row of the Excel spreadsheet like I just showed you now we're just going to move our fields in so I'm going to pick up date and move it right in there then I like to get the uh let's say uh the country I'm just dragging these fields in the expense type and then I'm going to scroll over and maybe I like to use the US dollar amount uh now there there's going to be other videos that'll show you how to enhance these reports of course but at this point I would just want to run the report by picking on view and then print preview that's one way to run it and just like that there's the report so this is an example of how we can make a report from an Excel data source to start a new report with an Excel data source you're going to say file new blank report and then uh you're going to come over here where it says create new connection you're going to use the axis Excel category you're going to pick on make new connection on this screen for the database type make sure you change it to excel 8.0 which really goes from Excel 97 to Excel 2003 and then you're going to pick on browse and point to your exact Excel spreadsheet on your computer and that is how we can make uh a crystal report come from an Excel data source