Shared Variable- Grand Total from sub report not pulling correctly 1

[BHAT100]

I would be very grateful for reply to the following. it would be really great help. thx

while I am pulling grand total from subreport using shared variables, it is picking up previous customer total only instead of grand total of all customers

customer is linked with main report

Main report has data for the yaer 2005 and sub total has data for 2004.

Main Report:
GRP1 LOCATION
GRP2 SALESPERSON
GRP3 Customer
Detail Invoice total- formula

GRP3 header has six subsection for
3a customer name
3b sales 2005
3c cost 2005
3d gp 2005
3e gp% 2005
3f placed subreport with same subsection but sub report does not have grouping by salesperson

SUB Report:
GRP1 - LOCATION
GRP2 - CUSTOMER
DETAIL- INVOICE TOTAL - FORMULA

Subreport is placed in GRP-3E

I am using following formula in main shared formula in sub report and put that in RF

whileprintingrecords;
Shared NumberVar Shared_Total:= sum(@cur_sales);
Shared_Total;
//Report footer

Formula in mianreports:

Shared NumberVar Shared_Total;
Shared_Total;

I am placing this also in RF

thx everybody for reply

do I Need to reset anything ? what ? and where ?

 

[lbass]

You gave two different locations for your subreport. Let's say it is really in GF#3f. Insert a Grp#3g section, and then place the following formula there:

//{@accum}:
whileprintingrecords;
Shared NumberVar Shared_Total;
numbervar sumsharedtotal := sumsharedtotal + Shared_Total;

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar sumsharedtotal;

If a shared total can sometimes be null, then you also need a reset formula in the main report:

//{@reset} to be placed in group header #3:
whileprintingrecords;
shared numbervar Shared_Total := 0;

-LB

 

[BHAT100]

Thank you very much L Bass for your help.
It is really great help to me

Thanks a lot

Bhat

 

[BHAT100]

shared variable number are not pulling correctly. main report numbers are lessor than subreport.

I am pulling report total from the subtotal

I am following shared variable in subreport;

whileprintingrecords;
shared NumberVar sub_report_total:= Sum ({@cur_sale_jan});
sub_report_total;

In main report, I am using following formula:

//{@reset} above section to subreport placed in same group:

whileprintingrecords;
Shared NumberVar Sub_Report_Total:= 0;

//{@accum}:section below subreport placed in a same group:
whileprintingrecords;
Shared NumberVar Sub_Report_Total;
NumberVar sumsharetotal:= sumsharetotal + Sub_Report_Total;

//{@display} placed in the report footer:
whileprintingrecords;
numberVar sumsharetotal;

-Any idea would be great help to me

Thanks

-b

 

[lbass]

What you are doing looks correct, although you didn't show the exact location of these formulas--I assume the first two are in Group#3. Can you verify that the shared variable is displaying correctly in the main report before you accumulate it? In the {@accum} formula, try commenting out the numbervar sumsharedtotal to check.

In what way are your results different from the expected results? Can you show some sample results as the formula accumulates?

-LB

 

[BHAT100]

-Shared formula placed in Report Footer in sub report

In main report:
-//{@reset} Formula placed in 3B:
-Subreport placed in 3J
-//{@accum}:Formula in 3K
-display} formula placed in the report footer:

it seems to me @accum is accumulating total correctly. I am suspecting that sub report has more customers than main report and main report is picking up only those customer records who are existed in main report.

my link to subreport to main report is:

customer id

Thanks for your reply. any idea on above would be great help to me

-bhat

 

[lbass]

Yes, that could happen, in which case, probably your main report should be your subreport--since a subreport behaves as if it is left joined.

-LB

 

[BHAT100]

-NO, it does not work this way too. because main report has different other new customers which have activity for 2005 transactions. Subreport is based on view, which was uploaded from excel sheet for 2004 data which was not integrated on conversion for new system i.e Great Plains and has customers which are not existed in new system. Main report has 2005 data

Any idea how can resolve this situation

Thanks

-bhat

 

[lbass]

What are you hoping to see in the subreport? You cannot link on the customer number and still have customers appear in the subreport that do not appear in the main report.

