Report "keep together on page" challenge!!!

[Progytech]

Re: Report "keep together on page" challenge!!!

Cornelius,
Since you are able to duplicate this issue, summit a bug report to Alpha team and they can take a look at it and fix it if it is really a bug or a setting you may be missing.

[Solarscholar]

Re: Report "keep together on page" challenge!!!

Cornelius,
Since you are able to duplicate this issue, summit a bug report to Alpha team and they can take a look at it and fix it if it is really a bug or a setting you may be missing.

Hi,

I have received the following feedback relating to this issue. It does seem to be a known issue with at least 3 people requesting it.

I have not done anything on the HTML keep implementation as there are other more immediate concerns I am looking into (first supporting services, then internationalization issues by switching from DBF to BSON - as BSON supports UTF8 encoding).

What should come for free with the internalization changes, is a BSON representation of the data - which should make it possible to do real subreports (with keep/ continuation headers and footers) - because BSON is an object database format, it can be used to represent nested rows, which is perfect for subreports.

In the DBF world, we had SET based subreports, and you could point at a one-to-many child table to source a child report, since the subreport data context was know to the parent report, it was possible to generate the supreports incrementally (page-by-page) rather than pre-render as we do with HTML.

So far, there are three people who have asked for pagination features (others have asked for continuation headers in subreports, but that is the same problem - knowledge of page layout), so this will be addressed, but only after I switch to BSON as the datasource for reports.

I will send you an email once I have something working, though it will likely require a redesign of your reports, as linked reports save an HTML template, this will require an actually nested report definition instead.

[roe3p]

Re: Report "keep together on page" challenge!!!

Ok, sounds promising - cheers for posting the response!

[Solarscholar]

Re: Report "keep together on page" challenge!!!

Hi,

I have actually tested this to more depth and found that the "linked report" does in actual fact not render most of its formatting settings/features once displayed in the main report.

Because of this my linked report does not pull in continuation headers which also could have provided a workaround for my problem. The linked report template as displayed in the main report does NOT even mention ANY code of the continuation headers/footers even though they are in the original table layout report.

Bummer!!! Cian, we really need you.....................................

[roe3p]

Re: Report "keep together on page" challenge!!!

