RW - Creating Branching Logic Fields in Report Writer

After a long hiatus from blogging, here I am again with another interesting case straight from one of my clients in Venezuela.

As much as many of you dislike Report Writer, I still have to acknowledge that it still has its benefits, especially when working with tightly integrated Dynamics GP reports. Yeah, yeah, build it in SSRS, build it in Crystal, but it's always good when you don't have to build a complex report from scratch and can leverage the built-in application functionality.

The client calls me up and asks, "how can I create a CASE...END CASE statement in Report Writer?" In other reporting systems and SmartList Builder for example, it is possible to enter simple formulas that support complex conditional branching logic, for example:


CASE X
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
END CASE

While it's not as apparent in Microsoft Dynamics GP Report Writer, it is possible to support this type of conditional structures by using cascading conditional fields. To illustrate cascading conditional fields, we will work with this simple example: say your company just acquired another firm with customers in Arizona, New Mexico, and Utha and you have to send out invoices this month incorporating a greeting message for customers in those states. The message says something along these lines "XYZ welcomes its new customers in [State here]". Let's see how we can accomplish this in Report Writer.

1. First, we create inner condition -- WHEN 3 THEN.



2. Second, we create the condition leading to the inner condition



3. Finally, we create the top level condition



4. With the calculated fields in place, we may now drag the 3 of them onto the Report Layout window, then change their Visibility property to Hide When Empty. That's it! Now you have created branching logic using cascading conditional fields. May not be pretty, but sure gets the job done.



A couple things to note:

a) you can create conditional branching on different fields as well using the same technique in Report Writer, for example:


CASE X
WHEN 1 THEN
CASE Y
WHEN 'A' THEN 'Z'
WHEN 'B' THEN 'Y'
...
END CASE
WHEN 2 THEN
'Something Else'
END CASE


Needless to say, the more complex your branching logic, you may be more inclined to use something like VBA, or.. ehem.. Crystal or SSRS.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com