Generate Row Tool to Insert Blank Row After Change in Grouping

[rwsarisky]

I am generating a report that tracks monthly metrics for specific customers/contracts and I am trying to insert a blank row in between each customer/contract group.  I am able to insert a blank row after each row on my report, but I want to be able to insert the blank row only after the last row for each customer/contract. 

My report data is similar to the below:

BUREAU	TARIFF	CONTRACT NUMBER	CUSTOMER	YYYYMM	REFUND AMOUNT	TONS	TALLY
AA	01	AA-01	A	201807	$0.00	0	0
AA	01	AA-01	A	201808	$0.00	0	0
AA	01				$0.00	0	0
AC	05	AC-05	B	201804	$500.00	500	500
AC	05	AC-05	B	201805	$500.00	500	500
AC	05	AC-05	B	201806	$500.00	500	500
AC	05	AC-05	B	201807	$500.00	500	500
AC	05	AC-05	B	201808	$750.00	750	750
AC	05				$2,750.00	2750	2750

Note, there can be varying line items per customer/contract.

I would like my report data to look like the below:

BUREAU	TARIFF	CONTRACT NUMBER	CUSTOMER	YYYYMM	REFUND AMOUNT	TONS	TALLY
AA	01	AA-01	A	201807	$0.00	0	0
AA	01	AA-01	A	201808	$0.00	0	0
AA	01				$0.00	0	0
AC	05	AC-05	B	201804	$500.00	500	500
AC	05	AC-05	B	201805	$500.00	500	500
AC	05	AC-05	B	201806	$500.00	500	500
AC	05	AC-05	B	201807	$500.00	500	500
AC	05	AC-05	B	201808	$750.00	750	750
AC	05				$2,750.00	2750	2750

I am able to insert a blank row after each individual row in my report using Generate Row/Multi-Field Formula; however, am unable to figure out how to adjust these tools to generate blank rows after each change in customer/contract.  Is there a way to do this?  Any help would be greatly appreciated!

[jwalder]

Could you create a new stream with a summary tool that does a group by on [BUREAU], union that with the original, sort appropriately and then follow up with a formula tool that blanks out [BUREAU] if IsNull([TALLY])?

[steven-barsalou]

jwalder's solution is exactly what I was thinking when reading the question.

[rwsarisky]

That is a good idea and though it is generating the blank rows that I want, I am unable to sort my data back into same report format once I do this.  For instance, it removes the line of data with my subtotals to the top along with my blank row followed by my monthly totals.  Is there a different way to insert blank rows after each change in bureau/tariff or customer without having to resort?

BUREAU	TARIFF	CONTRACT NUMBER	CUSTOMER	YYYYMM	REFUND AMOUNT	TONS	TALLY
AA	01			[Null]	$0.00	0	0
	[Null]			[Null]	[Null]	[Null]	[Null]
AA	01	AA-01	A	201807	$0.00	0	0
AA	01	AA-01	A	201808	$0.00	0	0
AC	05				$2,750.00	2750	2750
	[Null]			[Null]	[Null]	[Null]	[Null]
AC	05	AC-05	B	201804	$500.00	500	500
AC	05	AC-05	B	201805	$500.00	500	500
AC	05	AC-05	B	201806	$500.00	500	500
AC	05	AC-05	B	201807	$500.00	500	500
AC	05	AC-05	B	201808	$750.00	750	750

[Philip]

What about this approach? 

[rwsarisky]

This is great! It does generate a blank row in between each group that I have.  I did, however, fail to mention that I have a grand total line which has now moved to the first row in my dataset (because [BUREAU] and [TARIFF] are null, so they sort to the top).  Any ideas as to how I should account for my grand total line and get that back to the last row of my dataset?  I have attached the updated sample workflow.

[steven-barsalou · Accepted solution]

Just building on Philip's response. I made some tweaks to meet your requirements.

Using two Multi Row Formula tools, you can setup a "Bureau ID" and "Line Item ID" to sort all of the records in the correct order.

[rwsarisky]

This is perfect!  Thank you all for your help :)