Add section between certain parts of table on reporting output

[cowannbell]

In my table, each row has a column titled group.  When I output my data, I need for a row to show up between each set of groups, with the group name and be able to color the row.  I don't want the column ouput into the pdf.

Here is an example of what I'm looking to do.

[rzdodson]

@cowannbell here is one approach using a batch macro that can help you out. 

[cowannbell]

This is great.  I have only ran into two issues.  After this table, I combine a header and footer.  I need to sort the output on the code smallest to largest.  How can I do that?  I need to pass the Fee Sch through somehow because in the report render tool I group data into seperate reports based on the fee sch.

[rzdodson]

For the code sortation, we can add a Sort tool inside the BM - Group Reports (4) tool (the batch macro) to handle the custom sorting you need.

As for the fee schedule injection, I think we would need to include that field upstream in the initial text input so we can use it to trigger batch reporting based on that field. If you have those handy, let me know and I'll adjust the workflow to handle those fee schedules. Will require some very minor changes to the workflow itself. Also, if you have a mock-up of the header and footer, I can get those included for you so you have a complete solution for this use case. :)

[cowannbell]

Okay, so I did assume it would be a sort but I wasn't sure where.  So where in the macro should the sort go?  I played around with it but didn't seem to find the correct spot.

[rzdodson]

@cowannbell in the batch macro, this is where I would personally inject it. This will sort the Code values at the Group level prior to the macro creating the Group-specific table.

Keep in mind that since the entire data set is a combination of string fields, this Sort tool is going to list items out alphabetically in ascending order vice treating the actual Code values as numbers. May be an irrelevant distinction I am making based on the larger data set you are working with, but wanted to give the heads up early should you run in to any trouble.

[cowannbell]

Okay so that is where I had put it but it didn't work correctly.  I might need to clarify.  I need the entire layout to sort by the code, not each group to sort by code.

Or, if I can put the order in which I want the groups to be, that would be find also.  The codes in order will align to the correct group sort.

If we sort by group, then it should be in this order.  I didn't include all of these in my sample.

DIAGNOSTIC SERVICES

PREVENTIVE SERVICES

RESTORATIVE SERVICES

ENDODONTIC SERVICES

PERIODONTIC SERVICES

PROSTHODONTICS - REMOVABLE

IMPLANT SERVICES

PROSTHODONTICS - FIXED

ORAL SURGERY

ORTHODONTIC SERVICESºº

[rzdodson]

@cowannbell made an adjustment to the workflow above by including a Custom Sort table (Text Input 19), Find and Replace (F&R 20) and moving the Sort tool to the outside of the batch macro.

The Custom Sort table will allow you to order the final output however you may need in the future. 

That table is pushed in to Find and Replace (20) where I attach the SortKey field to the series of tables that were created from the batch macro before doing an ascending sort on that SortKey field. This will ensure that the final output is sequenced correctly prior to Summarize (6) assisting with the table consolidation in to one reporting snippet.

[cowannbell]

I just realized that on my original table, I did not include the Fee Schedule number.  So take my original table that I had and know that there are many fee schs.

So Fee Sch 1, 2, 3, etc.  This is what the render tool would use to group data into seperate reports on.

Here is an updated look at what the data table would actually look like.  Of course there are a lot more ADA codes per group.

[rzdodson]

@cowannbell thank you for the above table. I'll get to work on adjusting the workflow to batch out reports based on the fee schedule. Will have an updated workflow for you shortly. :)