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.
Hello @madisonflesner:
I believe the attached workflow may get you the output you are looking for. I hope this helps!