Something similar to a Manual Cross-Tab but not quite 1

[Melmeg]

I'm trying to generate a report in Crystal Reports 2008 that gives me an overview of Users' results for a Month ignoring the date it was taken. For example the results table looks like this:

User Name Result Date_taken
User1 80 4/2/2011
User1 98 4/3/2011
User1 100 4/3/2011
User2 78 4/3/2011
User2 99 4/5/2011
User2 73 4/5/2011
User2 96 4/7/2011

I do not want to use a cross-tab because it will generate the results based on the dates. This is how I wish to display the results:

User Name Result1 Result2 Result3 Result4 Result5
User1 80 98 100 N/A N/A
User2 78 99 73 96 N/A
User3 100 90 N/A N/A N/A
User4 74 96 91 92 81
User5 89 98 98 96 97
User6 95 85 73 N/A N/A
User7 94 91 100 73 100

You can see for example Result1 for User1 and User2 were not taken on the same date but the result displays on the same column because it is the first result for both. Is it really possible to achieve this? I really appreciate your response.

 

[hilfy]

I don't know if this will work, but it's worth a try:

1. Create a group by User.
2. Create a formula for each result (1 through 5) the first one looks something like this:

StringVar result1;
if PreviousIsNull({table.user}) or {table.user} <> previous({table.user}) then
  result1 := ToText({table.result}, 0);
result1;

The second will look something like this:

StringVar result2;
if PreviousIsNull({table.user}) or {table.user} <> previous({table.user}) then
  result2 := 'N/A'
else if result2 = 'N/A' then
  result2 := ToText({table.result}, 0);
result2;

The others will look something like this:

StringVar result3;
if PreviousIsNull({table.user}) or {table.user} <> previous({table.user}) then
  result3 := 'N/A'
else if {@result2} <> 'N/A' and result3 = 'N/A' then
  result3 := ToText({table.result}, 0);
result3;

3. Suppress the group header and details sections. Put your data in the group footer.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Melmeg]

Thanks so much Dell for your response. I have tried this method and I am getting duplicate records except for the first and last user. Here is an example of the first 3 results

User Name    Result1    Result2    Result3
User1    80    N/A    N/A    
User2    80    99    73   
User3    80    99   73   
User4    80    99    73    
User5    80    99    73   
User6    80    99    73  
User7    94    N/A    N/A   

 

[hilfy]

That's odd. Can you post your formulas for the three results?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Melmeg]

Here it is

//{@UserNameID} - grouping by a combination of User name and User ID

StringVar RESULT1;
if PreviousIsNull({@UserNameID})
or {@UserNameID} <> previous({@UserNameID}) then
RESULT1 := ToText({Level_3.Result}, 2);
RESULT1;

StringVar RESULT2;
if PreviousIsNull({@UserNameID})
or {@UserNameID} <> previous({@UserNameID}) then
RESULT2 := 'N/A'
else if RESULT2 = 'N/A' then
RESULT2 := ToText({Level_3.Result}, 0);
RESULT2;

StringVar RESULT3;
if PreviousIsNull({@UserNameID})
or {@UserNameID} <> previous({@UserNameID}) then
RESULT3 := 'N/A'
else if {@RESULTS2} <> 'N/A' and RESULT3 = 'N/A' then
RESULT3 := ToText({Level_3.Result}, 0);
RESULT3;

 

[Melmeg]

I placed the results in the details and created another set of formulas (Res1, Res2 etc)which I placed in the group footer

//{@Res1}
whileprintingrecords;
stringvar Result1;

This seems to work for the first 3 results but returns the same values after that. I guess I am not getting the formulas from fourth onwards correct.

Result1 Result2 Result3 Result4 Result5 Result6 Result7
User1 93.5 100 100 100 100 100 100
User2 98.5 98.5 72.5 72.5 72.5 72.5 72.5
User3 100 89.5 N/A N/A N/A N/A N/A
User4 95.5 100 88.5 88.5 88.5 88.5 88.5
User5 91.5 88 99 99 99 99 99
User6 70.5 60 42.5 42.5 42.5 42.5 42.5
User7 99 98 98.5 98.5 98.5 98.5 98.5
User8 100.00 N/A N/A N/A N/A N/A N/A

The correct results are supposed to be these

User1 93.50 100.00 100.00 N/A N/A N/A N/A
User2 98.50 98.50 72.50 96.00 N/A N/A N/A
User3 100.00 89.50 N/A N/A N/A N/A N/A
User4 95.50 100.00 88.50 N/A N/A N/A N/A
User5 91.50 88.00 99.00 99.00 N/A N/A N/A
User6 70.50 60.00 42.50 69.00 N/A N/A N/A
User7 99.00 98.00 98.50 N/A N/A N/A N/A
User8 100.00 N/A N/A N/A N/A N/A N/A

Can you please assist me with the formulas beyong 3? I really do appreciate. Thank you.

 

[hilfy]

Your IF statement will change from the fourth onward - in particular, the else line:

3rd:
else if {@RESULTS2} <> 'N/A' and RESULT3 = 'N/A'

4th:
else if {@RESULTS3} <> 'N/A' and RESULT4 = 'N/A'

5th:
else if {@RESULTS4} <> 'N/A' and RESULT5 = 'N/A'

etc.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Melmeg]

That's exactly what I did

StringVar RESULT4;
if PreviousIsNull({@UserNameID}) or {@UserNameID} <> previous({@UserNameID}) then
RESULT4 := 'N/A'
else if {@Res3} <> 'N/A' and RESULT4 = 'N/A' then
RESULT4 := ToText({Level_3.Result}, 0);
RESULT4;

 

[Melmeg]

I'm still unsuccessful in getting this resolved. I also tried the format in this thread

http://www.tek-tips.com/viewthread.cfm?qid=1647107&page=2

and the only problem I have with it is that the 'N/A' does not display and the results are all in one text box thus I cannot format the individual results to allign well with the headers. Any ideas please will be appreciated.

 

[lbass]

First group by user and then create these formulas:

//{@reset} to be placed in the user group header:
whileprintingrecords;
stringvar array x := "";
numbervar i :=0;
numbervar j :=0;
numbervar cols := 0;
numbervar k := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar array x;
numbervar i := i + 1;
numbervar cols := 5; //replace with the desired number of columns
numbervar k;
numbervar j := count({table.results},{table.user});
redim preserve x[j];
if i <= j then
x := totext({table.results},0,"");
if j < cols then (
for k := 1 to cols-j do(
redim preserve x[j+k];
x[j+k] := "N/A"
)
);

Then in the group footer you can use individual formulas like this, one for each array element:

whileprintingrecords;
stringvar array x;
x[3] //for the third element, etc.

-LB

 

[lbass]

PS. You are in the wrong forum. In the future, please post questions like this in forum149 or forum767.

-LB

 

[Melmeg]

Thanks LB. I'm new to this site so still trying to find my way around but will do as you suggest next time.

Thank you for the formulas too. I am working on them and will get back with the results.

 

[Melmeg]

This works perfect!!! Thanks so much LB!
Thanks too Dell!
I really do appreciate both your time. I have learnt a lot from this.