Help Designing New Report

How do I?
1

My one report, which used to work, now returns nothing. I haven’t worked on this report in months, as I’ve been working on other parts of my application. Now I need to get back to the reporting, and I need to find a tutorial that shows your new way of designing a report. I need to be able to generate the reports on demand, and also generate them by a process which will build the needed reports and email them to the intended recipients.

I heard some comments about being able to generate the reports server-side than download them or email them as pdf files. Is that true?

My first report, the Study Group Roster, needs to list all members in the group. It is driven by a StudyGroupRoster table, which I fill based on parameters chosen at run-time. It needs to be grouped by study group, with a header on each page, and the header needs to be able to show fields from the study group data, whereas the data comes from the member data on each record. Again, all driven by a single table.

Rather than fix the existing report, it seems like I should start over. I’m confused by the header object, and the group header which is created when I use the helper function to group by.

Here is what was generated by the helper function in the template:

{{#_GroupBy @root.[StudyGroupRoster].[Records] 'GroupName' as | StudyGroupRoster |}}
        {{#_Each StudyGroupRoster}}
        
            Group By Header {{GroupName}}
        
        {{#_Each GroupedRecords}}
        
            {{GroupName}}
        
        {{/_Each}}
        
            Group By Footer {{GroupName}}
        
        {{/_Each}}
        
            Group By Final StudyGroupRoster
        

    {{/_GroupBy}}

It is not clear what is going on here, and which of the elements shown above are above, below or inside the page header I insert into the template.

Is there a newer tutorial than the last one I looked at which was from February, 2024?

Thanks…

2

Hello,

Here are some documentation links regarding reports and mail merges.

Hope these help,

Thanks,
Riley.

3

Thanks for sending the links Riley. Mostly they show what is done after the report is designed. I really mean to drill down on how to design the report, and how/where to put the various report elements inside the list of instructions generated by selecting the group by helper function.

Those instructions include a Group By Header instruction. Please explain how this relates to a header object I designed for the report.

To simplify things, here is a report generated by my Access application.

image
image906×700 37.6 KB

I’d like the five generated report to look as close to this as possible. All the fields you see in the blue area are related to the study group level, as well as the 2 lines just below the blue area. You can see there are 10 records. Since the data source for this report is a single table, all the top area fields are repeated in all 10 records.

It’s quite confusing at this point whether I need a repeating header object at the top, and if it can contain records from the data source that contains all of the records I’m reporting. I’m pretty sure this is dependent on where the header is in the list of group commands above. If I had to guess, I would say to put the “real” header in the “Group By Header {{GroupName}}” area below the first 2 lines. And the rest of the records perhaps go in the “{{#_Each GroupedRecords}}” section?

Bear in mind, some groups contain too many records (members) to fit on one page, so I need them to continue on the next page (with another header). For a new group, I also need to have it appear on a new page.

If I can get this working in the foreground, I can later figure out the different ways to get it to the members.

Thanks…

4

Here is what I have so far:

image
image1495×745 47.3 KB

When I first designed this, added a group-by field, then added the header, the header appeared above the group-by statements. I split the rows and columns of the header to the desired format, then added some text in the grid. So the grid you see in the picture is supposed to show on all pages.

When I saved and ran the application, the only thing that shows on the blank page is “Group By Final StudyGroupRoster”. There is no header text. At first I just put “Study Group Roster” in the top middle cell of the header grid. It didn’t show. Then I moved the first 2 group-by lines above the header grid, because that seemed to be the way it was done in the older veraion of five. I also used a code for the study group name, which “used to” work, but now it doesn’t. Bear in mind, GroupName is a valid field in the data source table.

So I can’t even get started on this report. even the most simple information is not showing up. You’ll notice I put “this is the members area” in the right place for grouped records, but it doesn’t show.

Can you help?

P.S.: I forgot to mention, I know we have issues with FDF file size, but I just remembered I can share a folder on my OneDrive account and give you a link if you need to see my FDF and send me one.

5

Hello,

For this one I will most likely need to take a look at your FDF, the report editor can be quite finicky sometimes, so yes you will need to send me a OneDrive link to your FDF,

Thanks,
Riley.

6

Thanks Riley,
Here is a link to my OneDrive folder.
https://rmittelman-my.sharepoint.com/:f:/g/personal/ron_rmittelman_onmicrosoft_com/EoTQd4Mlg4dMnzlDshrMh8QBHgP_uAraoLs6GBmlOmxIjA?e=kByS17

This will take you to the folder where you can find the FDF file. Feel free to use the same link to send me anything needed from your end.

I have 3 reports defined, but only 2 of them are in my reports menu from the running program. The one ending in “OLD” is not in the menu.

I tried defining a report and also tried the report wizard. One of these reports, when run, attempts to create a report with hundreds of pages. Obviously this will not work.

In the StudyGroupRoster table are the records for 2 different study groups. If you need to refresh this table, go into the GenerateReports form, click any of the study group name buttons to either include or exclude those groups, then click the Generate Report action button at the top of the form. This should clear then re-load the StudyGroupRoster table.

What I really need is to know what components go where in the template. The Header seems to want to be at the top, above the Group-By code elements. What is not clear is the group header section in the Group-By area, and how that conflicts or works with the header element.

I don’t know where these codes for the Group-By function go. In some of the older documentation, it looked like some of them were inside a grid/table.

Thanks…

7

Hello,

I just had a look at the application and I cannot see any records within the StudyGroupRoster table, how does this table get populated?

I attempted to include all the study groups from the GenerateReports menu using the method you described, but when querying the StudyGroupRoster table, it showed no records.

I may need some more guidance as to how this table gets populated in order to test the reports further, I can also insert sample data if needed.

Thanks,
Riley.

8

Did you make sure at least 1 study group was selected, then click the Generate Report action button? That runs a function which runs a query that truncates then fills the table. If no groups are selected it won’t work. If one or more groups are selected you should then see them in the SelectedGroups table. Same with the SelectedPortfolios table. These tables are joined in the query which loads the Study group roster table. I think this query is called LoadStudyGroupRoster.

Please let me know if this works. If you’re willing I can meet with you on zoom and show you. I think I’m 17 hours behind you (US Pacific time).

Thanks…

Ron Mittelman

9

I think I found out why there are no records in the table. The query that fills the table stopped working. Here is the query:

/*TRUNCATE TABLE `StudyGroupRoster`;
INSERT INTO `StudyGroupRoster` (
  `GroupName`,`Category`,`GroupType`,`DayOfWeek`,`Weeks`,
  `MaxMembers`,`FromSFV`,`Portfolio`,`Vp`,`VpAddress`,`VpPhone`,`VpEmail`,`Registrar`,
  `RegistrarAddress`,`RegistrarPhone`,`RegistrarEmail`,`MemberSortName`,`MemberName`,
  `MemberEmail`,`MemberAddress`,`PrimaryPhone`,`SecondaryPhone`,`Position`,`Paid`,`CkNo`
  )*/
(SELECT
  `StudyGroups`.`GroupName` AS `GroupName`,
  `GroupCategories`.`Category` AS `Category`,
  `StudyGroups`.`GroupType` AS `GroupType`,
  case
    when `StudyGroups`.`DayOfWeek` = 1 then 'Sunday'
    when `StudyGroups`.`DayOfWeek` = 2 then 'Monday'
    when `StudyGroups`.`DayOfWeek` = 3 then 'Tuesday'
    when `StudyGroups`.`DayOfWeek` = 4 then 'Wednesday'
    when `StudyGroups`.`DayOfWeek` = 5 then 'Thursday'
    when `StudyGroups`.`DayOfWeek` = 6 then 'Friday'
    when `StudyGroups`.`DayOfWeek` = 7 then 'Saturday'
    else '' end as `DayOfWeek`,
  TRIM( 
    CONCAT(
      if(`StudyGroups`.`Week1`,'1',''),
      if(`StudyGroups`.`Week2`,' 2',''),
      if(`StudyGroups`.`Week3`,' 3',''),
      if(`StudyGroups`.`Week4`,' 4',''),
      if(`StudyGroups`.`Week5`,' 5','')
    )
  ) as Weeks,
  `StudyGroups`.`MaxMembers` AS `MaxMembers`,
  `StudyGroups`.`FromSFV` AS `FromSFV`,
  `Portfolios`.`Portfolio` AS `Portfolio`,
  (SELECT IFNULL(`Members`.`FullName`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`VpKey`) AS `Vp`,
  (SELECT IFNULL(`Members`.`Address`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`VpKey`) AS `VpAddress`,
  
  (SELECT case 
          when PrimaryPhone is null then '' 
          else concat('(', substring(PrimaryPhone, 1,3), ') ', 
                      substring(PrimaryPhone,4,3), '-', substring(PrimaryPhone,7,4) )
          end 
    FROM Members WHERE MembersKey = `Portfolios`.`VpKey`
  ) AS `VpPhone`,
  
  (SELECT IFNULL(`Members`.`Email`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`VpKey`) AS `VpEmail`,
  (SELECT IFNULL(`Members`.`FullName`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`RegistrarKey`) AS `Registrar`,
  (SELECT IFNULL(`Members`.`Address`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`RegistrarKey`) AS `RegistrarAddress`,

  (SELECT case 
          when PrimaryPhone is null then '' 
          else concat('(', substring(PrimaryPhone, 1,3), ') ', 
                      substring(PrimaryPhone,4,3), '-', substring(PrimaryPhone,7,4) )
          end 
    FROM Members WHERE MembersKey = `Portfolios`.`RegistrarKey`
  ) AS `RegistrarPhone`,

  (SELECT IFNULL(`Members`.`Email`,'') FROM `Members` WHERE `Members`.`MembersKey` = `Portfolios`.`RegistrarKey`) AS `RegistrarEmail`,
  IFNULL(`Members`.`SortName`,'') AS `MemberSortName`,
  IFNULL(`Members`.`FullName`,'') AS `MemberName`,
  IFNULL(`Members`.`Email`,'') AS `MemberEmail`,
  IFNULL(`Members`.`Address`,'') AS `MemberAddress`,

  case  when Members.PrimaryPhone is null or Members.PrimaryPhone = '' then '' 
        else concat('(', substring(Members.PrimaryPhone,1,3), ') ', 
                    substring(Members.PrimaryPhone,4,3), '-', 
                    substring(Members.PrimaryPHone,7,4), 
                    case  when Members.PrimaryPhoneType is null then '' 
                          else concat(' (',Members.PrimaryPhoneType, ')') end
                   ) 
        end AS `PrimaryPhone`,
  case  when Members.SecondaryPhone is null or Members.SecondaryPhone = '' then '' 
        else  concat('(', substring(Members.SecondaryPhone,1,3), ') ', 
                    substring(Members.SecondaryPhone,4,3), '-', 
                    substring(Members.SecondaryPhone,7,4), 
                    case  when Members.SecondaryPhoneType is null or Members.SecondaryPhoneType = '' then '' 
                          else concat(' (',Members.SecondaryPhoneType, ')') end
                    ) 
          end AS `SecondaryPhone`,

    -- combine 5 booleans into a single "Position" text field
  TRIM(
    CONCAT(
      if(`StudyGroupMembers`.`IsLeader`,'L',''),
      if(`StudyGroupMembers`.`IsCoordinator`,' C',''),
      if(`StudyGroupMembers`.`IsCoLeader`,' CL',''),
      if(`StudyGroupMembers`.`IsAssistant`,' A',''),
      if(`StudyGroupMembers`.`IsSunshine`, ' S','')
    ) 
  ) AS `Position`,

  `StudyGroupMembers`.`GroupPaidDate` AS `Paid`,
  IFNULL(`StudyGroupMembers`.`CheckNumber`,'') AS `CkNo`

FROM
  `StudyGroups`
  INNER JOIN `StudyGroupMembers` ON (
    `StudyGroups`.`StudyGroupsKey` = `StudyGroupMembers`.`StudyGroupKey`
  )
  INNER JOIN `Members` ON (
    `StudyGroupMembers`.`MemberKey` = `Members`.`MembersKey`
  )
  INNER JOIN `GroupCategories` ON (
    `StudyGroups`.`CategoryKey` = `GroupCategories`.`GroupCategoriesKey`
  )
  INNER JOIN `Portfolios` ON (
    `StudyGroups`.`PortfolioKey` = `Portfolios`.`PortfoliosKey`
  )
  INNER JOIN `SelectedGroups` ON (
    `StudyGroups`.`StudyGroupsKey` = `SelectedGroups`.`StudyGroupKey`
  )
  INNER JOIN `SelectedPortfolios` ON (
    `StudyGroups`.`PortfolioKey` = `SelectedPortfolios`.`PortfolioKey`
  )
WHERE
  (`StudyGroups`.`Active` = true)
AND
  (`SelectedGroups`.`IsSelected` = true)
AND
  (`SelectedPortfolios`.`IsSelected` = true)
ORDER BY
  `StudyGroups`.`GroupName` ASC,
  `Members`.`SortName` ASC);
 -- select * from `StudyGroupRoster`;

If I run the query AS SHOWN ABOVE, it returns the desired records.
However, if I un-comment the first 7 lines before the SELECT so that it works as designed, I get an error:

Error 1292: Truncated incorrect DOUBLE value: 'Y'

I’ve looked at the table definition, and can’t find any DOUBLE type columns, and I can’t see anywhere the value ‘Y’ is being retrieved from the SELECT.

This used to work, but now it doesn’t. Can you see what is going wrong?

10

Hi Riley,

Sorry for the last post, I found the error. The LoadStudyGroupRoster query was blowing up because of some null values.

I modified the last area of the select statement as follows:

I replaced

    -- combine 5 booleans into a single "Position" text field
  TRIM(
    CONCAT(
      if(`StudyGroupMembers`.`IsLeader`,'L',''),
      if(`StudyGroupMembers`.`IsCoordinator`,' C',''),
      if(`StudyGroupMembers`.`IsCoLeader`,' CL',''),
      if(`StudyGroupMembers`.`IsAssistant`,' A',''),
      if(`StudyGroupMembers`.`IsSunshine`, ' S','')
    ) 
  ) AS `Position`,

With:

    -- combine 5 booleans into a single "Position" text field
    TRIM(
    CONCAT(
      if(IFNULL(`StudyGroupMembers`.`IsLeader`, 0), 'L', ''),
      if(IFNULL(`StudyGroupMembers`.`IsCoordinator`, 0),' C',''),
      if(IFNULL(`StudyGroupMembers`.`IsCoLeader`, 0),' CL',''),
      if(IFNULL(`StudyGroupMembers`.`IsAssistant`, 0),' A',''),
      if(IFNULL(`StudyGroupMembers`.`IsSunshine`, 0), ' S','')
    ) 
  ) AS `Position`,

Now clicking the Generate Report action button in the GenerateReports form will load the StudyGroupRoster table correctly.

Please advise if you would like me to create another FDF file with this change, or for expedience you would prefer to edit the query yourself.

Once we have data in the StudyGroupRoster table, maybe you can see what I’m doing wrong in the report definition template. If there is more specific examples or instructions in the documentation or your blogs other than what you’ve already supplied, please let me know.

There are currently 2 study groups selected to print by default in the Generate Reports form. If you prefer to start with only a single study group to make things easier, please open the GenerateReports form and select Roster from the list, then click the first study group name to “turn it off”, then click Generate Report action button to reload the table.

THANKS!!!

11

Hello,

Glad you found the issue, would you please be able to upload the new FDF to the One Drive and delete the current one. Just for future reference, to speed things up, if you make a change to the FDF, add it to the One Drive so we can be on the same page.

Let me know when it is in there and I can take another look at the reports,

Thanks,
Riley.

12

Just uploaded latest FDF.

Here is shortcut:
https://rmittelman-my.sharepoint.com/:f:/g/personal/ron_rmittelman_onmicrosoft_com/EoTQd4Mlg4dMnzlDshrMh8QBHgP_uAraoLs6GBmlOmxIjA?e=WyxZQS

This shortcut should not change. Please advise if you are keeping track of it or you need me to paste the link into my messages to you ongoing.

Thanks…

13

Hello,

I have just uploaded an FDF to the OneDrive, I was still experiencing issues with the query where I was also getting the truncate error. I had a look and was able to find a fix in the query, however, when running the query, it results in 56 records (for 4 study groups selected), which would lead to the report being a large amount of pages.

I am just wondering, is this query supposed to return such a large amount of records when only a couple of study groups are selected?

If so, we could potentially decrease the sizing of things in the report to reduce the page count.

Thanks,
Riley.

14

Thanks Riley!

Since each study group may have 8-10 members, and some may have up to 20 members, I wouldn’t worry about the number of pages.

Don’t understand why the query gave an error, I thought I fixed it before sending you the FDF. I will take a look at this.

Thanks…

Ron Mittelman

15

I appreciate your input, but in the query, you changed the 2 places where I was doing a CONCAT, and it seems like you expected the individual values to be “Y” or (not sure what). But those are boolean fields, so they will never be “Y” values, just true or false. I presume that equates to 1 or 0, right?

When you put

IF(IFNULL(`StudyGroupMembers`.`IsLeader`, '') = 'Y', 'L', ''),

in the CONCAT statements, it looks like you are expecting the individual values to be “Y” or “” or “N”, but they should only be true or false, 1 or 0.

Same goes for the other CONCAT area for Week1…Week5 fields.

I see why you put in DISTINCT after the SELECT, because I was getting 2 records for each person in the study group. Don’t quite know why, but adding the DISTINCT was a good call. Thanks.

Given only a few study groups selected, having 50 records or so is not a problem. If a study group has 25 members, the roster for that group will be 2 or 3 pages, depending on font size, etc.

Still, when I run the Roster report at run-time, it still gives me many many pages.
It’s not clear whether you did anything on the report itself. I really need to know what is wrong with the reports. One report (the Roster) keeps incrementing the number of pages as it’s building, and I finally have to give up and cancel. The other report (StudyGroupRoster) returns nothing but a blank page.
As far as number of records

The main problem I have now is how to define a report, and have it properly group-by the desired field, and what is the difference between a header and a group header (as it appears in the template when you choose group-by).

Thanks…

16

Hi Riley,

I restored my original query (but added DISTINCT after SELECT). Thanks!
I also added IFNULL to the first set of CONCATs for the week numbers, just in case that was causing the error for you.

I exported the new FDF, and it’s on the OneDrive folder.

I just need you to help me figure out why I can’t define a report that works. Please see my immediate prior reply to you. I have no idea at this time how to make a grouped report with a header on every page. Using GroupedRecords.[0].xxxxx should work in the page header as long as I put the for-each statement above the header in the template, right? I’m just not sure how that will affect the header itself, because even if there is “stuff” in the template, when you add a header, it wants to put it at the very top, right? PLEASE help with this.

The current StudyGroupRoster table should have 1 or 2 study groups, and I need to make a report for that data.

Thanks…

17

Hello,

I have just uploaded an FDF (10.3MB) to the OneDrive, if you click on the ‘Study Group Roster’ menu item, you should now be presented with a report that looks like this:

Screenshot 2024-12-09 at 8.38.20 am
Screenshot 2024-12-09 at 8.38.20 am1920×1080 270 KB

For now, I have created a new report template just to show an example of how this works, it is called ‘TestReport’, that way, you can customise your main report however you want while keeping this example to look back on. Also keep in mind that I made an adjustment to your query (ShowStudyGroupRoster), instead of using

SELECT *

I selected each column individually. From a query perspective, these do the exact same thing, but within the report, you need to be able to select values to show from the query, and therefore, need to select by the actual column names. You will also need to change the report which is attached to this menu item as it is currently my TestReport one.

One more thing, each time I use your app, I need to turn off the Multiple Users switch in order to use it as I do not know the log in. If this is annoying, you are welcome to email me the log in details, otherwise, I am happy to continue going on as is.

Let me know how you go,

Thanks,
Riley.

18

I will take a look at this later, as I’m on my way out. However, the report was based on the StudyGroupRoster table, not the ShowStudyGroupRoster query. This query (and all the other “Show…” queries) is just used for me to see what is in the table.

I’m glad you were able to get something happening. I mainly need to know what happens when you use group-by and a page heading in the same report.

I will send you the password on a separate email.

Thanks…

Ron Mittelman

19

Thanks Riley.

First, I can’t get the report to run from your fdf file. It just displays a blank report page when I click “StudyGroupRoster” from the menu.

Second, you left me with a non-multi user application. When I removed the app from development, then changed to multi user, the deployed again, it now says user name or password is invalid.

When my account finally was locked, it told me to click the forgot password button. When I tried this, it now says “Invite email template needs to be specified”.

So now I can’t even run the application. Can you help me with this?
I’ve deleted my original FDF file dated 5/12/2024 but kept your FDF dated 8/12/2024.
I added a new FDF file dated 9/12/2024 just now.

When I switched back to your most recent FDF, I no longer get the invalid user or password message, but I do get this message:
“User not found for key : 10000000-4444-4444-4444-000000000001”
Then nothing works, I just have a rotating logo in the middle of the screen.

Please be aware that I had some time ago added a function call to InitialSetup function from the Do Logon event:

function InitialSetup(five, context, result)  {
    
    // save userKey
    five.showMessage('curUsrKey: ' + five.currentUserKey);
    const userKey = five.currentUserKey();
    five.setVariable("UserKey", userKey);
    


    return five.success(result);
}

I just added the showMessage line, and it may not be in the FDF I uploaded. But I never get to that point, because I get the user-not-found error before it can display.

20

Hello,

I have just uploaded an FDF, with the admin user:
Username: admin

And the password is the same one you emailed me. When I log into this account, select the reports menu, and select StudyGroupRoster, it works for me, so let me know if it works in this FDF for you.

If you experience any further issues regarding accounts, let me know and we can look into reverting your FDF back to a working version.

Thanks,
Riley.