Applies to: Nevron Chart for SharePoint (WSS3.0, SharePoint 2007/2010/2013)
How to create Chart by using Dynamic Named Range in Excel Services?
When using Excel Services as the data source for Nevron Chart Web Part, you may need to dynamically expand the defined range in order to accommodate new rows and columns of data added to the Excel spreadsheet. To achieve that, you can create Named Ranges in Excel and, using the OFFSET function, they can become "dynamic", i.e. automatically expanding to contain all new rows/cols entries when they are made.
Let's say that we have the following sample data in our Excel spreadsheet (we are only going to use Sheet1):
Note that it also has empty rows and cols.
Here are the steps necessary to create a Dynamic Named Range:
1. Select the "Formulas" tab from the menu. From the Defined Names group, click on "Define Name":
2. Name the range "DynamicRange" and add the following formula in the "Refers To" field:
=OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A:$A,-1),MATCH("*",Sheet1!$A$1:$AAA$1,-1))
We are using the MATCH function in order to resolve issues with the empty rows. If you have empty (DBNull) rows like in our sample, the dynamic range will not work correctly if you use the COUNT or COUNTA functions in the formula.
3. Click OK and save the Excel spreadsheet. Upload the example Excel Spreadsheet into a document library housed with the SharePoint site.
For more details about the OFFSET function in Excel, you can refer to the MS documentation.
Breakdown of the “OFFSET” function - the Excel syntax for the OFFSET function is: =OFFSET(reference,rows,columns,height,width)
- Reference: is the reference from which you want to base the offset. Reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
- Rows: is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the Starting reference) or negative (which means above the Starting reference).
- Cols: is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the Starting reference) or negative (which means to the left of the Starting reference).
- If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.
- Height: is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
- Width: is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.
- If height or width is omitted, it is assumed to be the same height or width as reference.
- In the example in the formula used for this demonstration, the “Height” and “Width” values have been manipulated to include all necessary data.
- Height equals to "MATCH("*",Sheet1!$A:$A,-1)" or all the rows in Sheet1 column A, including the empty rows.
- Width equals to "MATCH("*",Sheet1!$A$1:$AAA$1,-1)" or all the columns between A and AAA (huge number of cols), including the empty cols.
- By starting at Sheet1!$A$1, Cell “0,0” - this means that the range will expand downward to the last used row and to the right including all columns to the last used col.
Create and configure the Chart Web Part
1. Add a Nevron Chart web part and configure it to connect to the Excel spreadsheet that was uploaded in the SharePoint site.
2. Run the Chart Designer. In the "Data Source" tab change the "Data Source Type" drop down to "Excel Services":
3. Enter the URL of the target spreadsheet and also the name of the dynamic range:
4. In this example, we will use Combo Chart type. From the Chart >> Chart Areas >> General tab, select Combo:
5. From the Pivot tab, configure the Data Groupings – Values and Categories.
In our example we have 3 Values for: Sales (Bar subtype), Profit (Bar subtype) and Quantity (Smooth Area subtype):
In the Categories tab, we are grouping by "Item_Title": =Fields!Item_Title
6. In order to remove the empty rows, we can create a global filter with the following expression:
=ISEMPTY(Fields!Item_Title)=FALSE
7. Click "OK" and return to the SharePoint page. We were able to generate the following Chart:
8. Now edit the source data (the Excel spreadsheet) and add a new row to the bottom of the list:
Then save the spreadsheet and refresh the page with the Nevron Chart Web Part:
Note that a "Watermelon" column is now present in the chart.
There was no need to modify the named range as it dynamically expanded to accommodate the new row.
The new row contained a new category and so it was automatically added to the chart.
Article ID: 230, Created On: 1/15/2013, Modified: 1/29/2013