Solved

Line chart based on sparse matrix

Posted on 2009-04-02
8
511 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you have to generate for a full year?

Can you generate for each month in turn?

mlmcc
0
 

Author Comment

by:guardus2007
Comment Utility
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
Comment Utility
I am not sure you can change tha with a parameter.

mlmcc
0
 

Accepted Solution

by:
guardus2007 earned 0 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

11 Experts available now in Live!

Get 1:1 Help Now