Power BI - Calculate Weeks

Create a new column

WeekNum = "Week " & RIGHT("00" & WEEKNUM(View_Billed_Time[Bill Date]),3)

Dynamics NAV - Fatal Error during installation

• This error may occur if the path for the installation files is too long
• Create a folder on the root drive and move the install files there
• Rename the folder to something shorter if necessary

Power BI - Parameters to accept inputs

• Modeling>Parameters
• Right click on Parameter>Allow Load
• Parameter is displayed in table list

Power BI - Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)"

clear permissions under Global permissions in Data Source Settings.

Dynamics GP - Web Client "An unexpected error has occurred. Please try again later or contact your administrator."

• Error
• An unexpected error has occurred.
• Please try again later or contact your administrator.
• Correlation ID: 4c74aba9-5976-46c0-828c-233c03920f40
• The following steps were taken to resolve the error
• Restarted GP Session Central Service
• Restarted GP Session Service
• Run cmd as administrator>type iisreset>Enter to restart the iis service

Teams - Move files or folders between teams and channels

• Select File/Folder>Click Ellipsis>Open in sharepoint
• Select files in Sharepoint>Move
• Select Team Site>Documents>Destination
• Be careful not to use the Teams Wiki Data, because it will move the files there in Sharepoint, but they will not be visible anywhere in Teams

NAV 2018 - Configuration Package Imports are Slow

https://dynamicsuser.net/nav/b/demiliani/posts/nav-2018-and-dynamics-365-business-central-is-rapidstart-too-slow-this-is-the-fix

• C8611
• Rem out this line GraphMgtGeneralTools.ApiSetup
• Add new function that only calls APISetup when IsAPIEnabled is true

LS NAV - Pole display does not display entire word

Drivers are incorrect.
Download latest drivers and reinstall

Dynamics NAV - Flowfilters and Flowfields to rapidly calculate totals without cycling through records

• A flowfilter can be used to store filters which will be used by a flowfield that is connected to a flowfilter
• By default, if the flowfilter value is blank, it is not applied to the flowfield (just like a regular filter)
• Therefore you can add any number of flowfilters to a flowfield, and it will not affect the value until the flowfilter value is updated
• How do you update the flowfilter values?
• CAL Example where Item2."Date Filter" is a flowfilter, and Item2."Net change" is a flowfield that is configured to use Item2."Date filter" in it's flowfield setup

    Item2.SETRANGE(Item2."No.",Item."No.");

    Item2.SETFILTER(Item2."Date Filter",'%1..%2',dDay0+1,TODAY);

    Item2.CALCFIELDS(Item2."Net Change");

    Item.CALCFIELDS(Item.Inventory);

LS NAV - "Cannot create an instance of the following...LSRetail.DD.Control.TransAutomClient" when trying to test connection

• Data director is not installed properly, or user does not have access to run data director
• Reinstall Data Director
• Tick the top 2 and bottom 2 options

SSRS - Export to Excel "We found a problem with some content" " Do you want us to try to recover as much as we can?"

This error occurs sometimes if you have null values, or values that export to excel as -0, or any other type of invalid formula that causes Excel to throw errors when it reads the file.

Review all fields on your SSRS report to ensure they are exporting valid values to excel that will not be accidentally translated into bad formulas.

SSRS- Export to Excel - "couldn't be downloaded"

https://answers.microsoft.com/en-us/ie/forum/ie9-windows_7/when-i-try-to-download-an-xls-file-from-ie9-i-get/55838041-3744-e011-9577-d485645a8348?msgId=fafbb7a2-3479-42e2-92cc-6abf6c9f99d9

1. Click the gear icon at the top right
2. Choose Internet Options from the menu
3. Choose the Advanced Tab
4. Scroll down to Security
5. Un-check Do not save encrypted pages to disk
6. Restart IE

Even if this does not work, You should be able to save to disk, and then open.

The error only occurs when trying to run directly without saving to disk first

Dynamics NAV - How to handle Catchweights for meat

https://www.olofsimren.com/add-fields-to-the-item-tracking-lines/

1. Setup
1. Define UofM conversions using estimated box weight to allow purchasing in KG, but buying in Boxes
2. Base unit for item is KG or lbs
3. Define Lot Tracking for item 
4. Locations - Grey, require pick ,require put away,  bins mandatory
2. Mods
1. additional field to track # of pieces in each box using Lot Information table
1. T5767 Warehouse Activity Line - add pieces field
2. P7376 Inventory Put-Away subform - add pieces field
3. P7376 Add Page Action to open Lot No. Information Card P6505
4. Add Pieces to the Lot No. Information card, page and list
3. Simplest Process
1. Purchase stock in KG's using kg uofm on PO
2. Confirm with Vendor>Create Put Away
3. Delivery Arrives>Warehouse Opens Inventory Put-Aways
1. Enter or scan box numbers as lot numbers
2. Enter weight as qty to handle for each lot
3. Split line until all lines put away
4. Create and Enter # of Pieces for each lot
5. Select Bins
6. Post>Receive and Invoice
4. Enter Sales Order in kg or boxes
1. Click "Create Inventory Put Away/Pick"
2. Tick "Create Invt. Pick"
3. Select Bin, Qty, Lot
4. Post and Print>Ship and Invoice
5. Shipment Prints (Pack Truck)
6. Invoice Prints

