Showing posts with label formatting. Show all posts
Showing posts with label formatting. Show all posts

26 May 2026

📉Graphical Representation: Format (Just the Quotes)

"A graph presents a limited number of figures in a bold and forceful manner. To do this it usually must omit a large number of figures available on the subject. The choice of what graphic format to use is largely a matter of deciding what figures have the greatest significance to the intended reader and what figures he can best afford to skip." (Peter H Selby, "Interpreting Graphs and Tables", 1976)

"Any graphic format can be executed well, or poorly, for a particular purpose. This is often a more significant variable than the choice of format." (Macdonald-Ross, 1977)

"The main benefit of tabular presentation is its compactness; a great deal of data can be put on a single page. Also, even with the two-digit restriction a table presents numbers more exactly than bar or pie charts do. Therefore it seems likely that tables will remain the preferred format for professional users. The great weakness of tables is their abstract nature. A table consists entirely of abstract symbols-words and numbers." (Michael Macdonald-Ross, "Graphics in Texts", Review of Research in Education Vol. 5, 1977)

"Some believe that the vertical bar should be used when comparing similar items for different time periods and the horizontal bar for comparing different items for the same time period. However, most people find the vertical-bar format easier to prepare and read. and a more effective way to show most types of comparisons." (Robert Lefferts, "Elements of Graphics: How to prepare charts and graphs for effective reports", 1981)

"Charts are used to represent quantitative data in a graphic format. A chart visually illustrates relationships between numbers. When creating a chart, keep in mind that the goal is to represent the data in a simplified and appealing way so as not to muddle the message the chart is meant to convey." (Dennis K Lieu & Sheryl Sorby, "Visualization, Modeling, and Graphics for Engineering Design", 2009)

"For a visual to qualify as beautiful, it must be aesthetically pleasing, yes, but it must also be novel, informative, and efficient. [...] For a visual to truly be beautiful, it must go beyond merely being a conduit for information and offer some novelty: a fresh look at the data or a format that gives readers a spark of excitement and results in a new level of understanding. Well-understood formats (e.g., scatterplots) may be accessible and effective, but for the most part they no longer have the ability to surprise or delight us. Most often, designs that delight us do so not because they were designed to be novel, but because they were designed to be effective; their novelty is a byproduct of effectively revealing some new insight about the world." (Noah Iliinsky, "On Beauty", [in "Beautiful Visualization"] 2010)

"The first requirement of a beautiful visualization is that it is novel, fresh, or unique. It is difficult (though not impossible) to achieve the necessary novelty using default formats. In most situations, well-defined formats have well-defined, rational conventions of use: line graphs for continuous data, bar graphs for discrete data, pie graphs for when you are more interested in a pretty picture than conveying knowledge." (Noah Iliinsky, "On Beauty", [in "Beautiful Visualization"] 2010)

"The best visualizations will reveal what is interesting about the specific data set you’re working with. Different data may require different approaches, encodings, or techniques to reveal its interesting aspects. While default visualization formats are a great place to start, and may come with the correct design choices pre-selected, sometimes the data will yield new knowledge when a different visualization approach or format is used." (Noah Iliinsky & Julie Steel, "Designing Data Visualizations", 2011)

"Infographics combine data with design to enable visual learning. This communication process helps deliver complex information in a way that is more quickly and easily understood. [...] In an era of data overload, infographics offer your audience information in a format that is easy to consume and share. [...] A well-placed, self-contained infographic addresses our need to be confident about the content we’re sharing. Infographics relay the gist of your information quickly, increasing the chance for it to be shared and fueling its spread across a wide variety of digital channels." (Mark Smiciklas, "The Power of Infographics: Using Pictures to Communicate and Connect with Your Audiences", 2012)

"Presenting data in a graphical format makes it much easier to see and understand what is happening with the data. Data visualization applies to all phases of the data science process."  (John D Kelleher & Brendan Tierney, "Data Science", 2018)

"There is often no one 'best' visualization, because it depends on context, what your audience already knows, how numerate or scientifically trained they are, what formats and conventions are regarded as standard in the particular field you’re working in, the medium you can use, and so on. It’s also partly scientific and partly artistic, so you get to express your own design style in it, which is what makes it so fascinating." (Robert Grant, "Data Visualization: Charts, Maps and Interactive Graphics", 2019)

