Urgent help required - shared variables & subreports 1

[BizzyLizzy]

Hi guys

Hoping someone can help me urgently.

I am using CR 8.5 with an ODBC link to an SQL database.

The report I working on is taking call data from a main table. The report is grouped by Agent ID and then by Date - so far so good. I also have a subreport included in the main report which is built on an stored procedure that pulls details from our phone system (ie how long an agent was logged on for and talk time etc).

The problem I have got is that I can pull the total logged in time PER AGENT into the main report, but I cannot pull in the grand total logged in hours (for all agents). What I have done is this:-

The subreport is placed in Group H1a

Within the subreport I have used the 3 formula method to get the running totals per agent. This is working fine the variable has been pulled through to the main report ok.

I have also created the following -

whileprintingrecords;
shared numbervar TOTALS:=Sum ({CCGETAStatsJan13;1.LOGGEDINTIME});
TOTALS

This displays fine on the subreport, but when I pull it into the Report footer on the main report it is only showing the logged in time for the last agent in the group! I have tried putting it in different places but I still get the same result.

Is this a linking problem? I am linking by Agent ID.

The reason I need to have the totals pulled in as variables is that I need to use them to work out per hourly call rates, sales rates etc!!!!!!!

Please help me - going very rapidly mad here!

Lizzy

 

[Madawc]

I don't see anything wrong with your method, but maybe you could switch to using a Running Total instead? I have used them in subreports of my own, and they always pass back OK.

Madawc Williams
East Anglia, Great Britain

 

[Madawc]

I don't see anything wrong with your method, but maybe you could switch to using a Running Total instead? I have used them in subreports of my own, and they always pass back OK.

To get to running totals, use the [Insert Fields] box. Or right-click on a field and then select.

Madawc Williams
East Anglia, Great Britain

 

[scottostanek]

In the main report do you have another formula that looks like this?

whileprintingrecords;
shared numbervar TOTALS;


After all, this is how you get the shared variable out again.

Scotto the Unwise

 

[BizzyLizzy]

hi guys

thanks for the replies. Scotto yes I do have the formula in the main report. I have used this method a few times before and it has always worked before.

Madawc - I tried with the running totals (thanks for the suggestion) - but not joy.

On further mucking about with the report - I have discovered that If I dont link the subreport by agent id (group 1) then the grand totals are correct but the agent totals - however, if I link it by agent id then the agent totals are correct but the grand total then switches back to the total for the last agent in the group.

I have tried using a completely separate subreport to get the grand totals and I have moved it around the main report. I put in every single section in the report to see if I could get it to work. I am sure this problem has something to do with the fact that the subreport is using a stored procedure, because as I said, I havent had this problem before.

This is driving me up the wall.

Lizzy
Frustrated of Perth, Australia

 

[lbass]

Link the subreport on agentID and place it in GH1_a (as you have already). Within the subreport, you should have created a formula like the following (if you can use simple summaries):

whileprintingrecords;
shared numbervar subtot := sum({table.amt},{table.agentID});

If you must use the three formula method for whatever reason, with a reset in the subreport GH, an accumulation formula in the subreprot details section, and a display formula in the subreport group footer, the variable should be declared as a shared var, and in this case the display formula probably looks like:

whileprintingrecords;
shared numbervar subtot;

Regardless of how you created the result in the subreport, you would then in the main report create the following formula {@grtot}:

whileprintingrecords;
shared numbervar subtot;
numbervar grtot;

grtot := grtot + subtot;

Place this formula in the GH1_b section (it must be in a section after the subreport), and suppress it if you like, and then in the report footer place the following formula:

whileprintingrecords;
numbervar grtot;

That should give you the results you are looking for.

-LB

 

[BizzyLizzy]

Guys - what can I say. Brilliant, wonderfull etc etc etc.

Ibass this was just what I needed. Thank you so so so much. What is annoying me is that I didnt think of this solution first! Ah well comes with experience I suppose.

Lizzy

 

[BizzyLizzy]

Grrrrr Management!!!!

They have now decided that they want the report grouped by Agent location as well! This will be the top level group.

I have tried the same method of totalling that you suggested Ibass and this doesnt work. I tried moving the subreport up to the now new group h1 but this completely stuffed all the totals!. And I cannot figure how to link the subreport in light of this new group. I am being completely thick here or just missing the bloody obvious.

Help

Lizzy

 

[lbass]

Can agents appear in more than one agent location?

-LB

 

[BizzyLizzy]

ibass

No they cant!

Lizzy

 

[jedownsjr]

I feel your pain -- I have experienced this same problem (or one that sounds just like it). Using the shared variable works great to pass numbers between subreport and main report (or vice versa). I had to play around with it quite a bit to get past what you described, where the value for the last record in the subreport was being passed through rather than the total. I created 2 formulas -- #1 contains a VARIABLE such as myTotal. #2 contains a formula similar to the one you show which uses myTotal and adds to it every time a record is found in the subreport, then the subtotal is passed through to the shared variable of the same name (myTotal) in the main report. The key was resetting the variable's value by placing my formula #1 in the proper section of the report. If you are sorting by agent in the subreport and want to subtotal for that agent & pass the subtotal back to the main report, you would want to reset the value before the subreport runs again for the next agent...and so forth. Reset your variable in the report header or page header of the subreport each time the subreport runs, accumulate in your formula, then pass that value through. Your problem sounds like one of those frustrating timing issues which is resolved by looking at when the formula is being evaluated, and that is driven by the area in which you place the formula. Keep going -- you've almost got it! Understanding how to use shared variables between subreports & main reports is pretty powerful. One other thing I noticed is that you must place the subreport before where the shared variable in the main report tries to use that value -- and don't suppress the subreport! If you need to hide it, suppress the individual formulas in the subreport rather than the whole subreport itself.

 

[lbass]

Lizzy,

If agents are in only one location, then you should leave the subreports where they were (in the agents GH1_a, leave the links the same, and the subtotals and totals should already work properly. If the issue is that you now need a subtotal at the agent location level, then change {@grtot} to the following, but leave the formula in its current group header (agent):

whileprintingrecords;
shared numbervar subtot;
numbervar grtot;
numbervar loctot;

grtot := grtot + subtot;
loctot := loctot + subtot;

Then add two additional formulas:

//{@resetloctot} to be placed in the location group header:
whileprintingrecords;
numbervar loctot := 0;

//{@displloctot} to be placed in the location group footer:
whileprintingrecords;
numbervar loctot;

The latter is the summary for the location.

-LB

 

[BizzyLizzy]

Many many thanks lbass.

Just what I needed. I think I need to some serious study on crystal reports then maybe oneday I will be as good as you.

Cheers.

Lizzy