charting with multiple subreport shared variables in the main report

[jat3333]

I have 4 subreports on my main report, each presenting different data (some of these I have had to write SQL commands to get the exact data I was looking for). The main report has a group (SAMPLE.GROUP_NAME) in which each subreport is placed in the group footer of this group.
e.g. GF2A = samples not released
GF2B = samples not reviewed etc....

I have used shared variables to give me a grand total of each of these subreports placed in the Report Footer of the main report. This is calculating perfectly fine. Problem: I need to chart these values

i tried using that Charting for print time formulas technical document but it doesn't work for me because I don't have anything in my details section in my report. I tried placing the chart in my main report footer and all I see are zeros. I tried placing a chart in the Group Footer of my main report and was hoping to suppress all other reports except for the final one where I would have a grand total but it is always off the first set of numbers. e.g. dataset 1 = 9, dataset 2 = 7--- it starts with 7. I understand I am tricking the system using whileprintingrecords when it needs to use whilereadingrecords first etc.... regardless i can't get this work and I cannot find anything within this forum that is similar to my situation that actually has an answer attached to it.

Any help is much appreciated. I have attached a screenshot of the design view

Thx

J

 

[APB-Reports]

Hi J,

I have had a similar issue before.
Share the sub-report values with the main report like:

//Sub report 1:
WHILEPRINTINGRECORDS;
Shared NumberVar sub1 := (IF ISNULL({yourdata}) THEN 0 ELSE {yourdata});

//Main report:
Shared NumberVar sub1;

Then pass these values back into a new sub-report (using sub-report links).
Make sure the shared variables are on the report canvas and in a section below the 4 sub-reports and above the chart sub-report.
CR needs to process them in correct sequence.
You should then have the values to work with in your new chart sub-report.

Try this and let us know how it goes.

 

[jat3333]

Hi,
Thank you for the response to this, much appreciated.


I change my subreport formula to match above. the main report formula - is this supposed to be the "final - grand total" one I am to modify? If so, this is the one I modified to look as you have listed above.

My questions: the new subreport I need to create to contain the chart--

1. am I using all the same tables and grouping as in the main one?
2. the subreport links - is the OnChangeOf and ShowValue formulas that are specified in the technical document what you are referring to?
3. do i write the same formula in of the final-grand total from the main into the new charting subreport?
4. do I place the chart in the charting subreport report footer?

Sorry for all the questions - I can't seem to just get this right and it is driving me crazy.

Thanks again for the help with this

 

[APB-Reports]

Do you need a chart per group or one for all groups?
If the chart is per group will it just contain 4 values - so 4 bars or pie slices for example?
Another simple solution would be to create another subreport with an sql command. In the sql you could write 4 union all queries and just return the query Id and total values.
A, 100
B, 75
C, 120
D, 200
Then build the chart against this data set.

I need more info to give you an accurate answer. Maybe upload a report with saved data if it is not sensitive info.

 

[jat3333]

It would be one chart for all the subreports included on the main report, not one chart per group. I included some screenshots. Unfortunately the report contains some sensitive information that I cannot remove without completely messing up the report.

I wanted the ability to place the chart in the report footer of the main report, to chart on the grand total values from the 4 subreports. The grand totals are formulas written to include the shared variables.

Ex. --- in the subreport of the samples not released I am using this formula:
Whileprintingrecords;
Shared numbervar SampNotrel;
SampNotrel:= count({SAMPLE.SAMPLE_NUMBER})

In the mainreport:

The initiate formula:
whileprintingrecords;
shared numbervar SampNotrel := 0;

Total formula:
Whileprintingrecords;
Shared numbervar NotRelgt := 0;

Subtotal:
whileprintingrecords;
shared numbervar SampNotrel;
shared numbervar NotRelgt;
NotRelgt := NotRelgt + SampNotrel;

Final which gives me the grand total of all the values within the subreport. This is returned in the Report footer of the main report to give me the correct value:
whileprintingrecords;
shared numbervar NotRelgt;

I need to take all these "Final" values, which are the grand totals from the 4 subreports and chart these on the main report. (So all the examples I listed above for the samples not released I have done three other times for the other subreports)

Tried the technical paper for charting on print time formulas but it doesn't work for me because the subreports are not in the details section of the main report.

So if I were to create a new subreport to place the chart in I wasn't sure what I would need to include on it which is why I was asking if I should use the same tables and groups as I did within the main, carry over the "Final" formula into the new subreport, and use those same formulas from the technical document.

Hope this helps.

 

[lbass]

