How to change the appearance of a summary report?

Question

Hi everyone,I'm not sure if it's possible, but it's what my boss wants for a particular report.I currently have a report for # of people registered for events, organized by health regions, and then by community.&nbsp; It looks like this (but with around 80 communities - so long and hard to read in this default format)​:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Health Region 1&nbsp; |&nbsp; Health Region 2&nbsp; |&nbsp; &nbsp;Health Region 3Community 1&nbsp; &nbsp;-&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5Community 2 -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10Community 3 -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7and so forth.&nbsp; But a community can only exist within a specific health region; so what she wants to see is a different way of rolling this data up.&nbsp; Preferably a table more like this:Health Region 1 - 50&nbsp; &nbsp;|&nbsp; &nbsp; Health Region 2 - 75&nbsp; &nbsp;|&nbsp; &nbsp;Health Region 3 - 90&nbsp; &nbsp;Community 1 - 5&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;Community 2 - 10&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; Community 3 - 7&nbsp; &nbsp;Community 7 - 15&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;Community 4 - 15&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; Community5 - 15&nbsp; &nbsp;Community 8 - 20&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp;Community 6 - 25&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp;Community 9 - 35&nbsp; &nbsp; etc.Is this possible?&nbsp;&nbsp;Thank you for any guidance you can offer!Take care,Jen------------------------------Jennifer Juhasz------------------------------

jenniferjuhasz · Answer

I've spent the whole day on this, and am no closer to a solution.

At this time, I've created 5 separate summary reports - one per health region - that lists their individual communities with the total # of registrations; and then have just put them side-by-side in a Dashboard.

Even if I could find some way to add sub-totals to a summary report so I could break up the rows into groupings by the 'health region' parameter, it would be a step forward; but I don't think this is possible either.

I also am not sure if I create a totally separate table and use formula queries to populate it, that I could get the data that I need. Is this just another function that Quickbase cannot do, but Excel can?

Thank you!

------------------------------
Jennifer Juhasz
------------------------------

markshnier__you · Answer

Do you have a relationship where one Health Region has many communities?&nbsp;&nbsp;If so, you could create a text field on the community record which contained the name of the community and that count value.Then use a Combined Text Summary field to roll those up to the Health Region.Then use a formula to get rid of the Bubble appearance and&nbsp; change to a vertical list.SerachAndReplace(ToText([My Combined Text Summary field]), " ; ", "
")&nbsp;------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

jenniferjuhasz · Answer

Hi Mark,Thank you so much for this!&nbsp; I do have a relationship where one Health Region has many communities.&nbsp;It took me a bit of thinking to figure it out - but I have everything here, except where to put in the formula you suggest:SerachAndReplace(ToText([My Combined Text Summary field], " ; ", "
")The Summary Field defaults to a 'multi-select' text field, with no option to change to a formula-multi-select text field... at least so far as I can see.&nbsp; Where do you input the formula?Thank you again!&nbsp; I appreciate your help so much,Jen------------------------------Jennifer Juhasz------------------------------

jenniferjuhasz · Answer

Trying again - I created a 'formula' multi-select text field, and used your formula, referencing the 'combined' summary field, but I am getting a syntax error...&nbsp; See screenshot.

I seem to always stumble on syntax errors... what have I missed?Thank you!
------------------------------Jennifer Juhasz------------------------------

markshnier__you · Answer

I have corrected the missing closing ) in my post above.&nbsp; If you still&nbsp; have any trouble debugging your formula, please copy and paste the actual text as I can't edit a screenshot.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

How to change the appearance of a summary report?