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: BUREAUTARIFFCONTRACT NUMBERCUSTOMERYYYYMMREFUND AMOUNTTONSTALLYAA01AA-01A201807$0.0000AA01AA-01A201808$0.0000AA01 $0.0000AC05AC-05B201804$500.00500500AC05AC-05B201805$500.00500500AC05AC-05B201806$500.00500500AC05AC-05B201807$500.00500500AC05AC-05B201808$750.00750750AC05 $2,750.0027502750 Note, there can be varying line items per customer/contract. I would like my report data to look like the below: BUREAUTARIFFCONTRACT NUMBERCUSTOMERYYYYMMREFUND AMOUNTTONSTALLYAA01AA-01A201807$0.0000AA01AA-01A201808$0.0000AA01 $0.0000 AC05AC-05B201804$500.00500500AC05AC-05B201805$500.00500500AC05AC-05B201806$500.00500500AC05AC-05B201807$500.00500500AC05AC-05B201808$750.00750750AC05 $2,750.0027502750 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!

Generate Row Tool to Insert Blank Row After Change in Grouping

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!

Help

Accepted answers

steven-barsalou

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.

Insert empty rows by groups.yxmd

All comments

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335