Dynamic Sorting

[myty1998]

I am trying to sort a grouped report by results in a control.

My first grouping is for Divison, then employee. I need the employees within each Division to sort descending by results. The control that holds the results contains the following logic in the value exp.

Sum([PolCount]) Where([REF_PRODLINE.PRODLINENAME] = A AND [EfftEndDate] >= CDate(StartDate) AND [EfftEndDate] <= CDate(EndDate))

I have overridden the ObtainSelectStatement and entered -- Orderby (Region Column #), (Employee Column #). When I drop the Employee field I get duplicates (because employees may exist in mulitple locations in the database (transfers, promotions etc.)

Any suggestions?????

Thanks!
Chris

 

[bcaslow]

I guess I need a little more info on what you need to accomplish, and what exactly your problem is. Are you saying you're trying to sort the data on a control's computed value? If this is the case, it's possible to do, but it will be a performance killer; I had to have one of these reports and it is unbelievably slow compared to just returning the data from the database.

Before I get into that -- as I may be off base in my assumption -- please explain in more detail what you have, and what you need for end results.


Bill

 

[myty1998]

That is what I need to do. I have a grouped report that I need to highlight the best performers in each product by division. That control is a computed value.

What I've read for this type of sorting seems to include extensive coding as well as performance issues. Is that correct?

Chris

 

[bcaslow]

Before I get nto how to handle this within Actuate, let me ask you this: is there any particular reason why you can't create the computed column in your SQL and return it in your datarow, thereby making it MUCH easier to sort on? (Basic rule of thumb: if there is ANYTHING you can do on the database side, DO IT THERE. It will be MUCH more efficient than having Actuate do it!)

 

[myty1998]

I'm not quite sure I follow. If you mean do I have access to the database then no, I don't have access to the data where the information I need is stored. My only access to this data (aside from pulling down tables into access to run analysis) is through Actuate.

I'm sorry if I'm not following you.

 

[bcaslow]

What I was referring to was something along the lines of a stroed proc or creating a view where you could create a calculated field which is returned in your record set.

Another question: are the StartDate and EndDate the same throughout the report, or can this vary for different employees?

In other words, for all employees across all divisions will this report:
SUM([PolCount])
Will (REF_PRODLINE.PRODLINENAME] Always equals A?
And EfftEndDate is always between the same StartDate and EndDates?

What I am trying to get at is why you are needing these conditions there, in the control, rather than just filtering your results set with your query (
Select Column1...ColumnN
From Table1...TableN
Where([REF_PRODLINE.PRODLINENAME] = A AND [EfftEndDate] >= CDate(StartDate) AND [EfftEndDate] <= CDate(EndDate)) AND..

If it absolutely has to be done in Actuate, it would probably be easier for me to send you an example. Have you included your email in any posts> Sorry, I don't remember...

 

[myty1998]

OK - The Start and End date is always the same (Parameters for report dates (ie 1/1/04 - 1/31/04)

I suppose I could set 3 controls using the conditions. The report is looking at one specific result type "a", but each employee that qualifies also needs to meet the min results in "b", "c", "d" as well. So I am using three/four controls. The Start and End Date are already in the ObtainSelectStatement so I guessI don't need them in the control...The consultant that started our base reports still had it in the control - I just copied the design. I suppose I could use:
Sum([PolCount]) Where([REF_PRODLINE.PRODLINENAME] = A
Sum([PolCount]) Where([REF_PRODLINE.PRODLINENAME] = B etc.

Once I determine if a employee has met all standards across the board I need the report to sort by A. Listing the top performers at the top of each Division section.

My email is Christine.foskett@libertymutual.com

I haven't used stored procedures - before. Like I said ---this is all new to me. I'm amazed I've gotten as far as I've gotten.

I really appreciate any help you can provide.

Thanks!