remove duplicate groups & records from subreport

[maas2011]

Hello All,

I need your help in removing the duplicated values from the subreport.

In my main report:
Titles: Deal No Amount %

Group#1 Currency Name (USD)- Select Expert I have chosen (USD, GBP, JPY)

Detials: 555 123456 10%
222 145877 20%

and at the end of Group#1, I have create a subreport which is called Currency

I have imported the same table for having extra fields of information for the users, My subreport looks like:

Group#1 Cuurency Name (USD)- Select Expert I have chosen (USD, GBP, JPY)

Detials: 555 123456 10%
222 145877 20%

The problem is that there is duplicated records in the subreport. However, I want to exclude the Currencies which are shown in the main report from the subreport. My subreport should show the currencies which has not be displayed in the main report.

I tried to create a subreport link and pass Currency.name as a parameter to the subreport {?main_ccy} through sub report link and then in the selected expert, I have added one more line which is

not ({Currency.Name} in [{?main_ccy}]).

When I ran the report, null values will be shown and when I remove this condition, the report will be displayed but, with the repeated group name and data.

Can you please help in solving this...

 

[lbass]

Do you want all other currencies except the one in the current main report group to appear in the subreport? Or do you want only those currencies to appear in the subreport that are not in ANY of the main report groups?

What field is showing nulls when you use the above formula?

-LB

 

[maas2011]

Yes lbass, I want all other currencies except the one in the current main report group to appear in the subreport. In other words, I want to exclude all the currencies which are showing the main report and show only the other currencies in the subreport.

In some cases, I am getting the main report only USD group. In the subreport, I am getting also the USD which I do not want it to be displayed because it will be duplicated.

 

[lbass]

Well, your response isn't that clear. It sounds like for each group instance you want all other currencies to show except the one in the main report group instance. In other words, for each main group instance ONLY that instance will be excluded in the subreport in that group footer. If so, then you should like on the group field, and change the subreport selection formula to:

not({Currency.Name}={?pm-?main_ccy})

I don't really see what would be duplicating. If this doesn't give you the desired results, you should show some mock data indicating the main report value and then show the corresponding values returned in the sub. Show both what you are getting and what you want to get.

-LB

 

[maas2011]

Thanks lbass for your help, but the solution did not work if there are no currencies in the main report.

The report will display blank if there is no currencies in the main report where in the subreport, all the curremcies should be displayed.

To describe the issue clearly, here is my data:

In my main report:

Select Expert I have chosen (USD, GBP, JPY) to be displayed

Titles: Deal No Amount %

Group#1 Currency Name (USD)
Detials: 555 123456 10%
222 145877 20%
and at the end of Group#1, I have created a subreport which is called Currency subreport.

I have imported the same table for having extra fields of information for the users, My subreport looks like:
Select Expert I have chosen (USD, GBP, JPY)- same as the main report.

Group#1 Cuurency Name (USD)

Detials: 555 123456 10%
222 145877 20%
Total: 57777

Group#1 Cuurency Name (GBP)

Total: 123.44

Group#1 Cuurency Name (JPY)

Total: 55896.55

What I want exactly is, if the currency group name has been displayed in the main report, then it should be excluded from the subreport. The subreport should only display the GBP and the JPY currencies as the USD was already printed in the main report.

I tired to do as you have mentioned is not like, but if there aren'y any records in the main report (ex. no deals in this period). The subreport should display all the currencies totals:

Group#1 Cuurency Name (USD)

Total: 999

Group#1 Cuurency Name (GBP)

Total: 123.44

Group#1 Cuurency Name (JPY)

Total: 55896.55

 

[lbass]

It might help to understand what the purpose of the sub is compared to the main report. Obviously, no subreport will appear if there is no group instance in the main report. So one option would be to leave the sub as is in the group footer and then add a second sub in the report footer that reports on those currencies not present in the main report. Would that work for you?

-LB

 

[maas2011]

lbass, thanks for the help.

I am looking for something in my subreport that can exclude any currency which is printed in the main report.

I will pass the group name {Currency.Name} as a shared variable to the subreport.

Now how do I exclude the currency names which will be passed to the subreport where in the select expert, I am defining the currencies should equal to (USD, GBP, JPY).

 

[lbass]

I can't tell what approach you are thinking of taking. One sub or two? Located where?

-LB

 

[maas2011]

One Subreport and it is located in group footer #1 of {Currency.Name}

 

[lbass]

As I suggested earlier, you won't be able to do show currencies in the sub if there is no main report currency to link to, so while you could get what you wanted for existing main report group instances you would have to use a second sub in the RF for those where there isn't. For example:

Groups
Main Report Sub
A B
C
D
E

B A
C
D
E

D A
B
C
E

Report Footer
A
B
C
D
E

Using my sample, can you verify that this is what you are looking for?

I also wasn't asking you to restate the problem in my last post, but to explain what you are talking about here conceptually. In other words, what is the purpose of the report? How will it be used? What does it tell the user?

-LB