Ever wondered how to use Crystal Reports shared variables in formulas efficiently? This article assumes that you have pretty good understanding of Crystal Reports basics, and some understanding of formulas. It shows an example of using shared variables in sub-reports. It is based on a recent example of OE invoice form modification to show the list of shipments at the end of the invoice. The modification required the use of sub-reports, shared variables, and some intermediate use of Crystal Reports formulas.
Requirement
The customer invoices multiple OE shipments together in one invoice. They want to see which shipments are included in the invoice. They requested that the list of shipments be added at the end of each invoice, and if possible, show the shipment numbers in one line.
Implementation
The version of the invoice form the customer used is based on data-pipes, which means it is not possible to add new tables to the main report directly. The use of sub-report is required. A new sub-report is added to the main report. The sub-report contains only one table – OEINVD, which is the table that contains invoice details. One of the fields in this table is the shipment number – SHINUMBER. The invoice may contain multiple lines and some of those lines may be from the same shipment. Therefore, while generating the list of shipment numbers, we need to remove the duplicates.
Sub-report design
Here is how the sub-report looks:
There are three sections, Report Header, Details and Report Footer. Each has a formula, but only the Report Footer appears in the main report. The other two sections are empty and are only used to execute the formulas that generate the list of shipments.
Formulas are executed in report sections from top to bottom, so it’s important which section is used for each formula. The formula in the Report Header section is executed first, followed by the formula in the Details section (once for each detail line in the sub-report), and finally the formula in the Report Footer section is executed.
Shared Variables
Crystal reports formulas allow you to declare variables that can be used inside the formula. In this example, however, we are going to use shared variables. Shared variables maintain their value across all formulas (even across the sub-report and the main report). Therefore, if you declare a shared variable in one formula, you can access its value in a different formula. We are going to use this functionality to generate the list of unique shipment numbers.
We’ll have a look at the formulas next.
Initializing the List of Shipments
First things first, we need to initialize our variables. This is done in the Report Header section, in the InitShipmentNumber function, which looks like this:
The “shared” keyword makes the variable shared, the “stringvar” tells Crystal Reports that the variable is of string type. Finally, we assign an empty string to the variable. The second line just returns an empty string as a formula result. This ensures that nothing gets printed in the report header section.
Compiling the List of Shipments
Next up, we need to build the list of shipments in the invoice. Remember that there may be multiple detail lines from the same shipment; we want to list each shipment only once, so we need to check if the shipment already exists in the list, before adding it. The formula for this is in the Details section of the sub-report and looks like this:
Let’s take a look at it. The first line declares the shared variable. It is exactly the same as in the first formula, except it does not initialize the variable, only declares it. This makes the variable visible in our formula.
The second line declares a new variable called “Shipment” (not “Shipments”) and assigns to it the shipment number from the current report line, while trimming spaces from it. Notice that this is not a shared variable, so it’s only visible inside this formula.
The line with the “if” statement checks if the shipment already exists in the list of shipments. It uses the built-in Instr formula, which searches one string and tries to find another string in it. If not found, it returns zero. We then add the current shipment number to the list in the “Shipments” variable, separated by a comma.
Finally, the formula returns an empty string again. The purpose is to hide the detail lines, which is accomplished by configuring the Details section to Suppress Blank Sections in the Section Expert. Meanwhile, the value of the “Shipments” variable keeps getting updated with new and new shipment numbers.
Showing the List of Shipments
Finally, we need to show the list of shipments. This is accomplished by yet another formula, which looks like this:
This one is very simple, it just declares the shared variable and immediately returns its value.
Final Results
Here is how the final results look on the invoice. Notice the section titled “DN Numbers”, this is where the list of shipments is.
One More Thing
Let us know if you have any custom software development, integration or customization need related to Sage 300, Sage Intacct, or Acumatica. With over 20 years of experience and hundreds of successful projects under the belt, Optimize Business Consulting has a proven track record of delivering results on time and under budget. Contact us for more information.
Leave a Reply