Cannot summarize or add a shared number to a graph

[panosSivTS]

Hello all,

I have a problem with a shared number from a subreport (empl_work_hours)

Cannot be summarized so we have create a shared number var "total" which is increased by adding the value every time to have this

The problem is that we need this value to put it in a graph

Is there a way or a different way to do that?

You can find the report attached

Thank you

 

[lbass]

Try searching the BO forums using “Charting on print-time formulas” as keywords. There is a white paper on this topic on the SAP Business Objects website that these posts will be referencing. It is complex, but can be done. Of course, it would be simpler if you could eliminate the need for a subreport.

-LB

 

[panosSivTS]

Sorry, but i cannot understand which part of this document can fit to this specific need

I have already the data needed but seems impossible to put them on one chart (in the main or in the subreport)

 

[lbass]

The Charting on Print-time white paper is focused specifically on how to chart on formulas that contain print-time variables. Did you try to work through it? It is somewhat complex to do and there are tricks to it. Please explain what you tried.

Also, you could potentially get around all of this by eliminating the subreport. Could you have linked to the tables that would give you the employee work hours? You could also have used a conditional formula instead of a running total for your calculation in the subreport and then just inserted a summary on it. As a general principle, you could also make your report design easier to follow by eliminating all of the nested formulas, which make it hard to track back to what you are really doing.

-LB

 

[panosSivTS]

Hello,

I have attached a zipped file with the report without the charting print time add and with it

the problem is that without i have no values on employees_hours formula even if there are, and with i get the error attached (the array dimension must be between 1 and 1000) so i cannot even see if there are results appeared with this way

I have also tried to created a formula instead of running total (hours) in the subreport but cannot be summarized or inserted to chart in the main report also

 

[lbass]

I can't troubleshoot this easily without seeing the actual report saved with data.

You do not need a running total and it will only complicate matters. First make a formula {@null} by creating a new formula and then saving it without entering anything. Then create a second formula:

//{@cntreedtype}:
if {EMPLOYEE_DAY.REED_TYPE}=4 then 1 else
tonumber({@null})

You can either insert a sum (not count) on this formula at the group level, or you can create a formula:

sum({@cntreedtype},{Employee.REEM_Code})

Some pointers:

In the employee group footer, you should have two formulas for use in the chart:

//{@onchangeof}:
whileprintingrecords;
shared stringvar onchgof;
shared stringvar onchgof := onchgof + totext({Employee.REEM_Code},0,"")+ "^";

//{@showvalue}:
shared numbervar empl_work_days;
shared numbervar empl_work_hours := empl_work_days*{EMPLOYEE.REEM_AVAILABLE_HOURS};
shared stringvar showval;
showval := showval + totext(empl_work_hours,2,"") + "^"; //2 for 2 decimals or 0 for none; "" for no comma

The chart must be in its own subreport in the report footer (Add the employee table as the datasource). The sub should be linked on the main report formula {@onchangeof} and the selection formula within the sub should be:

totext({Employee.REEM_Code},0,"") in split({?Pm-@onchgof},"^")

Insert a group on {Employee.REEM_Code}.

Next create a formula within the sub:

//{@showval:
whileprintingrecords;
shared stringvar showval;
tonumber(split(showval,"^")[groupnumber]);

Insert the chart in the subreport report footer.
Add {Employee.REEM_Code} to the "on change of" box.
Add {@showval} to the show value box.

Do not leave the chart expert before finishing this. There is a trick to this, as formulas in the report list of the chart expert will disappear after the report is run, if I remember correctly.

If you need more assistance with this, please send the report saved with data.

-LB

 

[panosSivTS]

Thank you very much

until now the only error that i get is on totext({Employee.REEM_Code},0,"") (too many arguments)

I cannot see why, the syntax seems ok. Can you help me with this? When this is fixed i will be able to check if working

Thanks again

 

[lbass]

If the code is already a string, then use the code field directly without the to text and related arguments.

-LB

 

[panosSivTS]

on the showvalue formula line
tonumber(split(showval,"^")[groupnumber]);
of the subreport

we get an error "the subscript must be between 1 and the size of the array

 

[lbass]

There is a limit of 1000 for an array. How many employees are there?

-LB

 

[panosSivTS]

261 employees, the problem maybe is about the lines on other table (employee_day or vcr_employee_feedback) where the employee code appeared many times

 

[lbass]

If you have a group on employee, then the array should only include 261 members. Please send your report saved with changes you have made so far. I can’t look at it until late this afternoon though.

Also make sure you have removed old formulas from the report layout that contain accumulating variables and that you are no longer using to make the chart. You don’t want variables accumulating in two different formulas.

-LB

 

[panosSivTS]

Hello again,

I am sending you the report without the array because is impossible to be executed in order to send it to you with data

We need to have the results appeared in charts

 

[lbass]

This report doesn't tell me much. You have added an outer group for category, which affects everything. Would you be charting separately for each category? (Placing the chart in the category group footer?) If so, you would have to have reset formulas in the category group header (in a group_a section, with the calendar report in the group_b section.

-LB

 

[lbass]

PS. A question and two comments.

Question--instead of charting by employee, do you mean to chart by category???

You can get the group 2a header to not show by doing two things:
1) in the main report, go to format->subreport and check "suppress blank subreport"
2) in the section expert->group header 2a, check "suppress blank section".

-LB

 

[panosSivTS]

In order to simplify the need, i have delete the outergroup and i want the two total number in a chart for start. I think that if this can be done, we can proceed then with the group per category, analysis per employee etc

 

[lbass]

What two total numbers? Also, please identify exactly what you want to end up with. Starting out simple only means a bunch of changes later on.

-LB

 

[panosSivTS]

@total_print and/or @avg_percent

Finally we need a chart with two bars comparing the total hours worked and the available hours of the employees

 

[lbass]

Sorry, I can’t help you further. You cannot do the chart piecemeal. It’s tricky as it is with only one summary and by it’s nature, you have to set it up in all at once or it won’t work. You are talking two summaries and potentially at a group level, and i still don’t have a clear picture of what that would look like. I think you have made this report more complex than it needs to be by using the employee subreport in the first place (which you haven’t justified)—you might be able to avoid using variables at all, in which case charting would be simplified. I could do this chart for you, but I can’t without access to the data source, and I am unwilling to continue walking you down this path that keeps getting longer. I also don’t see any evidence that you have incorporated any of my suggestions.

-LB

 

[panosSivTS]

Thank you very much for the assistance till now.

I have a report with your first suggestion but is unable to save it with data as i took the error "the subscript must be between 1 and the size of the array