Need help with a grouping formula 1

[Scroller52]

Hi, I'm using CR XI release 2 on a SQL database.

Here is a sample of my data:

Investor Combined Name Capital

A 100
B 200
B1 B Combined 100
B2 B Combined 300
C1 C Combined 50
C2 C Combined 100

I need to produce individual reports for each of my investors. And in the instances where the 'Combined Name' is populated, Crystal needs to generate a combined report for that investor. I need to produce 1 report for A, B, B1, B2, C1 and C2 as well as combined reports for B Combined and C Combined.

Do you think there is a way to do this within 1 report? I came into this client and another developer created 12 different reports to handle specific scenarios like this. I've consolidated them into 6, but if I can't solve this problem, I'll be back at 10 reports. I've thought about different groupings and such, but could not come up with a way around this. I don't think subreports would do much since it would be a report within a report when it would be easier for the end user to maintain two stand alone reports. Any help is greatly appreciated. If you have any questions let me know. Thanks!

 

[lbass]

Why not create an outer group on the combined name field. Then you could summarize the records belonging to each group in a group section. Suppress the group #1 sections whenever the combined group name is null or blank.

-LB

 

[Scroller52]

Maybe my data example didn't include enough information.

Fund Investor Combined Investor Capital
1 A 100
2 A 200
1 B 200
1 B1 B COMBINED 50
2 B B COMBINED 50

So in this example, investor 'B' in funds 1 and 2 will need to come up in the same report. and 'B COMBINED' will need to show up in the same report for fund 1 investor B1 and fund 2 investor B. Does that clear things up? So if i group by investor or combined investor in either order, it needs to group then somehow ungroup?

 

[lbass]

I'm confused by your fake data. Is B1 a separate investor or is it still investor B?

It might help if you showed how you would want to display the information in your last post in your final report.

-LB

 

[Scroller52]

B1 is a separate investor than B. This is how i want the information to output:

Investor A:
Fund Capital
1 100
2 200
Total 300

Investor B:
Fund Capital
1 50
2 200
Total 250

Investor B1:
Fund Capital
1 50
Total 50

Investor B Combined:
Fund Capital
1 50
2 50
Total 100

Does that help? So I need each investor to produce a report for all funds that they are invested in. And in the instances that they have a 'combined investor', I need a consolidated report for those investors.

 

[lbass]

You are trying to use the same data twice, so you can either:

1-Insert a crosstab in the report footer that uses the combined name as the column and fund as the row, and sum of capital as the summary.

2-Insert a subreport that is limited by requiring the combined name not to be null. Then group on the combined name field.

3-You could use running totals that were specific to each value of combined name field, but this would not be dynamic.

4-You could use a command as your datasource where you use a union all statement to replicate the data so you have two sets of data available for reporting.

-LB

 

[Scroller52]

Thanks LB

I think solutions 1 and 2 would pretty much make me create 2 reports within 1 crystal report. It would probably be easier if an end user just had 2 crystal reports to work with.

Solution 3 - it needs to be dynamic since they won't have a crystal user/developer when I leave.

Solution 4 - I don't have much experience with writing SQL commands or using unions. I currently use a OLEDB (ADO) command to connect to my tables. I have two commands in my Database Expert that connect to two different tables and I link them via a 'Type' How would replicate my data to I can use it again?

 

[Scroller52]

Lbass,

I think i've found a way around this. I created a parameter that asks whether the report should be run for 'investor' or 'combined investor'

this way i'm using the same report but i run the report twice to get the reports that i need.

one more question: when i run for the 'combined investors', there will be a lot of reports with blank 'combined investors'. Crystal is grouping these all in the beginning of the report. is there a way to 'skip nulls'? right now, there is an extra 100something pages of the report that i do not want/need.

 

[lbass]

I would check in the section expert and make sure you have set "suppress blank section" on the relevant sections.

-LB

 

[Scroller52]

Thanks LB,

i dont think the supress blank sections would work because there are sections that are not blank that i want suppressed. i added a isnull(Combined Investor) in the appropriate sections and it looks like it solved my problem. once again thanks for you help. its always good to get another POV on this stuff.

 

[lbass]

Why are you suppressing at all? Why not limit the records in the record selection formula to:

(
(
{?Investor} = "Combined" and
not isnull({table.combinedinvestor})
) or
(
{?Investor} = "Individual" and
isnull({table.combinedinvestor})
)
)

-LB