Hide field in sub-report based on condition

[vinsavant]

Hello,



I have a report that includes a sub-report. The main report shows a list of transactions made in a quarter and it is grouped by Department. Sometimes a transaction is part of a group that includes many related transactions under the same group ID. However, it is possible that one or many transactions within that group were assigned to a different Department.

The sub-report serves the purpose of listing all of the transactions that are part of a group within a Department and it is embedded bellow the main report regular Department heading. What I am trying to accomplish is to also include in the sub-report the Department name associated to each of the different transactions that are part of the group shown in the sub-report. That part was easy and I have done that. However here it is my challenge.

Most of the time this field will have the same value as the main department heading of the main report, however, as mentioned earlier, there will be times that one of these transactions was linked to another Department. It is then when I would like to only show the field name for that department so it will stand out easily, rather than to bury it with all of the other field names.

In other words, I would like for the department field to appear on the sub-report only if its value is different (does not equal) from the main department heading value of the main report group heading in which the sub-report is embedded.

I tried using conditional formatting to make the field color same as background if met the condition but that option doesn't work 100% for me because my report is banded (to improve readability due to the many lines) and it works well on the White bands/rows but on the Grey bands/rows it shows the hidden font which I have set its color to white. Unless there is a way to overcome this I will need help in creating a VBA code to make the matching text invisible.

Thanks in advance for your time and effort in helping me.

Regards,

[June7]

Don't understand. If records are organized by Department, how can other department records show? Want to provide db for analysis? Follow instructions at bottom of my post.

[vinsavant]

Don't understand. If records are organized by Department, how can other department records show? Want to provide db for analysis? Follow instructions at bottom of my post.

Hello,

Thanks for your prompt reply. You are right, the example I used is a bit vague. The reason is that I am trying to use an analogy for privacy issues since this is work related. However, that should not distract from the technical part of the question. Let’s just say that this scenario does takes place and it is relevant. Luckily we got this part figured out. Now we just need to try to resolve how to show only in the sub report values that are different from the group heading in the main report.

Here is a bit of the background story to give an idea of how this scenario happens:

This report has fields like this:

SCENARIO I

Department A Transactions (for April) ---- This is part of the main report heading

Sales ID Group ID Sales Date Sales Amount Department Name
100601 04/20/12 $12,560.00 Department A

The above example shows a single transaction that is not a part of a group therefore it does not have a group id. In this case a sub-report does not appear under the main report.

SCENARIO II

Main Report

Department A Transactions (for April) ---- This is part of the main report heading

Sales ID Group ID Sales Date Sales Amount Department Name
100601 1277 04/20/12 $12,560.00 Department A

Sub-Report (starts here)

Sales ID Group ID Sales Date Sales Amount Department Name
100401 1277 01/20/12 $15,324.00 Department A
100498 1277 02/14/12 $18,414.00 Department B
100534 1277 03/28/12 $17,667.00 Department A

This scenario shows the essence of this exercise. When a transaction occurs for the period that we are reporting, in this case April, and if that transaction is part of a group we want to show all of the transaction under that group (in a sub report which joins the main report through the Group ID). The idea is to provide perspective to the overall transaction. In one section of the main report we show the total of the transaction for April over the sub-report grand total to put in perspective the entire transaction. What we want to accomplish is not to show the name of the Department next to each transaction in the sub-report if the name is the same as the main report group heading. In this example we would like to see instances where the name of the department refers to another department e.g. Department B or C etc.

Hope this makes it a little clearer.

Thanks,
Manuel

[June7]

Try an expression in the subform department textbox.

=IIf([Department Name]<>Forms!mainformname![Department Name],[Department Name],"")

[vinsavant]

It worked like a charm. Thank you so much!

I greatly appreciate your time and effort.

Regards,