Search

How to retrive the unstructure data using iknow concept in Cache . Given real time Example of these concepts? Here are some articles on community about that:Free Text Search by Kyle BaxteriKnow demo apps (1, 2, 3, 4) by Benjamin DeBoe I want to take a moment here and advise you to be very careful with iKnow. iKnow is NOT a solution, it is a way for you to develop your own solutions (much like Caché and Ensemble, actually). While iKnow can give structure to your free text fields, it cannot tell you what to do with that information once you gather it. So before implementing iKnow and developing a solution, you need to know what it is you want to look for, the purpose of putting the iKnow structure on your data, and what you are going to display or show off once you get it.

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!Basic class queriesSimply put, basic class queries allow you to represent SQL SELECT queries. SQL optimizer and compiler handle them just as they would standard SQL queries, but they are more convenient when it comes to executing them from Caché Object Script context. They are declared as Query items in class definitions (similar to Method or Property) in the following way:Type: %SQLQueryAll arguments of your SQL query must be listed in the list of argumentsQuery type: SELECTUse the colon to access each argument (similar to static SQL)Define the ROWSPEC parameter which contains information about names and data types of the output results along with the order of fields(Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't need to return ID, don't assign any values to CONTAINID(Optional) Define the COMPILEMODE parameter which corresponds to the similar parameter in static SQL and specifies when the SQL expression must be compiled. When this parameter is set to IMMEDIATE (by default), the query will be compiled simultaneously with the class. When this parameter is set to DYNAMIC, the query will be compiled before its first execution (similar to dynamic SQL)(Optional) Define the SELECTMODE parameter which specifies the format of the query resultsAdd the SqlProc property, if you want to call this query as an SQL procedure.Set the SqlName property, if you want to rename the query. The default name of a query in SQL context is as follows: PackageName.ClassName_QueryNameCaché Studio provides the built-in wizard for creating class queriesSample definition of the Sample.Person class with the ByName query which returns all user names that begin with a specified letter Class Sample.Person Extends %Persistent { Property Name As %String; Property DOB As %Date; Property SSN As %String; Query ByName(name As %String = "") As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String,DOB:%Date,SSN:%String", CONTAINID = 1, SELECTMODE = "RUNTIME", COMPILEMODE = "IMMEDIATE") [ SqlName = SP_Sample_By_Name, SqlProc ] { SELECT ID, Name, DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } } You can call this query from Caché Object Script in the following way: Set statement=##class(%SQL.Statement).%New() Set status=statement.%PrepareClassQuery("Sample.Person","ByName") If $$$ISERR(status) { Do $system.OBJ.DisplayError(status) } Set resultset=statement.%Execute("A") While resultset.%Next() { Write !, resultset.%Get("Name") } Alternatively, you can obtain a resultset using the automatically generated method queryNameFunc: Set resultset = ##class(Sample.Person).ByNameFunc("A") While resultset.%Next() { Write !, resultset.%Get("Name") } This query can also be called from SQLcontext in these two ways: Call Sample.SP_Sample_By_Name('A') Select * from Sample.SP_Sample_By_Name('A') This class can be found in the SAMPLES default Caché namespace And that's all about simple queries. Now let's proceed to custom ones. Custom class queries Though basic class queries work fine in most cases, sometimes it is necessary to execute full control over the query behavior in applications, e.g.: Sophisticated selection criteria. Since in custom queries you implement a Caché Object Script method that returns the next row on your own, these criteria can be as sophisticated as you require.If data is accessible only via API in a format that you don't want to useIf data is stored in globals (without classes)If you need to escalate rights in order to access dataIf you need to call an external API in order to access dataIf you need to gain access to the file system in order to access dataYou need to perform additional operations before running the query (e.g. establish a connection, check permissions, etc.) So, how do you create custom class queries? First of all, you should define 4 methods which implement the entire workflow for your query, from initialization to destruction: queryName — provides information about a query (similar to basic class queries)queryNameExecute — constructs a queryqueryNameFetch — obtains the next row result of a queryqueryNameClose — destructs a query Now let's analyze these methods in more detail. The queryName method The queryName method represents information about a query. Type: %QueryLeave body blankDefine the ROWSPEC parameter which contains the information about names and data types of the output results along with the field order(Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't return ID, don't assign any value to CONTAINID For example, let's create the AllRecords query (queryName = AllRecords, and the method is simply called AllRecords) which will be outputting all instances of the new persistent class Utils.CustomQuery, one by one. First, let's create a new persistent class Utils.CustomQuery: Class Utils.CustomQuery Extends (%Persistent, %Populate){ Property Prop1 As %String; Property Prop2 As %Integer; } Now let's write the AllRecords query: Query AllRecords() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllRecords, SqlProc ] { } The queryNameExecute methodThe queryNameExecute method fully initializes a query. The signature of this method is as follows: ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status where: qHandle is used for communication with other methods of the query implementationThis method must set qHandle into the state which will then be passed to the queryNameFetch methodqHandle can be set to OREF, variable or a multi-dimensional variableargs are additional parameters passed to the query. You can add as many args as you need (or don't use them at all)The method must return query initialization status But let's get back to our example. You are free to iterate through the extent in multiple ways (I will describe the basic working approaches for custom queries below), but as for this example let's iterate through the global using the $Order function. In this case, qHandle will be storing the current ID, and since we don't need any additional arguments, the arg argument is not required. The result looks like this: ClassMethod AllRecordsExecute(ByRef qHandle As %Binary) As %Status { Set qHandle = "" Quit $$$OK } The queryNameFetch methodThe queryNameFetch method returns a single result in $List form. The signature of this method is as follows: ClassMethod queryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ] where: qHandle is used for communication with other methods of the query implementationWhen the query is executed, qHandle is being assigned values specified by queryNameExecute or by previous call of queryNameFetch.Row will be set either to a value of %List or to an empty string, if all data has been processedAtEnd must be set to 1, once the end of data is reached.The PlaceAfter keyword identifies the method's position in the int code . The "Fetch" method must be positioned after the "Execute" method, but this is important only for static SQL, i.e. cursors inside queries. In general, the following operations are performed within this method: Check whether we've reached the end of dataIf there is still some data left: Create a new %List and assign a value to the Row variableOtherwise, set AtEnd to 1Prepare qHandle for the next result fetchReturn the status This is how it will look like in our example: ClassMethod AllRecordsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status { #; Iterating through ^Utils.CustomQueryD #; Writing the next id to qHandle and writing the global's value with the new id into val Set qHandle = $Order(^Utils.CustomQueryD(qHandle),1,val) #; Checking whether there is any more data left If qHandle = "" { Set AtEnd = 1 Set Row = "" Quit $$$OK } #; If not, create %List #; val = $Lb("", Prop1, Prop2) see Storage definition #; Row =$lb(Id,Prop1, Prop2) see ROWSPEC for the AllRecords request Set Row = $Lb(qHandle, $Lg(val,2), $Lg(val,3)) Quit $$$OK } The queryNameClose methodThe queryNameClose method terminates the query, once all the data is obtained. The signature of this method is as follows: ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ] where: Caché executes this method after the final call to the queryNameFetch methodIn other words, this is a query destructorTherefore you should dispose all SQL cursors, queries and local variables in its implementationThe methods return the current status In our example, we have to delete the local variable qHandle: ClassMethod AllRecordsClose(ByRef qHandle As %Binary) As %Status { Kill qHandle Quit $$$OK } And here we are! Once you compile the class, you will be able to use the AllRecords query from %SQL.Statement – just as the basic class queries. Iteration logic approaches for custom queries So, what approaches can be used for custom queries? In general, there exist 3 basic approaches: Iteration through a globalStatic SQLDynamic SQL Iteration through a globalThe approach is based on using $Order and similar functions for iteration through a global. It can be used in the following cases: Data is stored in globals (without classes)You want to reduce the number of glorefs is the codeThe results must be/can be sorted by the global's subscript Static SQLThe approach is based on cursors and static SQL. This is used for: Making the int code more readableMaking the work with cursors easierSpeeding up the compilation process (static SQL is included into the class query and is therefore compiled only once). Note: Cursors generated from queries of the %SQLQuery type are named automatically, e.g. Q14.All cursors used within a class must have different names.Error messages are related to the internal names of cursors which have an additional characters at the end of their names. For example, an error in cursor Q140 is actually caused by cursor Q14.Use PlaceAfter and make sure that cursors are used in the same int routinewhere they have been declared.INTO must be used in conjunction with FETCH, but not DECLARE. Example of static SQL for Utils.CustomQuery: Query AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllStatic, SqlProc ] { } ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status { &sql(DECLARE C CURSOR FOR SELECT Id, Prop1, Prop2 FROM Utils.CustomQuery ) &sql(OPEN C) Quit $$$OK } ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ] { #; INTO must be with FETCH &sql(FETCH C INTO :Id, :Prop1, :Prop2) #; Check if we reached end of data If (SQLCODE'=0) { Set AtEnd = 1 Set Row = "" Quit $$$OK } Set Row = $Lb(Id, Prop1, Prop2) Quit $$$OK } ClassMethod AllStaticClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ] { &sql(CLOSE C) Quit $$$OK } Dynamic SQLThe approach is based on other class queries and dynamic SQL. This is reasonable when in addition to an SQL query itself, you also need to perform some additional operations, e.g. execute an SQL query in several namespaces or escalate permissions before running the query. Example of dynamic SQL for Utils.CustomQuery: Query AllDynamic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllDynamic, SqlProc ] { } ClassMethod AllDynamicExecute(ByRef qHandle As %Binary) As %Status { Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery") Quit $$$OK } ClassMethod AllDynamicFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status { If qHandle.%Next()=0 { Set AtEnd = 1 Set Row = "" Quit $$$OK } Set Row = $Lb(qHandle.%Get("Id"), qHandle.%Get("Prop1"), qHandle.%Get("Prop2")) Quit $$$OK } ClassMethod AllDynamicClose(ByRef qHandle As %Binary) As %Status { Kill qHandle Quit $$$OK } Alternative approach: %SQL.CustomResultSet Alternatively, you can create a query by subclassing from the %SQL.CustomResultSet class. Benefits of this approach are as follows: Slight increase in speedROWSPEC is unnecessary, since all metadata is obtained from the class definitionCompliance with the object-oriented design principles To create query from the subclass of %SQL.CustomResultSet class, make sure to perform the following steps: Define the properties corresponding to the resulting fieldsDefine the private properties where the query context will be storedOverride the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as wellOverride the %Next method (similar to queryNameFetch) which obtains the next result. Fill in the properties. The method returns 0 if all the data has been processed and 1 if some data is still remainingOverride the %CloseCursor method (similar to queryNameClose) if necessary Example of %SQL.CustomResultSet for Utils.CustomQuery: Class Utils.CustomQueryRS Extends %SQL.CustomResultSet { Property Id As %String; Property Prop1 As %String; Property Prop2 As %Integer; Method %OpenCursor() As %Library.Status { Set ..Id = "" Quit $$$OK } Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ] { Set sc = $$$OK Set ..Id = $Order(^Utils.CustomQueryD(..Id),1,val) Quit:..Id="" 0 Set ..Prop1 = $Lg(val,2) Set ..Prop2 = $Lg(val,3) Quit $$$OK } } You can call it from Caché Object Script code in the following way: Set resultset= ##class(Utils.CustomQueryRS).%New() While resultset.%Next() { Write resultset.Id,! } Another example is available in the SAMPLES namespace – it's the Sample.CustomResultSet class which implements a query for Samples.Person. Summary Custom queries will help you to separate SQL expressions from Caché Object Script code and implement sophisticated behavior which can be too difficult for pure SQL. References Class queries Iteration through a global Static SQL Dynamic SQL %SQL.CustomResultSet The Utils.CustomQuery class The Utils.CustomQueryRS class The author would like to thank Alexander Koblov for his assistance in writing this article. In regards to the first part of your write-up, as a developer, if you want a quick and easy way to test the query from your terminal window (i.e. Cache Terminal, PowerTerm, Putty, Reflections, etc...), then you can run the query as follows: Do ##class(%ResultSet).RunQuery({className},{queryName}) - if you have input parameters, then you would pass those values as subscripts 3 - n; Example of running query with two input parameters: Do ##class(%ResultSet).RunQuery({className},{queryName},{inputValue1},{inputValue2}) So an example of running the first query in your write-up from a terminal window might be: Do ##class(%ResultSet).RunQuery("Sample.Person","ByName","A") The output from the query might be: ID:Name:DOB:SSN:53:Adam,Ralph O.:41730:657-43-6149:33:Adam,Zoe X.:56117:982-36-6928:80:Ahmed,Edgar Q.:33719:546-61-2688:110:Alton,Umberto B.:30116:877-79-1057:146:Avery,Valery P.:39515:310-11-8847: Hope this Helps and Have a Great Day!!! Happy Coding/Testing those queries! Quick followup note - so folks don't freak out about the output from the query example - the names and birthdates and SSN's displayed in the example were from a SAMPLES testing environment where data was auto-generated at random - so the data is fake and not for real people :) Thanks! I often need to run queries from a terminal, so I extended Caché ObjectScript with zsql command to run queries and display the results. Here's how it works: zsql "SELECT TOP 2 Name FROM Sample.Person" Would output: Name Adams,Chris Z. Adams,Danielle P To achieve it I created %ZLANGC00 mac routine with the following code: ; %ZLANGC00 ; custom commands for ObjectScript ; http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSTU_customize Quit /// Execute Query and display the results /// Call like this: /// zsql "SELECT TOP 10 Name FROM Sample.Person" ZSQL(Query) #Dim ResultSet As %SQL.StatementResult Set ResultSet = ##class(%SQL.Statement).%ExecDirect(, Query) Do ResultSet.%Display() Quit Save and compile it and then you can execute sql (class queries too) in a terminal with zsql command: zsql "SELECT * FROM Sample.SP_Sample_By_Name('Z')" That said I myself prefer executing SQL queries in SMP because you don't need to type them there (drag&drop from the left panel or copy&paste from the code) - it's very convenient. I typically use the SQL shell in the terminal when doing this. It is very powerful.SAMPLES>do $system.SQL.Shell()SQL Command Line Shell----------------------------------------------------The command prefix is currently set to: <<nothing>>.Enter q to quit, ? for help.SAMPLES>>?Interactive SQL Shell (c) InterSystems Corporation 1999-2009 All Rights Reserved----------------------------------------------------To execute an SQL statement, type it in and press ENTER.To execute a multiline SQL statement, press <enter> to entermultiline statement mode, enter the each statement line andenter GO to exit multiline statement mode and execute the statement.etc...This uses %SQL.Statement to do all the work and has a number of options including saving of queries, etc. I would also recommend to try Caché Web Terminal SQL mode:This mode appeared with 4.0 version. for me personally, the greatest benefit of custom SQL queries is that you don't need any (persistent) data, you can just generate it on the fly, either computing them or bringing from any arbitrary external system. Dan Fantastic post @Eduard.Lebedyuk Alternative approach: %SQL.CustomResultSet is icing on the cake which is not covered by Intersystems documentation. It really should be. This is exactly what I needed for my current project. Thanks. 💡 The article is considered as InterSystems Data Platform Best Practice. For 2017.1CE and later as well as all IRIS versions, %SQL.CustomResultSet should not be used. Instead, use %SQL.CustomQuery. There are several good reasons for this. There is good class documentation available. I am happy to post examples if anyone is interested. Hi @Daniel.Pasco It would be great if you can share some examples with us. Best Regards,Henrique First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement. The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here. Define a new class that extends %SQL.CustomQuery; Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF; Define properties, in order, that are the columns of each row returned by this query. Let's call these "result columns". Each result column is defined as a non-private property; Define properties that you will need to maintain the source data, pointers, etc. that you will use to manage the data used to produce rows. These properties are defined as "private"; Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query; Override %FetchCursor. In this method, check for end of data. If not at the end then populate all of the result properties with data and return 1 (true). Otherwise, clear all result properties and return 0; Override %CloseCursor. In this override, release any resources acquired during instantiation and perform any necessary cleanup. I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument. The query: SELECT top 5 stateCode,name,population FROM example_custom.sample_custom_query('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7','Default') ORDER BY population DESC and the results: stateCode name population 06 California 38802500 48 Texas 26956958 12 Florida 19893297 36 New York 19746227 17 Illinois 12880580 5 row(s) affected Using this version of a custom query class: Class example.custom.JsonQuery Extends %SQL.CustomQuery { Parameter SQLNAME As String = "sample_custom_json_query"; Property data As %Library.DynamicAbstractObject [ Private ]; Property iterator As %Iterator.AbstractIterator [ Private ]; Property atEnd As %Integer [ InitialExpression = 0, Private ]; Property stateCode As %String; Property name As %String; Property population As %Integer; Method %OpenCursor(data As %String(MAXLEN="")) [ Private ] { try { if $isobject(data) { set ..data = data } else { set ..data = [].%FromJSON(data) } set ..iterator = ..data.%GetIterator() if '..iterator.%GetNext(.key,.value) { set ..atEnd = 0 set ..iterator = "" } } catch exception { // this is just a place holder, this method reports errors by throwing an exception // but a catch can allow the user to log errors or perform some self-healing action throw exception } } Method %FetchCursor() As %Library.Integer { set response = 0 if ($isObject(..iterator)) && ('..atEnd) { if ..iterator.%GetNext(.key,.value) { set ..name = value.%Get(0) set ..population = value.%Get(1) set ..stateCode = value.%Get(3) set response = 1 } else { set ..atEnd = 1 set ..iterator = "" } } else { set ..name = "" set ..population = "" set ..stateCode = "" } return response } Method %CloseCursor() [ PlaceAfter = %Next, Private ] { // not really necessary as %OnClose will automatically close the cursor during destruction // but users can place code here to clean up other resources allocated for this query instance // that are external to the query instance. Like a temporary global. set ..iterator = "" set ..data = "" } } and this query SELECT top 5 stateCode,name,population FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]') ORDER BY population DESC produces the same result: stateCode name population 06 California 38802500 48 Texas 26956958 12 Florida 19893297 36 New York 19746227 17 Illinois 12880580 5 row(s) affected I am happy to post other examples if you wish. Dan Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet. USER>set result = ##class(example.custom.Query).%New(,"https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7","Default") USER>write result.%Next() 1 USER>write result.name Alabama USER>write result.population 4849377 USER>while result.%Next() { write !,result.name,": ",result.population } Alaska: 736732 Arizona: 6731484 Arkansas: 2966369 California: 38802500 Another unknown feature that isn't mentioned anywhere else. I am a fan Robert...but... https://community.intersystems.com/post/new-video-sql-%E2%80%93-things-you-should-know Somewhere around the 36 minute mark of this video. Buried perhaps. Still, %SQL.CustomQuery has a lot of interesting capabilities. Incredible! I really missed something important!@Daniel.Pasco Thanks for the pointer. I never stop learning. This is a great feature Dan! I'm using it to write SQL queries to Pandas dataframes in Python (code). Some of the reasons why I focus on utilizing class queries include Studio and other editors are much better at providing coloring/syntax checking vs a strategy of setting a tSQL string as some arbitrary SQL statement As mentioned in the original post it provides the separation that allows for easy re-use and testing. If I have a class query I can decide to allow it to be reflected as a stored procedure, I can expose it to ZEN reports, ODBC/JDBC, JSON projection, %XML.DataSet, I can use it to satisfy a control in ZEN that allows for binding to a class query, as well as others. Basically, it provides for great separation. I also like the idea of considering writing the statement using SQL as %Query. If for some reason I run into performance issues that I cannot overcome I can change the implementation to %SQLQuery and implement COS code, the calling application would see the improved performance but does not have to understand the internal implementation. However, I've only done this on extremely rare occasions. I think one of the most important aspects of SQL to always read the query plan, it's there where you can understand what the optimizer is going to do. I care all about the query plan and almost always validate what it reports. Having a class query allows for easy Show Plan examination whereas it's generally hard to do if you take the approach of setting a tSQL string(sometimes with concatenation over several lines). Class Queries are really worth investing in IMHO.

