Show Graph in Sub-report with data from main report 1

[russula]

Hi All,

Sorry if there was already such question. Can't find it.

I don't know how to solve one problem. I calculate 6 variables in the main report. And I need to put them in the Doughnut Chart. I can't do it in the main report because during calculation I'm using "whileprintingrecords" function. I can pass them to sub-report but in this case I can't create any graph at all.
Does somebody any idea how to do it?

I'm using Crystal Reports XI.

Many thanks for suggestions,
Anna

 

[lbass]

Please see the following article. You probably need to follow these steps:

http://technicalsupport.businessobj...sliceId=&dialogID=2900837&stateId=1 0 2898842

-LB

 

[russula]

I have seen this article but unfortunately this is not my case. This article describes case with using sub-report based on the table and calculated data. But in my case I don't have any table in the sub-report. I have only calculated variables and I need to put them in one Doughnut Chart. CR doesn't allow me to create any chart without table and I don't know how to avoid this limitation.

 

[lbass]

I'm not aware of a way to add a subreport without referencing a table, but even if this were possible, then I don't know why you would need a subreport.

-LB

 

[russula]

There is no problem to add sub-report without any table and I can pass my variables to sub-report using "Change sub-report links" menu. The problem is - I can't create chart based on these variables. It might be I don't need sub-report at all, but again, I can't create chart based on these variables in the main report as well. Because whileprintingrecords function is used for calculating.

 

[lbass]

Try adding a table to the subreport and placing a field in the report header and then suppressing it.

Are you sure you need variables for your chart? What are the formulas for the variables?

-LB

 

[russula]

First, adding not needed table is not very good idea because tables are huge and it takes a lot of time to connect to it. I use only sql query to get data from DB.
Second, if I add table and put variable to the header I'm not allowed to add this variable to the chart. Only fields from table which I don't need. So it doesn't work as well.

I need variables for the chart because I need to do a lot of calculation on the same data. As I said I get data from sql query and it is not possible to run query several times, for example once in main report and second in the sub-report. I do calculation in the formulas and then I need to put results to the charts. For example, I calculate, how many records of Type 1 (variable1), Type2 (var2) and Type 3 (var 3). I can't do it by grouping because I grouped data already another way for another calculation. All three variables should be put in one Doughnut Chart on the end of report. In reality I need to perform more calculation with the same data very oft and it is every time a big problem. Sometimes I have to run query twice and it doubles time. Our DB's are very big. I don't know if it is possible to manipulate the same data in different way in the same report without querying DB several times in each sub-report.

 

[lbass]

Can't help if you don't share the actual formulas.

-LB

 

[russula]

Formula 1
whileprintinggrecords;
Shared NumberVar Count1:=0;
Shared NumberVar Count2:=0;
Shared NumberVar Count3:=0;

Formula 2
whileprintinggrecords;
Shared NumberVar Count1;
Shared NumberVar Count2;
Shared NumberVar Count3;

if Command.description like "*AAA*" then Count1:=Count1+1 else
if Command.description like "*BBB*" then Count2:=Count2+1 else
if Command.description like "*CCC*" then Count3:=Count3+1

Formula 3
whileprintinggrecords;
Shared NumberVar Count1;

Formula 4
whileprintinggrecords;
Shared NumberVar Count2;

Formula 5
whileprintinggrecords;
Shared NumberVar Count3;

Formula1 locates in the report header, formula2 - in the detailes, others - in the end of report.
Chart should comtain in one picture Formula3, Formula4, Formula5. I can show this Formula3, Formula4, Formula5 like fields, but I need Chart.

 

[lbass]

You don't need to use variables or a subreport. Create three formulas like:

//{@AAA}:
if Command.description like "*AAA*" then 1

//{@BBB}:
if Command.description like "*BBB*" then 1

//{@CCC}:
if Command.description like "*CCC*" then 1

Then insert a chart in the report footer and add sum of {@AAA}, sum of {@BBB} and sum of {@CCC} as the summaries, and change the "on change of" section to "for all records".

-LB

 

[russula]

Thank you very much for advice. It looks like I need to change my mind from normal programming to special Crystal Report . Maybe you would be so kind and would give me another good advice? Because I met again the problem with CR rules.
This time without whileprintingrecords but with next function. I need to calculate difference between current data and next (time in minutes). And then to find a max for each group. The formula:

DateDiff ("n", {Command.DataTimeField}, next({Command.DataTimeField}))

This formula is located in the Details Part. Then I wanted to use Maximum Function in the Group Name field and then I need to create graph based on this maximum. But I'm not allowed to use any built-in formula for my formula, maybe because of using next function. Is there possibility to avoid this limitation?
Thank you for help.

 

[lbass]

Now is when you need to use the whitepaper I mentioned earlier. You can set this up like this. Create these formulas:

//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar diff := datediff("n",{table.date}, next({table.date}));
numbervar maxdiff;
if diff > maxdiff then
maxdiff := diff;
maxdiff

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar maxdiff := 0;

//{@displmaxdiff} to be placed in the group footer:
whileprintingrecords;
numbervar maxdiff;
shared stringvar showval := showval + totext(maxdiff,0,"")+ "^";
shared stringvar onchgof := onchgof + {table.groupfield}+ "^"; //use the groupfield you want to chart on, NOT the groupname
maxdiff;

//{@onchgof} (you don't have to place this on the report:
whileprintingrecords;
shared stringvar onchgof;

Then insert a subreport in the report footer that uses the same table as your main report, and use {@onchgof} as the linking field. In the subreport, go to the record selection formula and change it to read:

{table.groupfield} in split({?Pm-@onchgof},"^")

Insert a group on the same group field. Then create a formula {@showval}:

whileprintingrecords;
shared stringvar showval;
val(split(showval,"^")[groupnumber])

Next insert a chart in the subreport report footer, and choose {table.groupfield} as the "on change of" value and choose {@showval} as the summary field, and check "do not summarize".

Note that after you then run the report, if you go into the chart expert you will no longer see {@showval} in the field list, although it will appear as your summary value.

PS. This is really a different topic, and should have been a separate post.

-LB