"Data is dirty. Let's just get that out there. How is it dirty? In all sorts of ways. Misspelled text values, date format problems, mismatching units, missing values, null values, incompatible geospatial coordinate formats, the list goes on and on." (Ben Jones, "Avoiding Data Pitfalls: How to Steer Clear of Common Blunders When Working with Data and Presenting Analysis and Visualizations", 2020) 

 

16 May 2026

🤖〽️Prompt Engineering: Copilot Unabridged (Part 190: How Invisible Prompt Injection Could Be a Good Thing for AI)

 

Prompt Engineering Series
Prompt Engineering Series

Prompt: "write a post of 600 words on how invisible prompt injection could be a good thing for AI"

Introduction

Invisible prompt injection is usually discussed as a threat - an attack vector that manipulates AI systems without the user’s awareness. And yes, in the wild, it is dangerous. But in controlled environments, invisible prompt injection can actually be a powerful tool for strengthening AI safety, improving robustness, and helping developers understand how models behave under pressure. By studying how AI systems respond to hidden instructions, researchers can build models that are more resilient, more transparent, and ultimately more trustworthy. In this sense, invisible prompt injection isn’t just a vulnerability; it’s also a diagnostic instrument that reveals how AI systems interpret, prioritize, and negotiate conflicting signals.

1. A Testing Ground for AI Robustness

Invisible prompt injection acts like a stress test. When researchers embed hidden instructions into text, images, or metadata, they can observe how the AI responds when its input channel is compromised. This helps developers identify:

  • Weak points in the model’s reasoning
  • Situations where the model over‑trusts user input
  • Scenarios where safety guardrails fail

By intentionally exposing the model to controlled injections, teams can strengthen its resistance to real‑world attacks. This transforms a vulnerability into a research tool that improves system resilience.

2. A Way to Understand How AI Prioritizes Instructions

Invisible prompt injection reveals how an AI model weighs different layers of input. Does it prioritize the user’s visible request? The hidden instruction? The system‑level rules? The model’s internal alignment?

Studying these interactions helps researchers map the model’s internal decision‑making. This is crucial for:

  • Improving interpretability
  • Refining alignment strategies
  • Ensuring consistent behavior across contexts

In other words, invisible prompt injection becomes a lens through which developers can examine the model’s internal hierarchy of influence.

3. A Tool for Building Better Defenses

You can’t defend against what you don’t understand. Controlled invisible prompt injection allows researchers to simulate attacks that malicious actors might attempt. This helps teams design:

  • Stronger input sanitization
  • Better content‑filtering pipelines
  • More resilient prompt‑parsing mechanisms

By studying how injections succeed, developers can build systems that automatically detect and neutralize them. This proactive approach turns a threat into a training mechanism for safer AI.

4. A Method for Evaluating Real‑World Risk

Invisible prompt injection helps researchers evaluate how AI systems behave in messy, unpredictable environments. Real‑world data is full of:

  • Hidden formatting
  • Embedded metadata
  • Unintended instructions
  • Noisy or adversarial contention 

Testing with invisible injections helps developers understand how the model behaves when confronted with ambiguous or corrupted inputs. This leads to AI systems that are more stable, more predictable, and more reliable in everyday use. 

5. A Catalyst for Better AI Governance

Invisible prompt injection research encourages organizations to adopt stronger governance practices. It highlights the need for:and 

  • Clear safety protocols
  • Rigorous red‑team testing
  • Transparent risk assessments
  • Continuous monitoring

By treating invisible prompt injection as a legitimate research tool, organizations can build a culture of proactive safety rather than reactive patching.

Closing Statement

Invisible prompt injection is dangerous when used maliciously - but in controlled, ethical research settings, it becomes a powerful instrument for strengthening AI. It exposes weaknesses, reveals hidden dynamics, and helps developers build systems that are more robust, more transparent, and more aligned with human values. By studying how AI responds to invisible manipulation, we gain the insight needed to design models that behave predictably and safely, even in the face of unexpected inputs. In this way, invisible prompt injection isn’t just a threat - it’s also an opportunity to build better, safer AI.

