Help with Rows with no value

[HEPennyPacker]

Help with Non-Data Rows

Okay, so here is what I have:

Data item expression - [Fiscal Year]
Data item expression - [Fund Level]
Data item expression - [Expense Amount]

Filtering by Fiscal Year

When I view as a tabular row without expense Amount listed in the query I get all the funds for that fiscal year including funds numbers 2005 and 2006.

However, when I add in the [Expense Amount] data item and view as tabular row again 2005 and 2006 are missing.

They had no expenses in that fiscal year. Not null, but nothing so there is no row at all when I include [Expense Amount], again, if it's just Fund Level and Fiscal Year, I see them.

How can I edit [Expense Amount] data item expression or create a query calculation that says, "Create a Row for ALL Funds and add in the Expense Amount. However, if there were no Expenses for a Fund just put 0.00 but make sure there is a row for every fund."

[HEPennyPacker]

Figured it out, had to outer join with a common field in a separate table, then make the null rows have 0.00 and voila!

[Cheeks100]

Coalesce function would have worked to. Turns null in to 0

[dougp]

coalesce() would not have worked. 

It would help if the question were clearer.  The data item expressions are more likely:

Fiscal Year = [namespace].[Fund].[Fund Fiscal Year]
Fund Level = [namespace].[Fund].[Fund Level]
Expense Amount = [namespace].[Expenditure Fact].[Expenditure Amount]

When getting the data from only the Fund table, the dimensional values appear.

When including a join to the fact table in a well designed data mart, where there are no measures associated with the Fund records for [Fund Level] in (2005, 2006), no results would be returned.

To bypass the problem, the OP had to create 3 queries...

Fund
Expense
Output = [Fund] LEFT OUTER JOIN [Expense] on [Expense].[FundID] = [Fund].[FundID]

...bypassing the intent of the data mart design.  (Except that if I had designed the FM model, FundID would not be exposed to the user.)

You may think this is a bit of extra work for the report developer.  I would argue that, based on the extensive knowledge shared by the highly intelligent, business-side subject matter experts during the design of the data mart, that the report developer has gone off the rails.

If this is a legitimate question the business would ask of the business, the data mart design requirements were incomplete.  It is possible for the ETL to deliberately add a fact record (measure = 0, perhaps?) for a valid combination of dimensional values in the event no measure exists in the source data.  In the absence of this explicit requirement, if no data exists the data modelers and ETL developers would have no reason to put some random value into the data mart.

Missing does not necessarily equal zero.  There are many things it could mean, or it could be meaningless.  Those decisions must be made during the data mart design process.