cancel
Showing results for 
Search instead for 
Did you mean: 

Show Rows with Empty Measures not Working

former_member844736
Discoverer
0 Kudos

I am very new and probably don't know all the right words/terms for the objects in my database.

I am using Web Intelligence to pull information from an existing Universe (I did not setup the Universe and do not know how all the data objects are linked together).

I have setup a query to get information over certain time periods using a dimension called YearMonth that is in Number format. There are several different Data Sources, but all have some type of field that is YearMonth in Number format. I want to merge the tables like below, but there are rows missing.

ere is what I have tried so far:

1) Checked to make sure the Format Table, Display properties are correct. The following are all checked: Show rows with all empty measure values, Show rows for which all measure values = 0, Show rows for which the sum of measure values = 0. Also, Avoid duplicate row aggregation is unchecked.

2) Tried checked the "Show rows with empty dimension values" option in Format Table, Display section. This did not help.

3) Checked YearMonth from data source 1, YearMonth from data source 2, and Merged YearMonth as stand alone columns (not linked via a table). Confirmed all 3 are missing the date rows that aren't shown in the merged table.

4) Checked the "Show rows with empty dimension values" option for all three of the individual YearMonth columns, that did not help either.

5) Tried to convert the number to a date using:

Converts a 6-digit to an 8-digit number: 8DigitNumber = FormatNumber([DataSource1].[YearMonth];"######11")

  • 201501 became 20150111 (could not figure out how to add 01 to the end, but it doesn't really matter)

Converts 8-digit number to a date: DateFormatted = ToDate([8DigitDateNumber];"yyyyMMdd")

  • 20150111 in Number format, became 01/11/15 (no idea why it ignored my yyyyMMdd listed)
  • Adding DateFormatted to the table did not get my missing rows, either in combination with MergedYearMonth or by itself.

Created 8-digit function for time period dimension date: TimeDimension = TimeDim([DateFormatted];MonthPeriod)

  • Confirmed its type is "date time" and not Number
  • Adding it as a column to my Merged table with YearMonth does not add my missing rows
  • Adding it as a column to my Merged table with Merged YearMonth does not add my missing rows
  • Adding it as a column to my Merged table and removing YearMonth and Merged YearMonth gets met a Multivalue error
  • Making it its own standalone column also gets a Multivalue column
  • Checking the "Show rows with empty dimension values" option in any of these iterations did not help

I'm so new, I don't even really know what software I'm using, the "About" page says:

    moshenaveh
    Community Manager
    Community Manager
    0 Kudos

    Hello yddeyma ,

    Welcome to the SAP Community! Thank you for visiting us to get answers to your questions.

    It seems your question was cut-off at end. Can you please complete the missing information?

    Also, since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

    First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members. Secondly, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will help you when submitting questions to the community.

    Finally, I recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

    I hope you find this advice useful, and we're happy to have you as part of SAP Community!

    Accepted Solutions (0)

    Answers (4)

    Answers (4)

    amitrathi239
    Active Contributor
    0 Kudos

    i hope you have merged the YearMonth objects from all three queries..and rest of dimensions you need to use as merged from two queries or if standalone then use as detail variable..

    If you are missing dimension values then more likely it's join issue.It's look like there is inner joins set between dimension and Fact tables.Ideally you should have all dimensions from dimension tables and only measures from Fact table.The join between Dimension and Fact always be Left Outer.In this case you should not miss values even though you have not any measure values.

    ayman_salem
    Active Contributor
    0 Kudos

    You can also create the time dimension from the menu

    ayman_salem
    Active Contributor
    0 Kudos

    You can use TimeDim to generate missing data. (for more information, see Help)

    Define the following variable:

    v_Date: =TimeDim([YearMonth]; MonthPeriod )

    ( [YearMonth] is the merged dimension )

    ....

    hope it helps to solve the problem.

    amitrathi239
    Active Contributor
    0 Kudos

    You need to add one more webi query for Year Month object (without any measure) and similar prompt condition to query1 or 2.The query3 will bring all continues YearMonth values.After that merge the newly added query YearMonth object to the merged dimension object.

    The reason you are not getting these two missing objects as there is no values in the query1 or 2.

    former_member844736
    Discoverer
    0 Kudos

    I did a stand alone webi query, and it returns all dates.

    If I add my measures to the table, it still returns all dates. Date column, measure column are the only things in the table.

    If I add any other dimensions from the data source to the table, it goes back to skipping dates.

    Do I have to do stand alone queries for each dimension? That seems really inefficient.