Yes, I found the same thing :-(

I tried quite a few workarounds - adding soft and hard page breaks into page headers, rtf fields with conditional page breaks, html fields with hard breaks, all to no avail as they get stripped out when embedded, as you've found.

The closest I came to a vaguely working solution was adding a running total calculation using each record's content (text length, whether an image is present) to estimate whether it might cause a page spillover - if so, it inserts enough <br>'s before the record to create a manual 'spacer' to push it onto the next page. It's very kludgy though, and only works for a couple of pages before becoming misaligned.

Waiting with bated breath for a proper solution!

[Solarscholar]

Re: Report &quot;keep together on page&quot; challenge!!!

No feedback as yet Rohan.

It seems that orders higher up in the chain of command would not allow for a "quick fix" but rather a comprehensive solution [which I understand], however, "time" is my enemy and the fact that this issue is actually a BUG not to be fixed vs. the time required to develop the comprehensive solution, is delaying progress to my client solutions. It is frustrating for there seems to be NO alternative and/or solution throughout Alpha 5 for this issue.

Sub reports vs linked reports - wow, both valid choices with issues on both sides.
Sub reports - issues with multiple one to many tables
Linked reports - HTML pre-rendering of report structure and formatting when linked.

I'm not clever enough for this and haven't felt this hopeless in a very long time.

[lgrupido]

Re: Report &quot;keep together on page&quot; challenge!!!

There is another way, I think. It can be kind of a pain to implement, but powerful. This assumes you are using SQL - though you might be able to do it in DBF too - I don't know.

Instead of using a subreport, just put all of the data you want into a memo type field.

You will build the field from a function that takes the multiple records you need and builds them into an HTML table.

So the value of the field is something like:

<table (table definition)>
<tr><td>my datat</td><td>more data</td></tr>
all the related rows that would be in the subreport
</table>

Then you use printhtml(fieldname) in alpha to put the field into the report.

That will give you complete control of the "subreport" field.

Maybe that will help?

[Al Buchholz]

Re: Report &quot;keep together on page&quot; challenge!!!

though you might be able to do it in DBF too - I don't know.

Pretty much the same way in .dbf

Been doing that for a long time.

[Solarscholar]

Re: Report &quot;keep together on page&quot; challenge!!!

There is another way, I think. It can be kind of a pain to implement, but powerful. This assumes you are using SQL - though you might be able to do it in DBF too - I don't know.

Instead of using a subreport, just put all of the data you want into a memo type field.

You will build the field from a function that takes the multiple records you need and builds them into an HTML table.


So the value of the field is something like:

<table (table definition)>
<tr><td>my datat</td><td>more data</td></tr>
all the related rows that would be in the subreport
</table>

Then you use printhtml(fieldname) in alpha to put the field into the report.

That will give you complete control of the "subreport" field.

Maybe that will help?

Pain is my only way out Ha Ha.

Kindly point me to where to create this function as it is SQL [fortunately] As specified earlier, this is way beyond my experience. Example of the function I could perhaps adapt.

[lgrupido]

Re: Report &quot;keep together on page&quot; challenge!!!

What version of SQL? My Sql, MS Sql or something else?

[Solarscholar]

Re: Report &quot;keep together on page&quot; challenge!!!

SQL 2014 Express

[lgrupido]

Re: Report &quot;keep together on page&quot; challenge!!!

OK. Take the code below and run it in a query window in the database of your choice. it will construct an example table and function that creates an HTML table of the data. My next post will contain information on how to use it:

CREATE TABLE [dbo].[FocusGroups](
	[TestID] [int] IDENTITY(1,1) NOT NULL,
	[FocusGroup] [varchar](15) NOT NULL,
	[FullName] [varchar](50) NOT NULL,
	[FullAddress] [varchar](50) NOT NULL,
 CONSTRAINT [PK_FocusGroups] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[FocusGroups] ON 

GO
INSERT [dbo].[FocusGroups] ([TestID], [FocusGroup], [FullName], [FullAddress]) VALUES (1, N'Group 1', N'Test Subject A', N'900 Downy street, testville, MD 34567')
GO
INSERT [dbo].[FocusGroups] ([TestID], [FocusGroup], [FullName], [FullAddress]) VALUES (2, N'Group 1', N'Test Subject B', N'123 anywhere lane, Detroit, MI')
GO
INSERT [dbo].[FocusGroups] ([TestID], [FocusGroup], [FullName], [FullAddress]) VALUES (3, N'Group 1', N'Test Subject C', N'456 Picup sticks, Chicago, IL')
GO
INSERT [dbo].[FocusGroups] ([TestID], [FocusGroup], [FullName], [FullAddress]) VALUES (4, N'Group 2', N'Test Subject D', N'789 Brandywine, Toledo, OH')
GO
INSERT [dbo].[FocusGroups] ([TestID], [FocusGroup], [FullName], [FullAddress]) VALUES (5, N'Group 2', N'Test Subject E', N'101 Sunnyville, Chicago, IL')
GO
SET IDENTITY_INSERT [dbo].[FocusGroups] OFF
GO

CREATE FUNCTION [dbo].[FocusGroupTable] 
(
     -- Add the parameters for the function here
     @Group varchar(15)
)
RETURNS varchar(max)
AS
BEGIN
     -- Declare the return variable here
DECLARE @XYList AS varchar(MAX) -- Leave as NULL
declare @Current_Row as varchar(999)
declare @fullname as varchar(50) 
declare @fulladdress  as varchar(50) 
declare @count as int

DECLARE XY_Cursor CURSOR FOR 
select FullName, FullAddress
from FocusGroups as fg
where fg.FocusGroup = @Group
order by FullName

-- create the start of the table
set @XYList = '<TABLE border="1" bordercolor="#000000" cellpadding="3" cellspacing="0" width="100%"><TBODY>'
-- create the header row
set @XYList = @XYList + '<TR><TD width="50%"><B>Name</B></TD><TD width="50%"><B>Address</B></TD></TR>'

OPEN XY_Cursor

set @Count = 0

FETCH NEXT FROM XY_Cursor INTO @fullname, @fullAddress

WHILE @@FETCH_STATUS = 0
BEGIN

set @XYlist = @XYList + '<tr><td valign="Top">' + @FullName + '</td><td>' + @fulladdress + '</td></tr>'

set @count = @count + 1

FETCH NEXT FROM XY_Cursor INTO @fullname, @fullAddress

END  -- while

CLOSE XY_Cursor
DEALLOCATE XY_Cursor 

if @Count = 0
set @XYList =  null 
else
begin
set @XYlist = @XYList + '<tr><td valign="Top">Total Count</td><td>' + str(@Count) + '</td></tr>'

set @XYList = @XYList + '</TBODY></TABLE>'
end

RETURN @xylist

END


GO
/****** Object:  Table [dbo].[FocusGroups]    Script Date: 3/10/2015 2:07:10 PM ******/
SET ANSI_NULLS ON
GO

[lgrupido]

Re: Report &quot;keep together on page&quot; challenge!!!

So, once you have successfully run the script, you can open a new query window and run this statement:

select focusgroup, dbo.FocusGroupTable(FocusGroup) as htmltable
from FocusGroups
group by FocusGroup

You could take the above select statement and use it to create a report.

In the report, create a calculated field as such:

mytable = printhtml(htmltable)

And use the calculated field in the report. Make sure it "can grow"!

See if you can make it work a little easier that way.

[Solarscholar]

Re: Report &quot;keep together on page&quot; challenge!!!

So, once you have successfully run the script, you can open a new query window and run this statement:

select focusgroup, dbo.FocusGroupTable(FocusGroup) as htmltable
from FocusGroups
group by FocusGroup

You could take the above select statement and use it to create a report.

In the report, create a calculated field as such:

mytable = printhtml(htmltable)

And use the calculated field in the report. Make sure it "can grow"!

See if you can make it work a little easier that way.

Hi Larry,

It actually worked but hugly complex.

I reviewed my setup and changed it as such;
Structure [Old];
Main Report [Freeform]
Linked Report 1 = "Specifications" [One to Many]
Linked Report 2 = "Analysis" [One to Many]

Changes
Linked Report 1 has many fields so I changed the main report to include this sql table "Specifications" on an inner join. I then grouped each of the "many" subsections individually in the main report. This works and does not "creep" or overflow anymore in the main freeform report.

Linked Report 2 DOES link correctly as it is the only linked report now, BUT, it creeps over the pages.

I have only 3 fields in this sql table called Analysis;
SpecificationLinkID [Foreign link key for table specification]
Imagefield [varchar(100), image reference field]
ImageInfo [sql text, alpha textarea field]

The whole report displays fine except that the linked report 2 "creeps" or overflow and does not start a new record on a new page [keep together]. It prints half on one page and half on the next page. I need to keep the records together and not overflow to the next page partially. [Remember, it is a linked report]

How do I structure your code to ONLY include this "Analysis" table with the 3 fields WITHIN THE CURRENT main freeform report.

Goodness me, I think I confused myself now........ Does it actually make sense?

[lgrupido]

Re: Report &quot;keep together on page&quot; challenge!!!

For a deeper analysis I would much rather help you in person, over the phone or maybe via remote control.

Message me your email or phone and we'll get it done. Then we can post the final solution here. Otherwise I fear it will take a lot of time.

Otherwise, please post your current select statement for your main report and I can show how I would add what you need to it.

I assume you are showing the image in the report and not just the filename, which will make the structure of the function a bit different since we need to add an image tag.