Advice for report with two details

[Fabio Pagano]

Sorry if it is a bit long, but i really need help on this.

I need to design a report (in this case an "offering") with this structure (to obtain indentation on forum i use the code tag):

Code: Select all

Offering header
	Name of installation
		Works to do (detail)
		TOTAL money amount of Works to do
		Variants to works to do (detail)
	Name of installation
		Works to do (detail)
		TOTAL money amount of Works to do
		Variants to works to do (detail)
	Name of installation
		...
	TOTAL MONEY OF AMOUNTS OF WORKS TO DO FOR THIS OFFERING
Offering feet

I have a flat datasource whose records contain all offerings data (no datarelation). The table structure is:

Offering number
Installation name
Work id
Total amount of works for the installation
IsAVariant (boolean)

The number of records is given by the number of works and variants.

DataSource example:
Say we have three installations, two in one offering and one in another.
Say that for each installation i have one work and one variant.
In this case the datasource is made of 6 rows (one for each work or variant); example (fields are in the sequence stated above):

(Offering 001)
Record 1: 001 Installation1 Work01 500 False
Record 2: 001 Installation1 Variant01 500 True

Record 3: 001 Installation2 Work02 130 False
Record 4: 001 Installation2 Variant02 130 True

(Offering 002)
Record 5: 002 Installation3 Work03 250 False
Record 6: 002 Installation3 Variant03 250 True

The final report result should be something like:

Code: Select all

Offering number: 001
	Installation1
		Works Id:
		   Work01
		Total amount of Works: 500
		Variants Id:
		   Variant01
	Installation2
		Works Id:
		   Work02
		Total amount of Works: 130
		Variants Id:
		   Variant02
Total offering number 001: 630

--- New Page

Offering number: 002
	Installation3
		Works Id:
		   Work03
		Total amount of Works: 250
		Variants Id:
		   Variant03
Total offering number 002: 250

So, i'm struggling with bands and groups but i can't achieve nothing.

Anyone can help with some design hint?

Note that the data source cannot be changed and i cannot write any code in report.

Thank you.

[Brendan]

Hi Fabio,

Can there be more than 1 Work ID per Offering and more than 1 Variant per Offering?

I think there's probably a number of different ways you could produce this report.
You say you cannot change the Datasource, this is ok, but can you use "Data from other Datasource" option in StimulReports?

If so you could build 3 new Tables within your report using the "Data from other Datasource" designer.

Lets say your original Datasource you register with your report is called Offering

You could create 3 new datasources based on this original data.
One Could be called OfferingList. This would be a Group list of OfferingNumber and InstallationName

The Second one would be a Datasource for Works. This could have a filter for IsAVariant = false and no grouped Columns. The Results page could have 4 columns (WorkID, TotalAmount, InstallationName, and OfferingNumber)

The Third one would be a Datasource for Variants. Almost exactly the same as the Works Datasource but with the IsAVariant filter = true and no need for the TotalAmount column.

You would need need to create 2 relations, one for the works datasource and one for the variants datasource that link back to the OfferingList datasource.


Now you can design your report with these 3 new Datasources based on your original Data.
If you'd like to see a sample let me know.

Regards,
Brendan.

[Fabio Pagano]

Yes, there can be more than 1 Work ID per Offering and more than 1 Variant per Offering.

I cannot change anything in datasource: not because i have not the capacity or authorizations, but because i am not the final user of the report: the final users are our clients (about 190 here in Italy), we are the developers of the accounting software in which we have integrated StimulReport as the reporting tool. Usually we provide in the report a single dataset, and the clients change the "base" report. The problem is that the datasource must remain with the structure i stated above, because the same datasource is used for many kinds of reports (and works fine).

So, given the datasource, the clients cannot use "Data from other Datasource", because this would be too complex for them.

I know this is a great limitation but actually we cannot do differently.

If you have other ideas, or somebody else, let me know.

Thank you very much for the answer.