Help Designing New Report

[RMittelman]

Thank you SO much Riley. I can now log on as admin and run the report. There are some questions now so I can proceed.

1: Regarding the report template
It is unclear to me what you did exactly from viewing the template. I see a for-each in the grid under the bold row, which just contains field names. Is the grid I see in the template the result of entering a header component? If yes, did you set it to appear on all pages? If no, do I now insert a header component? Is there a “place” I need to be in the template before inserting this? How do I cause each study group to appear on a new page? Also, when I originally put in a for-each field, a bunch of section codes appeared, but in your sample I can’t find any of these except for the for-each and the /_each. Why are some of the codes missing?

2: Regarding table structures for report printing
My application is simple yet complex in some regards. I have a reports table, a groups table and a report Groups table. The latter table gets populated when I use the Setup Reports form. Whenever I open the Generate Reports form, the Selected Groups table gets populated based on the ReportGroups join table. It uses the current user key as part of the record, since the application will be multi-user. This table gets re-set every time I open the Generate Reports form. Its data is transitory. When I click the GenerateReport button, the LoadStudyGroupRoster code causes the StudyGroupRoster table to be cleared and re-loaded with all the fields for the current report to use. However, it does not include the userKey, so it will never work for a multi-user system, as multiple users may need to run a report at the same time. I see 2 solutions to this: Either add the UserKey field to this table and only clear records with that UserKey (instead of truncating table), or create a temporary table somehow related to the current user only, and use that one to populate with report data. I’m curious which way would be more “correct” for five.

I hope I made these questions clear. Item #1 is probably a bit more important right now, because it will be a LONG time before this is ready for live users. So for now I’m the only user. But it will need to be addressed eventually.

Thanks again!!!

[RileyAnderssen]

Hello,

Report Walkthrough

Inserted a helper using the _GroupBy function. Selected the ShowStudyGroupRoster query I created and then selected GroupName to tell the function to group records by the group name.

Pressing insert will then create this text.
Group By Header {{…}}, this is the field in which the data is grouped by, it will display this header at the top of each set of records corresponding to each heading.

Group By Footer {{…}}, same as header but at the bottom.

Also keep in mind that the text ‘Group By Header’, ‘Group By Footer’, and ‘Group By Final ShowStudyGroupRoster’ can be deleted if you don’t want it on the report.

I then added the fields from the query into the ‘Each’ section of the helper.

‘I originally put in a for-each field, a bunch of section codes appeared’

Doing this ^^ will add a grid with the group name specified above the grid and the records corresponding to that group name will display below.

Note: All of this has been done through only the use of the _GroupBy helper function.

‘If yes, did you set it to appear on all pages? If no, do I now insert a header component?’
To insert a header component, click on the button in the top tool bar that is grey in the image below.

Then, specify a height and that you want it to repeat on every page.

Customise the top grid item, whatever is in this grid item will then be displayed at the top of every page.

QUESTION 2
Either one of these strategies would work, personally, I would add the user key to the current table as this would be the most efficient strategy given the current structure of the database.

Hope this helps,
Thanks,
Riley.

[RMittelman]

Thanks for all of the help. What you provided reinforces the article on designing reports.

Some of my questions are not quite answered:

The order of inserting the Group-By codes and inserting the header: Does it matter which I do first?

In the prior version, the Group-By items Group-By and For-Each for the group needed to be above the header. I’m guessing this is no longer the case?

If I have a header defined and a group-by field, and the header is above the group-by codes, can I still refer to the group-by fields themselves in the header by using GroupedRecords.[0].whatever… type syntax inside the header grid?

If I want each roster to be on a new page, would I insert the page break code inside the Group By Header {{GroupName}} section? If yes, would that go right below that code? Also, will that cause an extra blank page at the beginning of the report, or is the report generator smart enough to ignore the first one at the start of the report?

Thanks for all your help so far…

[RMittelman]

In addition to the other questions above, I am having yet more questions as I develop the report.

I saved your template as StudyGroupRosterOLD, then created a new StudyGroupRoster. I changed the data source to the StudyGroupRoster query, which I modified to show all fields from the table, sorted by GroupName and MemberSortName.

Here is the template after adding the group-by and the header:

Here is the rendered report:

Please note, it shows all groups and members, but the screen shot is abbreviated.