Hi, Community!Today, the 7th of December is the very good day - today is InterSystems Developer Community birthday!One year ago this day we sent tons of email invitations to you, dear members, to join DC.And you joined and hope you like the site!Please feel free to provide your feedback! Please provide only positive below in the comments, for the negative ones we have this page.So, what do we have after a year?How large are we?Here is the picture:We have 2,200 registered members from all over the world, a set of really helpful articles and the working Q&A forum.We have the site which gives us knowledge, experience and sometimes fun!Thank you for your contribution and participation!We can make the place better, please share your ideas too.We have great plans for the next year in Developer Community, stay tuned! Congratulations! Congratz Gratz! Congrats!!the articles and discussions are very useful for all levels of Caché developers. I find it interesting, when I think of Dec 7, I don't think of the Developers Community, I think of the sneak attach on Pearl Harbor, but, maybe I am just getting old. You asked for ideas, one thing I asked for a long time ago and still think it is needed is a Help function on how all of this works. It is not obvious to be sure. I must be getting old also... Actually that is an event that should always be remembered. Good point, Mike! Thank you! We'll do soon. A day that shall live in infamy. You're not old, just an American. I actually baked a cake to commemorate this occasion, and totally not because I'm fat and wanted cake. Hard to believe it's already been a year

Hello, Is there some way to reproduce the SQL GROUP_CONCAT(http://sql.sh/fonctions/group_concat) with the Caché DB ?Thanks Sébastien Exactly that :) Thanks a lot. Strange but with this function the perfermance are really bad :(0.04s without and 13s with it ... If you want to investigate the performance of the query you can always open a WRC Case (wrc.intersystems.com :-D).Otherwise, send out your class definition and query plan and I can take a cursory look at it. I tried with an other table with just 2 properties ( but more than 1million records too) and that's the same problem.see:http://imgbox.com/Df2WlAPohttp://imgbox.com/tEFMR5V8 Looks like, it works exactly as LIST function in Caché Looking at that documentation, one difference between LIST and GROUP_CONCAT is that GROUP_CONCAT lets you specify the separator, while LIST always uses a comma. If you wanted to use a different separator, and your data will never contain commas, then it's as easy as (for example): select home_city as "City", count(*) as "Count", REPLACE(LIST(Name),',',' ') as "Names" from sample.person group by home_city If "your data will never contain commas" is a bad assumption (as it is in the case of Name in Sample.Person), the solution is to use %DLIST and $ListToString. select home_city as "City", count(*) as "Count", $ListToString(%DLIST(Name),' ') as "Names" from sample.person group by home_city %DLIST builds a $ListBuild list, and $ListToString joins the list elements with the specified separator. %DLIST is useful in other cases too - for example, if your data might contain commas and you want to iterate over the aggregated data after running dynamic SQL.

In Episode 6 of Data Points, certification manager Jamie Kantor joins the podcast to tell us all about the InterSystems Certification program, what exams are currently being offered (and what ones are in development), and why it matters for developers and enterprises. Check it out! For more information and to subscribe on your favorite podcast app, head over to https://datapoints.intersystems.com. There, you can also find links to the previous episodes. Make sure to rate the podcast if you're using Apple Podcasts, as well!

Now, It is possible expose your persistent classes as OData REST services in seconds. See my app: https://openexchange.intersystems.com/package/OData-Server-for-IRIS. If you like vote in my app on: https://openexchange.intersystems.com/contest/current. If I have votes, I promise add more features. Help me! I don't have votes yet. #IRISContest

PDF version: https://github.com/yurimarx/iris-periodic-table/raw/master/periodic%20table%20iris.pdf GIT sources: https://github.com/yurimarx/iris-periodic-table InterSystems IRIS is a Data Platform with a lot of features. These features and relevant topics about IRIS is represented in the periodic table elements. A nice colored table but I miss almost all internal used languages: COS is hidden as "Obs", BUT: No BASIC, NO MultiValue, No HTML, No T-SQL / ISQL, no sign of any kind of Networking Also, Globals as THE core store technology seems to be lost. Thanks @Robert.Cemper1003 I wiil improve the table with your tips @Robert.Cemper1003 I improved the table. See if is good now. Thanks your contribution! @Yuri.Gomes Thanks for the quick improvement!Question:Is it possible to have some link behind the boxes ?What I have in mind is a fast directory into Documentation, Subjects in DC, Learning & Training, ...If all 3 three (or more) have it in common as a starting point,then search for help and information could become quite easier. Great idea! I will create a mapped image to allows click in the element and go to the documentation. Thanks, Robert! Will add a few corrections: COS stands for Caché ObjectScript. Now the name of language is InterSystems ObjectScript, so Obs is OK There is no BASIC anymore in IRIS (I think). Globals! This is a must. Yuri! What a beautiful idea! Thank you! If these boxes could be clickable and point to documentation/tag would be a nice features table! Ready for first column! It is clickable. During today I will create for all elements. newSpeak [Orwell 1984} : ISOS - accepted . or ISCOS ? pls. not IOS !! Not that I used BASIC more than for a quick 5 line demo. It is still visible in Studio. the most remarkable feature: it compiles directly to .obj code without touchable intermediate code (.INT) SUPER! works excellent! A strong improvement for Docs. Finally a serious follower to the FeatureMap we have seen last on C/E 2015.2 https://cedocs.intersystems.com/ens20152/csp/docbook/featuremapCache.csp Nice sample. This give me an idea to create a treemap mixed with heatmap as a web app. Wow! So fast! Thank you, Yuri! Wow! That's great! How to turn a png file into a web app idea Hi Yuri, The PDF link doesn't work. Link adjusted now. Thanks to report me. This is so cool! Thanks @Joseph.Lichtenberg! It's a fantastic idea. Remember old times. Thanks Andre Very Nicely presented, It shows your passion on Intersystem. KUDOS Thanks!

I am pleased to announce the availability of InterSystems Container Registry. This provides a new distribution channel for customers to access container-based releases and previews. All Community Edition images are available in a public repository with no login required. All full released images (IRIS, IRIS for Health, Health Connect, System Alerting and Monitoring, InterSystems Cloud Manager) and utility images (such as arbiter, Web Gateway, and PasswordHash) require a login token, generated from your WRC account credentials. The WRC Distributions site will continue to provide released images as tarballs for the time being. However, you can now configure your CI/CD pipelines to ‘docker pull’ images directly from InterSystems Container Registry. The registry can be accessed at https://containers.intersystems.com. Please refer below or to the documentation (Using the InterSystems Container Registry) for full usage instructions. If you run into any issue or have any feedback to share please let us know in the comments below, or contact support@intersystems.com. -------------------------------------------------------------- Using the InterSystems Container Registry This document provides instructions for using InterSystems Container Registry (ICR), located at containers.intersystems.com. Images in the ICR can be downloaded with the docker pull command, for example: docker pull containers.intersystems.com/intersystems/iris-community:2020.4.0.547.0 For a full listing of all available images, please refer to Container Images Available from InterSystems This document contains the following sections: Authenticating to the ICR Listing the ICR Inventory Authenticating to the ICR To log into the ICR, take the following steps: Load https://containers.intersystems.com/ in your browser and log in with your InterSystems/WRC credentials. Retrieve your Docker login token, or the full login command. In your Docker interface (for example, your PowerShell window or Linux command line), authenticate to the ICR using the provided credentials. You can do this by copying and pasting the full docker login command displayed, for example: docker login -u="bbinstoc" -p="provided_password" containers.intersystems.com For security reasons, however, you may want to instead enter the command docker login containers.intersystems.com, then enter your username at the Username prompt and paste your password into the Password: prompt. Note: If you are logged into another Docker registry, the docker login command may result in an error; log out of the other registry before logging into containers.intersystems.com. You can now pull images from the ICR, for example: docker pull containers.intersystems.com/intersystems/iris:2020.4.0.547.0 Listing the ICR Inventory APIs are available to list images and tags in a Docker registry. An example of an open source third-party utility that can be used to list a registry’s inventory is docker-ls, available at https://github.com/mayflower/docker-ls. There are several ways to obtain this utility. You can: Download precompiled docker-ls binaries for a variety of platforms. Install the utility directly on some platforms, for example on Linux systems with the command sudo snap install docker-ls Pull and run the image carinadigital/docker-ls:latest on Linux platforms to install the utility, for example: docker run --rm carinadigital/docker-ls:latest Once docker-ls is installed, you can use the following command to list the repositories in the ICR: docker-ls repositories --registry https://containers.intersystems.com --user username --password password Note: Use the --interactive-password option to be prompted for the password rather than including it on the command line. To list only the publicly available images, provide empty strings ("") as arguments to the --user and --password options, for example, the following lists only the tags of public InterSystems IRIS for Health images: docker-ls tags --registry https://containers.intersystems.com --user "" --password "" intersystems/irishealth-community If you wish to see the full list of non-public images, you will need to provide your username and password to this utility regardless of whether you are logged into containers.intersystems.com. Further examples are available at https://github.com/mayflower/docker-ls Error response from daemon: Get https://containers.intersystems.com/v2/: unauthorized: BAD_CREDENTIAL Hi, it's possible you're still logged into another registry. Can you try a 'docker logout' prior to attempting to access containers.intersystems.com? I have logged out and resume to login, the message is: WARNING! Using --password via the CLI is insecure. Use --password-stdin.Error response from daemon: Get https://containers.intersystems.com/v2/: unauthorized: BAD_CREDENTIAL Hi Abdullah,We needed to link your user account with your organization.That should be all set now, please let us know if you have any further issues. Error response from daemon: Get https://containers.intersystems.com/v2/: unauthorized: BAD_CREDENTIAL Even after another docker logout? Please reach out to the WRC to help with your authentication issue. thank you a lot, it is succeeded and I would like to send you the message: WARNING! Using --password via the CLI is insecure. Use --password-stdin.WARNING! Your password will be stored unencrypted in /root/snap/docker/471/.docker/config.json.Configure a credential helper to remove this warning. Seehttps://docs.docker.com/engine/reference/commandline/login/#credentials-store Login Succeeded But I have another Problem as I have done: docker-compose build: Step 1/16 : FROM containers.intersystems.com/intersystems/webgateway:2020.2.0.211.0ERROR: Service 'webserver' failed to build : manifest for containers.intersystems.com/intersystems/webgateway:2020.2.0.211.0 not found: manifest unknown: The named manifest is not known to the registry. I found that installing using "snap" installs a older version that does not support using the results of a previous 'docker login' command. Getting the latest version from https://github.com/mayflower/docker-ls/releases works. For those of you that like GUI tools in an IDE, I just installed the Microsoft Docker extension for VS Code. It shows my Containers and Images just like Docker Desktop Dashboard does, and lets me start/stop/launch CLI/inspect etc. But the real reason I installed it is because there's also a Registries section with a Connect Registry icon (looks like a plug). Using that, I chose "Generic Docker Registry" (other choices: Azure, Docker Hub, GitLab). I supplied the registry URL. It prompted me for my username and password (Docker login token as described above) and I can now browse the registry. Beautiful! I have this error in my WSL:~$ docker pull containers.intersystems.com/intersystems/webgateway:2021.1.0.205.0Cannot connect to the Docker daemon at unix:///var/run/docker.sock. Is the docker daemon running? I am so sorry, that's because I haven't run Docker yet I got that every time unless I did sudo for the docker pull. Steven, I'm getting this even after doing a docker logout too. Same error, BAD_CREDENTIAL. I see there's a comment above about linking an account to an organization. Do I need to do that? Hi David, yes I believe you'll need to contact the WRC to make sure that your email/login is linked to a supported organization, as that's required in order to access non-community images in the registry. You can contact support@intersystems.com and they can help straighten this out for you. If you are otherwise intending to access Community Edition images, you shouldn't need to log in at all. In Linux, create a docker group and add your user to that. This should enable you to do docker without sudo.

Images for other languages are often build using multistage build process. What about InterSystems IRIS? Consider this Dockerfile: FROM irishealth-community:2020.4.0.524.0 AS builder # Load code into USER and compile # Adjust settings, etc. FROM irishealth-community:2020.4.0.524.0 # replace in standard kit with what we modified in first stage COPY --from=builder /usr/irissys/iris.cpf /usr/irissys/. COPY --from=builder /usr/irissys/mgr/IRIS.DAT /usr/irissys/mgr/. COPY --from=builder /usr/irissys/mgr/user/IRIS.DAT /usr/irissys/mgr/user/. The advantage of this approach is the image size.The disadvantage is that on a final stage developer must know/remember all the modified places in the builder image.But otherwise is this approach OK for InterSystems IRIS?Have anyone tried to build IRIS images this way? We build IRIS with ZPM this way, and the reason was to shrink the final size But the best you achieve if you will not change anything in the system database, which is quite big, and it makes no sense in the end. Thank you! Why do you map %ZLANGC00/%ZLANGF00 to %ALL? They are available everywhere by default. The reason was to place them in the particular database, out of the system database. You may use this at init time https://openexchange.intersystems.com/package/helper-for-objectscript-language-extensions to add your ZPM command and function. It does a unique insert of the label For a simple case (add a couple of classes to the USER namespace) - what would be space savings here? Interesting question! Here are my findings for `store/intersystems/irishealth-community:2020.4.0.524.0` and a simple app in the `USER` namespace. | | Uncompressed (Mb) | Compressed (Mb) | |--------------------|--------------------|-----------------| | IRIS | 3 323 | 897 | | IRIS Squashed | 3 293 | 893 | | App | 8 436 | 1 953 | | App MSB | 3 526 | 937 | | App Squashed | 3 576 | 931 | | App MSB + Squashed | 3 363 | 904 | Notes: - MSB means [Multi Stage Build](https://docs.docker.com/develop/develop-images/multistage-build/) - Squashed means that an image was built with a [--squash option](https://github.com/docker/docker-ce/blob/master/components/cli/docs/reference/commandline/build.md#squash-an-images-layers---squash-experimental) - Uncompressed size is calculated via `docker inspect -f "{{ .Size }}" $image` - Compressed size is calculated via `(docker manifest inspect $image | ConvertFrom-Json).layers | Measure-Object -Property size -Sum` - More info on calculating image sizes is available [here](https://gist.github.com/MichaelSimons/fb588539dcefd9b5fdf45ba04c302db6) **Conclusion**: either MSB or Squashed work fine, but just MSB would be better for storage as it can have shared layers (squash always produces one unique layer). Squashed is easier to implement. Super benchmark, it's very interesting.Personally, I prefer the squash method because it is easy to implement (no change in the dockefile). I won't recommend using squashing, as it makes caching image layers useless. Have a look at this nice tool, which may help to discover why your final image is so big. btw, one thing I noticed working on macOS, is that the final image much bigger than expected, while the same Dockerfile on Linux, produces an image of a reasonable size. You might want to add /usr/irissys/csp/bin/CSP.ini to the list of files to copy. Without it chances are CSP Gateway would not be authorized to communicate to IRIS.

Hello everyone, let me introduce, one of my latest projects. It is a DataSource plugin for Grafana, which can connect directly to InterSystems IRIS and gather any data (in the future). Features Can show SAM metrics with periodic update, with a history, the metrics gathered by Grafana directly and only when requested while displayed Display messages.log and alerts.log Application errors from ^ERRORS global Features that can be added later Any SQL SELECT query for tables with or without DateTime fields View some data directly from any Global Call for any custom SQL Query on the IRIS side Even probably MDX Queries So, if you have some custom logic for logging within your application, it would be possible to connect Grafana to these logs and display it there. Testing To test it by yourself, you can clone the repo, and start the environment with docker-compose. The docker-compose environment is configured on using ports 3000, 3081, 3082; if those ports already in use in your system, just change them in the docker-compose.yml file. git clone https://github.com/caretdev/grafana-intersystems-datasource.git cd grafana-intersystems-datasource docker-compose up -d After pulling images, it will start Grafana and IRIS in two containers. Open Grafana by link http://localhost:3000/ Go to DataSources, it will have InterSystems IRIS connection, added by autoprovision. Diving inside will give a simple form with basic settings, and the Test button, to check the connection. When IRIS will start it should show green OK. Let's create some Dashboard and Panel Select Query Type: Metrics Let's select iris_db_latency for instance By default update interval is depends on a selected time interval, but can be changed in Query options, field Min Interval Log Files and Application Errors can be shown with Logs Vizualization and as a Table Please vote for the project You can contact me if you would like to get more functionality in the plugin. Btw, this project is written in Go, and uses a freshly developed go-irisnative project, as a connector to IRIS. With Go I can read and change data directly in globals, execute SQL, and work with objects. Some example of code in Go package main import ( "fmt" "os" "strings" "github.com/caretdev/go-irisnative/src/connection" ) func main() { var addr = "localhost:1972" var namespace = "%SYS" var login = "_SYSTEM" var password = "SYS" connection, err := connection.Connect(addr, namespace, login, password) if err != nil { println("Connection failed:", err.Error()) os.Exit(1) } defer connection.Disconnect() // Kill ^A connection.GlobalKill("A") // Set ^A(1) = 1 connection.GlobalSet("A", 1, 1) // Set ^A(1, 2) = "test" connection.GlobalSet("A", "test", 1, 1) // Set ^A(1, "2", 3) = "123" connection.GlobalSet("A", 123, 1, "a", 3) // Set ^A(2, 1) = "21test" connection.GlobalSet("A", "21test", 2, 1) // Set ^A(3, 1) = "test31" connection.GlobalSet("A", "test31", 3, 1) var globalFull = func(global string, subs ...interface{}) string { return fmt.Sprintf("^A(%v)", strings.Trim(strings.Join(strings.Split(fmt.Sprintf("%+v", subs), " "), ", "), "[]")) } var queryGlobal func(global string, subs ...interface{}) queryGlobal = func(global string, subs ...interface{}) { for i := ""; ; { if hasNext, _ := connection.GlobalNext("A", &i, subs...); !hasNext { break } var allSubs = []interface{}{i} allSubs = append(subs, allSubs...) hasValue, hasSubNode := connection.GlobalIsDefined("A", allSubs...) if hasValue { var value string connection.GlobalGet("A", &value, allSubs...) fmt.Printf("%v = %#v\n", globalFull("A", allSubs...), value) } if hasSubNode { queryGlobal("A", allSubs...) } } } queryGlobal("A") }

> InterSystems Reports is powered by Logi Report (formerly named JReport), a product of Logi Analytics. InterSystems Reports is supported by InterSystems IRIS and InterSystems IRIS for Health. It provides a robust modern reporting solution that includes: > > - Embedded operational reporting which can be customized by both report developers and end users. > - Pixel-perfect formatting that lets you develop highly specific form grids or other special layout elements for invoices, documents, and forms. > - Banded layouts that provide structure for aggregated and detailed data. > - Exact positioning of headers, footers, aggregations, detailed data, images, and sub-reports. > - A variety of page report types. > - Large-scale dynamic report scheduling and distribution including export to PDF, XLS, HTML, XML, and other file formats, printing, and archiving for regulatory compliance. > > InterSystems Reports consists of: > > - A report designer, which provides Design and Preview Tabs that enable report developers to create and preview reports with live data. > - A report server which provides end users browser-based access to run, schedule, filter, and modify reports. From [InterSystems documentation](https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GISR_intro). This article focuses on the Server part of InterSystems Reports and provides a guide on running Report Server in containers while persisting all the data. # Prerequisites Before we start, this software must be available for the InterSystems Reports to work: - [Docker](https://docs.docker.com/engine/install/) - while InterSystems Reports can work *without* Docker, this article focuses on Dockerised setup. - (Optional) [git](https://git-scm.com/book/en/v2/Getting-Started-Installing-Git) - to clone this repo, otherwise [download it as an archive](https://github.com/eduard93/reports/archive/refs/heads/master.zip). - (Optional) [InterSystems Reports Designer](https://wrc.intersystems.com/) - to create new reports if desired. Additionally, you'll need: - Login on [containers.intersystems.com](https://containers.intersystems.com) Docker registry - InterSystems Reports License (contact InterSystems for it) # Configuration Before we start, here's what we're going to do: - First, we are starting Reports and IRIS in setup mode to setup IRIS as a database (not DataSource!) for Reports. - After that, we are configuring Reports and persisting this configuration on the host. - Finally, we are running Reports with persisted data. # First start Let's go. Note that all steps here - 1-8 use `docker-compose_setup.yml` as a docker-compose configuration file. All additional docker-compose commands during these steps must be run as `docker-compose -f docker-compose_setup.yml`. 1. Clone this repo: `git clone https://github.com/eduard93/reports.git` or download an [archive](https://github.com/eduard93/reports/archive/refs/heads/master.zip). 2. Edit `config.properties` and specify your InterSystems Reports Server license information (User and Key). If you don't have them - contact InterSystems. There are many other properties described in the [documentation](https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GISR_server). Note that IRIS, in that case, refers to the database for Reports and not the data source for reports (which comes later). 3. Start InterSystems Reports Server with initialization: `docker-compose -f docker-compose_setup.yml up -d` 4. Wait for InterSystems Reports Server to start (check with `docker-compose -f docker-compose_setup.yml logs reports`). It can take 5-10 minutes. Reports Server is ready for work when logs show: `reports_1 | Logi Report Server is ready for service.` 5. Open [Reports Server](http://localhost:8888). (User/pass: `admin`/`admin`). In a case, it shows an expired window enter the same license info again. It should look like this:  # Persisting configuration Now that Reports is running, we need to adjust configuration a little and persist it on a host (note that InterSystems IRIS part of a configuration is persisted using [Durable %SYS](https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=ADOCK#ADOCK_iris_durable). 6. Check `Enable Resources from Real Paths` option in the `server console` > `Administration` > `Configuration` > `Advanced` page. [Docs](https://devnet.logianalytics.com/hc/en-us/articles/1500009750141-Getting-and-Using-Resources-from-a-Real-Path). It would allow us to publish reports as simple as copying them into the `reports` folder in the repository.  7. Copy persistent storage files to host ([docs](https://hub.docker.com/r/logianalytics/logireport-server)): ``` docker cp reports_reports_1:/opt/LogiReport/Server/bin . docker cp reports_reports_1:/opt/LogiReport/Server/derby . docker cp reports_reports_1:/opt/LogiReport/Server/font . docker cp reports_reports_1:/opt/LogiReport/Server/history . docker cp reports_reports_1:/opt/LogiReport/Server/style . ``` 8. Shutdown InterSystems Reports Server: `docker-compose -f docker-compose_setup.yml down` # Second start Now we're ready to start Reports with persisted data storage - this is how it would run in production. 9. Start InterSystems Reports Server without initialization: `docker-compose up -d` 10. Create a new folder resource in `Public Reports` with Real Path: `/reports`. [Docs](https://devnet.logianalytics.com/hc/en-us/articles/1500009750141-Getting-and-Using-Resources-from-a-Real-Path). To do that open `Public Reports` and select `Publish` > `From Server Machine`:  Create a new folder pointing to `/reports`:   It should contain a catalog (which defines a connection to IRIS) and two reports (`reportset1` and `reportset2`). Run them (use `Run` button to see it in a browser and `Advanced Run` to choose between HTML, PDF, Excel, Text, RTF, XML, and PostScript formats). Here's what reports look like:   As you can see, Reports supports Unicode out of the box. In this example, I'm using the same IRIS as a data source, but in general, it can be any other IRIS instance - as defined in a catalog. This demo uses the `HoleFoods` dataset (installed with `zpm "install samples-bi"`). To add new connections, create a new catalog in Designer. After that, create new reports and export everything in a new subfolder in a `reports` folder. Of course Server container must have network access to any data source IRIS instance. That's it! Now, if you want to stop Reports, execute: `docker-compose stop`. And to start Reports again execute: `docker-compose up -d`. Note that all reports are still available. # Debugging All logs are stored in `/opt/LogiReport/Server/logs` folder. In a case of errors, add it to volumes, restart Reports and reproduce the error. Documentation describes how to adjust [log levels](https://documentation.logianalytics.com/rsg17u1/content/html/config/config_log.htm?Highlight=logging). If Reports doesn't exactly get to the UI adjust `LogConfig.properties` file located in the `bin` folder: ``` logger.Engine.level = TRIVIAL logger.DHTML.level = TRIVIAL logger.Designer.level = TRIVIAL logger.Event.level = TRIVIAL logger.Error.level = TRIVIAL logger.Access.level = TRIVIAL logger.Manage.level = TRIVIAL logger.Debug.level = TRIVIAL logger.Performance.level = TRIVIAL logger.Dump.level = TRIVIAL ``` # Embedding and APIs To embed reports in your web application, use [Embedded API](https://documentation.logianalytics.com/logiinfov12/content/embedded-reports-api.htm). Other [available APIs](https://documentation.logianalytics.com/logireportserverguidev17/content/html/api/wkapi_srv.htm). # Summary InterSystems Reports provides a robust modern reporting solution with embedded operational reporting. InterSystems Reports Server provides end users browser-based access to run, schedule, filter, and modify reports. InterSystems Reports Server can be efficiently run in a Docker environment. # Links - [Repository](https://github.com/eduard93/reports) - [Documentation](https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GISR_server) - [Logging](https://documentation.logianalytics.com/rsg17u1/content/html/config/config_log.htm?Highlight=logging) 💡 This article is considered as InterSystems Data Platform Best Practice. Thank you for taking the time to make such a clear tutorial!

Hey Developers, Are you ready for the new challenge? We're pleased to announce the first InterSystems technical article writing competition: 🏆 InterSystems Tech Article Contest 🏆 Write an article on any topic related to InterSystems technology from July 15 to August 15 August 22 – extended! Prizes for everyone: Everyone who publishes an article on DC during this period will receive a special prize pack! Main Prize: Apple iPad Join our new contest and your content will be seen by over 55K monthly readers! Details below. Prizes 1. Everyone is a winner in InterSystems Tech Article Contest! Any user who writes an article during the competition period will receive special prizes: 🎁 unique Developer Community Hoody 🎁 InterSystems sticker 2. Expert Awards – articles will be judged by InterSystems experts: 🥇 1st place: Apple iPad 128GB 🥈 2nd place: Amazon Kindle 8G Paperwhite 🥉 3rd place: Nike Utility Speed Backpack Or as an alternative: Raspberry Pi 4 8GB with InterSystems IRIS Community Edition ARM installed 3. Developer Community Award – article with the most likes. The winner will have an option to choose one from the following prizes: 🎁 Nike Utility Speed Backpack 🎁 Amazon Kindle 8G Paperwhite 🎁 Raspberry Pi 4 8GB with InterSystems IRIS Community Edition ARM installed Who can participate? Any Developer Community member, except for InterSystems employees. Create an account! Contest Period 📝 July 15 - August 22: Publication of articles on the Community and voting time. Publish an article(s) throughout this period. DC members can vote for published articles with Likes – votes in the Community award. Note: The sooner you publish an article(s), the more time you will have to collect Likes. 🎉 August 23: Winners announcement. What are the requirements? ❗️ Any article written during the contest period and satisfying the requirements below will automatically enter the competition: The article must be related to InterSystems technology The article must be in English The article must be 100% new (it can be a continuation of an existing article) The article should not be plagiarized or translated (translations of your own DC articles from another language are allowed) Article size: >1,000 characters Team size: individual (multiple entries from the same author are allowed) What to write about? ❗️ You can choose any tech topic related to InterSystems technology. Here're some possible fields for choosing the article topic. These are just examples, you have the liberty to choose anything you want. # Topic Details 1 Embedded Python Introduction Embedded Python is an exciting new feature of InterSystems IRIS allowing developers to write methods, SQL procedures and more in Python. 2 Embedded Python from Interoperability Explore how Embedded Python can be leveraged from an Interoperability production. 3 Embedded Python: Translating by Language Constructs While we aim for seamless Embedded Python integration there are some tips & tricks to smooth things over. Underscore methods, dictionaries, lists and others. What are the best ways of calling Python features from ObjectScript? 4 Intro to InterSystems Reports Designer Continuation of this article. This article should cover: Catalog creation Creation of the basic report types, namely Chart (bar, pie, line, gauge, heatmap, ...) Table (summary and detailed) Crosstab Publishing Reports to Reports Server Creating a schedule A good tutorial to start with: Getting Started with InterSystems Reports 5 Calling Reports from Interoperability/IRIS An article describing how to execute (and get) InterSystems Reports Report from IRIS on from Interoperability Production. 6 Map Reports with InterSystems An article describing how to build InterSystems Reports Report with geospatial data. HoleFoods dataset contains locations for transactions which you can use. 7 How to do CI/CD with InterSystems IRIS – 8 Change Data Capture with Kafka Connect An example that shows how to set up Kafka Connect and export&import SQL data via the Kafal Connect JDBC connector. 9 Applying analytics / ML to the SQL Statement Index – 10 My favourite maintenance tasks, automated – 11 Leveraging the Audit database – 12 The three steps to set up GitHub Actions that make your app invincible – 13 OAuth2 authorization in IRIS instance – 14 Setup mirroring on K8s – 15 Using %MDX and %KPI instead of Subject Area in IRIS Analytics – 16 Trying External Language Gateways / compare to the gateways of old Example 17 Streaming events to Kafka from IAM – 18 IntegratedML walkthrough – 19 Exporting requests to Excel using Python – 20 Integrating cloud services with productions e.g. MS Azure Cognitive Services or Amazon Rekognition. 21 Working with IKO – 22 IKO IRIS on AWS Kubernetes with Hugepages – 23 Incorporating backups with IKO – 24 IKO – Create a cluster with compute nodes, SAM, and no sharding Include the CPF file to set up our best practices. 25 Data Science shared workgroup setup with ECP There is a data server and each data scientist has a compute node on their desktop. Show the data is available when disconnected and syncs when you re-connec. 26 Article discussing storage options for cloud deployments (performance difference between local storage, block storage, etc) and trade-offs (you might not need mirrors if using block storage, etc.) – 27 Building IRIS images with Docker Build Mounts Details 28 InterSystems IRIS CUDA image There's a way to use GPUs/CUDA from inside the container. Describe how to build an InterSystems IRIS image with CUDA support. Note: Articles on the same topic from different authors are allowed. Feel free to submit your topic ideas in the comments to this post. So, We're waiting for your great articles! Good luck and let the power of Pulitzer be with you! ✨ Article idea: Building IRIS images with Docker Build Mounts From the docs on Docker Build Mounts: RUN --mount allows you to create mounts that process running as part of the build can access. This can be used to bind files from other part of the build without copying, accessing build secrets or ssh-agent sockets, or creating cache locations to speed up your build. This is a recent Docker feature allowing users to build compact images (since we no longer need to COPY everything inside). Try it out and write an article about it? Article idea: InterSystems IRIS CUDA image There's a way to use GPUs/CUDA from inside the container. Describe how to build an InterSystems IRIS image with CUDA support. Hey Developers,Each article will bring you points on Global Masters, and you also could earn some of related badges:✅ Badges for Number of Articles: 1st / 5 / 10 / 25 / 50 articles✅ Badges for Number of Votes (votes for all your DC posts in sum): 50 / 100 / 500 / 1000 votes✅ Badges for Number of Views: 750 / 2,000 / 5,000 / 15,000 viewsCheck these badges and how much points they can bring in this article. Hey DC members, The InterSystems Tech Article Contest started today! 🤩🤩 Can't wait for your great articles! 📍 Start here: https://community.intersystems.com/contests/1 Hey Community, We've implemented a new feature in our DC editor so that you can easily track the amount of content in your article! Please welcome: Word & character counter When you type any text on the post creation page, DC Editor reads all words and characters and automatically displays them in the lower right corner. p.s. Counter works only for WYSIWYG format. Enjoy! ✨ Hi Community! If you do not have a topic to write an article on, you can view the topics that we offer above in this post. Good luck and stay tuned! Hey Community! We are waiting for your great articles! Participate and win Apple iPad 128GB for the first place! Hey DC members, The InterSystems Tech Article Contest сontinues!🚀 We look forward to your articles! Hey DC members, 5 new articles are already in the game! 🤩 Visit our contest page to support the participants with Likes = votes in the Community Award! 📍 https://community.intersystems.com/contests/1 Wow!! 👏👏I love this contest!! 😁 Yeah! A lot of new things to read 😊👍🏼 Hey Developers! What great articles have already been made!🤩🤩 Who will be next? Hey Community, Another alternative prize was added to the Community Award: 🎁 Raspberry Pi 4 8GB with InterSystems IRIS Community Edition ARM installed Now the winner will have an option to choose one of 3 prizes! Hey Devs! Don't forget, that DC members can vote for published articles with Likes – votes in the Community award. So, the earlier you will write an article, the chance to get more likes is higher!🤩 Good luck and stay tuned! Hi, May I translate my article once I've publish in english? Kurro, translations of your own DC articles are also allowed, go ahead! ;) Hi Community! Only two weeks left to the end InterSystems Tech Article Contest! Don't be shy, publish your article and win the prize! Hey Devs! Now 12 incredible articles are in the game!🤩 Visit our contest page to support the participants with Likes = votes in the Community Award! 📍 https://community.intersystems.com/contests/1 Hey DC members, A new article by @Oliver.Wilms ! Check it out! Hi Comunity! Almost one week left to the end of InterSystems Tech Article Contest! Good luck and stay tuned! The prizes are waiting for you! Hi, Devs! 2 more articles are already in the game!😻 Only 6 days left to the end of the publication period! Good luck to everybody!🤞💪 Hey Community, ❗️ We decided to extend the contest period until 22 August! One more week to write an article and join our competition – don't miss your chance to win 😉 P.s. And don't forget about prizes for everyone who enters the contest ;) ______________ Yours, Anastasia Hey Community!2 more articles are being added to the contest!Interoperability with IRIS and Pharmacy Robotics by @Nigel.Salm5021 launch with the intelligent eco-system in InterSystems by @crys.su9670 Go check it out! And good luck to everybody! Hey Developers! One more participant has joined the contest with some awesome articles! Deploy to production module to convert query results to xlsx file based on python openpyxl library by @MikhailenkoSergey Hi Developers! How many awesome articles are already been made! Go check them out: https://community.intersystems.com/contests/1 Hi DC Members! A new article is added to the competition! Why I love ObjectScript and why I think I might love Python More by @Nigel.Salm5021 Take a look at it Hey Community! One more new article has been added to the game! GitHub Codespaces with IRIS by @Dmitry.Maslennikov Only one day left to the end of the contest! I have just published my third and final Tech Article. This article describes how you can develop a single Interface Code Base and then using Package and Global Mappings generate a multitude of Interfaces that either have date PUSHED into them or PULL data from a Source Application or Database and in my example, transform that data into either HL7 or FHIR messages. I then use HTTP to send the message to a Target Server. [12:15 AM] I have attached the article as a PDF which might make it a bit easier to read and I will be releasing the actual software on OEX in the near future. Hey Developers! 3 more articles have been added to the competition! Transferring Files via REST to Store in a Property, Part 3 by @Irene.Mikhaylova How to develop an interoperability solution in one code base and then use it to generate many individual interfaces by @Nigel.Salm5021 My opinion: IRIS Mirror not as reliable as expected in AWS Elastic Container Service by @Oliver.Wilms Last call Developers! Only 8 hours left to the end of the contest! great! thanks, Nigel

Organizations around the world lose an estimated five percent of their annual revenues to fraud, according to a survey of Certified Fraud Examiners (CFEs) who investigated cases between January 2010 and December 2011. Applied to the estimated 2011 Gross World Product, this figure translates to a potential total fraud loss of more than $3.5 trillion (source: https://www.acfe.com/press-release.aspx?id=4294973129). The most efficient way to reduce frauds is to collect and unify the transactions, assets and target data to identify patterns, produce anti fraud reports and algorithms to validate the next transactions. In summary, we have some this principles to be followed: Ability to collect, enrich and unify data on targets and assets Fluidity in the processing and exchange of data between systems, teams and internal and external information sources Multiformat and multi-model corporate database on targets and assets Intensive use of Artificial Intelligence applied to the business context Collaborative work based on the findings identified by the automations Rich composition of findings and dossiers based on flexible and well-grounded analytical artifacts Before data platforms like InterSystems IRIS, the challenge was hard, see: Expensive, closed and specialized Intelligence Systems Few data sources and little variety A lot of manual work Low collaboration capacity Low precision results Only experts were able to work Open systems (R and Python), more accessible and broad Explosion of data sources and formats (Big Data) Automation of 70% to 80% of Intelligence work High collaboration capacity High precision results (advanced use of statistics and AI algorithms) Multidisciplinary and self-service team InterSystems IRIS has a great data platform to do Anti fraud management, see: The benefits are clear because with 1 product we can: Collect data to analyze, create patterns and anti fraud algorithmns (R and Python) using IRIS Interoperability with BPL, DTL and Interoperability adapters and if something is special we can use Native API and PEX to do custom data adapters in Java, .NET or Python. Apply rules and deduplicate the data using BPL, DTL, ObjectScript and Native API, with visual Interoperability orchestraction. Store multimodel data and produce data results as networks, NLP, SQL, NoSQL and OLAP with InterSystems Database. All this data can be consumed with AI algorithmns running with IRIS to predict and identify frauds. Is possible use IRIS IntegratedML (AutoML) to accelerate and improve anti fraud analysis. Teams can produce dossiers and reports with the findings using IRIS Reports and IRIS BI and share all this with systems and people with User Portal, Report Server and IRIS API Management. In other platforms you need to buy some products like SQL database, NoSQL database, Data Bus, ETL engine, Rules and Intelligence Server with Machine Learning support, NLP engine, Analytics, Report Server and API Management solution. The costs are high, but with IRIS is possible reduce these costs, because we have "all in one", see: Interesting. Also check out the Fraud Prevention Demo by @Amir.Samary. Great sample!

Hello Community, Welcome to our first InterSystems Ideas News! The most important piece of news is the result of our very first and very successful Idea-A-Thon Contest. We've received 75 new interesting ideas. Some general stats of the Ideas Portal: ✓ 42 new ideas published last month✓ 147 new users joined last month✓ 142 ideas posted all time✓ 273 users joined all time The top 5 most voted for ideas of the month: IRIS and ZPM(Open Exchange) integration Move users, roles, resources, user tasks, Mappings (etc) to a seperate Database, other than %SYS, so these items can be mirrored RPMShare - Database solution for remote patient monitoring (RPM) datasets of high density vitals Create front-end package based on CSS and JS to be used in NodeJS and Angular projects PM platform And to round up this newsletter, here is a list of all ideas posted last month Add IRIS as a supported database for Apache Superset For community articles, let admins (and possibly article authors) pin particular comments to the top Add address standardization to Normalization (using Project US@ standards) PM platform Tool to convert legacy dot syntax code to use bracket syntax TTTC PDF reports for IRIS BI Sample code share opportunity Add basic tutorial of Docker or point to a Docker tutorial in Documentation The ability to export current settings to a %Installer Manifest Move users, roles, resources, user tasks, Mappings (etc) to a seperate Database, other than %SYS, so these items can be mirrored Common Data Modelling CI/CD support String format to numeric values in ZEN.proxyObject Patient Initiated Follow Up - Adding a document to an ROPWL I service Flags Linking I service to JIRA system Linux: iris session [command line] get commands from a file Journal file analysis/visualization Add the option to call class parameters in Embedded Python Create query builder Colour Background on Ward / Clinical Areas Floorplans A Dynamic Cheat Sheet to lookup for Common Core Functions for Reusability Version History for Classes Add wizard to create class with its properties RPMShare - Database solution for remote patient monitoring (RPM) datasets of high density vitals Better handle whitespace in Management Portal Text entry IRIS and ZPM(Open Exchange) integration Visual programming language Backup button before importing Adapting tools for people with special needs and/or disabilities 🌐🔰🦽🦼♿ Reserve licenses Interoperability Wizard Improve journal display in IRIS admin portal Create front-end package based on CSS and JS to be used in NodeJS and Angular projects Mirror Async Member Time Delay in Applying Journals Cache Journal Switch Schedule Monitoring and Programatic way of Starting/Stoping Gateways Embedded Python: Add a built-in variable to represent class LDAP Authentication method by default on Web Applications Please add google oauth authorization to login to the management portal Data Analyzer That's it for now. Visit our InterSystems Ideas portal, suggest your ideas and vote for the existing ones! Look out for the next announcement!

We're electrified to invite all our clients, partners, developers, and community members to our in-person InterSystems Global Summit 2023! Our Global Summit user conference is your opportunity to connect with trailblazing product developers, fellow users pushing our technology into new frontiers, and the people whose out-of-box thinking is rocking our universities and board rooms. All in the same space. And registration is now open! ➡️ InterSystems Global Summit 2023 🗓 Dates: June 4-7, 2023 📍 Location: The Diplomat Beach Resort, Hollywood, Florida, USA Join us this year for content on how customers like you use our technology for innovation and what trends affect our future innovations, including new and enhanced products and product offerings. Here is a short glimpse at the agenda. Sunday, June 4 Golf Outing or Morning Social ActivitiesBadge Pick-upTechnology BootcampCertification ExamsWomen's MeetupWelcome Reception Monday, June 5 Welcome and KeynotesBreakout SessionsHealthcare Leadership ConferencePartner Pavilion 1:1 MeetingsCertification ExamsFocus GroupsTech ExchangeAffinity SessionsEvening Drinks & Demos Tuesday, June 6 KeynotesBreakout SessionsPartner Pavilion 1:1 Meetings Certification Exams Focus GroupsTech ExchangeAffinity SessionsEvening Social Event Wednesday, June 7 Keynotes Breakout Sessions Partner Pavilion 1:1 Meetings Certification ExamsFocus Groups Tech Exchange Farewell Reception For more information on the agenda please visit this page. We look forward to seeing you at the InterSystems Global Summit 2023!