Wednesday, March 06, 2024

Extra parameters on Report

 I have created a class with 2 parameters however when the Dialog box is rendered there are 4 parameters displayed as shown below



So basically when the parameters are rendered these are done from the SSRS report. When we open the SSRS report parameters we can notice that these additional parameters are added to the report and these are the same parameters which are repeating. 

To solve the issue, we need to delete the unwanted parameters from the parameters list and redeploy the report. 






Tuesday, January 16, 2024

Find Hyper-V Host

 When lost between multiple Hyper Hosts and Images you can always find the host of a hyper-V image using the registry. 

The following powershell command can be used to read the registry 

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters"  | Select-Object HostName

Sunday, January 14, 2024

SQL remove invisible characters

You will need a combination of 3 functions 

1. PATINDEX: PATINDEX is used to find a position in a string where a specified character or pattern exists. In our chase we have specified all characters from ascii value 0 to 31, 127 and 255. 

2. SUBSTRING: Once the PATINDEX function return the location where one of the invisible characters is found, substring function extracts that 1 length string to isolate the invisible character. 

3. REPLACE: This function then replaces all occurrences of the invisible character found and replaces it with a zero length string.

select REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ') [Text] 
from DataExtract

Please note that casting the column to be replaced with binary collation is required else some of the characters are not parsed.  


As the above statement only replaces all occurences of the the first invisible character found. It might be more meaningful to extract this data into a table and update the text column replacement until there are no more rows found with invisible characters 


update DataExtract
set Text = REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
where  PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ) > 0


Sometimes a value starting with a double quote '"' might cause an issue when one tries to paste the data in Excel, as excel tries to interpret a start and end of text column using double quote as a delimiter. So if you are copying and pasting values in excel make sure that the column does not start with a double quote else best is to replace such occurrences:

Update DataExtract
set  [Text] = SUBSTRING( [Text], 2, 10000 )
where left( [Text], 1 ) = '"'


Tuesday, September 05, 2023

Fetch the Enum labels in SQL Statements

 When dealing with writing SQL Statements the Enum values and their corresponding names force us to write switch case statements which is time consuming. A shortcut for this method is as follows. 

There is already a table in the D365 Database called SRSANALYSISENUMS this table is used to store the D365 labels for use in the Datawarehouse cubes. By default all the tables that are part of any perspectives are populated in this table. However, for some of our enum we can explicitly populate this table and use the same in SQL. 

Create the following job to populate any given enum into the SRSANALYSISENUMS table. 


    public static void main(Args _args)
    {
        SRSAnalysisEnums analysisEnums;
        DictEnum currentEnum;
        str currentEnumName;
        int valueCount;
        str enumItemName;
        int enumItemValue;
        RecordInsertList records;
        Dialog dialog;
        DialogField  dfEnumName;

        dialog = new Dialog("Please enter the enum name");
        dfEnumName = dialog.addField(extendedTypeStr("Name"));
        dfEnumName.label("Enum name");
        if (dialog.run())
        {
            records = new RecordInsertList(tablenum(SRSAnalysisEnums));
         
            currentEnumName = dfEnumName.value();
            currentEnum = new DictEnum(enumName2Id(currentEnumName));
            if(currentEnum == null)
            {
                throw error(strfmt("Enum with name %1 does not exists.",currentEnumName));
            }
            valueCount = currentEnum.values();
            ttsbegin;
            delete_from analysisEnums where analysisEnums.EnumName == currentEnum.name();
            for(int j = 0; j < valueCount; j++)
            {
                enumItemName = currentEnum.index2Symbol(j);
                enumItemValue = currentEnum.index2Value(j);
             
                select firstfast forupdate EnumName, EnumItemName, EnumItemValue
                from analysisEnums where analysisEnums.EnumName == currentEnum.name() && analysisEnums.EnumItemValue == enumItemValue;

                if (analysisEnums)
                {
                    if (analysisEnums.EnumItemName != enumItemName)
                    {
                        analysisEnums.EnumItemName  = enumItemName;
                        analysisEnums.update();
                    }
                }
                else
                {
                    analysisEnums.EnumName      = currentEnum.name();
                    analysisEnums.EnumItemName  = enumItemName;
                    analysisEnums.EnumItemValue = enumItemValue;
                 
                    records.add(analysisEnums);
                }
            }
            records.insertDatabase();
            ttscommit;

            Info("Completed");
        }
    }

 

Once this job is created and compiled we can run this class for each Enum that we want to be populated. To run the class from the url use the following syntax: 


https://ds-dev-7658e683048e57b40devaos.cloudax.dynamics.com/?cmp=ds01&mi=SysClassRunner&Cls=SRSAnalysisEnumsGenerator

In the above URL the servername and cmp parameter should be replaced to match your environment.

Friday, September 01, 2023

Split CSV values as columns

To split a CSV value in a given column into different individual columns use the following trick. 

Firstly, let us look at the different functions that are involved in this process. 

1. Table Value Constructor: The Value clause can be used to create a table out of values. This clause is used in the insert statement however its interesting to note that it can also be used in joins to create a derived table with fixed values. 

E.g.: In the examples below a derived table Source has been created using literal values. 

select Source.NewReasonType, Source.NewName
from (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) 
     AS Source (NewName, NewReasonType)

2. Apply operator: Just like the join operator we have another operator apply which has been introduced in SQL and this operator indicates to the SQL Engine that the clause has to be evaluated for each row

3. JSON_VALUE: This function is used to extract a scalar value from a Json string. The function accepts 2 paramters 1. Json expression and 2. subscript of the node to refer.

E.g.

       

DECLARE @data NVARCHAR(MAX);
SET @data = N'{  
     "info":{    
       "ID":1,  
     "Name":"Akshat",
       "address":{    
         "City":"Gurgaon",  
         "Country":"India"  
       },  
       "Favorite_Subject":["English", "Science"]  
    },  
    "type":"student"  
 }';  
SELECT JSON_VALUE(@data, '$.info.Name') AS 'Name', 
       JSON_VALUE(@data, '$.info.Favorite_Subject[1]') AS 'Favorite Subject';

 

E.g: If Display value contains the dimension values separated by a pipe (|) the below statement will break it into individual dimension columns



 select
JSON_VALUE(JS,'$[0]') Dim1, JSON_VALUE(JS,'$[1]') Dim2, JSON_VALUE(JS,'$[2]') Dim3, JSON_VALUE(JS,'$[3]') Dim4
from DIMENSIONSETENTITY T17
Cross Apply (values ('["' + replace( T17.DISPLAYVALUE ,'|','","')+'"]') ) B(JS)
 

Tuesday, April 25, 2023

SSRS Report CreatedTransactionId

CreatedTransactionId, is a read-only field maintained by AX.  

For a table that has its CreatedTransactionId property set to Yes, when an insert occurs, AX automatically sets the CreatedTransactionId value (to the current value of appl.curTransactionId()).  

The CreatedTransactionId is tied to the outermost transaction only – that is, if there are nested transactions, they do not get their own CreatedTransactionId, but instead share the same one as the master (outermost) transaction.   Only after the ttsLevel has dropped back to 0 will a new CreatedTransactionId get generated.


Sunday, March 12, 2023

SQL Enable Maintenance Mode

Certain tasks like enabling of a new accounts Structure requires the SQL database to be in maintenance mode. This is done using the LCS portal for UAT and PROD environments. However, if the same has to be done on the DEV machine then the database has to be manually put into maintenance mode. 

To enable maintenance mode in the SQL server database of the DEV Machine using the following script. 

update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'