go to post Jean Millette · Mar 11 Thank you Luis for the link and for offering to save me a click (I admit I did click so I could read the details :-)). Best regards, Jean
go to post Jean Millette · Oct 16, 2023 Thank you David for writing this up. Will the Schema-level GRANT command (e.g., GRANT SELECT ON SCHEMA SQLUser TO NS1_Read) also provide "SELECT" privileges to the Views and any Stored Procedures in the schema or only to the tables? Thanks again
go to post Jean Millette · Sep 11, 2023 The containers web site is back up and running: InterSystems Container Repository Thank you for reporting the earlier outage.
go to post Jean Millette · Apr 28, 2023 An update on this... I had assumed that all was configured properly on the Report Server, particularly with respect to the required connections and gateways as described here: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls... I was mistaken. Also, we are running an older version of the report server (Version 1.12.7981.11681, from Sept. 2021), which does not provide the "New data source and gateway management experience" introduced in May, 2022. We're planning an upgrade of our Power BI Report Server and backing SQL Server and I'll revisit the use of DirectQuery to IRIS with some of our reports after that upgrade. Thanks again Carmen for the pointers and I'll update this post after getting this working as expected.
go to post Jean Millette · Apr 27, 2023 Hi Carmen, thank you for the clarification on the "Import mode" recommendation and for the link to something to try. Unfortunately, although the report shows up in the right spot on the report server, when I try to "Manage" the "Data Sources", that link is greyed out: I don't believe it's a folder/file permissions issue since I've saved other reports to this folder before. The example in the link you sent me used Direct Query to the SQL Server for its data. Since report configuration information is also stored in SQL Server and my report has no explicitly-defined connection to SQL Server, could that be an issue? I'm now looking at the backend SQL Server database where report information is stored for clues. I've found "rsInternalError" entries in the "....ExecutionLogStorage" table for each of the times I've tried to display the problem report from the server, but now looking for details of these errors. I'll update this post with more details as I find them, but other ideas (how to enable the "Data sources" link?) are most welcome. Thanks again, Jean
go to post Jean Millette · Apr 18, 2023 Thank you, Jeffrey. Yes, it would be good for completeness' sake and for making the "File->New File" less confusing. I'm good with the "right-click" in Explorer pane, etc. (got the same good "in-house" advice from @Pravin Barton )
go to post Jean Millette · Oct 27, 2022 Thanks @Ben Spead! It was fun to do and hope it helps (in a small way) increase people's use/understanding of embedded python.
go to post Jean Millette · Apr 25, 2022 Please check out this documentation, where there is an example of using an array holding the sql statement: %SQL.Statement - InterSystems IRIS Data Platform 2021.2 - including private class members According to that document, the query your code builds and passes to %Prepare is "UPDATE QUERY UPDATE QUERY", which is not valid.
go to post Jean Millette · Apr 22, 2022 When does "tSC" get defined? This line might be the problem, causing the for loop to stop: If $$$ISERR(tSC) Quit (Comment out that line and watch the results appear :-))
go to post Jean Millette · Apr 22, 2022 ...and, it's a shot in the dark, but how about putting parenthesis around each condition? (MessageName = :tMessageName) and (Identifier = :tIdentifier)
go to post Jean Millette · Apr 20, 2022 The "repr(ex)" Python method also prints a bit more information about the exception: except Exception as ex: print("Exception: %s" % str(ex)) print(repr(ex)) The "repr(ex)" Python method also prints a bit more information about the exception (e.g., the type of Error): Exception: division by zero ZeroDivisionError('division by zero')
go to post Jean Millette · Apr 20, 2022 If the goal is to have the method return an instance of %Exception.PythonException, the method could create one with "_New(str) method, supplying the "ex" string as the "str" parameter. The "except" clause would then return the instance. Not sure of the syntax, but it's likely something like this: newex = iris.cls('%Exception.PythonException')._New(ex) return newex If, instead of returning that exception, the method would rather "raise" the exception, there are some helpful hints here: https://stackoverflow.com/questions/2052390/manually-raising-throwing-an...
go to post Jean Millette · Apr 18, 2022 How about this? do ^%G (I tried to paste a screen shot, but no luck)...will try again, but the above command works to get you to the "Global ^" prompt.
go to post Jean Millette · Jan 6, 2022 Would be nice to add a parameter to %DisplayFormatted for specifying a separator character/string when the first parameter is "CSV". Until then, I'll go through the result set and use %Print("|") to use a non-tab, non-comma separator when exporting results with embedded commas as requested by a customer (they don't like tabs). Using Dynamic SQL | Using InterSystems SQL | InterSystems IRIS Data Platform 2021.1
go to post Jean Millette · Oct 28, 2021 Thanks Ben for the “shout out” and big thanks to you, @Matthew Giesmann, @Timothy Leavitt , @Philip Cantwell, @Paul Collins, and others on the AppServices and Trak teams for building key parts of the framework that enabled CCR’s move to InterSystems Reports.
go to post Jean Millette · Sep 15, 2021 Hello Rochdi, Have you resolved this? If not, are you using a report that is derived from %ZEN.Report.reportPage? If you are, you can use the "GenerateReport()" instance method to create an "xlsx" Excel file, which can then be exported to ".csv" after opening it from Excel. https://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?... Alternatively, the following code highlights how to generate a CSV file containing the results of a query: ClassMethod ExportCSV(/* some args */) As %String [ ZenMethod ] { /* get a results set named "rs" from a query (sql statement, or, in this example a query class): Set rs=##class(%ResultSet).%New("<some query class") Do rs.Prepare() Do rs.Execute(/*some args*/) */ // Define the delimeter to be used in CSV file (usually comma) Set Delim = "," // Define the file and its name Set File = ##class(%FileCharacterStream).%New() Set Filename = "filename.csv" Set File.BOM = $C(239,187,191) Set File.Filename = "C:\temp\"_Filename Set File.TranslateTable = "UTF8" // Define the names of the columns (should match up with the values in each row of the result // set. In this example, there are 3 columns Set FileHeader = $LB("Name","Rank","","SerialNumber") Do File.WriteLine($ZCVT($LTS(FileHeader, Delim), "O", "UTF8")) While rs.%Next() { Set Row = "" For i = 1:1:$LL(FileHeader) { Set col = $LG(FileHeader, i) Set Data = rs.%Get(col) // Replace characters that may mess up the CSV file (e.g., embedded commas in a data field) Set Data = $REPLACE(Data, $C(13), " ") Set Data = $REPLACE(Data, $C(10), " ") Set Data = $REPLACE(Data, """", "'") Set:(Data [ ",") Data = """"_Data_"""" Set Data = $ZCVT(Data, "O", "UTF8") // Add the datum to the row's list Set Row = Row_$LB(Data) } // Each element in the "row list" is written, separated by "Delim" (comma) on one line // in the file Do File.WriteLine($LTS(Row, Delim)) } // Set attributes of the file for easier reading by the right apps later. Do File.SetAttribute("ContentType","application/octet-stream; charset=utf-8") Do File.SetAttribute("ContentDisposition","attachment; filename="""_Filename_"""") Do File.SetAttribute("Expires",600) Do File.SetAttribute("Content-Length",File.Size) Do File.%Save() // Return the name of the full path to the CSV file Quit File.Filename } Please confirm if this is helpful or if you have other questions. Thank you
go to post Jean Millette · May 18, 2021 I've read that some users have seen the <MAXSTRING> error when they have "Log Trace Events" enabled for a particular business host. Can you try disabling this and re-running your program? http://docs.intersystems.com/ens201317/csp/docbook/DocBook.UI.Page.cls?K...