Cannot show values from subreport on main report

[gbuchman1]

Hello,
I have 2 reports that I am working on in Crystal XI. The main shows the actual costs posted to a combination of GL codes, and the subreport shows budget amounts posted to the same combination of GL codes. The main report uses the DLtrans table, which is a list of all the actual transactions. The subreport uses the BLtrans table which is a list of all the budget transactions. Both reports work nicely on their own, but I want to show the budget amount from the subreport on the main report. Then I want to have a calculated field showing the difference between actual and budget. And I want to show these by a combination of 3 GL codes: 1,6 and 7 (this is the transaction level of the report). The problem is that I cannot get the budget amount from the subreport to show on the main report. I tried using a shared currencyvar, but it only shows $0. I've successfully done a different report that showed budgeted amounts on the actual page, but only in the report footer (a total budget). Now I want to show it in the account code combination level (aka the transaction level). Please help me to solve this, so I can get the budget and actual amounts on the main report. Ive spent many hours on this and need to get this solved. Please help me to solve this, so I can get the budget and actual amounts on the main report. Thanks very much in advance for your help!

 

[kray4660]

Quick question. Is the sub report in a section before you try to show the value in the main report? Also is the report in a suppressed section? To get values back from a sub report. The sub report has to be in a section prior to where you want to see the value in the main report and it cannot be suppressed (sub report or section).

 

[gbuchman1]

Thanks for your reply, Kray. I do have the subreport in a section before the section where the shared variable will show, and I unsuppressed the section. I was able to get the shared variable to showup - hooray! However, it is giving the total amount of the first transaction and then repeating it for all the subsequent lines. How can I get each line to show the correct budget amount?

 

[Madawc]

If you do a normal summary total in the subreport, you can then use a Shared Variable at the end to pass it back.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[gbuchman1]

I used a SQL expression to get the total of the budget for each GL code combination and this only shows as zeroes on the main report. But I also took your suggestion, Madawc, and entered a formula and then a sum of the formula. However it is this sum that I now want to show on my main report. The field is called Sum of @BudgetAmt. I tried to share this sum field like this:
In the subreport:

WhilePrintingRecords;
Shared CurrencyVar TOTProvider := SUM({@BudgetAmt},{tblBLTrans.sCodeIDf_6});

and in the main report:
WhilePrintingRecords;
Shared CurrencyVar TOTProvider;

Also, the main report has a main table of DLtrans which consists of the actual transactions. The subreport has a main table of BLtrans which consists of the budget transactions. I thought of perhaps making a main report based on the GL account code combinations and then having the actual transactions and budget transactions be two subreports. Would that allow me to bring the actual and budget amounts together on the same report for the GL combinations?

 

[gbuchman1]

Is it possible to link a shared variable from a detail section of a subreport to a detail section of the main report? If so, do the tables on the subreport and the main report have to be the same? Do they have to be linked the same?

 

[Madawc]

What you've done should work.

As far as I know, it doesn't matter where in the subreport the name is used. But I've usually put totals in the subreport report footer.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

You should link the subreport to the main report using the GLCode field in each. Then place the sub in a group in the main report based on the GL code, e.g., in the GH_a section. Then reference the shared variable in a GH_b section.

-LB

 

[gbuchman1]

ok, I put the subreport in GH_3a and have the shared variable in GH_3b. Group 3 on the main report has fields from DLtrans and the subreport has fields from BLtrans. On main report, Group1 is the DLtrans_acctcode1 and on subreport Group1 is the BLtrans_acctcode1. Likewise group2 on each report references DLtrans_acctcode7 and BLtrans_acctcode7. Likewise group3 on each report references DL_acctcode6 and BLtrans_acctcode6. There are one or two more levels of detail but I dont want to show them. The lowest level of detail I want to show is the acctcode 6 level. However, when I put the shared variable in Group3, I get zeroes. Not sure what is wrong. The variable in the subreport is :
WhilePrintingRecords;
Shared CurrencyVar OTProvider := ({%TotalObligated})

