Use a calculated value in subreport in main report
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=8382
Printed Date: 27 Apr 2024 at 8:58am
Topic: Use a calculated value in subreport in main report
Posted By: cwhitmore
Subject: Use a calculated value in subreport in main report
Date Posted: 17 Nov 2009 at 12:24pm
|
I have a main report with 2 subreports. All the reports calculate the same values but are grouped differently. for example, in the main report, i have a field to calculate gross revenue. In the first subreport, it is grouped by market, so it calculates the market gross revenue for the same time period. I would like to know how to calculate the difference between the gross revenue in main report and gross revenue in the first subreport. Or better yet, how i can i use the value "as is" in the first subreport in the main report. Hope this makes sense.
Cecil
|
Replies:
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 6:16am
|
shared variables.
declare it in the subreport and access it in the main report.
in the sub
shared numbervar aVar;
aVar := somevalue you want;
in the main report
shared numbervar aVar;
use it how you want.
HTH
|
Posted By: cwhitmore
Date Posted: 18 Nov 2009 at 6:38am
|
thanks for the swift reply. I am familar with using shared variables within a report but no sure how to access it when using subreports. If i declare a shared variable in my subreport, for example in supreport i have, shared numbervar MarketOccasionDiff:=if {#Occasions-base}=0 then 0 else (({#Occasions-base}-{#Occasions-1})/{#Occasions-base})*100 this give me a value, let's say 10. Now i would like to pull that 10 from subreort to main report. i used the following in main report , Shared numbervar MarketOccasionsDiff:=MarketOccasionsDiff. It displays 0 instead of 10
how do i access it in the mainreport when i u....i hope this makes sense
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 6:58am
|
same way you would if it was in the main report, using a formula like:
shared numbervar MarketOccasionDiff;
and then use it as you would any other variable. Shared is the key word as it allows the variable to cross the boundary from subreport to main report.
HTH
|
Posted By: cwhitmore
Date Posted: 18 Nov 2009 at 7:12am
|
I think this is where my confusion is. I'll try and explain. the subreport had a shared variable, shared numbervar MarketOccasionsDiff:=formula, this returns 10 in the subreport. Now, i would like that 10 to display in the main report. So i used the following shared variable to try and call it over, Shared numbervar MarketOccasionsDiff:=MarketOccasionsDiff. The result is 0. It seems to me the shared variable in the main report is totally separate from the shared variable in subreport even though i'm using the same name.
Cecil
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 7:19am
|
they shouldn't be. The shared variable name is the same in both the subreport and the main report. No assignment should be needed. if the main is displaying 0 after the subreport has run, and I think that I have had this happen to me, you need to play with it...as I don't recall how I solved it...I think I needed to not suppress my subreport.
I know it is silly, but does your shared variable display a value in the subreport, and if so is it the correct value? I've had it where I thought the variable should be x and it displays y....only to find out that I set the variable late or forgot to reset it or some other flaw.
HTH
|
Posted By: cwhitmore
Date Posted: 18 Nov 2009 at 7:24am
|
Thanks, you have been most helpful. It looks like i may have mispelled the shared variable name in the main report. I checked spelling, and now everything is working properly. Once again, i appreciate your prompt response.
Cecil
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 7:25am
|
I hate when I do that....it is so easy to do, and so hard to find...especially in a complex report with lots of variables running around.
|
Posted By: cwhitmore
Date Posted: 18 Nov 2009 at 8:25am
|
OK, i have another problem with the shared variable that i didn't notice earlier. The shared variable is displaying the wrong info. For example on the first record it displays 0 instead of 16. But on the second record the 16 is displayed. So it seems it is one record behind or early, not sure as i don't know where the 0 comes from.
Cecil
|
Posted By: lockwelle
Date Posted: 19 Nov 2009 at 6:08am
|
the first question that comes to mind is: are you access the variable before the you call the subreport? Are they on/in the same row of the report?
|
Posted By: cwhitmore
Date Posted: 19 Nov 2009 at 6:33am
|
the subreport is grouped by market, the main report is grouped by market and location. My data is totaled in the subreport by market and the data in the main report is totaled by location. In short, the market subreport is an aggregate of the main report. the data in the main report is displayed in location footer and the data in the subreport is displayed in the market footer. I would like the shared value from the subreport displayed in the main report. So far when the shared value is displayed in the main report, it is one record behind. So on the first record the shared value is "0", but the second record the shared value is "16". The "16" i want displayed on the first record.
Cecil
|
Posted By: lockwelle
Date Posted: 23 Nov 2009 at 6:21am
|
you got me...you want a value, from what amounts to a future line, to be displayed in the current line. You might try calling the subreport using the value from the 'NEXT()' field, but no guarentees.
This is kind of a thorny quandry. I would bring out the next value in a new column in the result set (I use stored procs and can accomplish this...I think, but it probably won't be easy/pretty). Crystal, and just about every reporting system out there, is set up to deal with values in the this row of the data, so pointing to another row in the data is an iffy proposition and may not be doable.
|
Posted By: cwhitmore
Date Posted: 23 Nov 2009 at 1:10pm
|
I really appreciate your follow-up. I was able to get my report to work with a little trickery of grouped fields. It seems i was getting "0" on the first record because the subreport that uses the shared varible was not being calcluated before i displayed the results of the shared variable, in this case the market results needed to be calculated first. So i created another market field, "market2" in the database. Then i inserted a group based on the "market2" field. I placed another copy of the subreport in the original "market" header. Now, when viewing the results of the shared variable when grouped by location, i get the right result.
Cecil
|
|