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!
Solved! Go to Solution.
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])?
jwalder's solution is exactly what I was thinking when reading the question.
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 |
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.
This is perfect! Thank you all for your help :)