This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,956 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Pivot table show data for all dimension values

Hi All

I have the following requirement:

Sales
Product Name Week1 Week 2
B 10

If you notice, you'll see that Product B does not have sales for Week 2. I need to view the Weeks for which there have been no sales, and am trying to get this done in a pivot view by having Product Name as row, Week as column and Sales as the measure. But I get the resultant data only for Week 1 (since data exists only for Week 1). Note that Week 2 data is not a
null value, it means there have been no sales for that product for that week.

So basically, I need to see Weeks for which there have been no sales (dimension values even if there no corresponding measures) and not the
weeks for which there have been sales.

Thanks
Ananth
Tagged:

Answers

  • kart
    kart Member Posts: 804
    use case statement like case when sales is null then 0 else end.
  • 710389
    710389 Member Posts: 193
    I don't know if I understand your request completely...

    but if you want to show a '0' for week 2 you can do it this way..
    Go to the properties of your measure - format section
    - 'Override default Data Format'
    - choose 'Custom'
    - add the following Custom Numeric Format toe : #,##0;-#,##0;0

    If it's not that I think you need an outer-join somewhere...

    Hope ithihs helps you out

    Kr,
    A
  • 663424
    663424 Member Posts: 37
    Hi kart

    The problem is that the sales is not actually null for Week2. It's that there is no sales at all for that week.

    Thanks
  • 663424
    663424 Member Posts: 37
    edited Nov 27, 2009 7:00AM
    Hi ADB

    I think the override default data format would work if there is already existing data right? But I don't have data for the week in my fact (i.e. for a
    week in my time dimension I don't have a record in my fact. But I want to see the weeks for which there are no data.) Hope this helps.

    The user basically wants to see all the weeks in a particular time period (say year/month) and wants to see the sales for those weeks by product.
    The catch is if a product has not sold anything in a week, the fact does not store a null/0 value for that product that week, but there is no
    record at all.

    Edited by: Ananth V on Nov 27, 2009 3:58 AM
  • 710389
    710389 Member Posts: 193
    Hi,

    try once with an union query.

    A first query selecting the data as you have now...and a second query which only selects the corresponding weeks.

    Kr,
    A
    710389
  • Luis Cabral
    Luis Cabral Oracle Developer Perth, AustraliaMember Posts: 973
    edited Nov 27, 2009 11:16AM
    Hello

    One option is to use a "Direct Database Request" (available on Answers main page) where you can write any SELECT stmt to be run against your source database.

    Just write a SELECT with the weeks table as the driving table OUTER JOINing to your facts table. You will get all weeks, with or without associated measures.

    This is a bit untidy as you will be bypassing your business model but it works...

    Regards,
    Luis

    Note: You may need to enable DDRs using the Presentation Services settings, as it seems they are disabled by default.
    Luis Cabral
  • 663424
    663424 Member Posts: 37
    Hi ADB and Luis

    Thanks for the suggestions. I'll try them out.

    Thanks
    Ananth
  • Luis Cabral
    Luis Cabral Oracle Developer Perth, AustraliaMember Posts: 973
    Hi Ananth,

    Another suggestion, change the Logical Link type between your fact table and time table to "Left Outer", it will user an outer join to join those tables and you should get what you want. However this may affect other reports you may already have.

    Regards,
    Luis
This discussion has been closed.