Improve company productivity with a Business Account.Sign Up

x
?
Solved

Line chart based on sparse matrix

Posted on 2009-04-02
8
Medium Priority
?
535 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
7 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 101

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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 101

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 101

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
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 …
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

602 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