How to build a budget sheet report with side-by-side sections (1 Viewer)

[amorosik]

I would like to create a report to display information from 4 distinct sections, Attivita, Passivita, Costi, Ricavi
Like this figure
Attivita and Passivita must be placed side by side, the first on the left and the second on the right
The number of lines present in Attivita is variable, the same for the lines contained in Passivita
Once all the lines contained in Attivita and Passivita have been displayed, then the lines contained in Costi and Ricavi must be displayed in the same way, the first in the center on the left, the second on the right
I was thinking of inserting 4 subreports into a main report, each containing the rows of its own section, but it's not that simple
And so the question is: how to allow the individual subreports to 'grow' vertically depending on the number of rows present?
And also, how to start the Costi and Ricavi subreports at the same height and continue below with the lines contained in each of the two sections?

 

[Edgar_]

What I see in that screenshot is an interactive table capable of collapsing/uncollapsing nested data on demand. Easy to build using a web framework, but hard for Access on its own.

Hoping someone proves me wrong, the only alternatives you have here, that can almost mimic that behavior are a tree view control, but I think you wouldn't be able to view other columns in a tabular way, you'd have to append the data to each node. An alternative to that would be using a regular table where you add prefix indentations according to its nested level. Very rudimentary, but could probably work.

 

[amorosik]

What I see in that screenshot is an interactive table capable of collapsing/uncollapsing nested data on demand. Easy to build using a web framework, but hard for Access on its own.

Hoping someone proves me wrong, the only alternatives you have here, that can almost mimic that behavior are a tree view control, but I think you wouldn't be able to view other columns in a tabular way, you'd have to append the data to each node. An alternative to that would be using a regular table where you add prefix indentations according to its nested level. Very rudimentary, but could probably work.

Yes of course, I just wanted to make clear the aspect of what I want to achieve
Which is essentially the two sections side by side, and then the other two always side by side
I thought it could be done with the classic subreports but I see that it is not possible because once placed in the main report the subreport has a fixed height and therefore does not expand according to the number of lines present inside it

 

[June7]

No, subreports can shrink/grow. CanGrow and CanShrink properties of container control determine this. Otherwise, subreports would be useless.

Now, if you want side-by-side reports to show same number of rows, that is another matter.

What framework was that image obtained from?

 

[amorosik]

What do you mean by 'container control'?
I'm experimenting by inserting a subreport inside the Page Header section, I'm not using any 'container controls'

As regards side-by-side reports, the norm is that they have different numbers of records, and therefore the Attivita section will have 10 lines while the Passivita section will have 25 lines (for example)

"..What framework was that image obtained from?.." what do you mean exactly?

 

[Edgar_]

For them to grow, you must have your headers in the Page header and your items in the Detail section.

 

[amorosik]

For them to grow, you must have your headers in the Page header and your items in the Detail section.
View attachment 112364

This is a 'normal' report
But with this system I can't have two sections side by side
See this example in the Attivita and Passivita section

 

[June7]

Regardless of how you 'insert' a subreport, this results in installation of a container control that holds report object (same for subform). You can drag a report onto another report and Access will automatically install container control and name it same as report it holds. Same is manually created with Design tab Subform/Subreport control. Drag this control to form or report and set its SourceObject property.

I was curious how that image was created. The arrows to control expand/collapse feature is not normal Access construction - at least I have never seen.

 

[Edgar_]

But with this system I can't have two sections side by side

But you can... you need to have one main form with two subform controls side by side. Each subform control would be sourced by a report where you can embed other reports.

 

[amorosik]

Regardless of how you 'insert' a subreport, this results in installation of a container control that holds report object (same for subform). You can drag a report onto another report and Access will automatically install container control and name it same as report it holds. Same is manually created with Design tab Subform/Subreport control. Drag this control to form or report and set its SourceObject property.

I was curious how that image was created. The arrows to control expand/collapse feature is not normal Access construction - at least I have never seen.

Ah ok, In my case I have a report that I will call rptBilancio, and in the PageHeader section I have dragged two subreports, the first rptAttivita and the second rptPassivita

 

[amorosik]

But you can... you need to have one main form with two subform controls side by side. Each subform control would be sourced by a report where you can embed other reports.

Sorry but I don't understand
I have to prepare a report where the information can be displayed
Why do you write about forms and two suforms?

 

[June7]

Edgar just made a typing error. Main report with subreports.

Now I understand why subreports are not growing. Cannot be in page header/footer. Must be report header/footer or detail section.

 

[Edgar_]

Yeah, main report, two subreports side by side, sourced by yet more reports with embedded subreports.

 

[amorosik]

For anyone who has the patience to help me, I attach a file with an example of the report I am trying to prepare

 

[June7]

Why don't you try what is already suggested? Move subreports to Detail section.

 

[ebs17]

The number of records in the subreports varies due to the filtering in the ATTIVITA_PASSIVITA_COSTI_RIC field. So you could count and determine the maximum number before opening the main report.

SELECT TOP 1 Q.ATTIVITA_PASSIVITA_COSTI_RIC, Q.X
FROM
(SELECT ATTIVITA_PASSIVITA_COSTI_RIC, COUNT(*) AS X
FROM BILANCIOCONTABILE
WHERE ATTIVITA_PASSIVITA_COSTI_RIC > ""
GROUP BY ATTIVITA_PASSIVITA_COSTI_RIC) AS Q
ORDER BY Q.X DESC

You could now adjust the desired report heights using the dependency between the number of records and the subreport height, as well as the positioning of subreports 3 and 4. Everything in the details section of the main report.

 

[amorosik]

It was sufficient to move the subreports to the detail section and now the desired effect has been obtained
Thank you very much

 

[amorosik]

The number of records in the subreports varies due to the filtering in the ATTIVITA_PASSIVITA_COSTI_RIC field. So you could count and determine the maximum number before opening the main report.

SELECT TOP 1 Q.ATTIVITA_PASSIVITA_COSTI_RIC, Q.X
FROM
(SELECT ATTIVITA_PASSIVITA_COSTI_RIC, COUNT(*) AS X
FROM BILANCIOCONTABILE
WHERE ATTIVITA_PASSIVITA_COSTI_RIC > ""
GROUP BY ATTIVITA_PASSIVITA_COSTI_RIC) AS Q
ORDER BY Q.X DESC

You could now adjust the desired report heights using the dependency between the number of records and the subreport height, as well as the positioning of subreports 3 and 4. Everything in the details section of the main report.

I didn't use this tip because moving the subreports to detail already worked as required
But this tip is particularly interesting and I will definitely use it in other contexts
Thank you