NAV - How to set a field (and option field) to be mandatory or required

• On the field properties, set NotBlank = Yes
• On Page trigger, error the onvalidate if less than ! for text or 0 for numbers
• On the Page, add your logic to the OnQueryClosePage
• PAGE.RUN can be used to open a page and set focus on a field

 field(Comment; Comment)

                {

                    ShowMandatory = true;

                    trigger OnValidate()

                    begin

                        If Comment < '!' then

                            error('Comment cannot be blank');

                    end;

                }

OnQueryClosePage(CloseAction : Action None) : Boolean

IF "Address 2" = '' THEN BEGIN

  MESSAGE('Please enter Address');

EXIT(FALSE);

END;

In AL, create a default option value, set the default value on new, then check for that specific value

pageextension 50040 "Sales Order - Layout" extends "Sales Order"

{    

trigger OnNewRecord(BelowxRec: Boolean)

    begin

        "Pickup Option" := "Pickup Option"::" ";

    end;    

trigger OnQueryClosePage(Closeaction: Action): Boolean

    begin

        IF "Pickup Options" = "Pickup Options"::" " THEN

            Message('Please select a Pickup Option');

        EXIT(false);

    end;

}

ENUMS

enum 50000 PickupOption

{

    Extensible = true;

    value(0; " ") { }

    value(1; Delivery) { }

    value(2; Pickup) { }

}

Dynamics NAV - Warehouse Pick - "The total available quantity has already been applied"

One of the Ship transfer entries did not register correctly, and posted with 0's instead of actual values.

The issue was resolved by manually inserting an entry to the Warehouse entries table with a Bin Type Code of "SHIP" and the correct amounts.

Power BI - How to Connect to Dynamics 365 (CRM) data

https://www.powerobjects.com/blog/2016/11/18/connect-to-dynamics-365-data-from-powerbi-desktop/

LS One Development - NuGet Package restore failed for project SM\Plugins\SiteService: Unable to find version '1.0.0' of package 'ReportViewer'

https://portal.lsretail.com/Old-Support-Interface/Knowledge-Base/aft/37391

• Removed reference from DevPack\Source\SM\Plugins\SiteService\packages.config
• All Nuget references are no longer required as all files are included in local folders
• Was able to build successfully

Power BI - Share a dataset with other users to allow them to edit Power BI Reports

• On the Workspace, click the ellipsis (...)
• Edit Workspace
• Advanced
• Privacy
• Members can edit Power BI Content
• Now, any user in the group can select a report, and click "Edit Report" from the toolbar

Visual Studio - C# - Console - Hello World, Run SQL Query Example

• C# Tutorial
• https://www.youtube.com/watch?v=GhQdlIFylQ8
• Download Free Visual Studio 
• https://visualstudio.microsoft.com/vs/community/
• Install, Run
• File>New>Project>
• Installed>Visual C#>Get Started>Console App
• Add code to Program.cs

 using System;  

 using System.Collections.Generic;  

 using System.Linq;  

 using System.Text;  

 using System.Threading.Tasks;  

 using System.Data.SqlClient;  

 namespace ConsoleApp1  

 {  

   class Program  

   {  

     static void Main(string[] args)  

     {  

       // The code provided will print ‘Hello World’ to the console.  

       // Press Ctrl+F5 (or go to Debug > Start Without Debugging) to run your app.  

       Console.WriteLine("Hello World!");  

       Console.ReadKey();  

       // Go to http://aka.ms/dotnet-get-started-console to continue learning how to build a console app!   

       string queryString = "SELECT * FROM [LSONE].[dbo].[COUNTRY]";  

       string connectionString = "Server=TEST;Database=;User Id=sa;Password=mypassword;";  

       using (SqlConnection connection = new SqlConnection(connectionString))  

       {  

         SqlCommand command = new SqlCommand(queryString, connection);  

         command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");  

         connection.Open();  

         SqlDataReader reader = command.ExecuteReader();  

         try  

         {  

           while (reader.Read())  

           {  

             Console.WriteLine(String.Format("{0}, {1}",  

             reader["COUNTRYID"], reader["NAME"]));// etc  

             Console.ReadKey();  

           }  

         }  

         finally  

         {  

           // Always call Close when done reading.  

           reader.Close();  

         }  

       }  

     }  

   }  

 }  

NAV CAL - Report performance and totals Out of memory

Use CREATETOTALS in CAL to calculate total values for variables instead of allowing SSRS to sum actual values to improve report performance.
This also replaces any sum login in the rdl with the calculated CREATETOTAL value

Dynamics NAV - How to get values for ledger entries to shortcut dimensions

• All ledger entry tables have a Dimension Set ID field
• This field corresponds to the dimension Set ID in the T480 Dimension Set Entry
• Join on Dimension Set ID, display the Dimension Value Code

Dynamics GP - Historical Stock Counts

select * from iv30700
select * from iv30701

BI Data Warehouse Rules

• Must have time dimension
• Must have empty entries for each field intended to be filtered against to allow for 0 sales to display correctly
• One entry per month can suffice unless daily reporting is required
• Must have a full log to show every time the sync job is run, and the totals of all data at the end to ensure no issues with data transport and synchronization across schedules