Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Displaying the report....help

Status
Not open for further replies.
unknownly

unknownly

Programmer
Jul 7, 2003
181
US
Have 3 column which should be grouped by

but I am unable to show them the way user wants it.


This is how the user wants it:


Unit Code Codedescr count of code sum(amount) //heading


Assets //grouping on @type

11111 100 AAAAAAAAA 19 2039485766
11111 200 BBBBBBBBB 10 1205485700
11111 300 CCCCCCCCC 10 110480066
11111 400 DDDDDDDDD 03 1239432001
---------------------------------
42 4594883533

Liab.

11111 100 AAAAAAAAA 17 -2039485766
11111 200 BBBBBBBBB 09 1205485700
11111 300 CCCCCCCCC 08 -110480066
11111 400 DDDDDDDDD 02 1239432001
---------------------------------
36 294951869


Net Value //grouping on unit

11111 100 AAAAAAAAA 36 0
11111 200 BBBBBBBBB 19 2410971400
11111 300 CCCCCCCCC 18 0
11111 400 DDDDDDDDD 05 2478864002
----------------------------------------------------------------
78 4889835402



type is a field and using this formula

@ type

IF {tab1.L2_ID} like 'IC*' AND RIGHT({tab1.L5_ID},2) = '01' THEN
'ASSETS'
ELSE
IF {tab1.L2_ID} like 'IC*' AND RIGHT({tab1.L5_ID},2) = '02' THEN
'LIAB.'

Any help is greatly apperciated

Thanks,

Sweetie
 
Sort by date Sort by votes
You haven't really identified what the problem is, so I'll guess. Based on your display, if you create an outer group that contains both assets and liabilities based on a formula like:

IF {tab1.L2_ID} like 'IC*' AND RIGHT({tab1.L5_ID},2) in ['01','02'] THEN "Net Value"

...then you can display results in the footers of each group. Or if you have no other outer groups, you could just use the report footer for the Net Value results, with no need for an outer group. Use running totals and text boxes to create the results in the Net Value group footer or the report footer, e.g., select {table.code}, count, evaluate based on a formula: {table.code} = 100
Reset never (if for the report footer).

-LB
 
Upvote 0 Downvote
We'll give it a shot Sweetie :)

this bothers me a bit: sum(amount)
It implies to me that there is another group required based on CODE

First Structure your report this way

Group 1 header Unit (put the column headings)enable repeat group header
Group 2 header @Type (put the Value of @type)
Group 3 header CODE
Details (Suppressed)
Group 3 footer (suppressed)
Group 2 footer (put your Type data/summary calculations)
Group 1 (put your net calculations)

Re: @Type

You have no provision for data that does not fall into these 2 categories...there should be...so I would make the formula something like this

//@Type

IF {tab1.L2_ID} like 'IC*' AND RIGHT({tab1.L5_ID},2) = '01' THEN
'ASSETS'
ELSE IF {tab1.L2_ID} like 'IC*' AND RIGHT({tab1.L5_ID},2) = '02' THEN
'LIAB.'
ELSE
"OTHER";

Now you have a chance to catch data errors


Now to produce the data in the Group 3 footer you simply need to place your fields and do a summary operation on the "code" to get the count and "Amount" to get the sum

For the Group 2 footer summary You do another count of code based on your @Type group and Sum of Amount for the total amount for this grouping.

To get the Net summary in the Group 1 footer...you have to collect this data in arrays

In the Group 1 header place this formula

//@Init
WhilePrintingRecords;
//estimate the number of codes then add 50% to that total
//let us say there are 20 codes then dim the arrays for 30 elements
StringVar Array Code := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""};
StringVar Array CodeDescr := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""};
StringVar Array CodeCount := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""};
StringVar Array CodeSum := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""};
NumberVar pointer := 0;
StringVar Warning := "";

The arrays are strings for display reasons that will be obvious later.

Now in the Group 2 footer place this formula

//@CollectSummaryInfo
WhilePrintingRecords;

