Crystal Reports: Exporting Memo Fields to Excel or CSV

A Chelsea Technologies client report had a table with a memo field with several lines of data in the field. When we exported from Crystal Reports 2008 to Excel Data, the field value was ignored. In Crystal Reports 10, we got all the text from the field, but with the CR/LF characters removed. Neither was what the client wanted.

Our Grid User Function Library helped develop a solution here. It is a powerful and cost effective solution to extract data from your report into a structured format like CSV or tab delimited data.

This example creates a CSV file with three columns (Record ID, Name and Memo Field), with field names in the first row. It also assumes that there is on average, 10 lines per memo field. Increase this if you have lots of lines in your field.

The {@ClearGrid} formula goes in the Report Header section:

WhilePrintingRecords;
Global Numbervar StartLine :=0;
GridCellClear (Count({Table.id}) * 10, 3);
GridCellLoadRawValue (0, 0 ,"Record ID" );
GridCellLoadRawValue (0, 1, "Name");
GridCellLoadRawValue (0, 2, "MemoField");
The {@BuildGrid} Formula goes in the Details section of the report
WhilePrintingRecords;
Global Numbervar StartLine := StartLine + 1; //Use Next Line Number
Local Numbervar i;
Local StringVar Array Work;
GridCellLoadRawValue (StartLine,0 ,{Table.Record_Id});
GridCellLoadRawValue (StartLine, 1, {Table.Name});
Work := Split({Table.MemoField}, chr(13) + chr(10));
For i := 1 to count(work) do
GridCellLoadRawValue (StartLine + i -1, 2, Work [i]);
StartLine := StartLine + i -1
//Need to start next line after the memo field
The final {@SaveGrid} formula goes in the Report Footer.
WhilePrintingRecords;
GridCellSave ("c:\ProgramData\Output.csv", ",")

Place the Page N of M special field in the page footer to force the processing of all pages, and every time you view or refresh the report, the file will be recreated.

You can also export part of a report:

The report looked great. It had many pages showing each of the invoices prepared over the previous month. These were exported to a PDF and sent to a document management company for storage.

But then came the challenge. We needed a second document to list each invoice and page number of the master documents they appeared on.

Our collection of User Function Libraries came to the rescue. Using these inside a formula can create a disk file while the report is processing.

• Our File Management Function Library can write a simple text file.
• Our Grid UFL provides you the proper control of your Crystal Reports Export.
• Our XML UFL  library can write a xml document with the required information.

As they are all "WhilePrintingRecords" formulas, they can accurately include information such as the page number and print time.

Now, here's something else to consider. If you are exporting data, there's a fundamental problem. Either your report users like to waste time, or you are delivering formatted data instead of business intelligence.

You need to ensure your reports contain business information, not business data. They need to be decision tools, not intermediary data sources.

To get there, start by determining what business questions need answering. For example:

• Which employees bring the most value to the company? You have various data at hand to help determine the answer. The "layoff by spreadsheet" method focuses on labor cost per hour. It ignores that Employee X worked hard on his own time to get a particular idea adopted, and that idea now brings in twice X's salary in profit each year. It makes sense to keep X on the payroll, even if X gets 11 months of paid vacation each year. It's likely that X will keep producing such ideas, simply because X has already proven he's the smart, tenacious person who makes far more money for the company than he's paid. But the spreadsheet won't show this. A report can.
   
• Which customers produce the lowest net profit? The dressed up data approach looks at only part of the picture. For Example, ACME Holdings spends $1 million a year with your company. The nominal profit margin is 22%. Looks great on a spreadsheet, and you have the data to back it up. But what you don't have is the cost of servicing ACME Holdings. The sales people love ACME Holdings, but the support department hates them. It turns out that it costs your company $1.03 for every dollar ACME Holdings spends. A report that provides this business information helps your company tremendously.
   
• What is the real compliance cost of Sarbanes-Oxley? This legislation is hugely expensive. The GAO reported that it costs the top ten USA banks $83 million a year to comply with it. Corporate boards and stockholders really have no idea how damaging S-O and other misguided laws are. Nor do your company's customers understand how much they pay for these laws with every purchase.
  
  Yet, even years after it has passed, there is no evidence that S-O does anything to prevent the crimes it was intended to prevent. In fact, S-O drains off resources that could be used for that purpose. S-O isn't even aimed at where the problem really is. Getting such nonsense repealed can happen only if a large number of people can clearly see how deeply they are gouged to pay for this useless law. As the true compliance costs also include other factors such as opportunity costs, you can't extract this from any database. You must calculate it.

When people want to export data to Excel, they are usually attempting to answer questions not directly answered by the data. In the examples above, you would have to perform analysis in the report, using formulas and other tools available in the reporting software.

The data approach

The normal approach is to start with the database and push data to the users. No matter how you dress up data, you still have data. You do not have information. Data are raw materials and information is a finished product.

Suppose you walk into a Toyota dealer and say you need transportation. Do you expect to walk out of there with a container of parts, or to drive out of there in a car? That is the difference between data and information.

When you shove data at people, they try to assemble the data into information. Which is why you have all these folks wasting millions of salary-hours manipulating spreadsheets instead of doing the jobs they are paid to do. Which is why you have information silos. Which is why you have all sorts of other problems, which is why your job is less secure than it should be.

End-users need analysis, trends, conclusions, snapshots, summaries, thumbnails, overviews, projections, comparisons, and other things that are very different from data. When they don't have those things, out come the spreadsheets.

The report approach

Instead of starting with the database, start with the business questions. Talk with the senior executives (who may or may not be on your existing distribution list), and ask each one to tell you what the top three business questions are. If they give you more, that's OK. Compile a list, and see what data you would need for you to answer those questions with your reports.

Next, repeat this process with the people who are already on your distribution list. You are now ready to determine what will be reported.

Why talk with the senior executives, first? Those are the folks who run your company, so figure out what they want. Providing that helps secure your job and future raises in no small way. But it also helps you build the correct framework for your entire process, so that all users are marching to the same tune. You have to start at the beginning, not in the middle. By definition, middle managers can't see the big picture.

This raises another point. Surveys conducted between 2005 and 2008 showed that senior executives rarely have an accurate picture of their organization or the conditions under which it operates. They have a much rosier view, because people generally try to please them. These same people lack the time to dig through the data to see the real picture. In most companies, senior executives also lack the skills to do so. This means the data-oriented reports they get are essentially useless. If you are in charge of those reports, what does this say about your value to the company?

If you provide the senior executives with the business information they need, and you provide middle managers with the business information they need plus the business information the senior executives are working from, how do you think this will affect the effectiveness of management to make good decisions?

Instead of working from the detail level up, work from the information level down. Determine what information people really need. Then, use the power of Crystal Reports to assemble that information from the data you can get.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.