Linking ID field (subreport)

[bouser69]

Hi

I am calculating the averages of 2 fields. One field in main report and one field in subreport. The result of Average should be based on the Id's that are in mainreport and subreport. I need to link the Main report Id and Subreport Id. For this purpose I created a Id parameter at subreport query and I linked the main report field to this parameter field. If I do this I am not getting any result. How could I link this fields.

To be clear with the data

In main report data subreport data
ID Response ID Respone
111 6 111 8
222 10 122 10
333 15 133 12
444 20 222 16.

Only 2 IDs are in main report and subrpeort so I need main report average as 8 and subreport average as 12. Please let me know if you need more information.

 

[IanWaterman]

You will need shared variables to capture value from main report and then use within subreport to calculate average.

Sub report will need to evaluate in the group footer of the ID in the main report.

Main report var will then need to be reset in header of next ID.

Ian

 

[lbass]

Is there a reason you are using a subreport instead of linking the subreport table to the main report table? Because the main problem is how to now limit the main report fields not present in the subreport from contributing to the main report average.

Where is the subreport currently located (what report section)?

-LB

 

[bouser69]

Thanks Ian

after creating the shared variable for Id in mainreport I I used in subreport. But how can I calculate average. I don't have any groups in mainreport or in subreport.

 

[bouser69]

Thanks lbass

The reason that I am using subreports are I do have different conditions for subrpeor and mainreport. I couldn't clearly understand this question

instead of linking the subreport table to the main report table?

I am placing subreport in report footer

 

[lbass]

Are you using the same table in both main report and subreport? To get the same IDs for the averages, it would be simpler if you eliminated the subreport and instead added the table a second time, linking only on the ID field. You could add your criteria to each table in the record selection formula. Then only the matching IDs would appear in the report. Then you could simply insert summaries (average) on the response fields from each table.

-LB

 

[bouser69]

Hi lbass

ok I got you. The problem there is in report I do have prompts if I add second query I need to add these 2 prompts in 2 queries then I am getting double prompts issue. If I remove prompts in one query it is impacting on performance. for that reason I am including the subreport

 

[lbass]

No, I don't see why you would get double prompts. There would be only one query. Can you verify whether both sets of data come from one table?

-LB

 

[bouser69]

I am using universe as a datasource. The joins and tables in both queries are same with different conditions. Even if I remove those conditions also I am getting double prompts.

I am not sure what I am doing wrong.

 

[bouser69]

Thanks a lot for your replies lbass

It worked. The query name also should be the same. Before that it is different now I changed the query names it worked.