Solved

Line chart based on sparse matrix

Posted on 2009-04-02
8
516 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

24 Experts available now in Live!

Get 1:1 Help Now