Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Line chart based on sparse matrix

I have a database table with the approximately the following fields:
bar code, quantity, date (neither of which are unique).

I need to create a line chart which reflects the table (x axis is time, y axis quantity, one line per bar code), but as a 'sparse matrix':
that is, for each day in an interval (start date and end date can be considered report parameters), i show a data point for each bar code.

If an record with the bar code exists for a particular day, it is printed with the known quantity value (this part is working).
If there is no record for a bar code, date pair, I need to print a 0 quantity data point in the chart (this part is
not working - no data point is printed and more importantly, the on the x axis the days with no data are nor marked).

How can I accomplish this?

The table is of course a dense matrix with many bar code, date pairs missing.
I can't modify the table and insert records like (bar code, 0, date) for all the missing data points ( the table size will grow too large).
0
guardus2007
Asked:
guardus2007
  • 3
  • 3
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
Create a master DATE table that contains ALL calendar dates..and left join your other tables to it, so that if there is no activity for that day,...it will still have the DATE place holder...and a ZERO or NULL will appear.
0
 
mlmccCommented:
Crystal cannot manufacture data so you will need to make sure there is a date field for each date you want.

mlmcc
0
 
guardus2007Author Commented:
Thank you for your input, but I should have mentioned I already did try using a table with all dates, and the left join operation is too expensive (i have in the extreme case to join thousands of bar codes and thousands of days - the selection could span years) regarding disk space.

I have run into the tablespace limit for the database and I have no permission to alter it.
When Crystal generates a chart with all the bar codes over a span of a year the report fails because Oracle can't extend the tablespace.

What I am looking for is some sort of space/time trade off. The report generation time could be extended, but I need to avoid generating so much data that it overflows up the tablespace.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mlmccCommented:
Do you have to generate for a full year?

Can you generate for each month in turn?

mlmcc
0
 
guardus2007Author Commented:
The report must have a parameter which selects the unit for the time axis: days, weeks, months, years.
So the selection interval can span multiple years (and the data accumulation is done in the chart for time units greater than days).
So I need to display on the same chart data for intervals greater than a month.
0
 
mlmccCommented:
I am not sure you can change tha with a parameter.

mlmcc
0
 
guardus2007Author Commented:
If I build 4 charts (one for each type of time axis:days...years) and suppress the ones i don't need it works.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now