Pivot Table with Writeback

Coordinator
Oct 9, 2009 at 6:11 PM
Edited Oct 9, 2009 at 6:38 PM
Writeback cube:
Ranet UILibrary Controls. Pivot table with Writeback

Ranet.UILibrary.Olap feature overview: http://www.youtube.com/watch?v=dDNa3D8RO4Y

>>>Image 1: Update script
>>>Image 2: Recaculate cell
>>>Image 3: Changes cell
>>>Image 4: Update Cube Log
Mar 11, 2013 at 11:22 AM
can you please send me a simple write back demo code. Also please suggest me what changes i need to do in AdventureWorks Db to enable to do this demo.

Thanks,
Liyo Jose.
Coordinator
Mar 12, 2013 at 4:32 PM
Edited Mar 12, 2013 at 5:19 PM
liyojose wrote:
can you please send me a simple write back demo code. Also please suggest me what changes i need to do in AdventureWorks Db to enable to do this demo.

Thanks,
Liyo Jose.
1. Create Writeback Partition
a) Start the SQL Server Business Intelligence Development Studio (BIDS)
b) In SQL Server BIDS open the database Adventure Works (File -> Open -> Analysis Services Database...)
c) In the tree Solution Explorer select the cube Adventure Works
d) On the tab Partitions select group of measures Sales Targets
e) Create for her Writeback Partition (call the local menu Writeback Setting...)
f) Process the cube Adventure Works

Next, run Ranet OLAP Sample Web Application and pass the settings:

2. Set the Connection string
a) Click on the tab [Configurations] and set OLAPConnectionString
For example:
Provider=MSOLAP.4;Data Source=server\sql2008r2;Catalog=Adventure Works DW;

3. Set the Mdx Query
a) Click on the tab [Mdx Query] and copy
SELECT 
HIERARCHIZE([Date].[Calendar].[Calendar Quarter].Members) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 0, 
HIERARCHIZE(CrossJoin([Employee].[Employees].[(All)].Members, [Sales Territory].[Sales Territory].Levels(0).Members)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 1 
FROM 
[Adventure Works] 
WHERE ([Measures].[Sales Amount Quota]) 
CELL PROPERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE
4. Set the Update Script
a) Click on the tab [Mdx Update Script] and copy
UPDATE CUBE [Adventure Works]
SET 
(
 [Measures].[Sales Amount Quota]
,<%[Date].[Calendar]%>
,<%[Employee].[Employees]%>
,<%[Sales Territory].[Sales Territory]%>
)
= <%newValue%> 
In the <%[Dimension].[Hierarchy]%> - the cell coordinate in the visible part of the table. If you remove the hierarchy of the rows or columns - it should be removed from the Update Script. The coordinates for the invisible hierarchies are defined explicitly or by any algorithm.

4. Editing of the data cube
a) Click on the tab [Pivo tGrid]
b) Initialize PivotGrid
If the settings are made correctly, then you will get the table where the data area is highlighted in yellow background.
c) Try to enter the value into empty cell

5. The limitations of the current version
a) The Update script not automatically generated.
In the nearest future this problem will be eliminated. The update scripts setting will be made by analogy with Excel.
Coordinator
Apr 3, 2013 at 2:17 PM
Edited Apr 3, 2013 at 4:15 PM
The example of settings is described in the PDF-document Ranet OLAP: Editing data in the pivot grid
  1. Setting up the Writeback partition for the database Adventure Works
  2. Setting up the MDX query and the UPDATE SCRIPT
  3. Editing data in the Pivot Grid
  4. Examples of the code
Use the version Ranet OLAP 2.5 for Silverlight.
May 19, 2014 at 3:46 AM