Fix the X axis scale in a chart

[rmitchell]

Here is my problem. I have a chart with a measure of 'Duration' by Hour. Not every hour has data but when I chart it I would like the X-axis to have the full range of hours from 0 to 23.

The Y-axis has a 'Scale' option (because it is a measure I suppose) but not the X-axis (because it is a dimension?).

I have tried lots of ways to force the axis scale e.g. Creating a new single-column table with a row for each hour then outer joining this to my data table and using it to populate my 'Hour' dimension. However there are lots of issues like not liking the outer join, or producing cartesian joins or needing to link 2 data providers. Seems like every way I try it ends up in a dead end.

Does anyone have any clever ideas?

Beck

 

[promero]

What I do is to create a data provider with the dimensiones of the X axis, as you sugest. It works for me very well.

This data provider can be free-hand SQL, personal file or VBA provider. Link the dimensions with the data provider with the measures.

Since BO version 5.1.4: If the table or graph has filters, or if the report has filters, you must remake them for this solution to work. See thread393-329182

Personal File
If the list of x values is fixed, the data provider can be an imported personal file. Remember to uncheck the "Can be refreshed".

Free-hand SQL
In the first times, i used free-hand SQL for that type of data providers. I don't need an aux table, because i make the query like this:
[tt]
select 1 from sysibm.sysdummy1
union all select 2 from sysibm.sysdummy1
union all select 3 from sysibm.sysdummy1
...
[/tt]
Note: [tt]sysibm.sysdummy1[/tt] is a dummy table in DB2, that has a single record.

You can use @Promt and @Variable on the query, and you
can use special registers like CURRENT DATE, and
expressions.

VBA Provider
Recently, i started to use VBA data providers. They are
database independent, fast, and can return an arbitrary list. The con is that you cannot use @Prompt or @Variable.

 

[rmitchell]

Thanks promero,

But I'm still confused. I did try the tactic of having 2 data providers thus:

DP1: Date (dimension), Hour (dimension), Duration (measure)
DP2: Hour (dimension)

Where the Hour dimensions in each are linked, and Hour in DP1 only has values where a Duration was recorded, but Hour in DP2 comes from a different source and has all integers between 0 and 23.

My problem is that once the 'Hours' are linked no matter which one I use in my chart as the x axis, it is still limited to only those hours that have Durations recorded (obviously they have to be linked or some kind of cartesion join ensues).
Which means my graphs do not visually show where data is missing, the users have to study them carefully to see which hours are missing (this can also make the width of the graphs change which isn't visually very appealing).

What do I do to make sure the X axis shows all Hour values between 0 and 23 regardless of whether or not any measure was recorded?

Thanks

Beck

 

[PAULSGS]

What you are asking BO to do is to supply an observation where one doesn't exist.

It is not going to this, even with two data providers, they can only link a real record to a real record.

What you need to do is to create the missing observations. You can do this at the database level, but don't worry you won't have to type in 1000's of records, if you can do the following

Create a new table, call it HOURS
This should have one field or column called HOUR of character data type, and this should contain 24 rows each one representing an hour of the day

HOURS table
HOUR column
1
2
3
... etc
... etc
22
23
24

This table should be bought in to your BO universe and the HOURS table should be linked with an outer join to the HOUR column in the table which contains your observations e.g. HOURS.HOUR=OBSERVATIONS.HOUR(+)

The HOURS.HOUR column should be made a dimension object, pick this object, together with your object equivalent to OBSERVATIONS.DURATION from your universe, which should be a measure object. This will force the outer join to link the HOURS and OBSERVATIONS tables

What the outer join (+) does is supply a blank record on the side it is placed if there isn't one to match that which comes from the other table in the join. And this is exactly what you need for your chart.

The other thing to check for, is that the length of the X axis is sufficient to allow all points to display, if it is too short, only certain points will show.

Good luck

 

[rmitchell]

Thanks Paul,

But I have tried that too.

I am using SQL Server 2000. To include all values in HOURS even when there is none in OBSERVATIONS means I need an outer join on the HOURS side.

This might work if my schema only had those tables, but unfortunately (as in all things) it is more complicated than that. OBSERVATIONS is one table in several that I will use to return my results. As it is therefore involved in other joins SQL Server will not let me have the outer join. I get the following:

'The table OBSERVATIONS is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.'

I can't propogate the outer joins to the rest of the schema, so I think I am still stuck!

Any other ideas?

Beck

 

[PAULSGS]

I am convinced that the solution to this problem requires the generation of the missing observations.

Presumably you have tried various combinations of aliases to get around your outer-join v. regular-join conflict?

One other idea which you might get to work is to UNION your real results with a dummy set of missing ones. The following SQL does this


select hour,observation from observations
union
select hour,0 from hours
where hour not in (select hour from observations)

I appreciate your select statement is going to be more complicated than this, but the first part (above the UNION) will have been generated by BO. What you have to do is come up with a way of providing a matching bottom half. The easiest way, and perhaps the way to test this is to modify the query with freehand sql. The UNIONed statements must have the same number of columns, and the columns must be of the same type when considered as stacked items.

If you can get this to work through freehand, is that enough, or do your end users modify the queries? If they do is it practical to modify your dummy table to reflect the structure of the first part of the UNIONed query so that you could build objects against it?

 

[promero]

> I did try the tactic of having 2 data providers thus:
>
> DP1: Date (dimension), Hour (dimension), Duration (measure)
> DP2: Hour (dimension)
>
> My problem is that once the 'Hours' are linked no matter
> which one I use in my chart as the x axis, it is still
> limited to only those hours that have Durations recorded.

I can't understand. It works for me.

What variables are you using on the graph? Only <Hour(DP2)> and <Duration(DP1)>?

Do you have some filter over a variable not from DP2? That filter could cause your problem if you are using BO 5.1.4.

 

[rmitchell]

Hmm,

I think I'm going to have to work on this some more. Thanks guys for the suggestions, unfortunately they don't work for me so far!

If I find an answer I'll post it here.

Beck

&quot;You can do foolish things, but do them with enthusiasm&quot;