how to display data that is not on the table/database

For you initial question...

In your report, you'll insert two groups, The first will be a group on the "Name1" field, the second will be on the "P" field.  The "Closed" and "Open" field can then be placed in the detail section...  This should give you the formatting your looking for in the report.

I haven't used the Chart Control in CR, I'll look into it...

Let me know if you need a more in depth answer on creating groups in CR.

Oh yeah, if your "Closed" and "Open" values are coming from the same columns, you may want to insert a group for these as well.  This would be your third group.  You can then put the values for "Open" and "Closed" in that groups header.  In this case, you would just surpress your details line.

Hi RaisinJ,

Thanks for your quick reply on my problem.  

I already did the grouping but the problem is CR will only set a group if there's a matching record in the database.

So in my sample data, if I create a second grouping based on the P field, it will only give me a sub-group for P1.  I need CR to create a sub-grouping also for P2 and P3 even though it does not have any matching records in the database.  My boss wants to see a comparison of the 3 P's per names.

I think if this thing will be solve first, then I would be able to create the bar chart without any problem.  I just need to lay out my groupings and details first.

ann

Hi Experts,

I don't really need to have the exact report format that I posted in my original question.

But I really want to point out that I need to have the 3 P's displayed per name.  

Re the Closed and Open values, they refer to the same field in the database.  I used a running total field and specified a condition to get the values for each.

It's in the displaying of the 3 P's that's I'm having trouble with.

Thanks.

ann

Sorry, I mistyped something.  In my sample data, CR will only give my a sub-grouping for P1 and P2 under name1.  I need CR to give me a sub-grouping for P1, P2 and P3 under name1.

ann

So... we need to create a formula to display all three P groups even if they not there.  Then we can group on the Forumla instead of the "P" field itself.  I'll see what I can come up with and get back to you...

Hi Raisin,

Yes, that's exactly what I'm doing right now.  

But so far, all the logic that I have put in my formula is not producing what I want.

Thanks =)

ann

How are you pulling the data for the report?  Are you simply connecting to you DB and putting the tables in your report or, are you using a procedure to get your data?

I am connecting to our Oracle DB thru ODBC.  From there, I will choose the tables that will be used by the report.

What version of Oracle are your running, eg 9i?  If you can, you could create a view which would pull in all the information you need with P1, P2, and P3 for each "Name" whether it's null or not.  I can help you but, I need to know what version of Oracle your using as in new versions, they JOIN syntax on tables is different from the version of Oracle I use.  The JOIN syntax is what will support getting the P1, P2, and P3 information on records for which the actual values do not exist in the database.

You could then just point your report at the view.

Ann,

Just a thought - you could very easily generate the data you want with a crosstab report.  It would look like this instead of what you had laid out:

              Closed     Open
    P1           1           1
    P2           1           0
    P3           0           0

If that layout doesn't work for you then disregard this message.  I'm just tossing it out as an option because you could do this w/out any db changes.

frodoman

Unless you wish to create a report that is very specific to a certain set of data (ie P1, P2, P3) will never change and new values will never be added, you won't be able to do this in Crystal alone.

Can the database be changed to include a table with a single cloumn with P1, P2, P3 as the values.  You could then use this table as the first table and left join it to the existing query.  You will then have all the groups.

mlmcc

Hi Everyone,

mlmcc, I have a separate table that contains the P1,P2 and P3 column.  as per your suggestion, I'm currently trying right now to use this as the first table and link it to the table where the number of calls are kept.  I set the relationship as left join.

RaisinJ, we're using Oracle 9i, but I can only do everything in CR.  The BA in charge of the Oracle server are not so keen in adding anything at the moment.  But I would be very interested in your SQL statement.  But I could use it and play around with it.  Can you please post it here?

ann

Make sure it is a left outer join.

mlmcc

Yup, I did a left-outer join.  But still, CR does not display the P if it does not have any corresponding record in the right table.

Can you display the SQL Crystal is using?

I believe it is under  DATABASE --> SHOW SQL QUERY

mlmcc

Here it is:
 SELECT "SU_CALL_PRIORITY"."NAME", "CL_CALL_LOGGING"."CALL_NUMBER", "CL_CALL_LOGGING"."RESOLVED", "CL_CALL_LOGGING"."SUSPENDED", "REPV_HELP_CENTRE"."FULL_NAME", "CL_CALL_LOGGING"."TIMEDATE_CALL_LOG" FROM   "INFRA"."CL_CALL_LOGGING" "CL_CALL_LOGGING", "INFRA"."SU_CALL_PRIORITY" "SU_CALL_PRIORITY", "INFRA"."REPV_HELP_CENTRE" "REPV_HELP_CENTRE"
 WHERE  ("CL_CALL_LOGGING"."CALL_PRIORITY"="SU_CALL_PRIORITY"."REF" (+)) AND ("CL_CALL_LOGGING"."OFFICER_REF"="REPV_HELP_CENTRE"."REF") AND ("CL_CALL_LOGGING"."TIMEDATE_CALL_LOG">={ts '2004-09-01 15:27:07'} AND "CL_CALL_LOGGING"."TIMEDATE_CALL_LOG"<{ts '2004-10-08 15:27:08'})
 ORDER BY "REPV_HELP_CENTRE"."FULL_NAME"

I am using 3 tables.  
1. SU_CALL_PRIORITY has all the list of P's that I need to print.
2. CL_CALL_LOGGING records all the calls
3. REPV_HELP_CENTRE has all the full names

I need to group the report per names then view ALL the P's per names.

I set group 1 to names and group 2 to P.

ann

I see the problem.  The "missing" records are being deleted by the rest of the where clause.  Try something like this

WHERE  ("CL_CALL_LOGGING"."CALL_PRIORITY"="SU_CALL_PRIORITY"."REF" (+)) AND (("CL_CALL_LOGGING"."OFFICER_REF"="REPV_HELP_CENTRE"."REF") AND ("CL_CALL_LOGGING"."TIMEDATE_CALL_LOG">={ts '2004-09-01 15:27:07'} AND "CL_CALL_LOGGING"."TIMEDATE_CALL_LOG"<{ts '2004-10-08 15:27:08'}) OR
("CL_CALL_LOGGING"."TIMEDATE_CALL_LOG" IS NULL))


You need to include the records that have NULL for the CL_CALL_LOGGING table.

mlmcc

hi mlmcc,

I included the condition:

OR isnull({CL_CALL_LOGGING.TIMEDATE_CALL_LOG})

to my record selection criteria and I'm still getting the same result.


ann

No objection.