Difference between revisions of "Driving Grids from an Excel Range"

(→‎Example)
Line 8: Line 8:
 
[[File:DrivingGrid1.png|center|300px]]
 
[[File:DrivingGrid1.png|center|300px]]
  
−
==Example (based on the [[BicycleSales Demo Cube|Bicycle Sales cube]])==
+
We will change it to use an Excel range to drive which calendar periods are displayed.
  
−
We will create a dropdown to automatically switch the {{Code|Time}} dimension of our Grid.
+
We will set up cell E2 as the Excel range:
 +
 
 +
[[File:DrivingGrid2.png|center|300px]]
 +
 
 +
Now edit the Calendar Date hierarchy to use E2 as its input.  Click on the Excel range button and select E2 as the driving range:
 +
 
 +
[[File:DrivingGrid3.png|center|300px]]
 +
 
 +
The selected range is added to the list of selections:
 +
 
 +
[[File:DrivingGrid4.png|center|300px]]
 +
 
 +
Click OK.
 +
 
 +
The grid will update to use the new range for the Date Calendar hierarchy.
 +
 
 +
[[File:DrivingGrid5.png|center|300px]]
 +
 
 +
Similarly, we can use an Excel range to drive the grid rows:
 +
 
 +
[[File:DrivingGrid6.png|center|300px]]
 +
 
 +
Now edit the Geography hierarchy to use E2 as its input.  Click on the Excel range button and select E2 as the driving range:
 +
 
 +
[[File:DrivingGrid7.png|center|300px]]
 +
 
 +
Click OK.
 +
 
 +
The grid will update to use the new range for the Geography hierarchy.
 +
 
 +
[[File:DrivingGrid8.png|center|300px]]
 +
 
 +
Additionally, you can change the selection to include children, descendants etc:
 +
 
 +
[[File:DrivingGrid9.png|center|300px]]
 +
 
 +
Let’s select Children:
 +
 
 +
[[File:DrivingGrid10.png|center|300px]]
 +
 
 +
Click OK and the grid will now show all children (states) of the selection in E2, currently United States.
 +
 
 +
[[File:DrivingGrid11.png|center|300px]]
 +
 
 +
Let’s update E2 to be Germany (by typing straight into E2) – the grid now shows all German states:
 +
 
 +
[[File:DrivingGrid12.png|center|300px]]
 +
 
 +
The Excel range can also be across a number of cells:
 +
 
 +
[[File:DrivingGrid13.png|center|300px]]
  
−
<ol>
 
−
<li>Insert a Grid based on the Bicycle Sales demo cube, starting from {{Code|A7}}. Select {{Code|Time}} and {{Code|Measures}} on Slicers, {{Code|Region}} on Columns and {{Code|Product}} on Rows.</li>
 
−
<li>In cells {{Code|B3}} to {{Code|B5}}, we enter the years we want to analyse:</li>
 
−
[[File:DrivingGridExample1.png|center|300px]]
 
−
<li>To create a validation dropdown cell:</li>
 
−
<ol type="a">
 
−
<li>Select the {{Code|B2}} cell for the dropdown</li>
 
−
<li>Select the Data Validation ribbon item:</li>
 
−
[[File:DrivingGridExample2.png|center|300px]]
 
−
<li>Choose a '''List''' type validation, and enter {{Code|=$B$3:$B$5}} as the source:</li>
 
−
[[File:DrivingGridExample3.png|center|300px]]
 
−
<li>Click '''OK''', and the dropdown will be created in cell {{Code|B2}}:</li>
 
−
[[File:DrivingGridExample4.png|center|300px]]
 
−
</ol>
 
−
<li>Now we need to drive the Grid from the dropdown cell:</li>
 
−
<ol type="a">
 
−
<li>Double-click on the {{Code|Time}} label to display the Hierarchy editor.</li>
 
−
<li>Click on the '''Excel Range''' button, and choose {{Code|B2}} for the driving range:</li>
 
−
[[File:DrivingGridExample5.png|center|300px]]
 
−
<li>The selected range is added to the list of selections:</li>
 
−
[[File:DrivingGridExample6.png|center|300px]]
 
−
<li>Click '''OK'''
 
−
</ol>
 
−
<li>The Grid will update to use the new range for the {{Code|Time}} hierarchy. Because the range is currently empty, the Grid seems to be unaffected, but if we now use the dropdown to switch the year to {{Code|2003}}, we'll see the Grid update:</li>
 
−
[[File:DrivingGridExample7.png|center|300px]]
 
−
<li>As you can see, the validation list range can be hidden to keep the report tidy.</li>
 
−
<li>Even after drilling the Grid, the driving cell is still maintained:</li>
 
−
[[File:DrivingGridExample8.png|center|300px]]
 
−
</ol>
 
  
 
==Further Exercises==
 
==Further Exercises==

Revision as of 09:03, 24 March 2016

For many standard reports, you will want to use data from the Excel workbook to influence your Grids. Driving dimensions from a range provides a simple way to do this, especially when multiple Grids need to use the same parameters.

If the input to the Grid can contain members that aren't found in the Cube, then the error message that is shown for invalid members can be hidden. See the Grid Properties Display error on invalid members option.

Example

Here’s a simple grid:

DrivingGrid1.png

We will change it to use an Excel range to drive which calendar periods are displayed.

We will set up cell E2 as the Excel range:

300px

Now edit the Calendar Date hierarchy to use E2 as its input. Click on the Excel range button and select E2 as the driving range:

DrivingGrid3.png

The selected range is added to the list of selections:

DrivingGrid4.png

Click OK.

The grid will update to use the new range for the Date Calendar hierarchy.

DrivingGrid5.png

Similarly, we can use an Excel range to drive the grid rows:

DrivingGrid6.png

Now edit the Geography hierarchy to use E2 as its input. Click on the Excel range button and select E2 as the driving range:

DrivingGrid7.png

Click OK.

The grid will update to use the new range for the Geography hierarchy.

DrivingGrid8.png

Additionally, you can change the selection to include children, descendants etc:

DrivingGrid9.png

Let’s select Children:

DrivingGrid10.png

Click OK and the grid will now show all children (states) of the selection in E2, currently United States.

DrivingGrid11.png

Let’s update E2 to be Germany (by typing straight into E2) – the grid now shows all German states:

DrivingGrid12.png

The Excel range can also be across a number of cells:

DrivingGrid13.png


Further Exercises

  • Drive a second hierarchy: perhaps the Product hierarchy
  • Drive a second Grid from the same cell, and watch as both Grids change at once
  • Drive the Grid from formulae
Retrieved from ‘https://help.fluencexl.com/index.php?title=Driving_Grids_from_an_Excel_Range&oldid=3003’