Microsoft Sharepoint Web Parts

SQL Chart Web Part

The SQL Chart Web Part allows to chart data selected from the specified SQL Server database, OLE-DB or ODBC data source. The charts are created using Google's public Chart API (see official Google Documentation). The SQL Chart Web Part accepts Web Part connections for filtering. The Web Part can be used with Windows Sharepoint Services V3, MOSS 2007, Sharepoint 2010, Sharepoint 2013 and Sharepoint 2016. The following parameters can be configured: SQL query or Stored Procedure to select the data to be visualized Chart Type: Pie Chart 3D Pie Chart Horizontal Bar Chart Horizontal Stacked Bar Chart Vertical Bar Chart Vertical Stacked Bar Chart Line Chart Chart dimensions Chart Title Chart background colors Chart Color Palette Chart Legend   Product Price SQL Chart Web Part for SP 2007/WSS3.0 and SP 2010 30 day Evaluation Version Free SQL Chart Web Part for SharePoint 2013 30 day Evaluation Version Free SQL Chart Web Part for SharePoint 2016 30 day Evaluation Version Free SQL Chart Web Part for SP 2007, 2010 and 2013 SQL Chart Web Part per WFE Server License Key USD 150.00 SQL Chart Web Part for SharePoint 2016 SQL Chart Web Part per WFE Server License Key USD 150.00 SQL Chart Web Part Installation Instructions Deployment Instructions for SP 2010/2013

Installation Instructions:

1. download and unpack the AESQLChartWebpart.zip file.
   The ZIP archive contains the AESQLChartWebpart.dll and the AESQLChart.webpart files.
   
2. download the SQL Chart Web Part Installation Instructions (PDF file, see above) 
   
3. either install the web part manually or deploy the feature to your server/farm as described in the instructions.

4. Navigate to a page where you would like to place the webpart and choose "Site Actions/Edit Page"

5. Add the web part to the appropriate zone. The web part is listed in the "Amrein Engineering" or "Miscellaneous" web part gallery section depending on the chosen installation procedure (Feature deployment or manual installation)