StringVar Array Code;
StringVar Array CodeDescr ;
StringVar Array CodeCount ;
StringVar Array CodeSum ;
NumberVar pointer ;
StringVar warning;
numberVar counter;
booleanVar flag := False;

if pointer = 0 then
(
pointer := pointer + 1;
Code[pointer] := {Table.code};
CodeDescr[pointer] := {Table.codeDescr};
CodeCount[pointer] := totext(count({Table.code,@Type),0,"");
CodeAmount[pointer] := totext(sum({Table.amount,@Type),0,"");
)
else
(
for counter := 1 to pointer do
(
if Code[counter] = {Table.code} then
(
CodeCount[counter] := totext(tonumber(CodeCount[counter]) + count({Table.code,@Type),0,"");
CodeAmount[counter] := totext(tonumber(CodeAmount[counter]) + sum({Table.amount,@Type),0,"");
flag := True;
)
if flag then Exit For;
);
if not flag then
(
if pointer + 1 > ubound(Code) then
warning := "warning report maintenance required: too many codes for the array"
else
(
pointer := pointer +1;
Code[pointer] := {Table.code};
CodeDescr[pointer] := {Table.codeDescr};
CodeCount[pointer] := totext(count({Table.code,@Type),0,"");
CodeAmount[pointer] := totext(sum({Table.amount,@Type),0,"");
warning := "";
);
);
);

Now you have your data.

The display of it depends n the size of your {Table.codeDescr} size. Let us say this is 25 char MAX...then a string formula can hold 254/25 or 10 {Table.codeDescr}'s

So each display of the footer variables will have a formula similar to this one

//@DisplayCode1
WhilePrintingRecords;
stringVar array Code;
numberVar pointer;
StringVar result := "";

for pointer := 1 to 10 do
(
result := result + Code[pointer] + chr(13);
)
result;

now you see the benefits of the string array

For each group of 10 results make a new subsection of the footer and Enable "Suppress BLank Section"

the formula is very similar except for the range.
//@DisplayCode1
WhilePrintingRecords;
stringVar array Code;
numberVar pointer;
StringVar result := "";

for pointer := 11 to 20 do
(
result := result + Code[pointer] + chr(13);
)
result;

add one more the footer section place and a warning formula
(Make sure this has Suppress Blank Section Enabled)

//@Warning
WhilePrintingRecords;
StringVar Warning;

Warning;

There that is how it is done...it may not be 100% error free but you get the idea

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!
 
Upvote 0 Downvote
WOW! It was little too much for me to understand.

This is how I did.

Group 1 header @UnitCODE (suppressed)
Group 2 header @TypeCODE (put the Value of @type)
Details (Suppressed)
Group 2 footer (put your Type data/summary calculations)
Group 1 (put your net calculations)

@typecode

@type + {tab1.Code}

@ UnitCode
{tab1.Unit} +{tab1.Code}

Intially the report run fine(grouping by assets/Liab. and showing the codes in assets/Liab.)

ex.
Assets
11111 100 AAAAAA 19 2039485766

Liab.
11111 100 AAAAAA 17 -2039485766
NetValue
11111 100 AAAAAA 36 0

so...on for each code

How can I display it in sections and then do the running totals and count of code for each section.


Hope I didn't not confuse

Thanks,

Sweetie



 
Upvote 0 Downvote
Hi Jim

I replaced your formula and I get this error " the ) is missing" and the cursor blinks at the

if pointer = 0 then


****CodeAmount[pointer] := totext(sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
)




//@CollectSummaryInfo
WhilePrintingRecords;

StringVar Array Code;
StringVar Array CodeDescr ;
StringVar Array CodeCount ;
StringVar Array CodeSum ;
NumberVar pointer ;
StringVar warning;
numberVar counter;
booleanVar flag := False;

if pointer = 0 then
(
pointer := pointer + 1;
Code[pointer] := {CGF_FR_GARSRPT8.GC_MATCH_CODE};
CodeDescr[pointer] := {CGF_FR_GARSRPT8.DESCR.C};
CodeCount[pointer] := totext(count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[pointer] := totext(sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
)
else
(
for counter := 1 to pointer do
(
if Code[counter] = {CGF_FR_GARSRPT8.GC_MATCH_CODE} then
(
CodeCount[counter] := totext(tonumber(CodeCount[counter]) + count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[counter] := totext(tonumber(CodeAmount[counter]) + sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
flag := True;
)
if flag then Exit For;
);
if not flag then
(
if pointer + 1 > ubound(Code) then
warning := "warning report maintenance required: too many codes for the array"
else
(
pointer := pointer +1;
Code[pointer] := {CGF_FR_GARSRPT8.GC_MATCH_CODE};
CodeDescr[pointer] := {CGF_FR_GARSRPT8.DESCR.C};
CodeCount[pointer] := totext(count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[pointer] := totext(sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
warning := "";
);
);

);
 
Upvote 0 Downvote
Try making {table.unit} your Group1, {table.type} your Group2 and {table.code} your Group3 (you don't have to create this third group necessarily).

-LB
 
Upvote 0 Downvote
Sorry Sweetie...needs one more ); at the bottom

//@CollectSummaryInfo
WhilePrintingRecords;

StringVar Array Code;
StringVar Array CodeDescr ;
StringVar Array CodeCount ;
StringVar Array CodeSum ;
NumberVar pointer ;
StringVar warning;
numberVar counter;
booleanVar flag := False;

if pointer = 0 then
(
pointer := pointer + 1;
Code[pointer] := {CGF_FR_GARSRPT8.GC_MATCH_CODE};
CodeDescr[pointer] := {CGF_FR_GARSRPT8.DESCR.C};
CodeCount[pointer] := totext(count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[pointer] := totext(sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
)
else
(
for counter := 1 to pointer do
(
if Code[counter] = {CGF_FR_GARSRPT8.GC_MATCH_CODE} then
(
CodeCount[counter] := totext(tonumber(CodeCount[counter]) + count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[counter] := totext(tonumber(CodeAmount[counter]) + sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
flag := True;
)
if flag then Exit For;
);
if not flag then
(
if pointer + 1 > ubound(Code) then
warning := "warning report maintenance required: too many codes for the array"
else
(
pointer := pointer +1;
Code[pointer] := {CGF_FR_GARSRPT8.GC_MATCH_CODE};
CodeDescr[pointer] := {CGF_FR_GARSRPT8.DESCR.C};
CodeCount[pointer] := totext(count({CGF_FR_GARSRPT8.GC_MATCH_CODE},{@Accttype}),0,"");
CodeAmount[pointer] := totext(sum({CGF_FR_GARSRPT8.POSTED_TOTAL_AMT},{@Accttype}),0,"");
warning := "";
);
);
);
);

I hope you are following the grouping I suggested.

The reason for adding the Group 3 based on Code is to allow you to use the COUNT summary to count the instances of each code.

I suppose it is a bit overwhelming but it should work as I described it...and you will learn a lot in the process :)

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!
 
Upvote 0 Downvote
This is what I tried...


Group1 by unit
group2 by type
group3 by code


Displayed data in the footers and to display the data and summary for unit using a subreport. It works fine so far.

I am trying your idea too Jim.

Thanks for all the help..

Sweetie
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

john432
  • Locked
  • Question
Crystal Report Help 1
Replies
2
Views
308
john432
john432
jchewsmith
  • Locked
  • Question
Report filter to run report yeartodate through yesterday
Replies
0
Views
286
jchewsmith
jchewsmith
john432
  • Locked
  • Question
Crystal Report
Replies
2
Views
317
john432
john432
milisql
  • Locked
  • Question
Column Header in CrossTab Report
Replies
1
Views
618
hilfy
hilfy
john432
  • Locked
  • Question
Crystal Report formula (finding with key words) 1
Replies
2
Views
339
john432
john432

Part and Inventory Search

Sponsor

Back
Top