How to switch on the commands and options Menu in the portfolio analyzer?

[Diaa Hussein - I would be glad to help]

Hello Guys

I have found so many questions about how to re-enable the commands and
options in the portfolio analyzer after getting it switched off

The problem is that when you switch the commands and options off you
can not edit your view anymore and you will have to create a new view
as so many people have done before

Microsoft are using XML to describe pivot this XML is stored in the
DATABASE
Through this XML you can change anything in the pivot characteristics
You can change the "field caption", field format, enable or disable
the "commands and options"
Even change the data source of your pivot without the need to re-build
the pivot back again "very helpful if you want to create multiple
similar views that reads from deferent cubes (DATA SOURCE) "

To get this XML you will need access to both your DATABASE server and
Application server

1. Open the SQL profiler and make it ready for the capture -"but do
not start capturing yet or you will get a huge log"
2. Open the EPM web interface and login with an account which has an
administrative privilege
3. Select Admin
4. Select manage views
5. Choose to modify the view which the "commands and options" is
disabled on and you want to re-enable it back
6. After you open this view for modification
7. Go back to the SQL profiler and start capturing
8. Go back to the WEB view and choose to save the view
9. Switch back to the SQL profiler and stop the capturing
10. Scroll up until you find an entry starts with this
_______________________________________________________________________________________________
exec sp_executesql N'UPDATE "Projectserver".."MSP_WEB_VIEW_REPORTS"
SET
"WVIEW_OWC_PIVOT_XML"=@P1,"WVIEW_OWC_CHART_XML"=@P2,"WVIEW_DEFAULT_SETTINGS"=@P3
WHERE "WVIEW_ID"=@P4', N'@P1 ntext,@P2 ntext,@P3 ntext,@P4 int',
N'<xml xmlns:x="urn:schemas-microsoft-comffice:excel">
<xivotTable>
<x:OWCVersion>11.0.0.6255 </x:OWCVersion>
<xisplayScreenTips/>
<x:MaxHeight>500</x:MaxHeight>
<x:MaxWidth>1000</x:MaxWidth>
<x:NoAllowPropertyToolbox/>
<x:NoAllowCustomOrdering/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<x:CacheDetails/>
<x:ConnectionString>Provider=MSOLAP.2;Data Source=SQLSERVER;Initial
Catalog=EPM;Client Cache Size=25;Auto Synch Period=10000</
x:ConnectionString>
<xataMember>XXXXXXX</xataMember>
<x:NoViewCalculatedMembers/>
<x:Name>Microsoft Office PivotTable 11.0</x:Name>
<xivotField>
....
.....
......
"I HAVE CUTED THIS PART TO MAKE THE DOC SMALL AND BECAUSE THEY HAVE
COMPANY CONFIDENTIAL INFORMATION"
.......
........
</xml>', N'<ViewDefaults><ShowOWCToolbar/></ViewDefaults>''', 130
_______________________________________________________________________________________________
This entry will be very long

11. Copy this entry out to a notepad and delete this line
_______________________________________________________________________________________________
<x:NoAllowPropertyToolbox/>
_______________________________________________________________________________________________

12. Open the query analyzer and paste the edited text into it and then
run the query it shall give you an output that 1 raw is effected

You are done; The "Commands and options" is enabled again

Caution: each view has its own XML file identified by the VIEW_ID
which is the last number in the SQL entry (XML file) in this example
it is 130
Do not change this number or you will end up editing another view and
you might miss the view up

If you want to change any field property, Search for the field name
and start editing
And paste it in the query analyzer and you are done