Forcing a row for an entry with no data

[thedon_1]

I have a list which is filtered for one year and is based on period, product and value.

Period     Product    Value
1            A            100
4            A            200

I have filtered on the whole year in the query so I want lines for all the other periods too, even though I know the value would be blank.

I know the database doesn't have these entries, therefore they arn't null values, they're things that don't exist.

Is there anyway to make this happened?

[Lynn]

You can try an outer join.

Create a query that returns all the periods you want and then outer join it to your fact query. Set the value expression to coalesce to zero.

This will be easiest if you have a date dimension from which you can get all periods.

Union is another option. Create a query that returns all periods for the selected product with 0 for value. Then union that with your existing query, setting the unioned value to sum.

Both will involve local processing and may not be terribly efficient depending on your data volumes.

[thedon_1]

Thanks for the reply.

Unforunately, neither method worked.

[Grim]

I have a list which is filtered for one year and is based on period, product and value.

Period     Product    Value
1            A            100
4            A            200

I have filtered on the whole year in the query so I want lines for all the other periods too, even though I know the value would be blank.

I know the database doesn't have these entries, therefore they arn't null values, they're things that don't exist.

Is there anyway to make this happened?

You can't pull records in if they don't exist. There's no magic wand function to pull records that don't exist in the DB.

If you want to pull in empty rows then ensure those period rows exist in the DB but contain nulls or zeros.

[Lynn]

Thanks for the reply.

Unforunately, neither method worked.

As Grim points out, you can retrieve data that doesn't exist. The only thing you can do is to retrieve a query that has a row for every product and period you want and then outer join that or union it to the query you already have. Essentially you are creating a "dummy" zero record for every possibility and then combining it with the actual fact data.

Usually in a data warehouse environment creating fact records for non-events isn't feasible, but you can query dimensions to achieve what you describe.

It is difficult to provide more detail based on your reply. When you say neither method worked it isn't clear to me what the problem was.

[blom0344]

In SQL terms one would add the following as second set to a union:

select a.Period,b.Product,0
from
Period_table a
cross join
Product_table b

Generating all possible combinations of Period and Product , but with value zero to act as dummy fact

note: assumption here that both Period and Product are related to primary keys in there respective tables (otherwise the entire set could too large..)