Drill Down -Losing Shared Variable value

[VensCH]

Hi All,

I am using CR 10 with SAP.

Here is situation:

I have a main Report which is having three groups Region, State, and Center.

Why using SubReport ?
I have to use Sub report to get the calculation for each employee in a Center, Subreport used tables are designed if they get commission then only we will have a record out there,I cannot do join in main report / left outer won’t work.

So I am using Sub report to get all the calculations done and I will be printed against the employee in Main report.

Subreport will be having some 1 million records, so I wanted run once in Report Header and get all the values dumped into Arrays and get into main report,so that I will not be running sub report for each region or center.

Here is the Problem :

When run the Report, the Commission for each employee will be shown under their respective centers, lets say XX against it shows 100 for center AA, once I click (DrillDown) on AA Center ,XX Commision will be shown ‘0’.

This when Sub Report is ReportHeader of the Main Report runs for few minutes.

If I place the same Sub report into GroupHeader of AA then everything is fine but take few hours to run.


Any help is appreciated and I am in touch with Crystal Decisions,My query is queue and I will update as soon as I get the info,

Thanks,
Venu

 

[lbass]

When you place the subreport in the group header, you should be linking it to the main report on the center group field. Then it will only run for those employees in that center for that group.

-LB

 

[VensCH]

Thanks for the reposne,
yes lbass,we can link on center
But i don't want subreport to run for the each and every center, the way i am doing is run the subreport once and get all the values into arrays, count based on main report value.

Subreport is having transactional tables which may have more data in the future.

Thanks,
venu

 

[lbass]

I don't follow your rationale. Your current method doesn't work, but it does work if you use the subreport at the center level. If you link on the center group, the number of records per subreport run will be limited to the number in that particular group. My guess is that it would be as fast as a subreport in the report header. Not sure why you feel that future increases in data in the transaction tables is a factor either.

-LB

 

[VensCH]

Thanks for the reposne,
Sorry that I did not give you the clear details in the second post,There is no center exists in the sub report.

if you look into my first post,this subreport should be a unlinked one reason is 'There may or may not be corresponding record for the main report records,
So subreport will run for all the records for each group.
I am doing the caliculation like this,

Our business rules says - at any given point of time one employee can work for only on Center,

So, I am getting all the ative employees in main report and gourping on Region, State, and Center.

Now at the Center Level i have those employees who are belongs to that center.

what subreport is doing ? is all the employees with EmpID,Commision who are present in those transaction tables.

In main report center group details level , i am checking

mainreport.empid = subreport.empid then
sureport.commision
else
0;

let me know if you need any more details,

Thanks,
Venu

 

[lbass]

I would probably group on employee as a fourth group in the main report, and then link the subreport to the main report on employee ID and place the subreport in the employee group header.

If you need to work with the subreport commission value in the main report (not just display it), you would set it the commission up as a shared variable and then work with it in the main report in a section below the one in which the subreport is located, maybe in the employee group footer.

-LB

 

[VensCH]

Thanks for the reposne,

Ibass I really appreciate your help so far.

Yah, I did that too, sub report will run for each employee group once and works fine, problem is takes few hours to run and when we open it from CE10 Active X /Dhtml also take few minutes to open.

I got what you are saying but is it wise to do when you have 50,000 Active employees.Sub Report will run for 50,000 times.thats why i was going for Unlinked sub report.
which runs once and runs for few minutes but else everything is very quick, when open in CE10 or in Drill down.



Thanks,
Venu

 

[lbass]

It may not be wise, but the question is probably moot, since I don't think that running the subreport in the report header will give you the correct values during drilldown.

Are you ruling out using a left join to the employee transaction table because of the huge amount of records?

I don't know for sure whether it would help with speed, but what if instead of designing the report for drilldown, you used the employee transaction table in an on-demand subreport format?

-LB

 

[VensCH]

Ibass Thnaks for the Help and Here is the solution what BusinessObjects suggested and it works,the only thing is header will be shown to the drill down and need to do
conditional suppression.

To be able to drill-down you have to File -> Report Options. Here you check the "Show All headers on Drill Down".


Thanks,
Venu

 

[lbass]

I apologize for being so (adamantly) mistaken. The solution seems obvious in retrospect (as is so often the case!). I was recently working on a problem that made use of the fact that variables will not be passed down in drilldown, but I remembered too late that that was true ONLY if "show all headers on drilldown" was NOT checked. Sorry if I led you astray.

-LB