Solved

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

Posted on 2004-10-07
23
254 Views
Last Modified: 2006-11-17
Hi Experts,

I am using CR9.  

I have the following data:

Name1   P1   Closed
Name1   P1   Open
Name1   P2   Closed
Name2   P1   Closed
Name3   P3   Open

I need a report with the ff format:

Name1
    P1  
        Closed  1
        Open    1
    P2  
        Closed  1
        Open    0
    P3  
        Closed  0
        Open    0

I also need a bar chart that will show the total number
for each P (from 1 to 3).  Each P will have 2 bars on it,
the Closed and Open items.

I will have to do everything in CR9. I know I can't include
in CR any data that is not on the table but I was hoping
that maybe someone can give me an idea on what "approach" I can do to achieve my desired report format.

I know that this question deserves a thousand points but I only have 230 remaining in my points. =(

Thanks in advance.

ann
0
Comment
Question by:wala_lang
  • 10
  • 5
  • 4
  • +1
23 Comments
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12256040
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.
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12256060
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.
0
 

Author Comment

by:wala_lang
ID: 12256065
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
0
 

Author Comment

by:wala_lang
ID: 12256075
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
0
 

Author Comment

by:wala_lang
ID: 12256082
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
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12256084
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...
0
 

Author Comment

by:wala_lang
ID: 12256094
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
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12256357
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?
0
 

Author Comment

by:wala_lang
ID: 12257219
I am connecting to our Oracle DB thru ODBC.  From there, I will choose the tables that will be used by the report.
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12258201
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Expert Comment

by:frodoman
ID: 12258251
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12258256
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
0
 

Author Comment

by:wala_lang
ID: 12273511
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12273965
Make sure it is a left outer join.

mlmcc
0
 

Author Comment

by:wala_lang
ID: 12274019
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12274222
Can you display the SQL Crystal is using?

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

mlmcc
0
 

Author Comment

by:wala_lang
ID: 12281869
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12281933
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
0
 

Author Comment

by:wala_lang
ID: 12282506
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
0
 

Accepted Solution

by:
wala_lang earned 0 total points
ID: 12283110
Hi Everyone,

I was able to create my report by doing the ff:

1. I created a main report and filter all P1.  I created a grouping based on the names.  I added a running total for Open and Closed items.
2. I created a sub-report and filter all P2.  I added a running total for Open and Closed items. No report grouping here.  The record selection criteria will be based on the name chosen in the main report.
3. I created another sub-report and filter all P3.  I added a running total for Open and Closed items. No report grouping here.  The record selection criteria will be based on the name chosen in the main report.

Then I added a bar chart for each. I put the 2 sub-reports in the main report (footer section) and adjusted it so that the bar chart are shown side by side.

CR can now display the number of Closed and Open calls for ALL P's.  My boss can now do a comparison over all P's in just a single report.

Thanks all for your help guys.  I really appreciate the time you've spent on my problem.  I hope you don't mind if I refund my points.  Its the only remaining points that I have.  I might use them in the future.  I know I will use them because I always look for help here =)

Regards.

ann
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12307524
No objection.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now