Simple Crosstab???

[TEM3]

Using Crystal Reports 8.5 and one Oracle Table I am trying to create a fairly simple cross tab reports and summarizes two activities (assignments reviewed and assignments completed) for people in a particular unit over a particular period of time. Both activities are in the same table using two fields: the user ID and the date of the activity (Analyst Assigned - Date Completed, Reviewed By - Reviewed Date). There are about 30 users involved.

I can't for the life of me figure out if I need to try to do it all with a cross tab, try a grouping and/or create some summing formulas???

Do I need to use an alias of my table to access both activities????

 

[TEM3]

OK, the easy solution is to summarize both activities in two different reports, one being a sub-report. I have created plenty reports with multiple sub-reports, but always one followed by the other horizontally (top to bottom). Is there a trick or technique to displaying two reports (a main and a sub) side-by-side??

Would I just try playing with margins??

 

[lbass]

Going back to your original question, if there are two fields {table.UserID} for analyst and reviewer, and {table.date} for date completed and date reviewed, is there another field that distinguishes whether the activity is analysis or review?

Please provide a sample of data at your detail level if you were to lay these two (or three, I hope) fields on the report canvas.

-LB

 

[TEM3]

Yes, the Date Completed is the date the Analyst Assigned completes the assignment. The Reviewed Date is the date the reviewer (Reviewed By) reviews a completed assignment. These are two distinct tasks that the same group of users do (Reviewed By and Analyst Assigned uses the same set of User ID's). They want the two activities summarized side by side (User - # Assignments Reviewed - # Assignments Completed) for between two dates passed by parameters......

 

[lbass]

You didn't really answer my question. Are there only one date field and one user field? If so, is there a third field that tells you whether the row is for Analysis or Review?

And please provide a sample.

-LB

 

[TEM3]

This is what I need to do:

I want to end up with a Cross Tab formatted report that looks like:

For Date1 to Date2

Analyst Assignments Reviewed Assignments Completed

User01 10 20
User02 15 10

... and so on (for all the analyst in this unit).

Since the review date, review user, completed date, and analyst assigned (who will get credit for the completed assignment) are all separate fields, I guess I will have to parse the table twice (or check all four fields for each record).

Basically the logic is:

For user1 - is he in Reviewed By? If so, does the Review Date fall between Date1 and Date2? Is he in Analyst Assigned? If so, does the Completed Date fall between Date1 and Date2?

I know I could do all this with formulas. But I don't want to have to create a formula for all 30 or more users. I was hoping a could automate most of the work with a Cross Tab or Groupings.......

 

[lbass]

Does each user appear in both analyst and reviewer fields? Or might a user appear in one but not the other?

If the first is the case, then set the completed date equal to the daterange parameter in the record selection formula, insert a group on the analyst field, and then add a subreport where the table is limited again by the review date set equal to the daterange parameter. Then link the subreport to the main report using the parameter field in both (use the dropdown to select {?daterange} for the subreport, not {?pm-?daterange}) and also link the reviewer field from the subreport to the analyst field in the main report, and place the subreport in the group footer, next to the summary for the reviewer.

If a user might not be present in one or the other field, then it would be best if you had another table containing all users, and then you would group on the user field from that table. You would use a left join from that table to the main table, and then use a formula like:

if {table.compldate} in {?daterange} then 1 else 0

Then insert a summary on that. Use a subreport for the second field and link the subreport to the first tables user field and also by {?daterange}.

Please note that your data does not really work as a crosstab since you are not working with two instances of one column field, but instead, two different fields.

-LB

 

[TEM3]

Yes, each user field will be a different user (the analyst who completes the assignment is never the analyst who reviews same). There is an analyst table which I can add in twice (once as an alias) and link the Reviewer and Assigned Analyst to the Analyst field in the Analsyt Name table......

 

[TEM3]

How about this:

I load my main table twice (as an alias). I link it to my Analsyt table ({LABRPT.Analyst Assigned} --> {ANALYST.Analyst} and {LABRPT_1.Reviewed By} --> {ANALYST.Analyst} - not quite sure how I should do the linking and joins??) and try either a Cross Tab or Grouping from there???

 

[lbass]

Do a left join FROM {Analyst.Analyst} to {LABRPT.Analyst Assigned} and a left join FROM {Analyst.Analyst} to {LABRPT_1.Reviewed By}. Use {Analyst.Analyst} as your group field and then follow the rest of my suggestion. This assumes that the Analyst table contains all analyst and reviewer user IDs.

-LB

 

[TEM3]

This assumes that the Analyst table contains all analyst and reviewer user IDs."

Yes it does. Thanks. Will give it a shot.....

 

[TEM3]

I created an alias of LABRPT and Left Joined both LABRPT.Analyst and LABRPT_1.Reviewed By to ANALYST.Analyst. I grouped by ANALYST.Analyst and did a Summary count of LABRPT_1.Reviewed By and LABRPT.Analyst. Got really weird (and high) numbers.

I next tried every linking option I could. Same weird-high numbers. Then I also linked LABRPT TO LABRPT_1 using a Key Field in each (Case Key) and got numbers that looked reasonable. But upon checking, they were wrong too. I ever tried summing a formula (if {LABRPT.Analyst} = {ANALYST.Analyst} then 1 else 0) and got the same numbers.

Still stuck.....

 

[lbass]

The links should be FROM the analyst table to the other tables. You will get duplicate data with this approach, but you can use running totals that evaluate on change of field to get correct counts. If you lay out your data on the report canvas you can observe how it is repeating and then set your evaluation in the running total to accommodate that. Or, even better...

I should have reread my earlier post before responding the last time, because my earlier suggestion prevents duplicate data and is, I think, the best approach in this case. That is, you should use a left join from Analyst to one of the tables, group on {Analyst.Analyst}, and then create the formula:

if {table.compldate} in {?daterange} then 1 else 0

You would then insert a summary on this. This should be accurate.

Then add the other table in a subreport and link the main report {Analyst.Analyst} to the subreport by the reviewer field. Also use the main report {?daterange} as a linking field and use the dropdown list to select the corresponding subreport {?daterange}(NOT {?pm-?daterange}) to link to. Place the subreport in the group footer. Within the subreport, create the corresponding formula using the review date, and then suppress all sections except the footer containing the summary you inserted on that formula. The subreport approach will prevent the duplicate data.

-LB