Subtitles of the Movie

In this movie we're going to go over the Options when Exporting to Excel. Let's go back to our Rough Report Format Support file. Let's take a look at our Preview and let's go ahead and Export. Now there are two different options. One you get with Excel 97-2000 and then the 97-2000 with Data Only. We're going to look at the first option. You can choose to Show the gridlines if applicable. You can create Page Breaks for each page in Excel, and Convert date values to strings, which sometimes comes in handy every once in a while. You can also choose the Column width based on the whole report. Now in this case you've prepped it, so you've chosen your Column width and keeping the whole report is good. You could also choose the Column width to be constant in points. The default is 36. I kind of recommend against this simply because header fields sometimes get cut off when you do so. And then the other option is Export page headers and page footers once per report, or one on each page, or you can choose None. You can also choose the Number of page ranges. In our case, let's go ahead and just do one to one. You can also create Page Breaks for each list, we can Show gridlines, even though in this case it's not going to matter; it's more for the Cross-Tab, and we can Convert the date values to strings, but we don't really have any date values so it doesn't really matter. We'll just call this Page 1. Notice it exported a lot quicker because it didn't have to go through all the records, and when we pull it up we have a nice clean formatted report that should be exactly one page long. Notice in Excel this here is our date range. Sometimes it's easier for date and numbers to be expanded in Excel than to lose real estate, if you will, on the report design page. Now, let's go back to our Crystal Report, let's go back to our CrossTab Report. This is where we need a little bit careful. The first option is fairly straightforward for both Cross-Tabs and regulars, but you have to be careful with this Data Only. You have a whole new set of options. One is Export Object Formatting, which you're familiar with. We can Export Images, we don't really have any, but you have to be careful with Use worksheet functions for summaries. Now in the case of the Cross-Tab, we'll select this and you can Maintain relative object position, it's just formatting. You can export the Page Headers and Footers, or show the Group Outlines, and you can Maintain column alignment. All of these are kind of things you need to play around with. I find they're not helpful but there's one you need to be careful with, and that's the Use worksheet functions for summaries. Now if I press OK, and I'll say Cross-Tab Test and save it, it's going to export it. I'm going to go to my Desktop, open up my Cross-Tab Test and it said it Exported the summary functions as functions in Excel. In this case, that's just simply not true. I, myself, have asked Business Object Support several times for an answer to this question and haven't received a satisfactory one. But let's go back to our Crystal and try a regular report, and in this case we're going to Export it and we're going to do Data Only. Now I want you to observe what happens when you take a regular report and we go Data Only. We're going to Export Object Formatting, and we're going to use the Worksheet functions for summary as a test. Everything else we pretty much lined up so we can use the Details. And notice in this option you don't get the whole report option, you have to choose one, which is why it's important to line up all your fields, make sure they're the same width and they line up with the guidelines and so on and so forth. Otherwise, you get a report that is even more difficult to format. And let's go ahead and press OK. In this case we'll call it Rough Report Test and save it to the Desktop. Now that the export is complete, we'll go to our Desktop and open it up. Notice how we did pull some formatting through, but it's a little bit off, and we also got a lot of our field headers cut off Ð not cut off completely like before, but just kind of sized incorrectly. The thing we're looking for here is when we scroll down this could possibly work for you, but as we pass into a new group we now have this sum, and it's summing F, column F, for this range. You have to be careful with these. Sometimes Crystal exports these fields a little incorrectly, especially in earlier versions. If you're not on the latest and greatest service pack I would caution against it. I would argue that you should export the Value as opposed to the whole Formula. Also, if you need to change the Formula once you're actually in Excel things can get a little bit tricky. This is just one of the things to kind of watch out for. Notice how this report is fairly clean as long as everything is lined up. If it's not well, doesn't really work out that great for you. Exporting to Excel is something that is not so much technically complicated as it is an exercise of patience. If you don't have a whole lot of time it's probably not going to work out too good for you, but if you do have the time to dedicate, it'll save you a whole bunch of time on the other end. If your report comes out near perfect, or perfect, that's less time you have to spend actually formatting it for people.