This yields the correct amount on the subreport. So I put in on the main report like this:
WhilePrintingRecords;
Shared CurrencyVar OTProvider

However it yields $0 on the main report. Both variables are at the Group3 level on each report. Yet I still $0 on my main report. I was linking by the DLtrans_acctcode1 to BLtrans_acctcode1 and it looks like it creates a parameter in the subreport called Pm-DLtrans_acctcode1. However, when I do this, I get the total for the acctcode1 on each line in the main report. I am going to try to link on DLtrans_acctcode6 and have this parameter in the main report = all codes, BUT also have a parameter in main report that lists one value for DLtrans_acctcode1, since I just want to see the actual and budget amts for just 1 value of DLtrans_acctcode1. I think this might fix it. Stay tuned!

 

[gbuchman1]

Ooops.. Meant to say: group3 on each report references DLtrans_acctcode6 and BLtrans_acctcode6. Also, it seems like I am forced to link the subreport to the main report by a parameter and then I have to enter one value for the parameter for the subreport ; I cannot enter a range of values. My parameter is the DLtrans_acctcode1 which is the Group1 on both main report and subreport. Since there are numerous values of acctcode7 and acctcode6 under each acctcode1, this is why I cannot have one parameter for acctcode6; the subreport keeps prompting me for one value for this. I can however have a parameter for acctcode1. Seems like the problem here now lies in linking the reports. The subreport is in group3a, all the $ amounts I want to see are in in group3b, but the link is on DLtrans_acctcode1. So is this the problem? Please help me to solve this so I can see actual and budget amounts for each acctcode6 value. Then I can sum these for acctcode7 totals. Ive spent so much time on this that I really need it to work - cant give up now. Thanks in advance!

 

[lbass]

Where you put the sub and how you link it will determine the results you get. If you want a value for each instance of acctcode1, then you should be placing the sub in GH1a with the shared variable formula in GH1b. You cannot suppress GH1a or the sub itself, but you could suppress all sections within the sub and then format GH1a to underlay following sections.

You should not be getting any parameter prompts for the sub. Links appear like parameters, e.g., {?m-table.acctcode1}, but wouldn't result in a prompt unless the link was not completed. If you have built a parameter into the sub to match a parameter in the main report, then you need to link them to each other by using the dropdown in the lower left of the linking screen to select {?parameter} instead of {?pm-?parameter}.

I think it would help if you showed the record selection formula in the sub. Go into the sub->report->selection formula->record and copy what you see there into the thread.

-LB

 

[gbuchman1]

Thanks everyone for your help, particularly lbass - thank you! Presently, I am not getting prompted for a value for {?Pm-tblDLTrans.sCodeIDf_1} when I run the subreport. Here is the Record Selection formula in the SUBREPORT:

{tblBLTrans.sCodeIDf_1} = {?Pm-tblDLTrans.sCodeIDf_1}

There is no parameter in the main report for the tblDLTrans.sCodeIDf_1. There is however a record selection in the MAIN REPORT. Here is that selection:

not ({tblDLTrans.sOrigDocNum} startswith ["API", "Cor"]) and
{tblDLTrans.sCodeIDf_1} = "004072660306"

For now, I am just testing the report with this one value for {tblDLTrans.sCodeIDf_1}, however, once the report is working properly, I want to run it for numerous valuse for this field.

 

[gbuchman1]

oops.. I just ran the subreport and I am getting prompted for the
Pm-tblDLTrans.sCodeIDf_1. Yet the main report still gets $0 for budget in each line in GH3b (report is in GH3a). please continue to help me with this. Thank you

 

[gbuchman1]

Hello,
Hate to say it but I have not been able to get my budget amount (from subreport) to show on the main report (actuals), even after trying the solutions given. I must have missed something. If any of you would please assist, I would certainly appreciate it. I have the shared variable on the main report after the subreport, and I have it at the same level of detail on both the subreport and main report. It shows at the sCodeIDf_3 level but shows zero $ on each line. Your valuable help will be much appreciated!