6. Configure the following Web Part properties in the Web Part Editor "Miscellaneous" pane section as needed:
   
   

   DB Connection String (SQL Server): if you use SQL Server authentication, enter the connection string as follows: UID=uuu;Initial Catalog=database;Data Source=servername where uuu = SQL Server User Account database = the database you want to connect to servername = the name of the SQL Server Example: UID=sa;Initial Catalog=Northwind;Data Source=TestSQL; Please note that the password is entered in the Connection Password field for security reasons. if you use Windows authentication, enter the connection string as follows: Initial Catalog=database;Data Source=servername;Integrated Security=SSPI; Example: Initial Catalog=Northwind;Data Source=TestSQL;Integrated Security=SSPI; DB Connection String (OLE-DB): either simply enter the physical file name of the data source file (MS Access, MS Excel, CSV files) as follows: Examples:   c:\data\northwind.mdb    d:\excel\somedata.xls   c:\data\somedata.csv or alternatively enter a fully qualified OLE-DB connection string as follows: Examples: Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true;Password=[ppp];User ID=[uuu];Data Source=[ddd] Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data\northwind.mdb Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data\;Extended Properties='text;FMT=Delimited' You can look up nearly every database connection string at connectionstrings.com DB Connection String (ODBC): either enter the ODBC Data Source name (as configured with the ODBC Data Source Administrator) as follows: dsn=MyDataSource or enter your fully qualified ODBC connection string as follows (Example): Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; Connection Password: if you use SQL Server authentication, enter the User account passord into this field. It will be automatically encrypted for security reasons. Leave this field empty if you use an OLE-DB connection. SQL Query: Enter the desired SQL Select statement into this field. Example (using the Microsoft Northwind database): SELECT TOP 5 COUNT(*) AS Total, CustomerID FROM Orders GROUP BY CustomerID Placeholders: - Use the {1} placeholder to pass a Filter value to the query. - Range: {1} for Field Value connections, {1} - {12} for Filter Values connections - Use the {uid} placeholder to pass the current logged on user name - Use the {xxx} placeholder (where xxx is an URL query string parameter name) to pass URL parameters     Example assuming that the URL parameter "id" is contained in the page URL:   SELECT Lastname,Firstname,Title FROM Employees WHERE ID={id} Example using a dynamic placeholder to filter as provided by a connected Filter Web Part: SELECT CompanyName, City, Phone FROM Suppliers WHERE City LIKE '{1}%' Dynamic placeholder to filter as provided by a connected multivalue choice Filter Web Part: Configured query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('{3}') Chosen in filter web part: Toronto, Chicago, Paris Resulting query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('Toronto','Chicago','Paris') Stored Procedures: Enter the name of the Stored Procedure, followed by optional parameters. Add each parameter as a "name=value" pair and separate multiple parameters by semicolons. Example (refers to Microsoft Northwind sample database): SalesByCategory;CategoryName=Seafood;OrdYear=1997 Data Series Column Name(s): enter the database table column name of the column(s) containing the data to be charted. You can specify multiple data series by entering multiple column names separated by commas (as eg. Sales 2008,Sales 2009) Label Column Name: enter the database table column name of the column that contains the data labels to be used. You can append the data value to each label by adding the x placeholder to the column name (separated by a colon). The placeholder can be surrounded by other characters. Example: append the data value to the labels, surrounded by parentheses: produces: If the labels consist of dates, you can append a date formatting string to the column name (separated by a colon): Example: TradeDate:MMM-dd The following formatting options are available: d - Numeric day of the month without a leading zero. dd - Numeric day of the month with a leading zero. ddd - Abbreviated name of the day of the week. dddd - Full name of the day of the week. h - 12 Hour clock, no leading zero. hh - 12 Hour clock with leading zero. H - 24 Hour clock, no leading zero. HH - 24 Hour clock with leading zero. m - Minutes with no leading zero. mm - Minutes with leading zero. M - Numeric month with no leading zero. MM - Numeric month with a leading zero. MMM - Abbreviated name of month. MMMM - Full month name. t - AM/PM but only the first letter. tt - AM/PM ( a.m. / p.m.) y - Year with out century and leading zero. yy - Year with out century, with leading zero. yyyy - Year with century. You can append the data value as a percentage to each label by adding the % placeholder to the "Label List Column Name" (separated by a colon). The placeholder can be surrounded by other characters. Example: append the data value as a percentage to the labels, surrounded by parentheses: ProductName:(%) produces: Chart Type: select the desired chart type: - Pie - Pie3D - Line - VerticalBar - HorizontalBar - VerticalStackedBar - HorizontalStackedBar - CombinedBarLine (combined vertical bar and line chart)   Chart Title: enter the desired Chart Title. You can optionally also specify the title color (as a 6 digit RRGGBB value) and the title font size (in points) by appending these 2 parameters separated by the pipe character. Example: Customers by Country|FFCC66|24 Chart Caption: enter the optional Chart caption to be displayed at the bottom of the chart. You can also embed HTML tags in order to format the caption text. Chart Width: enter the desired width of the Map Chart in pixels. The maximum chart width currently is 1000 pixels. Chart Height: enter the desired height of the Map Chart in pixels. The maximum chart height currently is 1000 pixels. Show every nth Label: allows to show only every nth chart data label (the default setting of "1" shows every data label) Chart Color: enter the RGB Hex value of the desired data series color. Specify one value for each data series (the values are separated by commas). Pie Charts: enter two (or more) RRGGBB hexadecimal numbers (separated by a comma) to specify the extremes of a color gradient that is used to color the pie segments. To control the gradient further, specify more colors. Chart Background Color: enter the RGB Hex value of the desired background color. The default value is FFFFFF (white). Specify colors with a 6-letter string of hexadecimal values in the format RRGGBB. For example: FF0000 = red 00FF00 = green 0000FF = blue 000000 = black FFFFFF = white Chart Legend Position: allows to control the placement of the chart legend if you display multiple data series (the chart legend is suppressed if only one data series is displayed) Chart Axis Labels: allows to add optional x and y axis labels (Bar and Line chart only). Enter the 2 labels separated by a semicolon. Example: Product;Customers Extra Google Chart Parameters: allows to append optional extra custom Google Chart API parameters (see official Google Documentation) Example: &chxt=x&chdlp=t The below example sets a horizontal red goal line at 60% (0.6) on the y-axis: &chm=h,FF0000,0,0.6,1 Search Filter(s): enter the desired data column name(s) to be used for interactive filtering. enter the name(s) of the column(s) to be filtered. enter multiple filters separated by semicolons. If you place an "@" ampersand character on front of the filter name, a combo box is used (as opposed to a text input box) which is automatically populated with the available List values. If you place an "#" character on front of the filter name, a date picker is used (as opposed to a text input box). Each filter can optionally be preset by adding the preset value as follows: @Month=April If you would like to display a �friendly" search box label instead of the data column name please append it separated by the �|� pipe symbol. Example: @Title;@ShipMonth|Monthname;Price displays the following 3 filter boxes: Search Filter Button: Enter the text of the Search Filter button (leave this field empty to suppress the button) Search Filter Reset Button: Enter the text of the Search Filter Reset button (leave this field empty to suppress the button) Example: Clear Filter Inspect SQL Query Data: this option dumps the data and labels as returned by your SQL query for inspection Page Refresh Interval: enter the optional page refresh interval in minutes (0 = no page refresh) License Key: enter your Product License Key (as supplied after purchase of the web part license). Leave this field empty if you are using the free evaluation version.