If you are seeing the correct totals in RFa, then collect them into an array by creating a formula like this (I named your four totals sum1 to 4 for simplicity's sake):

whileprintingrecords;
shared numbervar sum1;
shared numbervar sum2;
shared numbervar sum3;
shared numbervar sum4;
shared stringvar array showval := [totext(sum1,2,""),totext(sum2,2,""),totext(sum3,2,""),totext(sum4,2,"")]; //2 for 2 decimals
0

Place this in report footer a.

Then insert a subreport into RFb and then create four formulas, each like the following:

//{@val1}:
whileprintingrecords;
shared stringvar array showval;
if ubound(showval)>=1 then
tonumber(showval[1])

Repeat for val2 to val4, changing the ubound value and the subscript each time.

Without first adding the formulas to the report canvas, insert the chart and move the four formulas to the right into the "show value" area (allow it to say "sum of {@val1}", etc. In the top box, change it to read "for all records".

I think it is important to do this in the above order so that the val formulas are available in the chart expert. You will note that they disappear from the field explorer within the chart expert at some point.

-LB

 

[jat3333]

Thank you so much lbass for the reply. I honestly have learned so much from you from this forum so I am thrilled to hear from you

ok, so all formulas worked like a charm - they returned the correct values from my main into my sub. When I view it in my main report I can see the values (not in the subreport, which is fine, I assume this has to to with reading/printing the records)

Anyway, I tried to chart these values and the chart doesn't appear. I am obviously missing something. Do I need to use the "OnChangeOf" and "ShowValue" subreport links from the technical document? Do you know the cause of the chart not appearing at all?

Thanks
J

 

[lbass]

I can't tell what's going on. Do the Val formulas in the subreport RFb show the correct values? Or is that what you are showing in the top section of your attachment?

If you go back into the chart expert, do you still see 'sum of {@val1}' etc. or do you see sum of some default field?

-LB

 

[jat3333]

The Val formulas show the correct value when it is read in the main report. When I try to view them in the subreport I see 0's. And yes, I see sum of @val1 etc...

So it looks like it is reading the values in the main report but I see no chart, like it's not even charting anything even the 0's.

I uploaded another attachment to show the views to your questions. I have the formulas written as you have them presented above

 

[lbass]

Can you verify that variables in all formulas are declared as "shared"?

Did you place the subreport in a Report Footer section?

Also, I think you should spell out your exact steps you took. I did test my suggestion and it worked here.

-LB

 

[jat3333]

Verified, all formulas are using shared variables.

Subreport is in the main report footer B

Steps I followed:

1. I placed this formula in main report footer A - which in preview mode shows 0.00
@ChartingForm
whileprintingrecords;
shared numbervar NotRelgt;
shared numbervar NotReptgt;
shared numbervar gt;
shared numbervar NotSentgt;
shared stringvar array showval := [totext(NotRelgt,0,""),totext(NotReptgt,0,""),totext(gt,0,""),totext(NotSentgt,0,"")]; // I made them zeros to get whole numbers
0

2. creating subreport "Chartsub" which is placed in report footer B. this only has the SAMPLE table attached to it. (I have 4 other tables in the main report but only included the SAMPLE table as this is for the sample count)

3. created formulas as follows:
@val1
whileprintingrecords;
shared stringvar array showval;
if ubound(showval)>=1 then
tonumber(showval[1])

@val2
whileprintingrecords;
shared stringvar array showval;
if ubound(showval)>=1 then
tonumber(showval[2])

@val3
whileprintingrecords;
shared stringvar array showval;
if ubound(showval)>=1 then
tonumber(showval[3])

@val4
whileprintingrecords;
shared stringvar array showval;
if ubound(showval)>=1 then
tonumber(showval[4])

4. inserted the chart which is "for all records" and show value(s) of Sum of @val1, sum of @val2, sum of @val3, sum of @val4
5. placed this in the subreport report footer A
6. added the 4 @val formulas into the subreport report footer B (these are the correct numbers that are visible underneath where the chart should be)

I did not link the subreport

 

[jat3333]

I got it to chart!!

I had to actually link the subreport correctly and the chart itself needed to be 'OnChangeOf' - the SAMPLE.GROUP_NAME (so where all the subreports are located on the main report) and I had to uncheck the summarize for all 4 @val formulas. this is working for me now!!


Thank you so much for all the help!


J

 

[lbass]

Add a field from the Sample database to the subreport somewhere--it can be suppressed.

-LB

 

[lbass]

Meant the Sample table that you used to create the subreport.

-LB