Selecting the data source for a chart is the first step to create a chart.
Collabion Charts for SharePoint allows you to create charts using data from various types of data providers. You can choose one of the following data providers:
The steps for getting data from a data source can vary depending on the type of data provider that you are using.
Learn how to:
- Select data from SharePoint List
- Select data from Microsoft SQL Server Database
- Select data from Oracle Database
- Select data from CSV or Text File
- Select data from Business Data Catalog
- Select data from Microsoft Office Excel File
How to select data from SharePoint List
To connect your SharePoint List and provide data to the chart, follow the steps given below:
- Select SharePoint List in the Select data provider setting.
- Click Connect. A message box announces whether the connection is successful.
- Choose the SharePoint List containing the data for chart in the Select list setting.
NOTE: You can click the Refresh button on the right to retrieve the latest SharePoint Lists.
- You can optionally select a View created from the selected Select list view setting.
NOTE: You can click the Refresh button on the right to retrieve the latest Views.
Finally, you can view the data present in the List by clicking View Data.
To connect to a Microsoft SQL Server Database and provide data to the chart, follow the steps below:
- Select Microsoft SQL Server Database in the Select data provider setting.
- Provide the name of the Microsoft SQL Server in the Server setting.
NOTE: Provide period (.) to access the local Microsoft SQL server.
- Enter the name of the database in the Database text field.
- In the User name and the Password text boxes, provide the user name and password required to connect to the database.
- Click Connect. A message box announces whether the connection is successful.
- After successful connection, in the Select data source setting, choose :
- Table option to list all the tables from the connected Database or
- Result of a SQL Query option to make use of SQL Commands to retrieve data from the Database.
NOTE: Using Result of a SQL Query you can retrieve specific data based on a specific criteria. Here, you can make use of the advanced features of SQL Query.
How to use the Table option?
When you select the Table option, the Select data table drop-down list appears below the option. It contains the list of available database tables. Choose the required table from the list.
NOTE: You can click the Refresh button on the right to retrieve the latest tables.
Finally, click View Data to view the retrieved data.
How to use the Result of a SQL Query option?
When you select the Result of a SQL Query option, two boxes, labeled Query Variables and Query, appear below the option.
- Do not use DISTINCT keyword with queries that include GROUP BY.
For example: SELECT * FROM Salary WHERE emp_id = (SELECT DISTINCT emp_id FROM Employee GROUP BY emp_id)
- The COMPUTE and INTO clauses cannot be specified.
For example: SELECT * FROM Salary WHERE emp_name IN (SELECT emp_name INTO x FROM Employee WHERE emp_id = 1201)
- ORDER BY can only be specified when TOP is also specified.
For example: SELECT TOP 100 PERCENT ShipCountry AS Country, COUNT(OrderId) AS Orders FROM dbo.FC_Orders GROUP BY ShipCountry ORDER BY COUNT(OrderId) DESC
- The select list of a query introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
For Example: SELECT emp_name FROM Employee WHERE emp_id = (SELECT * FROM Salary WHERE emp_id = 1021);
- Queries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
For example: SELECT * FROM Salary WHERE emp_id = (SELECT DISTINCT emp_id FROM Employee GROUP BY emp_id)
- The select list of a query introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a query introduced with EXISTS are the same as those for a standard select list, because a query introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.
For example: SELECT emp_name FROM employ WHERE EXISTS (SELECT * FROM salary WHERE empid=1211)
- The ntext, text, and image data types cannot be used in the select list of queries.
To use SQL as the data source you need to do the following:
- Enter your SQL Query in the Query box.
For example, in the sample SQL Query below, we join data from two tables and get name and generated by sales managers.
SELECT TOP 100 PERCENT EMPLOYEE_MASTER.ID, EMPLOYEE_MASTER.NAME, SUM(SALES_DETAILS.AMOUNT)
FROM EMPLOYEE_MASTER, SALES_DETAILS
WHERE EMPLOYEE_MASTER.ID = SALES_DETAILS.EMPLOYEE_ID
GROUP BY SALES_DETAILS.EMPLOYEE_ID
ORDER BY AMOUNT DESC
You can optionally make use of the Query Variables box to declare variables with predefined values. These variables can then be used as part of your SQL Query.
For example, in the Query Variables box, you enter the following:
DECLARE @AGE_LIMIT int
SET @AGE_LIMIT = 18
Next, in the Query box you use this as:
SELECT NAME, ADDRESS, PHONE, DONATION FROM VISITORS
WHERE AGE >= @AGE_LIMIT
Finally, click View Data to view the retrieved data.
NEXT: Pick data fields from the Select Fields page.
To connect to a Oracle Database and provide data to the chart, follow the steps below:
- Select Oracle Database in the Select data provider setting.
- Provide the name of the Oracle Database Server in the Server setting.
- In the User name and the Password text boxes, provide the user name and password required to connect to the database.
- Click Connect. A message box announces whether the connection is successful.
- After successful connection, under the Select data source setting, choose :
- Table option to list all the tables from the connected Database or
- Result of a SQL Query option to make use of SQL Commands to retrieve data from the Database.
NOTE: Using Result of a SQL Query you can retrieve specific data based on a specific criteria. Here, you can make use of the advanced features of SQL Query.
How to use the Table option?
When you select the Table option, the Select data table drop-down list appears below the option. It contains the list of available database tables. Choose the required table from the list.
NOTE: You can click the Refresh button on the right to retrieve the latest Tables.
Finally, click View Data to view the retrieved data.
How to use the Result of a SQL Query option?
- Do not use DISTINCT keyword with queries that include GROUP BY.
For example: SELECT * FROM Salary WHERE emp_id = (SELECT DISTINCT emp_id FROM Employee GROUP BY emp_id)
- The COMPUTE and INTO clauses cannot be specified.
For example: SELECT * FROM Salary WHERE emp_name IN (SELECT emp_name INTO x FROM Employee WHERE emp_id = 1201)
- ORDER BY can only be specified when TOP is also specified.
For example: SELECT TOP 100 PERCENT ShipCountry AS Country, COUNT(OrderId) AS Orders FROM dbo.FC_Orders GROUP BY ShipCountry ORDER BY COUNT(OrderId) DESC
- The select list of a query introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
For Example: SELECT emp_name FROM Employee WHERE emp_id = (SELECT * FROM Salary WHERE emp_id = 1021);
- Queries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
For example: SELECT * FROM Salary WHERE emp_id = (SELECT DISTINCT emp_id FROM Employee GROUP BY emp_id)
- The select list of a query introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a query introduced with EXISTS are the same as those for a standard select list, because a query introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.
For example: SELECT emp_name FROM employ WHERE EXISTS (SELECT * FROM salary WHERE empid=1211)
- The ntext, text, and image data types cannot be used in the select list of queries.
When you select the Result of a SQL Query option, two boxes, labeled Query Variables and Query, appear below the option. Here, you need to do the following:
- Enter your SQL Query in the Query box.
For example, in the sample SQL Query below, we join data from two tables and get name and generated by sales managers.
SELECT EMPLOYEE_MASTER.ID, EMPLOYEE_MASTER.NAME, SUM(SALES_DETAILS.AMOUNT)
FROM EMPLOYEE_MASTER, SALES_DETAILS
WHERE EMPLOYEE_MASTER.ID = SALES_DETAILS.EMPLOYEE_ID
GROUP BY SALES_DETAILS.EMPLOYEE_ID
ORDER BY AMOUNT DESC
- You can optionally make use of the Query Variables box to declare variables with predefined values. These variables can then be used as part of your SQL Query.
For example, in the Query Variables box you enter the following:
DECLARE @AGE_LIMIT int
SET @AGE_LIMIT = 18
Next, in the Query box you use this as:
SELECT NAME, ADDRESS, PHONE, DONATION FROM VISITORS
WHERE AGE >= @AGE_LIMIT
Finally, click View Data to view the retrieved data.
NEXT: Pick data fields from the Select Fields page.
You may have your data in a text or a CSV file. You may have copied data from a Web page, a spread-sheet, etc. To use the data from the text or CSV file or to paste and use the copied data, follow the steps below:
- Select the CSV File in the Select data provider setting.
- If your data is stored locally or hosted on the Web:
- Select the Stored locally or hosted on web option.
- Enter the URL address of the CSV file in the Path URL of CSV file box.
- If the file requires a user name and password, provide the user name in the User name (optional) entry box and the password in the Password (optional) entry box.
- Click Connect. A message box announces whether the connection is successful.
- If you have copied the data from a Web page, spread-sheet or any other source:
- Select the Provide static CSV data inline data option.
- Paste the copied data in the Data box.
- Click Parse Data. The data, which you have provided, is verified and made ready for the chart.
- Finally, view the data present in the List by clicking View Data.
NOTE: The first line of the CSV data present in your file or in the data you have pasted is considered as the header row. The header row, in a CSV data, contains the names of the data series.
To use data from Business Data Catalog, follow the steps below:
- Select Business Data Catalog in the Select data provider setting.
- Specify the line of business system instance in the Specify LoB System Instance.
- Click Connect. A message box announces whether the connection is successful.
Finally, you can view the data present in the List by clicking View Data.
NEXT: Pick data fields from the Select Fields page.
You may have uploaded Microsoft Excel Spreadsheet documents which contain data for charts. To connect to a uploaded Microsoft Excel file and use its data, follow the steps below:
- Select Microsoft Office Excel File in the Select data provider setting.
- Specify the URL of the Excel file in the Path URL of excel file setting. You can also click the browse button on the right to select an Excel file.
- In the Sheet Name setting, specify the name of the sheet of the Excel file that contains the data for the chart.
- In the Sheet Range setting, set the cell range in the selected sheet that contains the data for the chart.
- Click Connect. A message box announces whether the connection is successful.
NOTE: To get access to the Excel files, you would need to configure Shared Services in your SharePoint server and put the Excel files in the Trusted Location of your SharePoint server. Excel services are only offered on SharePoint enterprise version. For more details see http://sharepoint.microsoft.com/en-us/buy/pages/editions-comparison.aspx
Finally, you can view the data present in the List by clicking View Data.