Compare Parameter before pushing to sub report

[beauman]

My main report is an Invoice register containing all of the invoices against a job number. The table looks like this.

Job# InvoiceID Amount Type
12345 00001 200.00 Inv.
12345 00002 -50.00 CAdj
54321 00105 500.00 Inv.
34561 00053 600.00 Inv.

Since I want all of the Invoices to appear on my main report, this is fine. However, I need info in another table that relates to the Job#. I only want to push that Job# info once. In order to link to my sub report I need to pass Job# as a parameter. Is there a way to pass the Job# i.e. 12345 only once. The linking formula should look something like this: if {Job#} is not equal to previous {Job#} then {Job#}.
In other words I need to compare the job# about to be passed with the previous Job# already passed and it is not a match push it to the sub report. If it is a match avoid pushing it to the sub report and move to the next Job#.

 

[Ngolem]

ok....How are you using the subreport? Are you displaying the values directly or passing information back to the main report via "Shared" variables.

Unless there is a reason not to....I would use the "shared variable" approach.

The subreport is then placed in the Group header for Job# and the sections of the subreport are suppressed and the subreport itself is made small and tucked away to one side of the header where it is out of the way.

Now you can create a formula that will display the shared variables in the detail of your main report .... the subreport will be executed only once this way.

Jim Broadbent

 

[beauman]

I am passing information back to the main report via Shared numbervar.
Main report has
Group1 is customerID
Group2 is Job#
My sub report has the cost for the job info and has been placed in the group2 header and minimized. I have no groups on the sub report. Should I insert a group on the sub by Job#?
The shared number var is returning the cost twice. Once for every time the Job# appears in the main report. I only want it to return the cost from the sub report once.

 

[synapsevampire]

Jim's suggestion will work, if as you say, you group the subreport by the job number, but there's a bigger problem here.

If the job number is unique per customer, then by grouping by it on the main report and linking the sub via the job number, the subreport is only getting it once from the main report, the fact that it returns more than one row is likely the result of the subreports query, NOT the linking.

You might resolve this by using a distinct in the subreport, hard to say, but a grouping via a job number and placing the subreport in the header or footer makes that job number unique in the main report.

-k

 

[Ngolem]

good catch...SV...I did not think about the subreport not reporting back just a single value

Jim Broadbent