Compare values in Subreport. Alternative?

[starsky39]

Hi,
I currently have two subreports and want to compare the values in each sub report within the main report, but I don't think this is possible.

Subreport 1 - shows number of hours logged by engineers for All Customers.
Subreport 2 - shows number of hours logged by engineers for Managed Service customers only. (Filtered by searching for a ticked check box).

I need to be able to compare the values in Subreport 1 and 2 to be able to say engineer1 spent 65% of his total time working on Managed Service or Engineer2 spend 45% of his total time working on Managed Service.

Any ideas how I can do this? I'm guessing it's possible using SQL scripting rather than subreports, but this is not my strong point. Thanks in advance.

 

[lbass]

Sure you can. Not sure why you are setting it up this way, but let's say you have a main report that is grouped on engineer. Place the subreports in a group header section and link them to the main report on the engineer field. In the subs, create formulas for shared variables like this and place them in the subreport footers:

//{@allcusts}:
whileprintingrecords;
shared numbervar allhrs := sum({table.hours});

//{@mgcusts}:
whileprintingrecords;
shared numbervar mghrs := sum({table.hours});

In the main report, create a formula for the group footer:

//{@compare}:
whileprintingrecords;
shared numbervar allhrs;
shared numbervar mghrs:
mghrs%allhrs

Insert a group footer_b section and add a reset formula in case the subs can have null values:

whileprintingrecords;
shared numbervar allhrs :=0;
shared numbervar mghrs :=0;

-LB

 

[starsky39]

LBass - that works perfectly! Thank you so much. I have to get this working on an individual engineer basis and incorporate it into graphs and crosstabs. I might come back to you if I have more questions, if you don't mind...

Thanks again. Nicky.

 

[starsky39]

Lbass - sorry back already

On the main report I want to create a crosstab with engineer names down the side, then hours from all in one column, hours from MS in the next and then the percentage difference in a third column for each individual. Like this:

...............Column1...............................Column2
.............Total hours from subreport1....Total hours from subreport2.......% of col1 to col2
Engineer1.............12 .....................................10...................................83%

Engineer2
Engineer3


Any ideas how I could do this?

Thanks in advance....
N.

 

[lbass]

You won't be able to use the shared variables in a crosstab since the crosstab is processed earlier than the subs. Charting becomes very complex as well.

I think you should explain why you are using subreports in the first place. There is no obvious reason for adding any subs, based on what you have told us about so far.

-LB

 

[starsky39]

Hi, thanks for the reply. I'm using subreports as it was the only way I knew how to do this. I'll try to explain what I'm doing and am happy to do it a different way if that is a better way of doing it.

I have 20 technical support people using a call logging system (HEAT) which has an SQL back end. When they are working on calls they raise a journal entry which records what they did on that call and how long they spent working on the call. We have two types of customers - Managed Service and Non Managed service. There is a checkbox on the Profile table for each customer record to denote whether they are a Managed Service customer or not.

I need a report that shows all the technical support people and the amount of time they have spent working on Managed Service customers and non Managed service customers within a given date range. Then the % difference between the two so we can say for Person A, of all the time they have recorded, x% of it was spent working on Managed Service.

The fields I have been using are:
{Journal.Tracker} - The technical consultant name
{journal.time} - the amount of time spent on the journal entry. This is also a text field so I have to use tonumber on this field.
{Profile.MS} - this is a T or F value (the checkbox that denotes whether they are managed service or not.)

So I had one subreport which included all customers and one subreport which filtered customers based on {profile.ms} = T. On each subreport I have a crosstab with the technical support people names as rows and Time as a column. The subreport give me the total hours for Managed Service or not Managed Service, but doesn't allow me to work out the percentage difference between the two.

Hope that helps, sorry for the lengthy reply, any ideas?

 

[lbass]

Limit the dates in the selection formula. Then in the field explorer, create a formula:

//{@managed}:
if {Profile.MS}="T" then
{journal.time}

Insert a group on {journal.tracker}. Add {@managed} and {journal.time} to the detail section and right click and insert a sum on each at the group level. Drag the groupname into the group footer. Suppress the GH and the detail section and then add another formula to the group footer:

sum({@managed},{journal.tracker})%sum({journal.time},{journal.tracker})

-LB

 

[starsky39]

That does exactly what I need. Thank you so much, I really appreciate you taking the time to help me.

N.