How to Design Reports?

[RMittelman]

I am trying to design my first report. I have read this article:
https://five.co/blog/generate-mysql-pdf-report/

Unfortunately, I can’t really make much sense of it. I followed the directions to create the report, add data sources, add it to a menu. I went into the template and added some fields, but they don’t show up when running the report from the menu. Only the report title lines show up. I watched the portion of the video showing how to do the report, but it didn’t seem to help, and the data did not show up like in the video when I ran the application. The documentation does not seem to show anything about reports.

This is a major requirement of any application. If I can’t get reports to work properly, then I would not be able to use Five. I’m sure there must be a way to do what I want.

For my first report, I want to do a Study Group Roster. This involves the Study Groups table, the Members table and the Study Group Members join table. It’s unclear at this point whether I need a query to return the data I need, then base the report on that, or whether I can just specify the 3 tables in data sources for the report.
I also need to control where the data shows on the report. The Study Group information needs to be in the page header for every page. Under that, the detail lines include information from both the Study Group Members table and the Members table. Here is an example report from my Access application:

Sometimes the report only uses a single page, and sometimes it uses multiple pages if the Study Group has many members. Either way, the header information should be on each page.

In general, I need to figure out how to design a report, and how to make it work at run-time. Sometimes I only need 1 Study Group’s roster, and sometimes I need to create a report with several rosters. Probably it makes sense to first figure out how to get the report, then I can figure out how to print 1 or more rosters in the same report based on parameters I supply at run-time.

Can you help me with this? Thanks…

Access makes it very easy to design a report, and I’m sure Five can do this, but I need to figure out how.

[RMittelman]

Not sure if I setup the report properly.
Here is my data sources screen:

Here is the template, to which I added the fields I want to start with:

Here is the result when I view the report:

It seems like it should work properly, but it doesn’t. I’m obviously doing something wrong.

UPDATE:
I modified the data source to use a query of the 3 tables instead. It returns the desired results:

Here is the data source screen:

Now the template looks like this:

But the result at run-time still looks blank like the previous screen shot.

[Jo-Anne]

Hi Ron,

Please look at this example in our Quick Start Guide. It will demonstrate that you need to use a table and our _Each in-built helper function to separate your records. Hopefully this helps, please let me know how you go

13 - Create a Report | Five | Low-Code For Real Developers

[RMittelman]

Thanks for the article, Jo-Anne. Here is what I have so far:

A few things need to be adjusted to give me what I need.

1: Need separate roster for each study group. The detail lines are for all rosters.

2: Need study group information as part of the header. Would I put a grid in the header, then add the study group fields in the grid without the _Each?

3: Need to replace the Leader…Sunshine columns with a single column called Position. For each position that has a value of “1”, I want to put a value in this new column. So if someone has 1/true for Leader, the column should say “L”. If Leader and Coordinator are both true, the column should say “L,C”. Etc… Would this be better done in the underlying query instead?

4: Need each detail record to have 2 rows, because there isn’t enough real estate to display all on a single row. Please see my Access report example above.

There may be more changes, but this would get me much closer to a good report. Thanks again!!!

UPDATE: Never mind item 3. I have figured out how to do this in the query. Here is what the report looks like for now:

[Pranoy]

Hi @RMittelman

1. To have a separate roster for each study group, you can use the ‘_GroupBy’ built-in function. You will need to create a group for the field ‘GroupName’
   
   