If you don't need to use the subreport data in calculations at the customer group level of the main report, then another option would be to remove the subreport link on customer number, and place the subreport in the report header or footer. Then the calculation for the subreport values will be correct at the report level.

-LB

 

[BHAT100]

Sorry LBASS, My mistake, ALL customer exist in customer master table i.e RM00101, which are not showing up for sub report in main report.

As I am pulling transactions from sales order tables which has transactons i.e SOP30200 in main report.
other tables in main report are:

Customer Master: RM00101
CUSTOMER CLASS: RM00201
SALESPERSON TABLE: RM00301
SALES TRANSACTION: SOP30200 (FOR 2005)

THESE TABLES ARE LINKED WITH EACH OTHER AS = INNER JOIN.

IN SUBREPORT I HAVE FOLLOWING TABLE:

RM00101: CUSTOMER MASTER
RM00201: CUSTOMER CLASS
TGO_SOP30200: SALES TRANSACTIONS FOR 2004

if I change links of these tables in main and sub report,left outer joins to cust id from RM00101 TO sop30200.

do you think,it would work or do you suggest any other way to solve.
because I can't put subreport at header or footer because i am placing each cusotmers 2004 record under the 2005 record for comparision purposes.

Thanks for your suggestions

-HAT

 

[lbass]

Yes, definitely use left joins from the Customer Master to the Sales Transactions tables in both main and subreport. I don't know what customer class is, and I'm wondering whether you should be linking the subreport to the main on both {Customer Master.Customer ID} and on the ID field in the Customer Class table.

-LB

 

[BHAT100]

customer class gropus customers by location for CAD and US Customers separately:

01C Vancouver CAD
01U Vancouver USD
02C Montreal CAD
02U Montreal USD

and so on for other Location

even after changing link to left outer from RM00101 TO SOP30200. there is no change in output.

Any Idea on this would be great

Thanks LB

bhat

 

[lbass]

Do you have selection criteria that use the sales transaction tables? If so, that would essentially undo the left join. That might not matter in the subreport, but in the main report, if this is the case, try removing the selection criteria and instead use a conditional formula to get the totals for sales transaction for 2005--that way you'll pull the correct IDs from the subreport.

The conditional formula could look like:

if year({salestransaction.date}) = 2005 then {table.salesamt}

-LB

 

[BHAT100]

This is record selection in main report.
{SOP30200.VOIDSTTS} = 0 and
{RM00101.CUSTCLAS} = {?Location } and
{SOP30200.SOPTYPE} in 3 to 4 and
{SOP30200.GLPOSTDT} <= {?Ending Date}

THE MAIN REPORT LOOK LIKE AS FOLLOWS

1) cusotmer class (group)
2) salesperson (group)

The report has jan to dec column and I am pulling sales and cost for each month by formula by hard coding dates

3a)2005 (cusotmer group above detail row) group header
3b)reset formula for sales jan to dec
3c)reset formula for cost jan to dec
3d)reset formula for gp jan to dec
3e)2005
3f)sales
3g)cost
3h)gp
3I)gp%
3j) sub report
3k) accum formula for sales for each month
3l) accum formuala for cost for each month
3m) accum formula for gp for each month

details a) sales formula for each month to pull sale
details b) cost formula for each month to pull cost
details c) gp formula for each month

report footer:

a) 2005
b) sales total
c) cost total
d) gp total

e) 2004
f) display sales 2004
g) display cost 2004
h) display gp 2004


In selection criteria, I am not restricting any record for 2005 because i am coding dates in formula to pull sales, cost and gp every month

Can you suggest me, is there still need to place your formula in selection criteria

Thanks

-bhat

 

[lbass]

If the problem is that some of the customers are not appearing in the main report and therefore not pulling the corresponding IDs in the subreport, then use the left join from the master table to the SOP30200 table and remove all references to the SOP30200 table in the record selection criteria, so that all that is left is:

{RM00101.CUSTCLAS} = {?Location }

If doing this results in the correct subreport values appearing, then in the main report, you just need to use the following conditions in formulas:

if {SOP30200.VOIDSTTS} = 0 and
{SOP30200.SOPTYPE} in 3 to 4 and
{SOP30200.GLPOSTDT} <= {?Ending Date} then {table.salesamt}

-LB