Recognising Data in separate Subreports 1

[Legend1]

I have the following issues:

I have 4 tables
CUSTOMER SALES BUDGETS ITEMS

I have a main report that is run by a customer record (from CUSTOMER), with 2 separate subreports. 1 for actual customer sales (from SALES) and the other for budgeted customer sales (from BUDGETS). So far, this is easy, I am linking these 2 reports via the customers code in the main report, however, I also want to add the items that were sold and also the items that have budgets. Now the problem is, that the main report does not contain items, but the 2 subreports do. What the report is doing is not grouping by the same item, instead, it is not looking at the item in the actual sales and placing the relevant budget beside it, but placing the budgeted item amount anywhere it wants.
So I need it to show basically
Customer 1 Item 1 Sales of 1000 Budget of 0
Customer 2 Item 1 Sales of 1000 Budget of 0
Customer 2 Item 3 Sales of 0 Budget of 100
Customer 2 Item 4 Sales of 500 Budget of 200

It is currently showing
Customer 1 Item 1 Sales of 1000 Budget of 0
Customer 2 Item 1 Sales of 1000 Budget of 100
Customer 2 Item 3 Sales of 0 Budget of 200
Customer 2 Item 4 Sales of 500 Budget of

Please help!!!

 

[Madawc]

I'm not sure I follow what you're doing. Isn't it possible for you to group by customer and sales, maybe suppressing details and showing totals in the group footer?

Do note that the result of a subreport is only available to the main report in the next section. The correct value may display from the subreport, but the shared variable will be something else. You can get round this by [Insert Section Below].

It also depends on the version of Crystal, I am assuming 8.5, but please clarify.

Madawc Williams
East Anglia, Great Britain

 

[Ngolem]

I think I understand the delima but not how your results are not processing since you give little information on your Report structure

Your problem is:

Sometimes there are items with sales but no budget
Sometimes there are items with budget but no sales
Sometimes there are items with budget and sales

trying to arrange the list of items in order and dovetail the items sales/budget properly is the challenge.

You don't say how the report is structured so I will propose one

Group 1 header A Customer
Group 1 header B
Details (suppressed)
Group 1 footer

I would do this report using Shared Arrays and 3 subreports

Display the customer info in Group 1 header subsection A
and then place this init formula to initialize your shared arrays

//@init (Suppressed)
WhilePrintingRecords;
//Initialize to a max of 1000 for CR8.5 or less
//Initialize for max number of items for a customer then
//add 100%..ie cust1 = 10 items; cust2 = 30 items ...
//custn = 100 items then initialize for 200 array elements

shared stringVar array S_Items := ["","","",..."","",""];
shared stringVar array S_Sales := ["","","",..."","",""];
shared stringVar array B_Items := ["","","",..."","",""];
shared stringVar array B_Sales := ["","","",..."","",""];
shared numberVar S_Position := 0; //position in array
shared numberVar B_Position := 0;

Now create the subreports linked by CustomerID

Subreport Budget
placed in Group 1 header Subsection b
supress all sections and borders of the subreport make as thin as possible
Tables: Budget, Item linked by item

Group 1 header {Item.ItemID}
details
Group 1 footer

//GatherBudgetInfo (Placed in details)
WhilePrintingRecords;

shared stringVar array B_Items ;
shared stringVar array B_Sales ;
shared numberVar B_Position ;

B_Position := B_Position + 1;
B_Items [B_Position} := {Item.ItemID};
B_Sales [B_Position} := {Budget.Value};

Subreport Sales

This is a mirror image of Subreport Budget except the SALES table is used insead of the BUDGET table and

shared stringVar array S_Items ;
shared stringVar array S_Sales ;
shared numberVar S_Position ;

variables are used

Displaying the results

NOW you have collected the sales/budget info....it is time to display it in the proper order

This is done in the main report in the customer footer with a subreport called

DisplayData

this is an unlinked subreport based on just the ITEMS table and the shared arrays you created

the structure is

Group 1 header {Item.ItemID}
details
Group 1 footer (suppressed)

the column headings of ITEM Number, Sales, Budget are placed in the Group 1 header

Place {Item.ItemNumber} , {@Sales} and {@Budget} under their respective columns

//@Sales
WhilePrintingRecords;
shared stringVar array S_Items ;
shared stringVar array S_Sales ;
shared numberVar S_Position;
numberVar counter;
stringVar Sresult := 0;
booleanVar Flag := False;

if {Item.ItemID} in S_Items then
(
for counter := 1 to S_Position Do
(
if S_Items[counter] = {Item.ItemID} then
(
Sresult := S_Sales[counter];
Flag := True;
);
if flag then exit for;
);
);

Sresult;

//@Budget
WhilePrintingRecords;
shared stringVar array B_Items ;
shared stringVar array B_Sales ;
shared numberVar B_Position;
numberVar counter;
stringVar Bresult := 0;
booleanVar Flag := False;

if {Item.ItemID} in B_Items then
(
for counter := 1 to B_Position Do
(
if B_Items[counter] = {Item.ItemID} then
(
Bresult := B_Sales[counter];
Flag := True;
);
if flag then exit for;
);
);

Bresult;

NOW...you must suppress details where there is no sales or budget numbers

this might be able to be done in the SELECT statement (prefered since it limits the testing to good Item data)

Something like

shared stringVar array B_Items ;
shared stringVar array S_Items ;
({Item.ItemID} in B_Items or
{Item.ItemID} in S_Items )

But I doubt you can use shared values in a Select Statement of a subreport...sorry cannot test

Alternatively you can place the following in the conditional suppress of the Detail section

shared stringVar array B_Items ;
shared stringVar array S_Items ;
not ({Item.ItemID} in B_Items or
{Item.ItemID} in S_Items );

that should do it



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Legend1]

Thanks Ngolem,

I only have one question that I am unsure the meaning of, and that is the section of the init formula that states:

shared stringVar array S_Items := ["","","",..."","",""];
shared stringVar array S_Sales := ["","","",..."","",""];
shared stringVar array B_Items := ["","","",..."","",""];
shared stringVar array B_Sales := ["","","",..."","",""];
shared numberVar S_Position := 0; //position in array
shared numberVar B_Position := 0;

What should I be putting in the quotation marks and replacing the 0's with? This has me a little confused and I'm unable to get around it. The rest seems to be the answer to all of my problems.
That's once I can get it to work that is!

Thanks for the reply!