Multiple data sources ... Passing variables between 2 RTF Templates

Question

Summary

2 Reports in 1 that share variables?

Content

Hi all,

I am converting a crystal report that has a subreport to a BI Publisher report made from 2 separate queries.

In the first part of the report is a list of contracts with work type codes.

The second part of the report (The subreport) contains a list of all Work Type codes and the number of contracts that exist for each of these work type codes. The number of contracts per work type code comes from the first part of the report.

In BI Publisher, and PS Query, it is not clear how to pass these Work Type Counts from the 1 report query to the second report query.

BIP reports can only be built on 1 data source. So, how would you approach this problem... a Connected query, Peoplecode and app engine, sub templates??

I've attached a visual of the current Crystal report for reference.

Please advise.

Thanks,

John

John Bruso1 · Answer

My solution for this was to write a custom view that used SQL from the first query and then left join to the work type code query. This allows me to run the two reports separately on their own. Then, I merge the two report result pdfs after processing into 1 pdf. I wish there was a more elegant solution for this, but it works.