User Tools

Site Tools


excel

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
excel [2018/07/31 11:39] – [IP Data Query UI] minor changes suexcel [2024/03/07 15:46] (current) – [Troubleshooting] su
Line 1: Line 1:
 ====== Excel Query Add-In ====== ====== Excel Query Add-In ======
  
-The Excel Query Add-In allows you to query your data sources directly from Excel.+The Excel Query Add-In allows you to query your data sources directly from Excel. You can query data by using the "IP Data Query" tab or by writing data query formulas.
  
 There are some instructional videos demonstrating how to use the Excel Add-In in [[https://www.youtube.com/playlist?list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY|this playlist]]. There are some instructional videos demonstrating how to use the Excel Add-In in [[https://www.youtube.com/playlist?list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY|this playlist]].
  
-==== Getting Started ====+You can download a spreadsheet containing examples of all the formulas [[https://appstore.intelligentplant.com/nuget/downloads/IP Excel Addin example.xlsx|here]].
  
-=== Setup ===+===== Contents =====
  
-[[https://www.youtube.com/watch?v=ab0DvCzBOy4&index=2&t=0s&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY|This video]] shows how to setup the Excel Query Add-In+  * [[excel:installation-files|Installation Files]]: Download and install Excel Query Add-in 32 or 64 bit  
 +  
 +  * [[excel:getting-started|Setup]]: Help installing, running, updating and uninstalling the add-in.
  
-  - Download the Excel Query Add-In from the [[https://appstore.intelligentplant.com/|App Store]] (scroll down to the "Spreadsheets" section in the home area) +  [[excel:ui|The IP Data Query Tab]]: Help using the data query tab.
-  - Run the installer +
-  - The Excel will launch automatically (skip step 1 in "Running the Add-In")+
  
-=== Running the Add-In ===+  * [[excel:formulas|Writing Data Query Formulas]]: Help writing data query formulas.
  
-  - Open Excel or double click the "Excel Query Add-In" icon on your desktop to launch the Add-In +===== Troubleshooting =====
-  - You may be asked if you trust the Add-In, select either "Always Trust this Publisher" or "Allow for this Session" +
-  - An App Store login window will open in Excel, sign in with your App Store account +
-  - When prompted to authorize the application click "Allow Access" +
-  - Authorize your data sources by visiting [[https://appstore.intelligentplant.com/Security/Apps +
-|Authorized Applications]]. Click "Excel query Add-In" authorize any data sources you need. //Currently this must been done each time you open the Excel Add-In//+
  
-//You can download a spreadsheet containing examples of all the formulas [[https://appstore.intelligentplant.com/nuget/downloads/IP Excel Addin example.xlsx|here]].//+Local logs are available in:
  
-=== Updating ===+  {LOCALAPPDATA}/Intelligent Plant/Data Core/Excel/Logs/ExcelDataAddin.log
  
-To update the Add-In double click the "Excel Query Add-In" icon on your desktop. If a new version is available you will be prompted to download it. 
  
-=== Uninstalling === 
- 
-  - Open "Add or Remove Programs" in settings or "Uninstall a program" in "Programs and Features" in control panel. 
-  - Select "Excel Data Query Add-In" 
-  - Click "Uninstall" 
-  - Unsure the "Remove the application from this computer" option is selected 
-  - Click "Ok" 
- 
-You may start getting an error when Excel opens that says the add-in can't be found, to fix this either reinstall the add-in or remove the add-in from Excel. 
- 
-To remove the add-in from Excel: 
- 
-  - Open Excel 
-  - Dismiss the warning and open a work book 
-  - Go to File -> Options -> Add-Ins 
-  - Select "Excel Add-Ins" in the selection box at the bottom of the dialogue 
-  - Click "Go" 
-  - Deselect "Gestaltdataexcel-Addin-Packed" in the list 
-  - Click "Yes" when prompted if you want to remove the add-in from the list 
- 
- 
-==== The IP Data Query Tab ==== 
- 
-[[https://www.youtube.com/watch?v=_Q_361HDaOE&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=2|This video]] shows how to use the "IP Data Query" tab. 
- 
-=== Get Current Value === 
- 
-Clicking "Get Current Value" allows you to configure a request to get the current value for some tags.  
-  * Select the relevant data source  
-  * Enter a filter to search for tags available from that data source and click "Search" 
-  * Select the tag(s) you would like to query 
-  * Select whether you would like a header row and time stamps 
-  * Click "Add" 
- 
-This will insert the result of the query you entered at the currently selected point in the spreadsheet. 
- 
-If the data source you want to query isn't listed it may be because it's not authorized, it doesn't support tag read operations or you aren't authorized to read from the data source. 
- 
-=== Get Historical Values === 
- 
-Clicking "Get Historical Values" allows you to configure a request to get the historical values for some tags.  
-  * Select the relevant data source  
-  * Enter a query to search for tags available from that data source 
-  * Select the tag(s) you would like to query 
-  * Enter the start, end times, interval and data funciton for the query 
-  * Enter the number of points (e.g. a 10 day query with interval 1h is 240 points) 
-  * Select whether you would like a header row and time stamps 
-  * Click "Add" 
- 
-This will insert the result of the query you entered at the currently selected point in the spreadsheet. 
- 
-If the data source you want to query isn't listed it may be because it's not authorized, it doesn't support tag read operations or you aren't authorized to read from the data source. 
- 
-=== Write Data === 
- 
-Clicking "Write Data" allows you to write a value to a tag in a data source. 
- 
-  * Select a data source from the drop down  
-  * Search for the tag(s) you would like to write to 
-  * Enter the time stamp you would like to associate with the value. You can enter the time as a date, an excel formula or a relative time (e.g. "*" for now or "*-10d" for 10 days ago) 
-  * Enter the value you want to write to the tag, this can be any excel formula that produces a number 
-  * Click "Write" 
- 
-If you don't see the data source you want to write to listed it may be because it's not authorized, it doesn't support write operations or you don't have permission to write to that data source. 
- 
-=== Advanced === 
- 
-All text boxes can will be evaluated as a formula if they begin with "=" 
- 
-You can use the "Custom Tag" box to add tags that aren't listed, this is primarily useful if you want to query Alarm Analysis Meta tags. 
- 
-Deselect "Enter As Formula" if you would like the result of the query to be added directly to the sheet. Leaving it selected generates a formula as described in the "Using Data Query Formulas" section 
- 
-=== Refresh Data === 
- 
-This button will reload all data query formulas in the currently opened sheet. 
- 
-=== Refresh Selection === 
- 
-This button will reload all data query formulas in the currently selected range. 
- 
-=== Wiki === 
- 
-This button will open this wiki page. 
- 
-==== Using Data Query Formulas ==== 
- 
-The Excel Query Add-In adds multiple Excel formulas that can be used to make queries. 
- 
-The formulas added by the Add-In are Excel array formulas, this means that the result of the formula occupies multiple cells. 
- 
-Before entering a formula select the range that the results of the query should occupy (if you don't know how many results there will be select a larger area, or a whole column, otherwise the results will be truncated to fit in the selected area). 
- 
-Once you have written (or edited) a formula press Ctrl + Shift + Enter to update the effected range, if you forget to do this you will get a "You can't change part of an array" error from Excel. 
- 
-If a cell contains "#VALUE" that means that the query has been sent but hasn't been returned yet. "#N/A" means that there is no result for that cell (i.e. the range you selected is larger than the number of returned data points). 
- 
-=== Get Available Data Sources === 
- 
-[[https://www.youtube.com/watch?v=Qip6u7J4sfA&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=3|This video]] shows how to do data source and tag searches. 
- 
-You can get the data sources available to the Add-In by using the "GetDataSources()" formula. 
- 
-  * Select 2 columns (e.g. A and B) 
-  * Enter "=GetDataSources()" in the formula bar 
-  * Press **Ctrl + Shift + Enter** 
- 
-This will get a list of data sources. The left column is the human readable data source name, the right is the "Fully Qualified Name" the unique identifier for the data source, this is what should be used in the data source parameter for other queries. 
- 
-For some data sources the human readable and fully qualified names may be the same. 
- 
-=== Get Tags === 
- 
-[[https://www.youtube.com/watch?v=Qip6u7J4sfA&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=3|This video]] shows how to do data source and tag searches. 
- 
-You can get the data sources available to the Add-In by using the "GetTags(...)" formula. 
- 
-  * Select 3 a columns (e.g. A:C) 
-  * Enter "=GetTags(<data source name>, <tag query>)" in the formula bar 
- 
-This takes 2 parameters: 
-  - The fully qualified name of the data source you would like to query (you can get this using the "GetDataSources()" formula) 
-  - A search query or filter (e.g. "*" for all tags or "pmp" for all pumps) 
- 
-  * Press **Ctrl + Shift + Enter** to enter the formula 
- 
- 
-This will return a list of tag names, descriptions and units on the specified data source that match the query. 
- 
-**Examples** 
- 
-//=GetTags("My Data Source", "*") \\ 
-=GetTags(B1, C1)// 
- 
-If the data source name is in cell B1 and filter in C1. This would select the first data source if you had a "GetDataSources()" query in columns A and B. 
- 
-If no data sources are available you will get a "#VALUE" error. You can authorize data sources by visiting [[https://appstore.intelligentplant.com/Security/Apps 
-|Authorized Applications]], select "Excel Query Add-In" and ensure that the correct data sources are selected 
- 
-=== Get Snapshot Data === 
- 
-[[https://www.youtube.com/watch?v=9ST_1PZ4Iz4&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=4|This video]] shows how to use the "GetNowValue(...)" formula. 
- 
-You can get the current value of a tag by using the "GetNowValue(...)" formula. 
- 
-It's also possible to have the timestamp and header row added to the result. If you wish to include these you will need to enter a range the correct size. There will be an extra column for the timestamps and an extra row for the header (i.e. to include headers and timestamps select a 2x2 range, just timestamps would be 2x1, just header would be 1x2 and just the value would be a single cell). 
- 
-  * Select a range the correct size (e.g. A1:B2) 
-  * Enter "=GetNowValue(<data source name>, <tag name>, <include timestamps>, <include headers>)" in the formula bar 
- 
-This takes 4 parameters: 
-  - The fully qualified name of the data source you would like to query (you can get this using the "GetDataSources()" formula) 
-  - The name of the tag you want to query 
-  - Whether a time stamps column should be included 
-  - Whether a header row should be included 
- 
-  * Press **Ctrl + Shift + Enter** to enter the formula 
- 
-This will return the current value of the given tag, with headers and timestamps as specified. 
- 
-**Examples** 
- 
-//=GetNowValue("My Data Source", "Example Tag", false, false)// 
- 
-Would get the current value of "Example Tag". 
- 
-//=GetNowValue("My Data Source", "Example Tag", true, true)// 
- 
-Would get the current value of "Example Tag" with a header row and timestamp. 
- 
-=== Get Historical Data === 
- 
-[[https://www.youtube.com/watch?v=GTZDaH9phew&list=PLsVwx5rtV9c0duV5jln2IX9BIMDCCEWrY&index=5|This video]] shows how to use the "GetHistoricalValues(...)" formula. 
- 
-You can get the historical data by using the "GetHistoricalValues(...)" formula. 
- 
-It's also possible to have the timestamp and header row added to the result. If you wish to include these you will need to enter a range the correct size. There will be an extra column for the timestamps and an extra row for the header. The number of rows required for the query will depend on the start and end dates as well as the interval, for example for 2 dates 31 days apart with an interval of "1d" there will by 31 points and therefore you'll need to select 31 rows (plus 1 more if you're including a header). 
- 
-  * Select a range the correct size (e.g. A1:B31) 
-  * Enter "=GetHistoricalValues(<data source name>, <tag name>, <start time>, <end time>, <interval>, <data function>, <include timestamps>, <include headers>)" in the formula bar 
- 
-This takes 8 parameters: 
-  - The fully qualified name of the data source you would like to query (you can get this using the "GetDataSources()" formula) 
-  - The name of the tag you want to query 
-  - The start time for the query, this should be an Excel date (e.g. DATE(2018, 01, 01)) 
-  - The end time for the query, this should be an Excel date (e.g. DATE(2018, 02, 01)) 
-  - The sample interval, e.g. "1d", "1h" 
-  - The data function, one of "Interp", "Plot", "Max", "Min", "Avg" or "Raw" 
-  - Whether a time stamps column should be included,  
-  - Whether a header row should be included 
- 
-  * Press **Ctrl + Shift + Enter** to enter the formula 
- 
-This will return the current value of the given tag, with headers and timestamps as specified. 
- 
-**Examples** 
- 
-//=GetHistoricalValues("My Data Source", "Example Tag", TODAY() - 10, TODAY(), "1d", "Plot", false, false)// 
- 
-Would get the historical value of "Example Tag" for the past 10 days, without timestamps or a header row. 
- 
-==== Tips and Tricks ==== 
- 
-=== Defined Names === 
- 
-You can define a name to make it easier to use the formulas.  
-  - Copy the fully qualified name of the data source (see the "Get Available Data Sources" section for details) e.g. "My Data Source" 
-  - Open the "Formulas" tab 
-  - Click "Define Name" in the "Defined Names" section 
-  - Enter a meaning full name in the name feel e.g. "DataSource" 
-  - Enter the fully qualified name of the data source into the "Referes to:" field like this ="<data source name>" e.g. ="My Data Source" 
- 
-You can now use the name you set in place of the data source name in formulas, e.g. =GetTags("My Data Source", "*") becomes =GetTags(DataSource, "*"). This makes it easier to change the data source and saves you typing out or copying long data source names, like those used by App Store connects. 
- 
-You can also use this for any other parameters in the UI or in fomrulas. 
- 
-=== Formula Wizard === 
- 
-You can more easily enter formulas by using Excel's built in formula wizard. 
- 
-  - Open the "Formulas" tab 
-  - Click "Insert Function" 
-  - Select "GestaltDataExcel Add-In" from the category drop down, or search for the relevant formula 
-  - Select a formula from the list 
-  - Fill in the prompted function arguments 
  
  
excel.1533037148.txt.gz · Last modified: 2018/07/31 11:39 by su