Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: combine stats in two subreports Post Reply Post New Topic
Author Message
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Topic: combine stats in two subreports
     Posted: 19 Aug 2012 at 9:24pm
Hi All,
 
I have a main report and two subreports using one parameter which works fine. My client wants to have a percentage drawing from these two subreports:
                                    
                                      May     Jun        Total
 
No. of  received items       10        15         25
 
                                      May      Jun         Total
 
No. of allocated Items       6          10          16
 
 
a third subreport would be:          
                                               May    Jun           Total
 
 Percentage of fullfilled items     0.6%    0.66%   0.64%
 
My problems are:
the previous subreports are based on different date table in the systems although they are passed in with a same date when report is run.
 
 
The SQL query from CR ( show SQL query from database Design menu) shows -
For all received items:
 
 SELECT "RVP"."DTE", "RVP"."IRN", "RVP"."BIBISS"
 FROM   "XXXXXX"."dbo"."RVP" "RVP"
 WHERE  "RVP"."BIBISS" IS  NOT  NULL  AND ("RVP"."DTE">={ts '2012-05-01 00:00:00'} AND "RVP"."DTE"<{ts '2012-08-01 00:00:00'})
 
for allocated items:
 
 SELECT "RVP"."DTE", "RVP"."IRN", "RVC"."CFLG", "RVC"."ADTE", "RVP"."BIBISS"
 FROM   "XXXXXX"."dbo"."RVC" "RVC" INNER JOIN XXXXXX"."dbo"."RVP" "RVP" ON "RVC"."IRN"="RVP"."IRN"
 WHERE  "RVP"."BIBISS" IS  NOT  NULL  AND ("RVC"."ADTE">={ts '2012-05-01 00:00:00'} AND "RVC"."ADTE"<{ts '2012-08-01 00:00:00'}) AND "RVC"."CFLG"=1
 
Note: My current main report and two subreports are all crosstab reports - just collecting statistics (count) monthly activities.
 
Could anyone advise how to build an SQL or other ways to combine (to display in another subreport to show the percentage of the existing subreports)
Thanks in advance.
 
JS



Edited by johnwsun - 20 Aug 2012 at 2:06am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 20 Aug 2012 at 4:19am
if you know the number of columns that will need to be returned, you can use shared variables.
say that you will always need the 3 columns in the example.
 
in the first subreport...probably the report footer
shared numbervar sub1month1;
shared numbervar sub1month2;
shared numbervar sub1total;
 
sub1month1:=sum({table.field1});
sub1month2:=sum({table.field2});
sub1total:=sum({table.field1} + {table.field2} );
""  //will hide the calculations
 
in subreport 2:
shared numbervar sub2month1;
shared numbervar sub2month2;
shared numbervar sub2total;
sub2month1:=sum({table.field1});
sub2month2:=sum({table.field2});
sub2total:=sum({table.field1} + {table.field2} );
"" //will hide the calculations
 
in the main report 3 formulas like:
shared numbervar sub1month1;
shared numbervar sub2month2;
 
sub1month1/sub2month1;
 
all done
HTH
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 20 Aug 2012 at 4:52pm
Hi Lockwelle,
 
my queries:
yes, the subreports are in the report footer ( where I insert section a, b...)
1. I would put the formula in the subreports when select 'editing subreport'? then put the formula in the detail section?
2. the data shows in my example are calculated from counting 'xxx.IRN' in the crosstab reports (subreports) in summaried fields  where distinctcount of xxx.irn
 
could you please elabaret a bit?
 
JS
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 21 Aug 2012 at 10:29am
where they are is immaterial, it's the number of columns that you need to reference/display in the main report. If that number is unknown, this method won't work. 
 
Where the data comes from isn't important either, as long as you can get a value and assign it to a variable.
 
in each of your subreports you need to assign values to different variables so that they can be accessed in the main report. you need to assign the variables before you use them in the main report. I gave the syntax for declaring/referencing the shared variables and examples of how to use them...in a simple case.  You would create formulas in the subreports that would assign the values to the variables. You would also create formulas in the main report to access the variables/calculate the values you want to display/display the calculations/variables.
 
any better?
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 21 Aug 2012 at 1:06pm
Hi,
 
Perhaps, I can make it for two columns - one Month only and the total - I have been waiting for the report user's response.
 
My main report will not be accessing the values of the two subreports, but the third subreport will - Is it possible that  your method can be applied in this context?
 
JS
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 22 Aug 2012 at 5:28am
yes, it should work.

shared variables cross all boundaries, so a subreport can access the variables as easily as the main report.

 
 
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 26 Aug 2012 at 6:05pm
Hi,
 
I have adapted your approach to something like below:
 
In the two subreports, I have added formula in the detail section ( Editing subreport) - you can't put formula in the report section where subreports sit.
 
subreport1:
 
whileprintingrecords;
shared numbervar sub1month;

sub1month := sub1month + 1;
 
subreport2:
 
whileprintingrecords;
shared numbervar sub2month;

sub2month := sub2month + 1;
 
while in the subreport3 where the percentage occurs, I have to remove the original crosstab report because it cannot 'see' the formula, instead, I just pull the formula in the report header:
 
whileprintingrecords;
shared numbervar sub1month;
shared numbervar sub2month;
 
sub2month / sub1month * 100;
 
It doesn't look nice ( as it's not consistent -  there are all crosstab report whether main or subreport), but I don't care as long as it works.
Thanks for the hint.
 
JS
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.