row limit/ execution limit blowup

[benbotektips]

There are 2 tables, a MEMBER with 1.1 million rows, ACTIVITY with 50 million (Yes, I know it's too big; try arguing with corporate execs who overpromise on projects)!
The report template needs to pull in an attribute of member.company id (common to both tables), and give the average number of days spread, registration to activity, both of which dates are on the member table. There's a fact of (member.last login date - member.registration date), which has the sum of the fact in a metric, and the average of that metric in another metric. So, the metric 'sum days spread' = sum {the fact above, '(member.last login date - member.registration date)'}, and the final metric 'average days spread' = avg((the metric 'sum days spread'}). There are fields in both tables for company-id and member-id, and there's a join of member.company-id = activity.company-id, and member.member-id = activity.member-id in the schema graphical view.
When I run a report on only the member table, attribute of member.company id, with nthe metric of average, great, perfect results. The problem is, I need to qualify (relate) to members in the ACTIVITY table; that is, to enforce the join, and show me avg # of spread days per company, from the member table only for those members that match (pass the join) to the acitivity table.
There are only 2 other fields in the activity table, and the only way I'm aware to enforce a join between 2 tables, is u have to include at least one field from each. The problem is, the other 2 attribute fields from ACTIVITY, there are millions of distinct values for each (one is timestamp, including seconds, the other is member id). The generated sql is grouping by selecting on member.company-id, activity.timestamp (which is the only field which'll enforce the join, as it's unique to activity), and predictable the report blows up on row limit. The admin has bumped up the row limit twice already and won't do it again.
So, how can I validate my list of member.company-id's, avg # of days difference between reistration & activity, against only those members who appear in the activity table? Is there a way to edit the generated SQL, or besides throwing in a field into the report objects box, to enforce the join?

 

[nlim]

if I understand it right, I would try a report as filter option.

1) create a report R1 which has member.companyid on the template, and filter it to show only the member.companyid you want from activity table. make sure it does not access the member table.

2) in your original report, add R1 to the filter by dragging it onto the filter section.

the sql should be a subquery join.

good luc

 

[entaroadun]

Add an attribute named "Activity Join" to the activity table, with one expression in the ID form. This expression should simply be '1'.

Add a attribute form value filter to your report, with "[Activity Join]@ID = 1" as the filter expression.

MSTR will add the Activity table to the report SQL in order to apply the filter. It should join properly. MSTR will add an expression to the WHERE clause: "1 = 1". The RDBMS should compile this out, so there's no performance hit.

Good luck!