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]

This issue falls into a general category of reporting on non-existing data which is pretty often discussed here, and there are FAQs also (ex. faq767-4532). The general solution is to outer join the table you are reporting from with some base table that has dates for all days in the reporting range. In your case the situation is easier because it is enough to have just one record per month. You can use UNION query to add one dummy record per each month. Example (for ORACLE):

(select datefield, datafield from table)
union
(select '01-JAN-2003', 0 from dual)
union
(select '01-FEB-2003', 0 from dual)
.....
union
(select '01-DEC-2003', 0 from dual)

 

[Halfcan]

Thanks nagornyi,
I've just recently set up mySQL and am trying to set the join up correctly, but am having problems. Please check out my other posts thread766-734587 and thread766-734845 .

Then, I realized that I do have the numbers calculated even for months without records, and thought of another possible way to do it, so I also just posted again thread767-734867

Same problem, I just simplified the first post by asking 3 instead of 6.

I'm very new to SQL, and am wondering what the "0 from dual"
means....

Thanks,
HC

 

[nagornyi]

Dual table is an Oracle thing that stands for some dummy table with one row that you can use when you have to use "select". Read more at

http://oldlook.experts-exchange.com/Databases/Oracle/Q_20702284.html

http://www.optimaldba.com/internals/oraint_dual.html

I am not sure how this is done is mySQL, but definitely you can create dual yourself, or you can even create entire table with rows
'01-JAN-2003'
....
'01-DEC-2003'
just for the purpose of having some base table with all months to outer join to it your reporting table.
Also, you can use some existing table and do something like this:

(select datefield, datafield from table)
union
(select distinct (select '01-JAN-2003', 0 from someexistingtable))
union
(select distinct (select '01-FEB-2003', 0 from someexistingtable))
.....
union
(select distinct (select '01-DEC-2003', 0 from someexistingtable))

 

[jenschmidt]

I am having the same issue - needing to graph data that really isn't there! My report has many graphs - the graph in question looks at 5 codes and graphs usage and a benchmark, but the report as a whole is pulling 100's of codes. Out of the 5 codes this graph is looking at, only three were used, but the end user wants to see at least a place holder on the group axis for those two codes. All 5 codes always have a benchmark.

I primarily use Crystal only (with Oracle databases), but don't go too much into creating tables etc - the closest I get is creating some very basic views using Crystal's "Show SQL Query" functionality.

Can I create a table in Access with all the codes I need, push it through Oracle, and then link it in to the report instead of going through the programming Nagornyi showed in FAQ767-4532? If that works, what should the table look like as far as populated data and columns needed?

Thanks so much!
Jennifer

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

You can create the base table in Access and in Excel, and you don't need to go through Oracle, you can just use the table in CR as differemt datasource.
The table should have a single column of date type and rows for each day (or week, or months - depending on what you need.) If you need to create the date range for long time period (like each day for entire year), it is espessially convenient to do it in Excel -- you put the first of January into A1, add 1 to the A1 in A2, and then copy A2 to the entire range A3.....A365 or even A10365.

 

[jenschmidt]

Thank you so much! Currently, the report has one main table left-outer-joined to several other secondary tables. Should I join this new table in front of the main table or like all the other secondary tables are already joined?

Thanks again!
Jennifer

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

This table should be outer yet to the left of the main, that is in front of it.

 

[jenschmidt]

