This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 10, 2010 2:12 PM by 762091 RSS

Section Values showing NULL in Pivot

762091 Newbie
Currently Being Moderated
Hi, please go through the following requirement and drop your suggestions.

I have a pivot table with one column1 in Sections with three entities and one column2 with subtotals. Also I have column3 as filter prompt selections for the report.

If any value is selected from filter prompt for column 3 and if that selection populates only column1.entity1, I need to show in reports like column1.entity=0, column1.entity3=0.

How could I achieve this...plz explain
  • 1. Re: Section Values showing NULL in Pivot
    bifacts Pro
    Currently Being Moderated
    if i understand it right, based on your prompt selection you will need to show the non-selected values as 0's in pivot sections instead of not displaying them at all. If not, can you please elaborate your issue. and also what is the prompt type (drop down or multi-select)?

    -bifacts
    http://www.obinotes.com
  • 2. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    Prompt selection is drop down menu. Based on the selection from the prompt report is getting populated but I have a column in pivot table which has 3 entities which must and should be in the report, if null they should be showing zero. Based on the prompt selection data is getting filtered but if I have to mention other entities in the report. Following is example, after selecting product_name from prompt

    http://i51.tinypic.com/6tcmkm.jpg

    I need 2.00=Null in report

    what can I do.....Do I need to give a case statement in column formula or something like that..plz suggest

    Edited by: Blue86 on Oct 7, 2010 9:49 PM

    Edited by: Blue86 on Oct 7, 2010 9:55 PM
  • 3. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    Any help on this issue....
  • 4. Re: Section Values showing NULL in Pivot
    bifacts Pro
    Currently Being Moderated
    Blue86,

    This is just a workaround you can play with.
    imagine you have the following report:
    report1:
    col1,col2,fact. in which col1 is assumed to have 'entity1', 'entity2', & 'entity3' values.

    Now create two identical reports of this kind, these reports should be individual reports as opposed to creating two identical views.
    based on your drop down prompt, assign a presentation variable say it as myVar. In the prompt default it to 'entity 1' & uncheck "All Choices".

    Now on report1,
    1. add a filter on col1 with is prompted. Thus as 'entity 1' is default and shows data for this row.

    Now on report2 (identical to report1):
    1. add a filter on col1 and convert this to SQL and set it to "yourcolumn" NOT IN ('@{myVar}')
    2. Now go to compund layout,edit pivot view on report2.In here click on hand symbol to view "content properties".The hand symbol just above the "Rows" box & just below "Sections" box.
    3. check "Hide content (show sections only)" and save your report2.

    In the dashboard put the 2 reports verticaly below your prompt.
    Thus, you see the pivot with 'enitity1' & empty pivot with 'entity2' & 'entity3'.

    -bifacts
    http://www.obinotes.com
  • 5. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    Thanks for your help bifacts. My issue is I have Col1 as section in pivot and other column as filter prompt for report. Based on prompt value selected it is showing col1 entities. Any suggestions
  • 6. Re: Section Values showing NULL in Pivot
    David_T Guru
    Currently Being Moderated
    If I understand correctly, the problem is you need a LEFT JOIN from your "column1" so that regardless of whether there is a "fact" for a value in the column1, a row will appear. As it is, and in your example, for column1 value = 2.00, there is no fact and therefore no record.

    If you want to work only in the Presentation Layer, here is another workaround that I have used to simulate a "LEFT JOIN" without making a change to the rpd.

    Do this:

    1) You have your existing report for column1, column2, and your fact, column3 with its filters. Leave this as is.

    2) Now click on "Combine with similar request" link to create a UNION query.

    3) In this second query, add the same columns to your report as in step 1), but with the following differences:
    a) Dummy out column2 and column3. What I mean is in each of these two columns, click on the fx button and enter CASE WHEN 1=0 THEN tablename.columname ELSE 'test' END for column2 and CASE WHEN 1=0 THEN tablename.columname ELSE 'test2' END for column 3.
    b) Take out all filters except for a TIME filter if you have one. I'll explain below.

    This second query in effect will produce one row for every value in column1 regardless of whether a fact exists for any particular value. Because an "extra" row will appear even if there are data, I dummy out the columns to make it less obtrusive to your report. For the value in column1 that has no value (2.00 in your example), you will have a section for it.

    The reason I say if you have a TIME filter in your report in step 1) to include it is because let's say the values in column1 are time dependent, i.e., depending on the time filter, only a subset of the values in column1 would appear. In that case, you want "all the possible values" of column1 in your UNION query to be the same subset and not "every single value housed in column1." Hope that makes sense.
  • 7. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    Thanks for your time David.I really appreciate it. My requirement is I have 3 column filters filtering data and I have 2 columns in report, one added to section in pivot which has specific set of values that need to be shown in report regardless of data, like entity is zero even entity does not get populated after selections from column filters. BAsically the requirement is of income statement.
  • 8. Re: Section Values showing NULL in Pivot
    David_T Guru
    Currently Being Moderated
    Did you try what I said? What part doesn't meet your requirements?
  • 9. Re: Section Values showing NULL in Pivot
    bifacts Pro
    Currently Being Moderated
    Yes even when I generated a mock-up i am having col1 set as section. On col1 i had the drop down prompt.

    -bifacts
  • 10. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    I tried your solution but was getting an error while giving case statement and displaying results..
  • 11. Re: Section Values showing NULL in Pivot
    David_T Guru
    Currently Being Moderated
    If you want more help on this, just saying "there was an error" won't get you any. If you followed the CASE statement literally, then make sure that column is a CHAR column. If you chose a number column, then you will get the "non-compatible" error. If this is not the error you received, post the exact CASE statement you typed, the exact error message you got, and the the format of the column you used.
  • 12. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    hello David, could you please let me know how do I add case statement to the column 3(one which is the column filter prompt). I was using case statement for parent hence the error before. Screenshot below.

    http://i53.tinypic.com/2cifzmb.jpg
  • 13. Re: Section Values showing NULL in Pivot
    David_T Guru
    Currently Being Moderated
    Blue86, you need to be more clear and explicit in your questions. I am not there so you need to provide everything I need to "see" what you are saying. Based on your screenshot, it looks like you are not doing what I said correctly. You should not have a CASE statement on Column 1. I will explain again and I will try to be explicit in my explanation.

    All references will be to your first screenshot.

    1) You currently have one query with 3 columns. Column 1 is an attribute that will be in the Sections area of your pivot table. (It is the one in your first screenshot that has values "1.00" and "3.00." Column 2 is your "City" column and Column 3 is your REVN column. If you have a column prompt, put it in this query.

    2) Create the second UNION query with the same 3 columns. Leave Column 1 as is. Don't do anything to it. In Column 2, put in the CASE statement to "dummy it out." Enter CASE WHEN 1=0 THEN "replace with name of your City column' ELSE 'TEST' END. In Column 3, put in this CASE statement: CASE WHEN 1=0 THEN "replace with name of your REVN column" ELSE 0 END. Notice that in the CASE statement, I have 0 instead of a text. That is because your REVN column is in number format. This will put a 0 in that column. If you wish this row to be "blank," enter this CASE statement: CASE WHEN 1=0 THEN "replace with name of your REVN column" ELSE NULL END.

    3) In your pivot table, put Column 1 in your Sections area, Column 2 in your Rows area and Column 3 in your Measures area.

    This will give you a pivot table for every value of Column 1 even if you have no data (fact) for it.

    Try it. Then come back and report on it.
  • 14. Re: Section Values showing NULL in Pivot
    762091 Newbie
    Currently Being Moderated
    David, My requirement is when prod_name is selected in the prompt, the month values (1.00,2.00,3.00) need to be seen in the report even if no corresponding data is present, like "Month 2.00 is Zero" dynamically based on the choice from the prompt. I have included prod_name in both queries and Revn value is sectionised even when month is placed in section. please go through the screenshots.....

    Original Query


    Second Query
    http://i51.tinypic.com/ipz4va.jpg

    Pivot View
    http://i54.tinypic.com/wbpid0.jpg

    When report is opened from shared folder iam getting following error
    http://i53.tinypic.com/2j0fate.jpg
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points