Horizontal 'groupings' of data

[DOTJake]

I am trying to to do the following without doing subreport.

My data is grouped at a number of levels but I want to show details in columns across without repeating data.

here's 'structure' the data
G1 G2 G3 AA x 1 a
G1 G2 G3 AA x 1 b
G1 G2 G3 AA x 1 c
G1 G2 G3 AA x 1 d
G1 G2 G3 AA x 3 a
G1 G2 G3 AA x 3 b
G1 G2 G3 AA x 3 c
G1 G2 G3 AA x 3 d
G1 G2 G3 AA y 1 a
G1 G2 G3 AA y 1 b
G1 G2 G3 AA y 1 c
G1 G2 G3 AA y 1 d
G1 G2 G3 AA y 3 a
.
.
.
G1 G2 G3 BB r 7 g
G1 G2 G3 BB r 7 h
G1 G2 G3 BB r 7 i
G1 G2 G3 BB s 7 g
G1 G2 G3 BB s 7 h
G1 G2 G3 BB s 7 1
etc, etc


Here is what I want to do

G1 (group)
G2 (group)
G3 AA (group)
x 1 a
y 3 b
z c
d
G3 BB
r 7 g
s h
i

etc, etc

Is there any way I can do this??

DOTJake

 

[synapsevampire]

Do you mean to say that you want only the unique values for each column to show with no regard for where they are together in the data?

If so, you'll need some sort of output variables that check for uniqueness, as in (Note your laziness in not showing field names doesn't help):

Innermost Group header formula:

whileprintingrecords;
stringvar MyValue1:="";
stringvar MyValue2:="";
stringvar MyValue3:="";

Details formula:

whileprintingrecords;
stringvar MyValue1;
stringvar MyValue2;
stringvar MyValue3;
if not({table.field1} in MyValue1) then
MyValues1:=MyValue1+{table.field1}+chr(13);
if not({table.field2} in MyValue2) then
MyValues2:=MyValue2+{table.field2}+chr(13);
if not({table.field3} in MyValue3) then
MyValues3:=MyValue3+{table.field3}+chr(13);

Group Footer (display results here by creating 3 formulas):

whileprintingrecords;
stringvar MyValue1
if len(MyValue1) > 0 then
left(MyValue1,len(MyValue1)-1)
else
""

whileprintingrecords;
stringvar MyValue2
if len(MyValue2) > 0 then
left(MyValue2,len(MyValue2)-1)
else
""

whileprintingrecords;
stringvar MyValue3
if len(MyValue3) > 0 then
left(MyValue3,len(MyValue3)-1)
else
""

This should get you close.

-k

 

[DOTJake]

Thanks for the reply (except for the 'lazy' comment).

First, the column names are c1,c2,c3,c4,c5,c6, and c7 (didn't think that was needed to understand the problem)needed). The 'details' columns are c5,c6,c7 and the groups are C1, c2 and c3+c4. column c6 is numeric. I do not need any group totals or footer.

When I tried your code, all I got in the detail was the last value in c7, repeated the number of values in c7 times.

I am new to Crystal (I am an experienced developer) so, while your code doesn't work, I did learn a little about the 'WhilePrintingRecords' which will help.

 

[synapsevampire]

Reread my post, I stated where to place each formula, and I guess I should have expressly stated that the details and group header should be suppressed, the display occurs in the Group Footer.

Had you posted the field names, I would have written the code using them, not sure why as a developer you think it odd that someone would think column names important when providing examples...

-k

 

[DOTJake]

Thanks. As I said, I'm new to Crystal so the displaying the values in the group footer was not obvious to me. I'll try that.

I'm not sure why, as a developer, you thought that column names mattered. did the c1,c2... help? I was able to figure out that your Field1, Field2, and Field3 were the last three columns for which I wanted. This is all made up columns and values to illustrate the structure of the dataset and the problem so it doesn't matter what they are called.

DOTJake

 

[DOTJake]

Thanks synapsevampire. That did it!!! The real report actually had 6 subreports which are replaced by this new methods and runs much, much, much faster.

Hope I can do the same for you some time.

DOTJake

 

[synapsevampire]

It's somewhat of a hack to do it this way, but it works.

Crystal has a decent programming language, and once you figure out how Crystal processes data you should be fine.

-k

 

[DOTJake]

It is sort of a hack but that's why they give us the programming capabilities.

I'm the Business Objects 'guru' at our shop and we are now incorporating Crystal into the XI enterprise and this was the first thing I've ever done with Crystal (other than creating a one table unformatted report so I could learn the process of checking it into the XI repository and scheduling it). This was actually a great learning experience as it gave me quite a bit of insight as to how Crystal processes records.

When I started seeing some of the Crystal stuff our report developers were doing with subreports (this, doing subreports instead of crosstabs, etc) and was watching how the long the reports were taking to run, I knew there had to be more efficient ways.

Thanks again!
DOTJake

 

[synapsevampire]

No worries.

Note that for every subreport, Crystal issues a new query against the database, which makes for really slow processing if it's in a group level (every value in the group fires a query).

Note that you can base reports off of Stored Procedures and Views as well.

-k

 

[DOTJake]

Yeah, some of these reports have hundreds and hundreds of 'primary' rows and mulitple sub reports. No wonder the refresh times (and the DB access charges) are so high. Thats why I was looking for a 'better way'.

Unfortunately our IT management has over the years (since reports no longer have to be green-bar mainframe) have adopted the attitude of "that's just reporting, all the vendors's 'shiny books' say how easy it is with thier tools so will push that resposiblility out to the user community". With the money that is being spent on bad queries/reports (we pay for CPU cycles) they could have hired a lot of good report developers.

<<Note that you can base reports off of Stored Procedures and Views as well>>
And Business Object universes too!!

DOTJake

 

[synapsevampire]

I'd avoid BO Universes unless you have an overiding reason, they are proprietary and slower, and don't lend themselves to reusability with other tools. Typical BO design mentality.

Sounds like you should make a case to IT mgmt, or get your resume out there, that way of thinking is silly.

-k