Hello again! I created a one column table which is populated only with the 15 codes that I always want shown on one of the graphs regardless of whether is has actually been used or not. Initially, I thought, since I had a left outer join from the new table to the main table in the report, it would pare down the entire report to just those 15 codes as oppose to just paring down the one graph (the other graphs use 100's of codes to compile the data). It actually didn't change any of the data - the graph in question and all the other graphs. Any ideas on what I could be doing wrong?

Thanks much!
Jennifer

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

For the graph you should pick codes from your new table -- the old table is missing some codes, right?. Also, make sore that the report SQL returns the correct dataset with all codes present.

 

[jenschmidt]

I put the field from the new table in the details section of the report and now the data did change - it is only displaying the codes that are in the new table for the entire report. It makes sense that it's doing that because of the left outer join. But, it didn't impact the graph in question appropriately. Any other ideas that would only impact the graph in question as opposed to the whole report?

Thank you so much again for your help! I really appreciate it!!
Jennifer

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

If for certain graph you need something different then for the rest of the report, you may cosider putting the grapg to a subreport, with its own data model and SQL. If the graph is not displaying what you expect to come from SQL, first make sure the SQL is working correctly by, for example, running your report query in Crystal Query Designer.

 

[jenschmidt]

Thanks so much for bearing with me! I'm a little dense sometimes!!

I created a new report with just the graph in question from the other report in order to use it as a subreport. I still can't get the unused codes to show up on the graph, even with the one column table I created to l-o join to the main table. I've used the field from that new table in the report and the graph - but still no success. I'm sure I'm doing something else wrong!

Thank for any help or advice!
Jennifer

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

To see what is wrong please create a plain simple report without graph, your data placed into detail section. Do you see all the codes?

 

[jenschmidt]

Yep - I see all the codes I need for this graph (about 15).

jennifer.giemza@uwmf.wisc.edu

 

[jenschmidt]

Sorry - I take that last comment back. I can only see the codes that are actually being used - the unused codes are not listed in the details section.


jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

So the problem in the SQL, not the graph. For some reason outer join is not working. Try to play with the join. Try to take out unsignificant fields. If the barebone SQL is still not working, please include it in your message.

 

[jenschmidt]

I think I'm going to move this into Oracle and see if it can insert new records into the database since I don't think I can do that through Crystal. It seems like that's effectually what I'd have to do. I tried playing with the joins, the other fields in the report and the formula that the graph is based on to no avail.

THanks so much for all your suggestions and help! I really appreciate it!
Jen

jennifer.giemza@uwmf.wisc.edu

 

[nagornyi]

Up to you, but looks like you are turning back one step from the finish... What is your SQL statement?

 

[jenschmidt]

You're right - I guess I'm just getting frustrated with this dumb thing! Thanks again for your help!!

When I go into "Show SQL Query", here's what it shows:
SELECT
"UWMF_VIEW_CODING_PROF"."BILLING_PROVIDER_ID",
"UWMF_CUSTOM_RVU"."CODE_SUFFIX_AND_MOD",
"UWMF_CUSTOM_RVU"."YEAR",
"UWMF_VIEW_CODING_PROF"."POST_DATE",
"UWMF_PP_PROC_CODE"."PROC_CODE",
"UWMF_VIEW_CODING_PROF"."INT_PAT_ID",
"UWMF_VIEW_CODING_PROF"."BILL_PROV_NAME",
"UWMF_VIEW_CODING_PROF"."ORIG_SERVICE_DATE",
"UWMF_CODING_BM"."BENCHMARK"
FROM
"UWMF_PP_PROC_CODE",
"UWMF_VIEW_CODING_PROF",
"UWMF_CUSTOM_RVU",
"UWMF_CODING_BM"
WHERE
("UWMF_PP_PROC_CODE"."PROC_CODE"="UWMF_VIEW_CODING_PROF"."PROC_CODE&quot AND
(&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;CPC_MOD&quot;=&quot;UWMF_CUSTOM_RVU&quot;.&quot;CODE_SUFFIX_AND_MOD&quot; (+)) AND ((&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;PROC_ID&quot;=&quot;UWMF_CODING_BM&quot;.&quot;CODE_ID&quot; (+)) AND (&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;RPT_GRP_SEVEN_ID&quot;=&quot;UWMF_CODING_BM&quot;.&quot;RPT_GRP_7&quot; (+))) AND (&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;POST_DATE&quot;>={ts '2003-07-01 00:00:00'} AND &quot;UWMF_VIEW_CODING_PROF&quot;.&quot;POST_DATE&quot;<{ts '2003-12-01 00:00:00'}) AND &quot;UWMF_VIEW_CODING_PROF&quot;.&quot;BILLING_PROVIDER_ID&quot;='100038' AND &quot;UWMF_CUSTOM_RVU&quot;.&quot;YEAR&quot;=2003
ORDER BY
&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;BILLING_PROVIDER_ID&quot;,
&quot;UWMF_VIEW_CODING_PROF&quot;.&quot;INT_PAT_ID&quot;

The graph is in the header of Group #1 Billing Provider ID.

Thanks again!
Jen

jennifer.giemza@uwmf.wisc.edu