Reports, for loop, indexing column

[madisonflesner]

I am trying to automate and format reports. Below is an example of what the data looks like. The issue is each question/answer is on its own row, and the policy summary info is repeated for each row. I need to be able to print out the policyholder name and other account summary info only once, but print out each question/answer for a report. How do I do this? Can I index by each group of policy holders?

I wrote out some pseudocode on how it could go but I'm not sure how to implement the looping?

CREATE new report

Uniques = [uniquePolicyName1, uniquePolicyName2, uniquePolicyName3]

FOR j=0: length(Uniques)

      APPEND TO REPORT summary info columns(j)

      FOR i = 0: count(Uniques(j))

               APPEND To REPORT question_column, answer_column where x = uniques(i)

      END

END

Export report to PDF

and it needs to print out as...

[Summary info]

[Q1]

[Answer1]

[Q2]

[Answer2]

[Q3]

[Answer3]

POLICYHOLDER_NAME	VISIT_REPORT_ID	VISIT_REPORT_VISIT_DATE	ACCOUNT_CLAIM_REGION	FIELD_DEF_TEXT	ANSWER_TEXT
heb	scere	10/10/2010	Austin	heb question 1:	heb answer 1
heb	scere	10/10/2010	Austin	heb question 2:	heb answer 2
heb	scere	10/10/2010	Austin	heb question 3:	heb answer 3
cvs	lkmpok	12/12/2012	Lubbock	cvs q1:	cvs ans1
cvs	lkmpok	12/12/2012	Lubbock	cvs q2:	cvs ans2
cvs	lkmpok	12/12/2012	Lubbock	cvs q3:	cvs ans2
walmart	bgfgtr	1/1/2011	Austin	walmart q1:	walmart ans 1
walmart	bgfgtr	1/1/2011	Austin	walmart q2:	walmart ans 2
walmart	bgfgtr	1/1/2011	Austin	walmart q3:	walmart ans 3

I also tried pivoting the data, but I still don't know how to go about it.

[MatthewO]

Hello @madisonflesner:

I believe the attached workflow may get you the output you are looking for. I hope this helps!