Start new page depending on Table Value

Questions related to nuBuilder Forte Reports and the Report Builder.

Start new page depending on Table Value

Postby fpdragon » Wed Jun 05, 2019 4:54 pm

Hi all and thank you for your hard work here at the board.

I'm working since a few weeks with nuBuilder and I have experienced a steep learning curve thanks to the videos on youtube and this forum.
However with this problem I am a little bit stuck and I'm not sure if this can be implemented with nuBuilder...

I have a working Report that creates a listing of a table. On every page there are several table entries printed.
One table column represents a kind of group.
The goal is to print out only elements with the same group on one page.
In other words, if the upcoming element group changed to the latest one then a new page shall be started.

Is this possible to be implemented with nuBuilder forte?

Thank you very much!
fpdragon
 
Posts: 5
Joined: Wed May 29, 2019 10:20 pm

Re: Start new page depending on Table Value

Postby fpdragon » Wed Jun 05, 2019 8:42 pm

Ok, I found out that under "Group Properties" I have to define a new group by entering filtering name inside one of the light grey fields.

Then I set the focus to this field again and on the bottom I was able to create a Header and Footer for this group by setting the "Section Height" > 0.

Here I can configure either for the Header or for the Footer a Page Break = Yes and it seems that this does the magic.

In addition I have to mention that every page needs to have the current group in the "Page Header".


When I enable Page Break for the Header I am quite near to what I need.
Elements are grouped per page depending on my grouping values.
The problem here is that it seems the the Page Break is executed for the first page also. So the first page is always empty.

Page 1:
- (Page Header Group 1)
-- empty page with page break

Page 2:
- (Page Header Group 1)
-- (Item 1, Group 1)
-- (Item 2, Group 1)
-- (Item 3, Group 1)
-- space and page break
Page 3:
- (Page Header Group 2)
-- (Item 1, Group 2)
-- (Item 2, Group 2)
-- space and page break
Page 4:
- (Page Header Group 3)
-- (Item 1, Group 3)
-- space and page break
...


When I enable Page Break for the Footer then the first page starts as expected correctly. No more empty page.
But now a new starting page seems to not recognize the new group. For example:
In the "Page Header" I have a Field referencing to the grouping value. But the value seems to be taken from the last value of the side before:

Page 1:
- (Page Header Group 1)
-- (Item 1, Group 1)
-- (Item 2, Group 1)
-- (Item 3, Group 1)
-- space and page break
Page 2:
- (Page Header Group 1)
-- (Item 1, Group 2)
-- (Item 2, Group 2)
-- space and page break
Page 3:
- (Page Header Group 2)
-- (Item 1, Group 3)
-- space and page break
...

So for Page 2 the values of the Page Header are seaming to reference to (Item 3, Group 1).
For Page 3 it is (Item 2, Group 2)


So I have two possible configurations but none is working correctly for me.
Any Idea how to handle this?
fpdragon
 
Posts: 5
Joined: Wed May 29, 2019 10:20 pm

Re: Start new page depending on Table Value

Postby Janusz » Thu Jun 06, 2019 9:22 am

Hi,
Potentially you could use following solution:
1. create new empty table and step by step add records you would like to place on the report
2. after each set - calculate how many records is missing to fully cover the page - use Modulo to calucate it - MOD or x % y.
3. add to the table the missing records - as empty records
4. go to the next group an so on

please find enlcosed an example - in this case I added manually empty records - but you need to automatise it based on calculated values.

https://drive.google.com/open?id=1H8leE ... A3jWgwZKJB


and here some code I used based on one of my existing table - it's completelly not automatised - just first trial - to verify general idea only. But normally there is no issue to have it working fully automatically for any numbers of groups. Here the missing records are calulated just for 2 groups, test1 and test2.
Code: Select all
SET @rows=26;   -> number of raws per raport page

Requests_temp -> list of common groups you can get with select distinct

SET @x1=(select rqt_ref FROM Requests_temp WHERE Requests_temp_id=1);
SET @x2=(select rqt_ref FROM Requests_temp WHERE Requests_temp_id=2);

qx1, qx2 - number of records per each group

SET @qx1= (SELECT COUNT(his_request) FROM historia2 WHERE his_request = @x1);
SET @qx2= (SELECT COUNT(his_request) FROM historia2 WHERE his_request = @x2);
SET @empty_records_1= @rows - (@qx1 % @rows);
SET @empty_records_2= @rows - (@qx2 % @rows);
SELECT @qx1, @qx2, @empty_records_1, @empty_records_2;
TRUNCATE historia2;

INSERT INTO historia2 select
null, his_num_sztuki, hist_location, his_request, his_info, his_created_by, his_updated_by, his_created_on, his_updated_on, his_removed
from historia WHERE his_request = (select rqt_ref FROM Requests_temp WHERE Requests_temp_id=1);

INSERT INTO historia2 (his_request) VALUE ('.');INSERT INTO historia2 (his_request) VALUE ('.');INSERT INTO historia2 (his_request) VALUE ('.');INSERT INTO historia2 (his_request) VALUE ('.');INSERT INTO historia2 (his_request) VALUE ('.');

INSERT INTO historia2 select
null, his_num_sztuki, hist_location, his_request, his_info, his_created_by, his_updated_by, his_created_on, his_updated_on, his_removed
from historia WHERE his_request = (select rqt_ref FROM Requests_temp WHERE Requests_temp_id=2);
Janusz
 
Posts: 162
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Start new page depending on Table Value

Postby fpdragon » Thu Jun 06, 2019 7:02 pm

Janusz wrote:Hi,
Potentially you could use following solution:
1. create new empty table and step by step add records you would like to place on the report
2. after each set - calculate how many records is missing to fully cover the page - use Modulo to calucate it - MOD or x % y.
3. add to the table the missing records - as empty records
4. go to the next group an so on
...


Wow, thank you for your detailed post. There is much knowledge in it.

I just found another simpler solution that might be also interesting for others. It's kind of a dirty hack but hey, it seems to work.

I am thinking of using the Page Break in the Group Footer. The problem was that elements in the "Page Header" are seeing the context of the last element from the page before, not the current page.
Then I came to the idea that I could move the needed Object to the "Page Footer" where the context seems to be switched to the latest element of the current page. That's the correct context I need.
So now the only problem is that the value is shown at the bottom of the page instead of the top where I intended to place it.
Now the work around comes into the game. I could keep the element logically in the "Page Footer" and change the optical position with a huge negative Top positioning value. Currently I'm using -700 for "Top". This makes it appear inside the Page Header but is still logically a part of the Page Footer and gets the correct element context and data value.

In the "Report Designer" we have to make sure to not touch or move this object from now on since the tool seems to move the element automatically into the Page Header depending regarding it's position. It is possible change the object's attributes without triggering this effect by using the "Select Objects" listing. Here the optical and the logical positioning is not changed and the element stays logically in the Page Footer.

I think I'm going with this solution. It should work as long as the paper size or the orientation is not changed.
fpdragon
 
Posts: 5
Joined: Wed May 29, 2019 10:20 pm


Return to Reports