Show all dimension values in Pivot table

[KuppuswamyS]

Hi All,

    I have a pivot table, ticked 'Show All Values' and unticked 'Suppress Zero-Values'  in all dimensions and tried both the expressions below to display all values even if it is null. Both the expression works but when selecting a record to view, system makes zero for all other records and shows all data. Is there anyway to hide all other records and display only selected records in pivot table? 

Expression 1

=if(sum({1} [# Total Hours]) <> sum([# Total Hours]), sum([# Total Hours]),0)

Expression 2 

=sum([# Total Hours]) + Sum({1}0)

Thank you. 

[edwin]

is it possible to attach a qvw even with made up data?

[KuppuswamyS]

Hi Edwin,

Please find qvw attached. Thank you. 

Expression sum([# Util%]) + Sum({1}0) displays all values but when selecting a machine id to view, system shows data for selected record, makes zero for all other records but not hidden. 

[edwin · Accepted solution]

the reason all machine IDs are shown because of this:
=sum([# Total Hours]) + Sum({1}0)

however, if you remove this any months that are not associated with the machine ID will not be displayed.

one thing you can do is supply the association, pad your data with all months and years with null measures.  that way there is a month for each machine ID and you will be able to display all months:

[KuppuswamyS]

Thank you so much Edwin. 

[edwin]

yw

[KuppuswamyS]

Hi Edwin,

Sorry, I used your code to pad data with all months and years with null measures but still system doesn't display data for all months. Can you please review my code to find where I'm wrong. Thank you. 

tmpYears:
load distinct [Posting Year]
Resident Machine;

tmpMonths:
load distinct [Posting Month]
resident Machine;

inner join (tmpMonths)
load [Posting Year]
resident tmpYears;

tmpMachine:
NoConcatenate
load
[Report Date],
[%Company Account],
[%Machine ID],
[# Actual Production Hours],
[# Util],
[Work Center],
[Machine Description],
[Machine Group Object],
[Machine Group Description],
[Machine Range],
[Machine Type],
[Posting Month],
[Posting Year],
[No of Days]
resident Machine
where not isnull([%Company Account]);
inner join (tmpMonths)
load
[Report Date],
[%Company Account],
[%Machine ID],
[# Actual Production Hours],
[# Util],
[Work Center],
[Machine Description],
[Machine Group Object],
[Machine Group Description],
[Machine Range],
[Machine Type],
[Posting Month],
[Posting Year],
[No of Days]
Resident tmpMachine;

concatenate (Machine)
load
[Report Date],
[%Company Account],
[%Machine ID],
[# Actual Production Hours],
[# Util],
[Work Center],
[Machine Description],
[Machine Group Object],
[Machine Group Description],
[Machine Range],
[Machine Type],
[Posting Month],
[Posting Year],
[No of Days]
Resident tmpMonths;
drop tables tmpMonths, tmpMachine, tmpYears;

DROP Table Machine;

[edwin]

i suggest you use the logic in the QVW i attached.  use only the company and machine IDs at first and check if it runs correctly.  then start adding other dimensions.  your code doesnt really follow the solution as you modified it incorrectly

[KuppuswamyS]

Hi Edwin,

It was my mistake. It works now, thank you.

[edwin]

yw