I'm building a paginated report with two lists, each tied to a different table in my dataset. These tables do not have a direct relationship as they are both separate fact tables, but they share a common field: Program Name. Each list contains visuals grouped by this field.
What I'm trying to achieve:
Each page of the report should display both lists vertically stacked for the same Marketing Program Name.
For example:
Current behavior:
I'm having a really hard time figuring this out and would sincerely appreciate any help from someone more experienced. Thank you!
The way I would normally build something like this is to use a main(Master) report and a subreport.
This is done from memory so might not be 100% accurate but should be close enough to follow
The subreport would accept a parameter, probably Program Name in your case and only be capable of showing data for that single program name.
So start by building this report, from your description it will only be a single page and let's say it has a single parameter called prog_name. Build your the report objects/tables/charts or whatever and make sure your dataset queries only return data for the specific parameter value. So in your case you might have two datasets, one for each report object and the queries would be something like SELECT * FROM myFirstTable WHERE progname = @prog_name .
Test the report by passing in different parameter values and once you are happy with it, save the report. Let say it's saved as _sub_MyReport.
Now create a new report. Create a dataset that just returns a list of program names. So the the query might be as simple as SELECT DISTINCT progname FROM myTable ORDER BY progname.
Now add a table to the report and delete all but one of the columns. Make the remaining column wide enough to accommodate your subreport (just smaller than the body width should be ok), the height does not matter.
Next we need to associate this table with your dataset, so you can either do this in the table properties or just drag the progname column from the dataset onto our single column.
Now in the table "cell" (textbox) delete anything that already exists (if you dragged the field to the table above) and the right-click this textbox and do Insert/Subreport. After the subreport control has been inserted, right-click it, then properties and then in the "report to use" select the sub report we created earlier, _sub_MyReport. On the parameters tab select the parameter prog_name (or type it in if it does not show in the dropdown) and then for the value, click the button to the right of text field and navigate to your dataset and choose the progname field.
Finally, in the rowgroup panel below the main designer, right-click the rowgroup (there should only be one) and in the properties, set page breaks to "between".
That should be it.
So, when you run the report, the main report will query the database and return a list of program names (e.g. 3 of them as per your example) and for each row it will generate a table row. In each table row will be a subreport and the value of progname will be passed to each in turn.