What I don’t understand:
Why isn’t the header showing at all?
Why do the report detail lines show as they do when the report is rendered? I’m only asking for the group name in the group header section and the member name (first+space+last) in the {{#_Each GroupedRecords}} section. Why is sort name, group category and other fields showing on the rendered report in the detail section?

Not sure of the significance of the Root checkbox. I noticed for the helper function _group, not checking Root causes a minimal list of codes, while checking Root shows all of the expected codes. It is unclear what would happen if I check Root when inserting data fields.

Could you please address these questions and the ones above? I will be happy to supply another FDF file, but I’m not after you doing the work, just trying to figure out why is does not seem to render as designed.

Just realized, you may need the FDF to see what is wrong. I put a new one up on OneDrive. If you see what I did wrong, perhaps you can let me know.

[RileyAnderssen]

Hello,

It is a bit hard to explain all this so I have made some adjustments to the template report.

I have just uploaded an FDF (9.95 mB), if you look at the TestReport template in this FDF you will see the changes I have made. It now displays the group name heading and records below the group heading, each displaying on a new page. A header will not work as it does not co-operate well with the group by statement, however, you can use a normal table within the group by statement in which will display at the top of each page, essentially imitating the use of a header.

Hope this answers your questions! If I have missed anything, just let me know,

Thanks,
Riley.

[RMittelman]

Thank you Riley. I won’t be able to look at this until tomorrow (it’s 7:30 pm here) but I appreciate your response.

This is the first time anyone’s told me that group by didn’t work with page header. That’s unfortunate. I can certainly mimic the header in the group header section. I’m a bit worried about it working properly in case the study group has too many members to display on a single page. We have several groups like that.

Also, did you figure out why all of those fields show on the member area below the group header?

I would suggest that you put this on the enhancement list so someday we can use an every-page header as expected.

Thanks…

Ron Mittelman

[RMittelman]

I looked at the test report and saw what you did. Then I went into the StudyGroupRoster template to try and get that to work. I couldn’t figure out how to remove the header I had previously inserted. There doesn’t seem to be an easy way to delete it. So I clicked the </> button to see the html. I found the tag for the header and deleted it there. Now here is what my template looks like:

This is pretty similar to the TestReport, except I have a grid in the group header, with one cell having a fill color. In the GroupedRecords section, I have another grid containing MemberName, Position and MemberAddress.

Please note that I modified the menu so clicking on StudyGroupRoster in the running application actually runs the StudyGroupRoster.

When I run the application and click on StudyGroupRoster in the menu, this is what I get:

(once again, small snippet of a longer report).

First Problem: The report is in portrait mode, so I can’t see all the columns. I can’t figure out how to change orientation to landscape. I searched the documentation but couldn’t find anything about landscape mode. I can’t seem to attach a pdf file to this message, so the full report is in the FDFs OneDrive folder.

Second Problem: As I feared after your last message, the grouped records just continue past the end of the page to the next page without a page header. This is quite unacceptable.

Is there ANY WAY to incorporate a page header AND group-by?
This is VERY important. A decent report designer should be able to do this. If a page header would work with group-by, I could include field codes like GroupedRecords.[0].FieldName in the header. This worked somewhat in the prior version of Five.

When I was having problems with the prior version, Dom assured me that the report designer/generator was vastly improved and would work for me. There must be a way to get this to work. I don’t know how I can continue porting my application to Five if it can’t do reporting.

If you don’t know of any answer, can you check to see if one of the other developers may know this? (no offense meant).

Just for clarity, I am also copying the report from my Access application to show you what I’m trying to accomplish. This is also in the FDFs folder.

Newest FDF is also in the FDFs folder.

Thanks SO MUCH for your help so far!

[RileyAnderssen]

Hello,

To alter the report so it is in landscape mode:

You need to press the ‘#’ symbol in the report tool bar which will take you to these page settings.

As for your second question, I will pass this one onto another developer.

Thanks,
Riley.

[RileyAnderssen]

Hello,

Regarding your second problem, adding the header to the Group By functionality is still currently a future implementation, we will be sure to keep you posted about how this progresses.

Thanks,
Riley.

[RMittelman]

I’m having an issue with my query that clears, then loads, the StudyGroupRoster table for the report. The LoadStudyGroupRoster query is executed from the LoadStudyGroupRoster function. Here is just the first part of the query:

TRUNCATE TABLE `StudyGroupRoster`;

INSERT INTO `StudyGroupRoster` (
  `UserKey`, `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 DISTINCT 'userkey' AS `UserKey`,
  `StudyGroups`.`GroupName` AS `GroupName`, 
  `GroupCategories`.`Category` AS `Category`,
  `StudyGroups`.`GroupType` AS `GroupType`,

I’ve decided that this query will not work for a multi-user system, since it truncates a table that other users may be trying to work with to print their own version of the StudyGroupRoster. Therefore, instead of truncating the table and re-loading it, I will instead need to remove all records for the current user instead before re-loading the table.

Please note that I’ve just added the UserKey field into the mix so the table can be multi-user. Currently, this query can be compiled and saved.

Also, I am putting a dummy value into the UserKey field just for now. When I save the query, it successfully saves and returns to the query’s General tab. I also added a parameter to the query called UserKey, an expression: {{five.variable.UserKey}}.

When I replace this:

TRUNCATE TABLE `StudyGroupRoster`;

with this:

DELETE FROM `StudyGroupRoster` WHERE `UserKey` = ?;

I then get the following error trying to save:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `StudyGroupRoster` (
`UserKey`, `GroupName`,`Category`,`GroupType`' at line 3

If I restore the TRUNCATE TABLE statement, then it will save again. But, if I replace ‘userkey’ literal text in the SELECT DISTINCT clause with ?, then I get the same error again trying to save the query text. I thought I could use the ? either as a parameter in a WHERE clause OR as a placeholder for literal text that needs to be used for the query. I thought the defined parameter in the query would work. Do I instead need to put the value of UserKey into the ExecuteQuery statement in the function?

It’s funny that the query won’t even save. I’m not trying to execute it at this point, just save it.

Is this something you can help me with?

Thanks…

[RileyAnderssen]

Hello,

Would you be able to upload this version of the application to the One Drive.

Also regarding the five.variable.UserKey value, are you setting this somewhere within your application prior to calling this query?

If not, you may need to retrieve this from the stack rather than variable,

Thanks,
Riley.

[RMittelman]

Latest FDF is on the OneDrive folder.

I am setting UserKey at log-in time. This means it is not yet set if I’m altering a query or doing other things without actually running the application. Probably is not yet in stack either.

The question is more around why I get an error when saving changes to a query. Even a simple change that happens to mention the UserKey variable. I can understand the query not running properly if UserKey is not yet saved as a variable at run-time. I don’t understand why it causes a syntax error when saving the query at design-time.

So as I mentioned earlier, instead of truncating the table entirely before filling it again, I need to only remove all records put in there by the current user, then add records indicating which study groups to print with UserKey as one of the fields.

If there is a way instead to create a temp table unique to the current user, I can then do away with having to store the UserKey in the report table. Let’s say the table name was the current logged-in user’s GUID. I could truncate this table, or even more secure, drop the table if it exists, then create the new table with the proper records. This seems more flexible if Five lets me create tables at run-time. Please advise on this method. If it works, I don’t need to include UserKey at all. Please advise on the viability of this approach.

Thanks…

[RMittelman]

My last post was a bit rambling, but I think it is a decision between

1: Having a permanent table with records that contain a UserKey, and having to clear any records with that user key, then load the table with the appropriate records for the report, including the UserKey.

2: Being able to create a table at run-time which is named using the UserKey, such as Report_10000000-0000-0000-0000-000000000001.

#2 has some advantages. I believe MySQL can do a make-table query. All reports can use the same table as a data source. Dropping then creating the table would be more efficient than deleting some records.

I could also have a pre-defined table for each report/user combination, then I would truncate then fill the table. Either approach to #2 would work, but dropping then recreating the table from a SQL query seems a bit more efficient.

Both #1 and one of the #2 variations would require a separate table for each report, while the other #2 variation could use a generic single table for each UserKey, but all reports for that user.

Would you please address this issue and advise the best method between #1 and the variations of #2?

Thanks…

UPDATE:

I tried the following function code just to see the result of trying to drop the table:

function LoadStudyGroupRoster(five, context, result)  {
    
    let UserKey = '10000000-0000-0000-0000-000000000001';
    
    let queryResults = five.executeQuery(`DROP TABLE IF EXISTS Report_${UserKey}`);

    // let queryResults = five.executeQuery(`LoadStudyGroupRoster`);
    five.log("Logging query results");
    five.log(JSON.stringify(queryResults));
    return five.success(result);
}

This returned an error (somewhat truncated from the log):

37 near \"-0000-0000-0000-000000000001\" ","Notification":"","SysError":"line 1 column 37 near \"-0000-0000-0000-000000000001\"

[Jo-Anne]

Hi RMittelman,

I would recommend #1 going forward, even though this can be done in MySQL via executeQuery() without any issues, Five’s security permissions will likely cause you problems as Five will be monitoring queries performed against tables, and if the permissions are not found correctly this can/will cause problems going forward with your #2 design.

In regards to your UPDATE, table names with hyphens are not supported, unless you put the table name in back ticks, but I wouldn’t recommend this.

EG : DROP TABLE IF EXISTS Report_123-456 needs to be DROP TABLE IF EXISTS `Report_123-456`

Kind regards,
Jo

[RMittelman]

Thanks for your quick reply Jo. Too bad the simpler solution is problematic.

Your answer is confusing to me. Reading your message on my phone, both examples look very similar. Some text plus an underscore plus some numbers with one or more minus signs. I don’t know why your minus sign looks different than mine, wider. I used the minus sign next to the equal sign on a typical keyboard. What character did you use?

That’s probably moot if Five won’t allow dropping a table or creating a table via a SQL query. Shame. This means I will need a different table for each report.

Does Five also not allow creating and querying a temp table? If that is allowed, I could write a function to do both and that could be the data source for the report.

Thanks…

Ron Mittelman

[Jo-Anne]

My apologies Ron, I didn’t realise the forum editor removed the back ticks in the final output, I have now amended this in the previous message so you can see the difference.

Currently, dropping and creating tables including temp tables is not supported, however, I will bring this up in our next product review meeting.

Kind regards,
Jo