Disclaimer: The whole text was generated by Copilot (under Windows 11) at the first attempt. This is just an experiment to evaluate feature's ability to answer standard general questions, independently on whether they are correctly or incorrectly posed. Moreover, the answers may reflect hallucinations and other types of inconsistent or incorrect reasoning.

Previous Post <<||>> Next Post

11 March 2025

🏭🎗️🗒️Microsoft Fabric: Real-Time Dashboards (RTD) [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 10-Mar-2025

Real-Time Intelligence architecture
Real-Time Intelligence architecture [5]

[Microsoft Fabric] Real-Time Dashboard

  • [def]
    • a collection of tiles
      • optionally organized in pages
        • act as containers of tiles
        • organize tiles into logical groups
          • e.g. by data source or by subject area
        • used to create a dashboard with multiple views
          • e.g. dashboard with a drillthrough from a summary page to a details page [1]
      • each tile has 
        • an underlying query
        • a visual representation
    • exists within the context of a workspace [1]
      • always associated with the workspace used to create it [1]
  • {concept} tile
    • uses  KQL snippets to retrieve data and render visuals [1]
    • can be added directly from queries written in a KQL queryset [1]
  • {concept} data source
    • reusable reference to a specific database in the same workspace as the dashboard [1]
  • {concept} parameters 
    • significantly improve dashboard rendering performance [1]
    • enable to use filter values as early as possible in the query
      • filtering is enabled when the parameter is included in the query associated with the tiles [1]
  • {concept} cloud connection
    • uses dashboard owner's identity to give access to the underlying data source to other users [2]
    • when not used for 90 days, it will expire [2]
      • ⇒ a new gateway connection must be set up [2]
      • via Manage connections >> Gateways page >> Edit credentials and verify the user again
    • a separate connection is needed for each data source [2]
  • {feature} natively export KQL queries to a dashboard as visuals and later modify their underlying queries and visual formatting as needed [1]
    • the fully integrated dashboard experience provides improved query and visualization performance [1]
  • {feature} encrypted at rest
    • dashboards and dashboard-related metadata about users are encrypted at rest using Microsoft-managed keys [1]
  • {feature} auto refresh
    • allows to automatically update the data on a dashboard without manually reloading the page or clicking a refresh button [1]
    • can be set by a database editor
      • both editors and viewers can change the actual rate of auto refresh while viewing a dashboard [1]
    • database editors can limit the minimum refresh rate that any viewer can set
      • ⇐ reduces the cluster load
      • when set, database users can't set a refresh rate lower than the minimum [1]
  • {feature} explore data 
    • enables users to extend the exploration of dashboards beyond the data displayed in the tiles [3]
      • begins with viewing the data and its corresponding visualization as they appear on the tile [3]
      • users can add or removing filters and aggregations, and use further visualizations [3]
        • ⇐ no knowledge of KQL is needed [3]
  • {feature} conditional formatting
    • allows users to format data points based on their values, utilizing 
      • colors
        • {rule} color by condition
          • allows to set one or more logical conditions that must be met for a value to be colored [4]
          • available for table, stat, and multi stat visuals [4]
        • {rule} color by value
          • allows to visualize values on a color gradient [4]
          • available for table visuals [4]
      • tags
      • icons
    • can be applied either 
      • to a specific set of cells within a designated column [4]
      • to entire rows [4]
    • one or more conditional formatting rules can be applied for each visual [4]
      • when multiple rules conflict, the last rule defined takes precedence over any previous ones [4]
  • {action} export dashboard
    • dashboards can be exported to a JSON file
    • can be useful in several scenarios 
      • {scenario} version control
        • the file can be used to restore the dashboard to a previous version [1]
      • {scenario} dashboard template
        • the file can be used as template for creating new dashboards [1]
      • {scenario} manual editing
      • edit the file to modify the dashboard and imported the file back to the dashboard [1]
    • ADX dashboards can be exported and imported as RT dashboards [6]
  • {action} share dashboard
    • one can specify if the user can view, edit, or share [2]
    • ⇐ the permissions are not for the underlying data [2]
      • permissions are set by defining the identity that the dashboard uses for accessing data from each data sources[2]
      • {type|default} pass-through identity
        • used when authenticating to access the underlying data source [2]
        • the user is only able to view the data in the tiles [2]
      • {type} dashboard editor’s identity:
        • allows the user to use editor’s identity, and thus permissions[2]
          • the editor defines a cloud connection that the dashboard uses to connect to the relevant data source [2]
          • only editors can define cloud connections and permissions for a specific real-time dashboard [2]
            • each editor that modifies the real-time dashboard needs to set up own cloud connection [2]
            • if a valid connection doesn't exist, the user is able to view the real-time dashboard but will only see data if they themselves have access to it [2]
  • {action} revoke a user’s access permissions
    • remove access from the dashboard [2]
    • remove the cloud connection.
      • via Settings >> Manage connections and gateways >> Options >> Remove
    • remove the user from the cloud connection.
      • via Settings >> Manage connections and gateways >> Options >> Manage users >> {select User} >> Delete
    • edit the Data source access permissions.
      • via Data source >> New data source >> edit >> Data source access >> Pass-through identity
      • ⇐ the user uses own identity to access the data source [2]
  • {prerequisite} a workspace with a Microsoft Fabric-enabled capacity [1]
  • {prerequisite} a KQL database with data [1]
  • {setting} Users can create real-time dashboards [1]
    References:
    [1] Microsoft Learn (2024) Fabric: Create a Real-Time Dashboard [link
    [2] Microsoft Learn (2024) Fabric: Real-Time Dashboard permissions (preview) [link
    [3] Microsoft Learn (2024) Fabric: Explore data in Real-Time Dashboard tiles [link
    [4] Microsoft Learn (2024) Fabric: Apply conditional formatting in Real-Time Dashboard visuals [link]
    [5] Microsoft Learn (2025) Fabric: Real Time Intelligence L200 Pitch Deck [link]
    [6] Microsoft Fabric Updates Blog (2024) Easily recreate your ADX dashboards as Real-Time Dashboards in Fabric, by Michal Bar [link]
    [7] Microsoft Learn (2025) Create Real-Time Dashboards with Microsoft Fabric [link]

    Resources:
    [R1] Microsoft Learn (2024) Microsoft Fabric exercises [link]
    [R2] Microsoft Fabric Updates Blog (2024) Announcing Real-Time Dashboards generally available [link]
    [R3] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

    Acronyms:
    ADX - Azure Data Explorer
    KQL  - Kusto Query Language
    MF - Microsoft Fabric
    RT - Real-Time

    26 February 2025

    💎🤖SQL Reloaded: Copilot Stories (Part III: Formatting Code via Prompts)

    When debugging poorly formatted code, usually it helps to format first the code before doing any other work on it. I observed in the previous post that Copilot can be used to restructure the code based on a set of commands that summarize the best practices. 

    Example 1: Simple Query

    Let's start with a simple example from the web

    Prompt:
    Format the below code by using the available SQL best practices
    1) each join condition is written on its own line
    2) the commas are placed at the beginning of each line
    3) use 3 letter based aliases for all tables
    4) compact the code by reducing the indentation

    SELECT po.ExpectedDeliveryDate, po.OrderDate, pol.LastReceiptDate, pol.LastEditedWhen, p.ValidFrom, p.ValidTo FROM Purchasing.PurchaseOrders AS po JOIN Purchasing.PurchaseOrderLines AS pol ON pol.PurchaseOrderID = po.PurchaseOrderID JOIN Application.People AS p ON p.PersonID = po.ContactPersonID WHERE LastReceiptDate > '1/1/2016';

    Output:

    SELECT po.ExpectedDeliveryDate
        , po.OrderDate
        , pol.LastReceiptDate
        , pol.LastEditedWhen
        , p.ValidFrom
        , p.ValidTo
    FROM Purchasing.PurchaseOrders AS po
    JOIN Purchasing.PurchaseOrderLines AS pol
        ON pol.PurchaseOrderID = po.PurchaseOrderID
    JOIN Application.People AS p
        ON p.PersonID = po.ContactPersonID
    WHERE LastReceiptDate > '1/1/2016';
    

    Of course, the colors were added by the hilite.me formatting to better identify the reserved words. The output of the above prompt is not exactly what I intended in 3), so repeating the slightly changed command seems to obtain the expected result. Even if a 3-letter alias was not used for all tables, the query looks better:

    Prompt:
    Use 3 letter based aliases for all tables.

    Output:

    SELECT po.ExpectedDeliveryDate
        , po.OrderDate
        , pol.LastReceiptDate
        , pol.LastEditedWhen
        , ppl.ValidFrom
        , ppl.ValidTo
    FROM Purchasing.PurchaseOrders AS po
    JOIN Purchasing.PurchaseOrderLines AS pol
        ON pol.PurchaseOrderID = po.PurchaseOrderID
    JOIN Application.People AS ppl
        ON ppl.PersonID = po.ContactPersonID
    WHERE LastReceiptDate > '1/1/2016';
    

    The script looks good, though it would be great if we could align the constraints with the order of the tables.

    Prompt:
    Rewrite the query by using the first table from the join in the join constraint.

    Output:

    SELECT po.ExpectedDeliveryDate
        , po.OrderDate
        , pol.LastReceiptDate
        , pol.LastEditedWhen
        , ppl.ValidFrom
        , ppl.ValidTo
    FROM Purchasing.PurchaseOrders AS po
    JOIN Purchasing.PurchaseOrderLines AS pol
        ON po.PurchaseOrderID = pol.PurchaseOrderID
    JOIN Application.People AS ppl
        ON po.ContactPersonID = ppl.PersonID
    WHERE LastReceiptDate > '1/1/2016';
    

    As observed in the last post, multiple scripts can be transformed withing the same prompt, especially when the statements were generated by Copilot as well. 

    Example 2: Complex Dynamics AX Query

    How about a more complex query? 
    Let's consider the below query written for Dynamics AX:

    Prompt:
    Format the below code by using the available SQL best practices
    1) each join condition is written on its own line
    2) the commas are placed at the beginning of each line
    3) use 3 letter based aliases for all tables
    4) compact the code by reducing the indentation

    SELECT ADR.DATAAREAID, DPT.FIRSTNAME, DPT.LASTNAME, DPT.LANGUAGEID, DPR.PartyId, DPR.IsPrimary, CASE DPR.Status WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' END Status, NullIf(Cast(DPR.ValidFromDateTime as Date), '31-12-1900') ValidFromDateTime, NullIf(Cast(DPR.ValidToDateTime as Date), '30-12-2154') ValidToDateTime, DPR.Shared  , ADR.ADDRTABLEID , ADR.ADDRRECID , ADR.TYPE , CASE ADR.TYPE WHEN 0 THEN 'None' WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Delivery'WHEN 3 THEN 'Alt. delivery'WHEN 4 THEN 'SWIFT'WHEN 5 THEN 'Payment' WHEN 6 THEN 'Service' WHEN 7 THEN 'Home' WHEN 8 THEN 'Other' WHEN 9 THEN 'Business' WHEN 10 THEN 'RemitTo' WHEN 11 THEN 'Third-party shipping address' END AddressDescription, ADR.NAME , ADR.ADDRESS , ADR.STREET, ADR.ZIPCODE , ADR.CITY , ADR.STATE , ADR.COUNTY , ADR.COUNTRYREGIONID , ADR.PHONE , ADR.TELEFAX , ADR.URL FROM ADDRESS ADR JOIN DIRPARTYADDRESSRELATIONSHI1066 DAC ON DAC.REFCOMPANYID = ADR.DATAAREAID and DAC.ADDRESSRECID = ADR.RECID and DAC.DATAAREAID = ADR.DATAAREAID JOIN DIRPARTYADDRESSRELATIONSHIP DPR on DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID and DPR.DATAAREAID = DAC.DATAAREAID JOIN DIRPARTYTABLE DPT on DPT.PARTYID = DPR.PARTYID and DPT.DATAAREAID = DPR.DATAAREAID  JOIN EmplTable EMP on DPT.PARTYID = EMP.PARTYID and DPT.DATAAREAID = EMP.DATAAREAID  

    Output:

    SELECT 
        ADR.DATAAREAID
        , DPT.FIRSTNAME
        , DPT.LASTNAME
        , DPT.LANGUAGEID
        , DPR.PartyId
        , DPR.IsPrimary
        , CASE DPR.Status 
            WHEN 0 THEN 'Active' 
            WHEN 1 THEN 'Inactive' 
          END AS Status
        , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
        , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
        , DPR.Shared
        , ADR.ADDRTABLEID
        , ADR.ADDRRECID
        , ADR.TYPE
        , CASE ADR.TYPE 
            WHEN 0 THEN 'None' 
            WHEN 1 THEN 'Invoice' 
            WHEN 2 THEN 'Delivery'
            WHEN 3 THEN 'Alt. delivery'
            WHEN 4 THEN 'SWIFT'
            WHEN 5 THEN 'Payment' 
            WHEN 6 THEN 'Service' 
            WHEN 7 THEN 'Home' 
            WHEN 8 THEN 'Other' 
            WHEN 9 THEN 'Business' 
            WHEN 10 THEN 'RemitTo' 
            WHEN 11 THEN 'Third-party shipping address' 
          END AS AddressDescription
        , ADR.NAME
        , ADR.ADDRESS
        , ADR.STREET
        , ADR.ZIPCODE
        , ADR.CITY
        , ADR.STATE
        , ADR.COUNTY
        , ADR.COUNTRYREGIONID
        , ADR.PHONE
        , ADR.TELEFAX
        , ADR.URL
    FROM 
        ADDRESS ADR
    JOIN 
        DIRPARTYADDRESSRELATIONSHI1066 DAC 
        ON DAC.REFCOMPANYID = ADR.DATAAREAID 
        AND DAC.ADDRESSRECID = ADR.RECID 
        AND DAC.DATAAREAID = ADR.DATAAREAID
    JOIN 
        DIRPARTYADDRESSRELATIONSHIP DPR 
        ON DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID 
        AND DPR.DATAAREAID = DAC.DATAAREAID
    JOIN 
        DIRPARTYTABLE DPT 
        ON DPT.PARTYID = DPR.PARTYID 
        AND DPT.DATAAREAID = DPR.DATAAREAID
    JOIN 
        EmplTable EMP 
        ON DPT.PARTYID = EMP.PARTYID 
        AND DPT.DATAAREAID = EMP.DATAAREAID;
    

    The query looks good, though it needs more transformations:

    Input (to be run line by line):

    Rewrite the query by writing names in camel with the beginning letter in upper case.
    Rewrite the query by writing table aliases in upper case.

    Output:

    SELECT 
          ADR.DataAreaId
        , DPT.FirstName
        , DPT.LastName
        , DPT.LanguageId
        , DPR.PartyId
        , DPR.IsPrimary
        , CASE DPR.Status 
            WHEN 0 THEN 'Active' 
            WHEN 1 THEN 'Inactive' 
          END AS Status
        , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
        , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
        , DPR.Shared
        , ADR.AddrTableId
        , ADR.AddrRecId
        , ADR.Type
        , CASE ADR.Type 
            WHEN 0 THEN 'None' 
            WHEN 1 THEN 'Invoice' 
            WHEN 2 THEN 'Delivery' 
            WHEN 3 THEN 'Alt. Delivery' 
            WHEN 4 THEN 'Swift' 
            WHEN 5 THEN 'Payment' 
            WHEN 6 THEN 'Service' 
            WHEN 7 THEN 'Home' 
            WHEN 8 THEN 'Other' 
            WHEN 9 THEN 'Business' 
            WHEN 10 THEN 'RemitTo' 
            WHEN 11 THEN 'Third-Party Shipping Address' 
          END AS AddressDescription
        , ADR.Name
        , ADR.Address
        , ADR.Street
        , ADR.ZipCode
        , ADR.City
        , ADR.State
        , ADR.County
        , ADR.CountryRegionId
        , ADR.Phone
        , ADR.Telefax
        , ADR.Url
    FROM 
        dbo.Address ADR
    JOIN 
        dbo.DirPartyAddressRelationship1066 DAC 
        ON DAC.RefCompanyId = ADR.DataAreaId 
        AND DAC.AddressRecId = ADR.RecId 
        AND DAC.DataAreaId = ADR.DataAreaId
    JOIN 
        dbo.DirPartyAddressRelationship DPR 
        ON DPR.RecId = DAC.PartyAddressRelationshipRecId 
        AND DPR.DataAreaId = DAC.DataAreaId
    JOIN 
        dbo.DirPartyTable DPT 
        ON DPT.PartyId = DPR.PartyId 
        AND DPT.DataAreaId = DPR.DataAreaId
    JOIN 
        dbo.EmplTable EMP 
        ON DPT.PartyId = EMP.PartyId 
        AND DPT.DataAreaId = EMP.DataAreaId;
    

    Example 3: Simple X++ Query

    How about code written in other languages (e.g. X++)?

    I tried to transform the definition behind X++ form from Dynamics 365 for Finance without effect

        InventItemPurchSetup.CalendarDays = NoYes::No;
        InventItemPurchSetup.HighestQty = 0.00;
        InventItemPurchSetup.InventDimId = "AllBlank";
        InventItemPurchSetup.InventDimIdDefault = "AllBlank";
        InventItemPurchSetup.ItemId = "1000";
        InventItemPurchSetup.LeadTime = 0;
        InventItemPurchSetup.LowestQty = 0.00;
        InventItemPurchSetup.MandatoryInventLocation = NoYes::No;
        InventItemPurchSetup.MandatoryInventSite = NoYes::No;
        InventItemPurchSetup.MultipleQty = 0.00;
        InventItemPurchSetup.Override = NoYes::No;
        InventItemPurchSetup.StandardQty = 0.00;
        InventItemPurchSetup.Stopped = NoYes::No;
        InventItemPurchSetup.Sequence = 0;
        InventItemPurchSetup.OverrideDefaultStorageDimensions = NoYes::No;
        InventItemPurchSetup.SysRowVersionNumber = 0;
        InventItemPurchSetup.insert();
    

    Multiple attempts were performed without any progress, though after a break I was able to apply the following transformations:

    Prompts:

    Replace the word "InventItemPurchSetup") with ", IPS." and remove the text after the equality sign.
    Add a comma in front of each line and remove the semicolon at the end.

    With a few manual change the code becomes:

    SELECT IPS.CalendarDays 
    , IPS.HighestQty 
    , IPS.InventDimId 
    , IPS.InventDimIdDefault 
    , IPS.ItemId 
    , IPS.LeadTime 
    , IPS.LowestQty 
    , IPS.MandatoryInventLocation 
    , IPS.MandatoryInventSite 
    , IPS.MultipleQty 
    , IPS.Override 
    , IPS.StandardQty 
    , IPS.Stopped 
    , IPS.Sequence 
    , IPS.OverrideDefaultStorageDimensions 
    , IPS.SysRowVersionNumber 
    FROM dbo.InventItemPurchSetup IPs
    

    The prompts reflect what I was doings in Excel manually via a replace, respectively the use of "Text to columns" functionality".
     
    Probably, there's the opportunity to test further various ideas. Frankly, the transformation from upper case to camel could have helped me in the past a lot!

    Disclaimer:
    The queries were not tested after the changes were performed (because of unavailability of the systems).

    Happy coding!

    31 January 2025

    🌌🏭KQL Reloaded: First Steps (Part II: Working with Dates)

    Knowing how to work with dates and manipulate them accordingly is probably one of the important pieces of knowledge in any query language. Fortunately, KQL has many date-related functions that can help developers in the process and the available documentation is detailed enough to support users in learning their use. The current post focuses on the main uses of dates. 

    Please note that the scripts consider the main time units. Please check the documentation for functions' call for other time units.

    Create dates via make_datetime, now and ago functions:

    // creating dates from parts
    print datetime1 = make_datetime(2025,1,1)
    , datetime2 = make_datetime(2025,1,1,0,0,0)
    , datetime3 = make_datetime(2025,1,1,1,2,3)
    
    // creating dates from values
    print date1 = datetime(2024-02-01)
    , datetime(2024-02-01 00:00:00)
    , datetime(2024-02-01 23:59:59)
    , datetime(2024-02-01 23:59:59.123)
    
    // now vs todatetime (return the same value)
    print datetime1 = now()
    , datetime2 = todatetime(now())
    , datetime3 = ago(0h)
    

    Extract information from dates via datetime_part, substring and individual functions: getyear, week_of_year, monthofyear, dayofyear, hourofday, dayofmonth, dayofweek, dayofyear, dayofmonth, dayofweek, endofyear, endofweek, endofmonth, endofday, startofyear, startofweek, startofmonth, startofday:

    // date parts front date
    print now = now()
    , year = datetime_part('year', now())
    , month = datetime_part('month', now())
    , day = datetime_part('day', now())
    , hour = datetime_part('hour', now())
    , minute = datetime_part('minute', now())
    , second = datetime_part('second', now())
    
    // date parts from string 
    let t = datetime("2024-12-31 10:35:59");
    print year = substring(t, 0, 4) 
    , month = substring(t, 5, 2) 
    , day = substring(t, 8, 2) 
    , hour = substring(t, 11, 2) 
    , minute = substring(t, 14, 2) 
    , second = substring(t, 17, 2) 
    
    // date parts via functions
    print year = getyear(now())
    , week = week_of_year(now())//ISO 8601 compliant
    , month = monthofyear(now())
    , day = dayofyear(now())
    , hour = hourofday(now())
    
    // day functions
    print year = dayofyear(now())
    , month = dayofmonth(now())
    , day = dayofweek(now())
    
    // end of time dates
    print year = endofyear(now())
    , week = endofweek(now())
    , month = endofmonth(now())
    , day = endofday(now())
    
    // start of time dates
    print year = startofyear(now())
    , week = startofweek(now())
    , month = startofmonth(now())
    , day = startofday(now())
    
    //time units
    print hours1 = time(1hour)
    , minutes1 = time(1min)
    , seconds1 = time(1second)
    , hours2 = totimespan("0.01:00:00")
    , minutes2 = totimespan("0.00:01:00")
    , seconds2 = totimespan("0.00:00:01")
    

    Working with dates via datetime_add and datetime_diff functions:

    // adding time units
    print year = datetime_add('year',1,now())
    , month = datetime_add('month',1,now())
    , day = datetime_add('day',1,now())
    , hour = datetime_add('hour',1,now())
    , minutes = datetime_add('minute',1,now())
    , seconds =  datetime_add('second',1,now())
    
    // data differences
    print years = datetime_diff('year', now(), ago(1h))
    , months = datetime_diff('month', now(), ago(1h))
    , days = datetime_diff('day', now(), ago(1h))
    , hours = datetime_diff('hour', now(), ago(1h))
    , minutes = datetime_diff('minute', now(), ago(1h))
    , seconds = datetime_diff('second', now(), ago(1h))
    

    Working with time zones via datetime_local_to_utc and datetime_utc_to_local functions:

    // local time across time zones
    print ParisTime = datetime_local_to_utc(now(),'Europe/Paris')
    , LondonTime = datetime_local_to_utc(now(),'Europe/London')
    , LondonTime = datetime_local_to_utc(now(),'Europe/Budapest')
    , AthensTime = datetime_local_to_utc(now(),'Europe/Athens') // local time across time zones print ParisTime = datetime_utc_to_local(now(),'Europe/Paris') , LondonTime = datetime_utc_to_local(ago(1h),'Europe/London') , BudapestTime = datetime_utc_to_local(ago(-1h),'Europe/Budapest') , AthensTime = datetime_utc_to_local(ago(-2h),'Europe/Athens')

    Applying different formatting with tostring, format_timespan and format_datetime functions:

    // date and time to string
    print date1 = tostring(datetime(2024-02-01 00:00:00))
    , time1 = tostring(totimespan("0.01:02:03"))
    , hours1 = tostring(time(1hour))
    
    // formatting timespans
    let t = time("25.10:35:59.123456");
    print date1 = format_timespan(t, 'dd.hh:mm:ss:FF')
    , date2 = format_timespan(t, 'ddd.h:mm:ss [ffff]')
    
    // formatting dates
    let t = datetime("2024-12-31 10:35:59");
    print date1 = format_datetime(t,'yyyy-MM-dd [HH:mm:ss]')
    , date2 = format_datetime(t,'yy-MM-dd HH:mm:ss')
    , date3 = format_datetime(t,'yy-MM-dd [HH:mm:ss tt]')
    

    Considering the above functions, one has a good basis for working with dates. 

    Happy coding!

    Previous Post <<||>> Next Post

    Related Posts Plugin for WordPress, Blogger...

    About Me

    My photo
    Koeln, NRW, Germany
    IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.