Wednesday, July 1, 2009

Copy Calculated Fields in Report Writer

Yes you can! The secret is in the editing of the .package file.

Let's imagine you want to use the rw_SelectAddrLine user-defined function to create the address block in the SOP Blank Invoice Form. KB (862653) entitled "Description of the rw_SelectAddrLine Report Writer function" will show you that 9 calculated fields could be required if you want to use all of the available fields in the address block. If you like going through that drill, the process definitely works.

I prefer the copy and paste method. What you need to do is create a couple of the calculated fields as directed in the article and then export the modified report to a .package file. Open the package file using the text editor of your choice and then copy and past until you have what you need. For the SOP Blank Invoice Form here is the text you can past into the calculated field portion of your report.

The calculated fields below skip the 'Name' field (the first variable)
CalculatedField "Ship To Address Line 1" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 1 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 2" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 2 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 3" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 3 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 4" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 4 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 5" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 5 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 6" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 6 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 7" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 7 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 8" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 8 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" } CalculatedField "Ship To Address Line 9" { EvaluateAfter "" Expression "'rw_SelectAddrLine'( 9 \"\" 'SOP_HDR_WORK'.'Contact Person''SOP_HDR_WORK'.'Address 1''SOP_HDR_WORK'.'Address 2''SOP_HDR_WORK'.'Address 3''SOP_HDR_WORK'.'City''SOP_HDR_WORK'.'State''SOP_HDR_WORK'.'Zip Code''SOP_HDR_WORK'.'Country' ) " ResultType "String" }

2 comments:

Bron Tamulis said...

Leslie - you seem to have the mystery of Report Writer solved.

Here's user defined question for you.......

There is a user defined funtion for Get Item Description. It's handy for anytime you have a report with 2 item number fields (such as Bill of Material - sales kits, etc.)

Is there a function for retrieving the UDF values as well?

In other words - my report is a Mfg Picklist - there is a FG item and a 'child or component item' - in the header I want UDF #5 for the FG item - in the detail I want UDF #5 for the 'child part or component' part......

Can I create the report writer functions or am I stuck?

Dynamics Confessor said...

Bron,

Thank you for contacting me. I just wish I had that function for you.

I don't know of a function that would pull that information for you, but that doesn't mean it isn't there. I can see two options that might be available for you.

First, use VBA with Report Writer to pull the extra field onto the report via an external connection to the database. You would need to own Modifier w/ VBA to get this option to work.

Second, get a copy of the Support Debugging Tool and write your own RW function. You need to know a bits of Dexterity to do this one, but the Support Debugging Tool is free!

There are hundreds of UD functions out there, if I come across what you need, I'll post it here.

Sorry I didn't have a better answer.

Kind regards,

Leslie