Skip to main content
InSource Solutions

TN HistClient142 Using the Concatenate Function with Historian Client Workbook (Excel)

  1. Last updated
  2. Save as PDF

Description

 

This article from InSource shows how to use the Concatenate function when trying to adjust a SQL Direct Query in Microsoft Excel (Historian Client Workbook).

  • Author: Glenn Yancey
  • Published: 06/26/2020
  • Applies to: Historian Client (Workbook) version 2017 and higher (32 bit version of Excel)

Details

In Microsoft Excel (the 32-bit version at least), there is a limitation of how many characters of alphanumerical text that can exist in a cell.  We expand on this when using the Direct Query function in the Historian Client Workbook add-on.   When using direct query, we can take a large query and place that into Excel in the form of a function.   However, some queries might be more complex due to using specific cells acting as StartDate and EndDate fields.   Plus, with the use of Wide queries, we are force to use the concept of an OpenQuery, which is a query IN a query.   So when working with such a large query where we have to customize it some, we need to make sure that we can use a function to allow for more than 255 characters, and allows us to enter in and out of the query (when referencing certain parameters).    So here is a demonstration of how we can use the "=Concatenate()" function in Excel.

=CONCATENATE(parameter1, parameter2)

This function will take the text of one cell and join it with another cell to eliminate the restriction of 255 characters.   If you would like to add a space when combining two cells, use the " " parameter which will add a needed space such as shown in the following example.  When using DirectQuery, it is important at least to make sure that you choose the right tags for your wide query columns first.  The purposed of concatenating to further define additional criteria that is not possible in the DirectQuery function of Historian Client Workbook.

It is important to note that ONLY STRAIGHT QUOTES are allowed.  DO NOT USE CURLY DOUBLE QUOTES NOR CURLY SINGLE QUOTES!

=CONCATENATE(B7, " ", B8) 

This query below is my objective, but my DateTimes are coming from two cells that contain PROPERLY FORMATTED DateTime fields from cell C3 and C4.

SET QUOTED_IDENTIFIER OFF 
SELECT * FROM OPENQUERY(INSQL," 
SELECT DateTime = convert(nvarchar, DateTime, 21), [GPM-AP4_FT03], [Sequence_Past-4], [Sequence_Step_Past-4] 
FROM WideHistory 
WHERE [Sequence_Past-4] = 111 
AND [GPM-AP4_FT03] >= 180 
AND wwRetrievalMode = 'Cyclic' 
AND wwResolution = 10000 
AND wwVersion = 'Latest' 
AND DateTime >= '04/22/2020 09:00:00 PM' 
AND DateTime <= '04/22/2020 11:40:06 PM' ")

Since the cell of B8 has some parameters that I need to pull in (C3 and C4 for DateTime), I need to use Concatenate to allow me to BREAK AWAY temporarily to pull in the value of those two cells to bring back in.   

I've color-coated some text for readability.   Double Quotes show where I temporarily break out of the query to pull in a value before finishing the rest of the function.   
clipboard_e613c2113d3f94f76f81cb3d0c1d44cef.png

So the final result of B10, is to expose the total query that may go over 255 character, and execute it for the DirectQuery, which is where B10 comes into play for being the target cell where I am to store the query.  I replaced it with =CONCATENATE(B7, " ", B8) in order to contain the query in its entirety, still allowing for interactivity with DateTime Columns and further criteria.  

clipboard_e2a76c8527d1f4f4c7df63d4d296d2975.png