We have several reports that are sectioned my Market. In the query conditions we specify which markets we want included in the report, for example, we may need only New York and Los Angeles, so that’s what the condition states. However, we’re getting three sections: New York, Los Angeles and #EMPTY. The #EMPTY section contains no data, and looks pretty ugly on the report. We’re using BO 5.0.1 against an Access 97 database. Has anybody seen this? Any ideas for a solution?
Michael,
Is the field in the database corresponding to your Market object a null field with some null values in it ?Or does your query contains an outer join with Market dimension ?
You can put a report level filter on #EMPTY value of Market but in that case the data may not roll up to exact summation of your transaction details .
There are no NULL values in the Market field. Every row has a market. And there are no Outer Joins. The condition in the query already states that I only want certain markets, so even if there were NULL values, the query shouldn’t bring them back. When I look at the data cube, it only contains rows for the specified market. So I don’t know what is causing the #EMPTY.
Michael Welter
Sr. Technical Analyst
AirTouch
Is the field in the database corresponding to your Market object a null field with some null values in it ?Or does your query contains an outer join with Market dimension ?
Is there another query in the report (data provider) that is trying unsuccessfully to link on that dimension? I have seen this before, and found a data problem. The linked dimension had right padding of spaces in one table and not in another, and that caused the problem.
Marian Cooney
McKessonHBOC
Malvern, PA
Vikas,
There are no NULL values in the Market field. Every row has a market. And there
are no Outer Joins. The condition in the query already states that I only want
certain markets, so even if there were NULL values, the query shouldn’t bring
them back. When I look at the data cube, it only contains rows for the specified
market. So I don’t know what is causing the #EMPTY.
You may be on to something here. What do you mean by “right padding of spaces?” I can check that if I know what to look for. Thanks.
Michael Welter
Sr. Technical Analyst
AirTouch
Is there another query in the report (data provider) that is trying unsuccessfully to link on that dimension? I have seen this before, and found a data problem. The linked dimension had right padding of spaces in one table and not in another, and that caused the problem.
I am loading data from flat files into SQL Server using BCP. If the table definition is created without ‘set ansi_padding off’, then the columns (although defined as varchar) are padded with spaces to their defined length.
What happened is that one of my tables was created WITHOUT the ‘set ansi_padding off’ and I was using a query with a dimension from that table to link another query from a table that was created WITH ‘set ansi_padding off’, so the dimensions didn’t match.
When I fixed the table, reloaded the data and refreshed the report everything was back to normal. Basically what was happening was that I was seeing all the rows from the first query and all of the rows from the second query.
I looked everywhere in the database table, but could find no such property. This database is an Access 97 database, so it might not be an option there.
Michael Welter
Sr. Technical Analyst
AirTouch
I am loading data from flat files into SQL Server using BCP. If the table definition is created without ‘set ansi_padding off’, then the columns (although defined as varchar) are padded with spaces to their defined length.