Grouping and Consolidating 1

[TheEagleHasLanded]

Hi,

I have a report that currently has two levels of grouping. Level 1 is by Customer and Level 2 is by currency.
Unfortunately because I am using an integrated solution with vb.net 2005 and cannot use parameters, I have been able to at least trap my parameter into a formula. (passing from subreport to main report in the header)
This parameter is a 'Y/N' flag for consolidation of customers. If the flag is 'N' then I leave the grouping as is (Customer, Currency).
If this parameter is a 'Y' then I need to only group by Currency and no longer by Customer (a consolidation of all customers).
How would I do this?

Secondly, when I group by customers, I have a label to show each customer grouping. When I consolidate, I need to concatenate all the customers and display at the top. How would I create that formula?

i.e. for non-consolidation, I have Customer 1, Customer 2 and Customer 3 grouped and labelled. for consolidation it will show at the top of the page Customer 1, Customer 2 and Customer 3.

I am using VB.NET 2005 and CR XI R2 against an SQL Server.


Thanks!!!
Trieu

 

[lbass]

Create a formula:

if {flag} = "Y" then {table.currency}

Insert a group on this formula and make it group #1. If the flag = "N" it will have no impact on the sort. Format the group #1 header and footer to "suppress blank section".

How many customers might there be? You cannot collect the values in the body and show them in the repeating group header section, since they would be collected in the "whileprintingrecords" phase. You could, however, insert a crosstab that uses the customer name as row or column, with a maximum of customer name as the summary. Then suppress either the label or the summary and remove the grid.

You could also insert a subreport in the group header where you CAN collect the values in a comma-delimited manner.

Or, if there are only a few customers, you could use a formula that uses:

Nthlargest(1,{table.customer},{table.group}) //N = 1

...changing the N for each possible value.

-LB

 

[TheEagleHasLanded]

Hi lbass,

Just to clarify, the formula you listed should be if {flag} = "Y" then {table.customers} right? Since this is the first level of grouping and then keep currency as the 2nd level of grouping.

Thanks,
Trieu

 

[TheEagleHasLanded]

Thanks LB,

I tried the grouping and it works like a charm !! I have to take off for a meeting so I will try the parsing of the customers later.

Appreciate the help!

Thanks,
Trieu