Lost in the groups...

[Jennypi]

Hello,

I have a quite complicated query. I can't manage to design the report.
I tried to split it in 2, but it's the same, I don't know how to arrange the groupheaders...

The most complete query works like this:
Plot | previous data | current data
1 | 1 | 1
1 | 1 | 3
1 | 2 | 1
1 | 2 | 3
1 | 3 | 1
1 | 3 | 3
2 | 1 | 8
2 | 1 | 10
2 | 1 | 11
2 | 2 | 8
2 | 2 | 10
2 | 2 | 11

In the report I would like to see:
- The groupHeader with the average of previous data
- and below a data band with the numbers of "current data" (1 and 3 in the plot 1 / 8, 10 and 11 in the plot 2):
---1-- (+ average of previous data (1, 2, 3))
1
3
--2--
8
10
11

As I couldn't manage to arrange this, I've split my query in 2.
First query retrieves:
Plot | previous data
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2

While the second query retrieves:
Plot | current data
1 | 1
1 | 3
2 | 8
2 | 10
2 | 11
I linked both query with a relation. Still, I can't have this report working properly...

could you please have a look?
I'm joining both reports (one or two datasources) with sample data.

Thank you.

[HighAley]

Hello, Jenny.

If we understand you right, here is a sample report with necessary result.
If there is something not as expected, please, send us a sample with columns from your data and more simple report template.

Thank you.

[Jennypi]

Thank you Aleksey for your reply.

I understand your solution and it works well with your example.
The problem is that my data are not so regular:
- sometimes previous_data is empty, while current_data is not empty
- sometimes it's the opposite: there are previous_data but no current_data
- and the number are not always starting with 1.

I'm joining a more simple example with simplified datasource and report.
Please have a look at:
- plot 318: current_data is empty, but problem: the DataBand for current_data is still printing
- plot 322: previous_data is empty. In that case, there seems to be no problem.
- plot 328: here we have both previous_data and current_data. Problem: current_data prints as many times as we have previous data.
Also, as you can see, the databand always print the first row twice: it repeats it at the top of each page, I don't know why.

Sans titre.png (8.69 KiB) Viewed 1990 times

I hope this is more understandable with this new example.
Thank you for your help!

[Jennypi]

It should work better if I attach the files

[HighAley]

Hello, Jenny.

- plot 318: current_data is empty, but problem: the DataBand for current_data is still printing

Please, set the Can Shrink property of all components and Data Band to True.

- plot 328: here we have both previous_data and current_data. Problem: current_data prints as many times as we have previous data.

You should add group header and show data on it. You should add a Condition to the Group Header to remove duplicates values. For instance, {data.current_data_details}

Also, as you can see, the databand always print the first row twice: it repeats it at the top of each page, I don't know why.

Please, set the Print On All Pages property of the Data band to false.

Thank you.

[Jennypi]

Hi Aleksey,

Thank you so much for your solution, it works as expected!
Instead of setting "Can shrink" to true, I added conditions in the Before Print event of the GroupHeaderBand1.

Now, I want to add something more
If there is no "current_data" for the plot, I want to print as many rows as "current_number".
So I added DataBand2, linked to DataBand1, and added "DataCount" code to the Before Print event of the GroupHeaderBand1.

Problem: DataBand2 prints as many times as I have rows for the plot, in the datasource.
You can find an example attached, with plot 318. 8 rows are printed twice.

I can't manage to have it printed once...

Can you please help me by having a look?

Thank you!

[Alex K.]

Hello,

In this case, you need use the additional data source (as a way "Data from other DataSource") with grouping by "plot", "current_data", "current_data_details" and "current_number" and use them as datasource for band.

Please check the modified report in the attachment.
Thank you.

[Jennypi]

Thank you Aleksey.
I am trying to reproduce this solution in my "real" report.
Problem: I'm using calculated columns in my main datasource (data) to get a number of days between some dates. I need those calculated columns, as I cannot calculate them directly in the SQL query, it's too much complex (fields retrieved by different sub queries)

It seems that I cannot use those calculated columns in the results of second datasource (data1), as the columns are not available in the list. I want indeed to calculate the average of those date differences.

How can I do that?

Thank you!

[Jennypi]

Ok, it seems I can calculate the date difference directly in the application I'm using (connecting to the DB). So I'm going to try to retrieve it directly, without calculation.
I will let you know how it goes

[Alex K.]

Hello,

Ok.
Please let us know if you need any additional help.

Thank you.