This function will generate a template code for you just like _Each which will look something like this:

    {{#_GroupBy @root.[StudyGroupRoster].[Records] 'StudyGroups.GroupName' as | StudyGroupRoster |}}
    
    {{#_Each StudyGroupRoster}}
    
    **{{StudyGroups.GroupName}}**
    
    //  place headers here
    
    {{#_Each GroupedRecords}}
    
    **{{StudyGroups.GroupName}}**
    
    // place additional fields here
    
    {{/_Each}}
    
    {{/_Each}}
    
    {{/_GroupBy}}

the group by function rearranges your data into 2 Groups specified by the field ‘GroupName’. which are GroupRoster and GroupedRecords

• . {{#_Each StudyGroupRoster}} will hold information about the name of each group and a list of the records belonging to that group

• . {{#_Each GroupedRecords}} this one holds only the records

In the template, you can add fields that will be displayed under the group by using this syntax {{[StudyGroups.FullName]}}. This syntax is mainly referencing the fields from your query, here you can replace ‘FullName’ with other fields, and then you can place these fields under {{#_Each GroupedRecords}} from the example response above.
Eg:

2. Since we have now made a group in the previous step, you can now add group information in the header by using the following syntax: {{GroupedRecords.[0].[StudyGroups.NameOfTheField]}} under {{#_Each StudyGroupRoster}}

   Here GroupedRecords = it is the method that holds all the records per group.

   [0] = is the first index of the list of the GroupedRecords.

   [StudyGroups.NameOfTheField] = is the name of the field you want to display such as:

   [StudyGroupMembers.GroupPaidDate] and [StudyGroups.Position]

eg:

4. if you want to add additional rows for a record by right-clicking on a cell and selecting the ‘add row’ option ( refer to the screenshot below )

I am also attaching the HTML for this report, you can have a look at it and let me know if there are any questions:

<style>
    @page {
        size: A4 landscape;
        margin-top: 10;
        margin-left: 10;
        margin-bottom: 10;
        margin-right: 10;
    }
    .page {
        color: #000000;
        background-color: #ffffff;
    }
</style>
<div style="text-align: center;"><span style="font-size: 12px;"><strong><span style="font-size: 14px;">Study Group Roster</span></strong></span></div>


<div style="text-align: center;"><span style="font-size: 12px;"><br></span></div>


<table style="width: 100%; border-style: solid; border-color: back">
    <tbody>
        <tr>
            <td><span style="font-size: 12px; ">{{#_GroupBy @root.[StudyGroupRoster].[Records] 'StudyGroups.GroupName' as | StudyGroupRoster |}}</span></td>
            <td><span style="font-size: 12px; "><br></span></td>

            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><br></td>
            <td><br></td>

            <td><br></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{#_Each StudyGroupRoster}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;">Group:&nbsp; {{[StudyGroups.GroupName]}}</span></td>
            <td><br></td>
            <td><span style="font-size: 12px;">Portfolio Group: {{GroupedRecords.[0].[StudyGroups.GroupType]}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><br></td>
            <td><br></td>
            <td><span style="font-size: 12px;">Category: {{GroupedRecords.[0].[StudyGroups.Category]}}</span></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
        </tr>
        <tr>
            <td><br></td>
            <td><br></td>
            <td><span style="font-size: 12px;">Meets day of week: {{GroupedRecords.[0].[StudyGroups.DayOfWeek]}}</span></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
        </tr>
        <tr>
            <td><br></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
            <td><br></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{#_Each GroupedRecords}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>



        <tr>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">Name/Address</span></td>
            <td><span style="font-size: 12px;">Position(s)</span></td>
            <td><span style="font-size: 12px;">Email/ Primary Phone /Secondary Phone</span></td>
            <td><span style="font-size: 12px;">Member to:</span></td>
            <td><span style="font-size: 12px;">SG Fee Paid</span></td>
            <td><span style="font-size: 12px;">Check Number</span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{[StudyGroups.FullName]}}</span></td>
            <td><span style="font-size: 12px;">{{[StudyGroups.Position]}}</span></td>
            <td><span style="font-size: 12px;">Add the email here</span></td>
            <td><span style="font-size: 12px;">Add the Member to here</span></td>
            <td><span style="font-size: 12px;">{{[StudyGroupMembers.GroupPaidDate]}}</span></td>
            <td><span style="font-size: 12px;">{{[StudyGroupMembers.CheckNumber]}}</span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">Add student address here</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;">Add the email Phone</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{/_Each}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{/_Each}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
        <tr>
            <td><span style="font-size: 12px;">{{/_GroupBy}}</span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
            <td><span style="font-size: 12px;"><br></span></td>
        </tr>
    </tbody>
</table>


<div> </div>

[RMittelman]

Hi @Pranoy.

I’m having a lot of trouble following your suggestions. I’m starting with a template with header defined. I add a table to it.

When I try to add the helper function, it sometimes works and sometimes doesn’t.
If I’m in the first cell of the table (which worked fine when I was adding the _Each), the form comes up like this:

I don’t know what to select in the “Apply To” area. If I choose Current, absolutely nothing happens. If I choose Row, it comes up “undefined”. If I choose Cell, it works, but everything is in a single cell.

When I earlier did a similar task trying to add _Each, it worked properly, and each line of the HTML-like results were in their own row of the table. It was easy to add fields in the proper position. Here it is not. Also, your next few pictures show the codes each in their own rows of the table.
I noticed your screen shot under #1 did not include an “Apply To” section. I can also make this happen by putting my cursor above the table, or doing this operation before I add a table. But then how to I get these codes that are generated into a table? Your screen shots show the codes inside rows of a table. If I highlight all lines containing the codes, then tap the table icon to create a table, the new table simply overwrites the code section rather than putting each row of the codes into a different row as I would expect. How can I get this to work properly? I’m so frustrated with this. It is much harder than using a report designer like in Access or Crystal Reports.

Do you have a blog or other document which shows this step-by-step?

Is there any way to arrange a training session at your convenience (Or Jo-Anne’s) so I could see the steps one-by-one and see their results? I have a Zoom account, and would be happy to host a meeting.

Here is my latest fdf file. It is slightly newer than the one I sent you earlier today.
BrandeisConejo-20240403-2034138595815.fdf (4.0 MB)

Please reach out if you can’t get the password from Jo-Anne.

[RMittelman]

Hi @Pranoy
I see your report is working, even though I would like slightly different formatting. Please see the Roster V2 in the attached fdf. I think it’s constructed the same as your test report, at least in regards to the group by codes and their positioning. The only thing I did differently is put a heading above the grouped records rather than with each one. My report does not work. It only shows the first page.

Can you tell me what I’m doing wrong with my Roster V2? Please let me know the error if you see it.

Also, how do I turn off the test report appearing immediately? Thanks… IGNORE THIS. Didn’t see Dom’s email soon enough.

BrandeisConejoLATEST-20240407-1633236991903.fdf (4.1 MB)

[RMittelman]

Hi @Pranoy
I have been playing with the template design of reports and can’t get anything to work right. The Roster is similar to Roster V2, but it has a logo in the upper left corner. I can’t get the logo to work properly. Sometimes it’s large, sometimes it’s small. In the template it’s small, but when the report gets created it is sometimes big and sometimes small.

I think there are many things I don’t know how this design works. I have a header, and the logo is in the header left cell. Maybe putting things in the header is making it not work. Can you please try all 3 reports and let me know why it doesn’t work?

I seem to be missing the report test report. Don’t know why. This is very very frustrating.

Here is the very latest fdf file:
BrandeisConejo-20240408-0011040926579.fdf (4.1 MB)

[Pranoy]

Hi @RMittelman

Try using this FDF I made a few changes
BrandeisConejo-20240408-0655156161218.fdf (4.1 MB)

1. fixed the Rooster Report with the image in the header, the issue here was that you were printing the header after the grouping
   

   

   you can see from the image that the header is existing below {{#_Each StudyGroupRoster}}. this was simply by placing the header before #_Each

2. For Rooster V2 there was an additional HMTL </div> tag in the code that was causing it not to display more than one record, this has been fixed as well

I did not see a third report in the FDF, you mentioned that the application seemed to be missing the test report?

Let me know if this helps or not.

[RMittelman]

Thanks @Pranoy.

That helped a LOT. My issues would be lessened a bit if I could find documentation on these things. The documentation on reporting that I’ve found is not complete. I would like to see documentation on all the built-in helper functions, as well as information covering object model, where I could read about the entire five object, the stack, etc. I have been unable to find that.

The “missing” report is the one you created, TestReport. I finally figured out (with Dom’s help) where to stop it from appearing immediately when you run the application. I forgot to put in a menu item for it.

The Roster and the RosterV2 are different, and the V2 version does not include the last study group. Don’t know why this is so, but I could compare the templates and figure that out.

The reason I put the {{#Each…}} tag above the header was because I wanted to put some of the group fields inside that area, similar to my Access report shown above. Now I see that is not going to work. Instead of an actual header object, I think I can add a div or other area after the {{#Each}} tag, and just make it LOOK like the header I have. That should solve the issue, right? Now I just need to delete the entire header and re-create it without actually using a header, right?

I notice that there is an extra blank page (with header) at the end. I think this is because the parser that creates the PDF just interprets the code and inserts a page break. Is it possible to make the parser smarter so it knows there are no more records, and ignores the final break? This would be a Five code change, rather tha anything I could do in the template, right?

[RMittelman]

Hi @Pranoy

I’ve done a lot of work, and got the report looking like it should. Unfortunately, I’ve lost the new page for each group. When I started working on it, I removed the header, and just added the desired text below the first ForEach. It worked, but the “heading” did not appear on every page, just the first.

Now I got things to work better, but there is no page break, just one page with all names for all groups below the first group “header”. I’ve looked for extra DIV tags, but can’t find any. Also, there is still a blank page at the end.

Thanks…

BrandeisConejoRON-20240408-2302266532023.fdf (4.1 MB)

UPDATE:
I added enough names to the first study group (even though they are not included in the above fdf file) to extend over a page break. Not only does the next study group not include the “header”, the first group which now extends beyond the page break also does not. I this this is expected, but not optimal. You should have the page header on every page, shouldn’t you?

Possible Fixes:
1: If the actual page header could be below the EACH code, that would solve the issue.
2: If the actual page header had a parameter to force it to be included at the top of every page, then you could leave it before the EACH code. This is the way Access and Crystal Reports do it.

Either of these fixes would involve some re-engineering at your end. Please consider this, as it would make the reporting much more versatile. Thanks…

[Pranoy]

Hi @RMittelman,

Thank you for the suggestion, we are aiming to improve in our future updates and these inputs are really helpful,

I am attaching an updated FDF file in which I’ve made some changes to the ‘Roster’ Report. The main issue I noticed was that StudyGroups.GroupName had been given a new alias in the query and was now simply called GroupName . However, the ‘group by’ syntax was still referring to StudyGroups.GroupName , which was causing the grouping to behave incorrectly. Now, you should receive the reports with each new group on a new page, and the header will repeat on each page as well.

BrandeisConejo-20240409-0550379282403.fdf (4.1 MB)

We are aware that the documentation for reports is not up to date, which is primarily due to Five’s constant evolution and changes. However, these resources might be helpful:

1. Properties and functions on the Five object.
   Base | Five | Low-Code For Real Developers
2. Stack and its examples:
   stack | Five | Low-Code For Real Developers ,
   eg: stack | Five | Low-Code For Real Developers
3. How Five handles the stack automatically
   The Stack | Five | Low-Code For Real Developers

Let me know if this helps

[RMittelman]

Thanks @Pranoy,

That is better. When I saw your message, I did a face-palm. I should have known when I simplified the field names that I needed to also change the ForEach. I changed that in my version, but it did not seem to help. I imported your version and it works better. This is still confusing that I make the same change as you did, and my template still doesn’t work. Perhaps you changed more than that one field name in the group by.

There are still issues. I added a bunch of people to my first group to see what happens, and it did not work properly. The names continue on the next page without a header. Of course this was somewhat expected, because I created my own “header” without using the built-in header that Five allows.

As a programmer, I feel like the only way this can work is if Five allowed the header definition AFTER the first ForEach, so the field names and values could be known. In any report, you need to have a header repeat on each page, and that header may contain fields from the database. It only makes sense in this report to have the group informational fields show again on page 2 of the same group. If we only wanted a visual report on screen, this may not be the case. But if you are creating a PDF file to send to people, we need headers on each page.

Also, not all of the page breaks work. the resulting PDF has page-break codes visible but not actual page breaks. I tried to upload the pdf file, but this forum doesn’t allow files with pdf extensions to be pasted into the message. Maybe if I simply replied to the forum’s email from you and attached the file to that??? In any case, you can try it yourself from the attached fdf file, which includes the extra group members.
BrandeisConejoRON-20240409-1758018451948.fdf (4.1 MB)