Displaying Bar in Graph for data that doesn't exist. 1

[Halfcan]

Hi, this is hard.
I'm using v.8.5

I have a report that breaks a Quarter down into 3 months.
month 1 dates are 01/01/03 to 03/31/03
month 2 dates are 04/01/03 to 06/30/03
month 3 dates are 07/01/03 to 9/30/03

In a perfect world, Each of my records has a month(DATE) that will fall into 1 of these time periods.
I want to create a graph that will show 3 bars, one for each month.

My problem is that I need to have at least 1 record that falls within each time period for the graph to display all 3 bars.

If only 2 months have records, then the 3rd bar for the month without records is not displayed.

Basically I need a way to check the data for records that don't exist.

Any Ideas?
Thanks,
HC

 

[nagornyi]

If I understand correctly, the auxilliary base table with all codes is UWMF_PP_PROC_CODE. If so, where is the outer join in the line

("UWMF_PP_PROC_CODE"."PROC_CODE"="UWMF_VIEW_CODING_PROF"."PROC_CODE&quot ?

Should be

("UWMF_PP_PROC_CODE"."PROC_CODE"="UWMF_VIEW_CODING_PROF"."PROC_CODE"(+))

also

"UWMF_VIEW_CODING_PROF"."BILLING_PROVIDER_ID"(+)='100038' AND "UWMF_CUSTOM_RVU"."YEAR"(+)=2003

The best tool to play with this is Crystal SQL Designer, which is in the Crystal Reports Tools program group.

 

[jenschmidt]

Actually, the base table initially was UWMF_VIEW_CODING_PROF. The UWMF_PP_PROC_CODE is the extra table I built yesterday with just one column of the specific codes for the graph. Is there a way to get to the Crystal SQL Designer through C drive - it's not listed under Start/Programs. Otherwise, I can create a view using the SQL statement I pasted in, make the changes you suggested and run it.

Thanks!
Jen

jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

On another post (thread 767-736018), I noticed someone said you can make changes to a database using Crystal 9.0 - I hadn't heard that and I'm not sure the extent to which changes can be made. Do you know much about it? Could it help me here?

Thanks again!
Jen

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

In CR9 query designer is brought inside the report designer. Anyway. Whichever base table you are using, it should be outer joined to the report table (or view). Additionally, if you are restricting other fields of the report table by constants, they also should be outer joined.
CR is not a good tool to write anything to the DB (unlike Oracle Reports), I don't thing it is the right way to go. Your problem CAN be solved with outer joins. Just simplify your sql step by step to leave only significant part that should return resultset with ALL codes.

 

[jenschmidt]

I think the biggest problem in this situation is my brain!! I'm just not getting how outer joins can add records to the database that aren't there. If a doctor hasn't billed one of the five codes, it's not in the data no matter how you join it. Maybe if I could get my brain in gear, I could take a better stab at fixing this!

Anyway, thanks again for all your help!
Jen

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

Those codes should be in the database, that is in the base table keeping all codes.
Let's consider an example.

Table1:
CODE BILLED
==== ======
002 223.94
003 121.11
005 443.23

Now if you do
select Table1.CODE, Table2.BILLED from Table1
you will get the results:

002 223.94
003 121.11
005 443.23

However, you know that there are also codes 001 and 004 which have not been selected because there were no services provided with these codes, so the codes do not occur in Table1. But you would like to see this fact, that there are also codes 001, 004, but nothing has been billed for these codes. That is your problem, right.

Now you create base table keepeng all codes (or use some other table that for sure has all codes). Let it be

Table2
CODE
====
001
002
003
004
005

Now, if you directly join those tables, you will not get the results you need:

select Table2.CODE, Table1.BILLED from Table1, Table2 where Table1.CODE = Table2.CODE

002 223.94
003 121.11
005 443.23

However, if you OUTER JOIN these tables ("plus" sign added to Table1.CODE)

select Table2.CODE, Table1.BILLED from Table1, Table2 where Table1.CODE(+)=Table2.CODE


you will get

001 NULL
002 223.94
003 121.11
004 NULL
005 443.23

That's how outer join is working. Try it on some simple example.

 

[jenschmidt]

Thank you so much for the example - now it makes perfect sense! No matter what I do though, I can't get it to do that on the report or even just a simpler example report. I'll keep trying because it seems like it should work.

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

As I mentioned before, if you are in any way restricting values in Table1, that should also be done with outer join.
Let's say you want to see only services billed 121.11 .
If you use

select Table2.CODE, Table1.BILLED from Table1, Table2 where Table1.CODE(+)=Table2.CODE and Table1.BILLED = 121.11

you will get

003 121.11

Note: the outer join is there!

To get all codes, you have to put one more outer join like:

select Table2.CODE, Table1.BILLED from Table1, Table2 where Table1.CODE(+)=Table2.CODE and Table1.BILLED(+) = 121.11

Then you will see

001 NULL
002 NULL
003 121.11
004 NULL
005 NULL

That's exactly the case in your situation. You have many additional restrictions. Try building your SQL gradually, adding tables and restriction step by step, to see at which step codes disappear.