Shared Variable Problem
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22840
Printed Date: 29 Apr 2024 at 6:39am
Topic: Shared Variable Problem
Posted By: sfullergs
Subject: Shared Variable Problem
Date Posted: 26 Nov 2019 at 2:39am
I have a subreport that is on a primary report. Everything on the subreport works fine and displays on the main report fine. I have two summary fields on the subreport that I need to pass to the main report, but cannot get the shared variable to work. The two summary fields I need to pass are sum of @actualcfs and count of tbnatur.desc. The subreport is in the group header on the main report.
I have this formula in the subreport on the report footer and it comes up with the proper number if I display it on the subreport:
WhilePrintingRecords;
Shared NumberVar CountCFS := Sum ({@ActualCFS});
I have this formula on the main report in the report footer:
WhilePrintingRecords;
Shared NumberVar CountCFS;
CountCFS
The problem is, on the main report, the only number that will display with this formula is the last number on the subreport. I don't really need this to do any math on the main report, as all of the math is done on the subreport and is correct. I just need it to pass the result of the summary fields from sub to main report.
Here is a screenshot: https://www.dropbox.com/s/jepnvf47caseerm/crystalPNG.PNG?dl=0
|
Replies:
Posted By: DBlank
Date Posted: 26 Nov 2019 at 4:49am
not entirely sure what your set up is...
here are a few things to try/think about
1. place the Count CFS result in the sub report to make sure it is giving you the total expected in the SR but displayed in the MR (not passed though).
2. the sub report cannot be be suppressed
3. The main report variable should be in a section below the SR execution (e.g. SR in reportfooter a and Main Report variable in reportfooter b)
4. Make sure you are not using that same variable in the MR for anything else.
|
Posted By: sfullergs
Date Posted: 26 Nov 2019 at 5:34am
|
Originally posted by DBlank
not entirely sure what your set up is...
here are a few things to try/think about
1. place the Count CFS result in the sub report to make sure it is giving you the total expected in the SR but displayed in the MR (not passed though).
2. the sub report cannot be be suppressed
3. The main report variable should be in a section below the SR execution (e.g. SR in reportfooter a and Main Report variable in reportfooter b)
4. Make sure you are not using that same variable in the MR for anything else.
1. The CountCFS result is in the subreport, and it does give the correct number.
2. The subreport is not suppressed.
3. On the main report, the subreport is in the group header and the variable is in the report footer.
4. The variable is not used anywhere else.
It seems like the variable is working per se, because I am getting at least one value from the subreport. Basically, both reports are grouped by the field tbnatur.desc. The value that gets passed is the value for the last entry, as you can see in the screen shot. Like I said, all the math is good on the subreport and it all works. For some reason it just isn't passing the value of the formula @ActualCFS on the subreport to the main report, it is just passing the value for the last entry on the subreport.
|
Posted By: DBlank
Date Posted: 26 Nov 2019 at 6:10am
The last entry or the last group sum?
If it is the last groups sum that is the 'correct' value for it to display based on where you have these items placed in the MR.
Because the SR is in a group header it will run one instance of the report for each group. It is not returning any value from one group to the next or back to the main report for it to summarize across groups but rather it is displaying the final group result in the report footer.
|
Posted By: sfullergs
Date Posted: 26 Nov 2019 at 7:13am
|
Originally posted by DBlank
The last entry or the last group sum?
If it is the last groups sum that is the 'correct' value for it to display based on where you have these items placed in the MR.
Because the SR is in a group header it will run one instance of the report for each group. It is not returning any value from one group to the next or back to the main report for it to summarize across groups but rather it is displaying the final group result in the report footer.
This sounds like an accurate assessment of what is happening. Any suggestions? I've been kicking this around for 2 days with no luck.
|
Posted By: sfullergs
Date Posted: 26 Nov 2019 at 8:01am
OK, so, it looks like I got these to work a little better by tweaking the formulas.
Subreport formula:
Shared NumberVar CountCFS;
CountCFS := CountCFS + Sum({@ActualCFS});
CountCFS;
Main report formula:
Shared NumberVar CountCFS;
The odd thing now is, this, and another shared variable that I'm passing are each short of what they should be by exactly 554. Oddly enough, they display correctly on the subreport.
|
Posted By: DBlank
Date Posted: 26 Nov 2019 at 10:07am
|
I think you will want to do what you had but in each GH1 set the variable to 0 then in GF2 place the variable returning result from the SR in GF2. Create another set of variable formula's inside the MR to add each returned instance. Place a set to 0 in the RH then an addition of each the field + the SR returned variable GF3.
|
Posted By: sfullergs
Date Posted: 26 Nov 2019 at 10:55am
Here are the two parts of what you said that I'm not sure on how to write it. I did the other things you mentioned and I see what it is getting at but I'm just not sure on the code for these.
Originally posted by DBlank
Create another set of variable formula's inside the MR to add each returned instance.
Originally posted by DBlank
then an addition of each the field + the SR returned variable GF3.
|
Posted By: DBlank
Date Posted: 27 Nov 2019 at 6:30am
Been a while since i did this but I think you will need to create the 3 formula's using a variable in the main report.This can reference the other variable passing thegroup sum back to the MR. This is used to then sum each of those instances. In the summing formula (second formula, reference the shared variable value you returned for each group. It should be placed in a GF section below where you passed the SR value back into the MR.
Whileprintingrecords; // place this formula in Report header
Numbervar ReportSum:=0;
Whileprintingrecords; // place this formula in Group footer 3
Numbervar ReportSum:=ReportSum+ {@CountCFS};
Whileprintingrecords; // place this formula in Report footer to get the total sum
Numbervar ReportSum;
|
Posted By: sfullergs
Date Posted: 29 Nov 2019 at 8:25am
So, the numbers that were displaying were actually correct. So it was right to begin with. The real problem seems to be this:
Basically, this report compares calls for service for one year (main report) compared to another year (sub report) and the tbnatur.desc field shows that the call nature is. The problem is that sometimes, for example, a call nature may not have been used in 2019 (main report) but was used in 2018 (sub report) but since that row does not exist on the main report, that result from the subreport will not show up on the main report. However, if that row exists on the main report but not on the subreport, the row will display and the value for the subreport will just be blank. That part is fine. Basically, I want to blend the subreport into the main report. If that row does not exist on the main report but does on the subreport, I want the result from the subreport to be on the main report.
The reason for the difference in the numbers was because of this issue. The subreport (year 2018) had some natures (tbnatur.desc) on it that had been used in 2018 but had not been used in 2019 (main report). Since there was no listing for the call nature in the main report, the numbers/listing would not come over from the subreport. I need to figure out how to get those fields that exist in the subreport but not in the main report to show up in the main report. Any suggestion?
|
Posted By: DBlank
Date Posted: 29 Nov 2019 at 8:30am
are you just wanting a 'final number' or is this incrementally showing the overall total from group to group?
If it is incremental you would have to create a MR data set that includes all values so you can create the correct groups.
If it is a final number you can just run a one time sr in a rf that gives your total across "all the groups" which are superfluous so can be omitted and pass that sum back and use it.
|
Posted By: sfullergs
Date Posted: 29 Nov 2019 at 8:43am
This may illustrate it a little better. It's not a number issue per-se. I have these grouped by tbnatur.desc. Some of these natures are on the subreport when I run it independently, but don't come over to the main report because those natures don't exist on the main report. Basically, I need to blend these two together to where if the main report doesn't have a call nature but the subreport does, the nature and count for that show up.
Screenshot of main report result:
https://www.dropbox.com/s/2fw4kyu1r9s6zfs/cfs.PNG?dl=0
Screen shot of subreport result:
https://www.dropbox.com/s/srblfrg1tg79jby/sr.png?dl=0
In these screenshots, the subreport is the column of numbers on the left and the main report is the column of numbers on the right. There are multiple instances where this takes place, I just used the one on the last line because it is easiest to show.
|
Posted By: DBlank
Date Posted: 29 Nov 2019 at 8:52am
|
Why are you using a subreport and not just one dataset? What is it about the process that requires you to split it out? Do you have the ability/rights to create a command or stored proc (or similar) instead of the sub report process? The problem you are describing will be hard to resolve keeping the Main and subreport model unless you can alter your selection process in the MR to somehow include all possible groups that can occur in the SR. It might be easier to just think about one data set instead.
|
Posted By: sfullergs
Date Posted: 29 Nov 2019 at 8:55am
|
Originally posted by DBlank
Why are you using a subreport and not just one dataset? What is it about the process that requires you to split it out? Do you have the ability/rights to create a command or stored proc (or similar) instead of the sub report process? The problem you are describing will be hard to resolve keeping the Main and subreport model unless you can alter your selection process in the MR to somehow include all possible groups that can occur in the SR. It might be easier to just think about one data set instead.
It is comparing two different years of data; 2018 (left column) and 2019 (right column), or whatever two years you select. I have the date selections tied to parameters. I have full rights but have absolutely no idea how to work with stored processes, etc. Even when forcing the main report to include any call nature that could exist, it still will not display it on the main report (2019) if it doesn't exist for that year.
|
Posted By: DBlank
Date Posted: 29 Nov 2019 at 9:13am
You can still have one set of data for the two years and use Running Totals super easy to create 'two columns'
Select criteria shows two full years
Group on the product
Create two running totals, one per year
in the RT type set it to use a condition and use the year in that. You can also use a formula to make it conditional so it adjusts based on the entry params or some sort of calendar condition if this is a pre-set condition of years against the current calendar date.
Way simpler and way less overhead. Sub reports can just kill the performance.
Not sure what you are counting or how exactly but can help with RT set up if you post that and need assist.
|
Posted By: sfullergs
Date Posted: 29 Nov 2019 at 9:34am
|
Originally posted by DBlank
You can still have one set of data for the two years and use Running Totals super easy to create 'two columns'
Select criteria shows two full years
Group on the product
Create two running totals, one per year
in the RT type set it to use a condition and use the year in that. You can also use a formula to make it conditional so it adjusts based on the entry params or some sort of calendar condition if this is a pre-set condition of years against the current calendar date.
Way simpler and way less overhead. Sub reports can just kill the performance.
Not sure what you are counting or how exactly but can help with RT set up if you post that and need assist.
The problem is, I'm not just trying to get a final total. I want to display the count of each call type for each year that is selected. Basically a side by side comparison then at the end I would total everything.
|
Posted By: sfullergs
Date Posted: 29 Nov 2019 at 10:15am
|
Originally posted by DBlank
You can still have one set of data for the two years and use Running Totals super easy to create 'two columns'
Select criteria shows two full years
Group on the product
Create two running totals, one per year
in the RT type set it to use a condition and use the year in that. You can also use a formula to make it conditional so it adjusts based on the entry params or some sort of calendar condition if this is a pre-set condition of years against the current calendar date.
Way simpler and way less overhead. Sub reports can just kill the performance.
Not sure what you are counting or how exactly but can help with RT set up if you post that and need assist.
Going by your suggestion, it seems like I can easily get the 2 years worth of data on the same report using parameters. It's just a matter of getting the numbers into two separate columns and having one column only numbers for parameters {?CBeginning Date} to {?CEnding Date} and the other for parameters {?Begin Date} and {?End Date}. I tried a running total where the field to summarize was tbnatur.desc and wrote and evaluation formula where cdcall.whenocc1 was in {CBeginning Date} to {?CEnding Date} and it reset on change of group tbnatur.desc but I get a 1 if something exists and 0 if something does not. I have the running totals to get the grand totals figured out already, I just can't figure out how to get only counts specific to {?CBeginning Date} to {?CEnding Date} to display a correct count.
|
Posted By: sfullergs
Date Posted: 30 Nov 2019 at 3:31am
OK, I got all of this figured out and working correctly using running totals, formulas, and summaries. Thank you very much for getting me on the right (and better) track to knock this out.
|
|