Solved

Line chart based on sparse matrix

Posted on 2009-04-02
8
520 Views
Last Modified: 2013-11-15
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
Comment
Question by:guardus2007
  • 3
  • 3
8 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 24051361
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24052595
Crystal cannot manufacture data so you will need to make sure there is a date field for each date you want.

mlmcc
0
 

Author Comment

by:guardus2007
ID: 24057265
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 100

Expert Comment

by:mlmcc
ID: 24062765
Do you have to generate for a full year?

Can you generate for each month in turn?

mlmcc
0
 

Author Comment

by:guardus2007
ID: 24075004
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24083953
I am not sure you can change tha with a parameter.

mlmcc
0
 

Accepted Solution

by:
guardus2007 earned 0 total points
ID: 24084430
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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