Power BI - "This Content isn't available" in Teams Tab

Publish to Team Workspace
Users need Power BI Pro Licenses to view the content

Dynamics NAV - How to Setup and Run the Consolidation Process

https://community.dynamics.com/nav/b/navvlogbypittis/archive/2017/10/20/how-to-consolidate-the-financial-statements-of-two-or-more-separate-companies-into-a-consolidated-financial-statement-in-microsoft-dynamics-nav-2017-microsoft-dynamics-nav-2017-features

1. Create Parent Company
1. Define Consolidated COA, Posting Groups, Dimensions, Accounting Periods
2. Define Children Business Units
3. Define Exchange Rates used for consolidation (Companies>Business Units)
1. Average Rate
2. Closing Rate
3. Last Closing Rate
2. In Children companies
1. Fill in Consol Debit Acc. and Consol Credit Acc., Consol Rate for each GL account 
2. Fill in the Consolidation code field on each GL account if you wish to map the dimension to a new consolidated dimension
3. Test consolidation from Parent Company
1. Go to Business Units>Select Child Company>Actions>Test Database
2. Resolve all errors
4. Run Consolidation
1. Go to Business Units>Select Child Company>Actions>Test Database
2. Enter Time period
3. Repeat for each Parent if multiple tier Parents are configured
5. Process Elimination Entries
1. Run elimination report to identify elimination entries
1. Business Units>Navigate>Eliminations
2. Manually enter and post elimination entries in Consolidation company
6. Re-Run consolidation for previous period if required
1. Go to Business Units>Select Child Company>Actions>Test Database
2. Enter Time period
3. The original imported entries for that time period will be modified to 0 value, and new entries will be imported

LS One - Print Multiple Labels

Hold Ctrl + Click items to print
Click Print Labels, enter quantity

Power BI Desktop - Refresh fails to refresh all data sources or times out

• One of your data tables has a problem.
• Fields are missing
• Bad characters in field names
• Data source has no connection
• All data sources will fail to refresh by using the refresh button if this happens.
• Try to refresh each table one at a time until you find the offending table

SSRS 2014, Report Builder, SQL Express and different version compatibility. DLL Missing.

• SQL Express 2014 - Comes with SSRS, but no Report Writer
• SQL Express 2014
• https://www.microsoft.com/en-us/download/details.aspx?id=42299
• Must download and install Report Builder 2014 (Other versions will cause compatibility issues with the reports)
• https://www.microsoft.com/en-us/download/details.aspx?id=42301
• Works on Windows Embedded Standard(SQL 2016 does not work)
• Report Builder 2014 Gives a DLL error on install
• Leave the URL Blank
• The url should look like this (include the / on the end)
• http://MYPC:80/ReportServer/
• You can set it in the Launch Report Builder>Click top left button>Options>Url after the install completes
• SQL 2014 can host SSRS 2016
• Report Writer 2016 is SUPPOSED to be compatible with SSRS 2014 reports
• My personal experience is that they are not compatible, and you cannot display reports edited in Report Builder 2016 on SSRS2014
• You must use Report Builder 2014

SQL Make Stored Procedures available across databases, or affect other databases

Create stored procedure in Master database.
Prefix it wit 'sp_' so sql identifies it as a public stored procedure.
Explicitly name your database objects in the stored procedure.

LS Retail - Enable Z-Report Autoprint after EOD (End of Day)

To enable auto print of z-report when doing an EOD go to (functionality profile > enable Z-Rep Autopr after T.De EOD) 

• Enable cashiers to print z-report by setting the option to yes in the Cashier Staff Permissions
• Set staff access to manager for the Z and X report button properties on the POS

BI Dashboard Design Guidelines

• Layout Schemes
• Examples from the community
• https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery;LiSESSIONID=56B24BC5176384B4867BBA3C8A265989
• Color Schemes
• Learn rules of what looks good
• https://lifehacker.com/learn-the-basics-of-color-theory-to-know-what-looks-goo-1608972072
• Color scheme generator
• http://colormind.io
• Power BI Advice
• https://exceleratorbi.com.au/exceleratorblog/

Smartconnect or Excel ODBC Equivalent of Getdate() = Now()

ODBC Equivalent of Getdate() = Now()

To adjust the date, use TIMESTAMPADD
https://docs.faircom.com/doc/sqlref/33476.htm

Example:

where "JrnlHdr"."TransactionDate" >= {fn TIMESTAMPADD( SQL_TSI_DAY, -2, Now())}

gets everything newer than two days ago

Power BI - Calculate Percentage % of Total Value

Create a Measure, Use this formula
DataSet Table is Combined_HistoricalSales.
Get the Sum of TotalAmount for the ProductClass. Store it in var bigTotal, then divide by Sum of TotalAmount. Multiply the result by 100.

-------------------------------
MeasureName = var bigTotal = SUMX(ALL(Combined_HistoricalSales[ProductClass]), CALCULATE(SUM(Combined_HistoricalSales[TotalAmount])))
                    return DIVIDE(SUM(Combined_HistoricalSales[TotalAmount]),bigTotal)*100


------------------------------
To get percentage totals of items within a class
------------------------------
% of Class Sales = var bigTotal = SUMX(ALL(Combined_HistoricalSales[StockCodeDesc]), CALCULATE(SUM(Combined_HistoricalSales[TotalAmount])))
                    return DIVIDE(SUMX(ALL(Combined_HistoricalSales[ProductClass]), CALCULATE(SUM(Combined_HistoricalSales[TotalAmount]))),bigTotal)*100

PowerBI-Step by Step Guide

Power BI is a Data Visualization and Dashboard tool that makes it easy to share and maintain summary-level and trend reports on the web and mobile devices, but using local data, with the ability to drill into detail if required.
It very good at displaying, sharing, and synchronizing data that that already been sanitized, or is very similar in nature.
Also works well with onedrive, sharepoint, azure, and other web, or cloud data sources
It is NOT a replacement for SSRS
It is NOT a standalone data warehouse (Use SQL).
It will NOT magically sanitize your data and join it perfectly (Use SQL).
It will NOT periodically run routines to transform existing data (Use SQL). (The gateway will synchronize data on a schedule between your local data sources and Power BI Online account.)

1. Download Power BI Desktop
1. https://powerbi.microsoft.com/en-us/desktop/
2. Enter Data (Custom Tables in Power BI)
3. Edit Queries (Filter Source Data)
4. Create Roles
1. Each Role can have predefined filters on tables
5. Home>Get Data>Choose Data Source
1. Data is copied into PowerBI
2. Excel Sheets are treated as File Data Sources
3. SQL Connections can use Windows or SQL authentication
1. If you update sql views or tables, the changes will reflect in the data source after you refresh data and reload the page
4. You can also put the excel sheet into onedrive, then use the excel sheet as a web data source using the onedrive web link for the file
1. this also auto-synchronizes data when onedrive synchronizes
6. PAGES
1. Select fields (just like Pivot Tables)
1. Select fields
2. Right click fields>Create Hierarchies
3. Right click fields>Create Measures
4. Set row, column, Filter options
5. Set display options
2. Add multiple panes in page to build dashboard
3. Selecting Values sets filter on all panes
4. Build Pretty Dashboards
1. https://www.youtube.com/watch?v=rS8xmkoasQU
2. Use Background images for dashboard theme
3. https://powerbi.tips/
4. 
   
5. Use appropriate charts
1. http://extremepresentation.typepad.com/files/choosing-a-good-chart-09.pdf
6. Edit Queries
1. Allows you to set sorting options that will be used for all data including matrix reports
7. TABLES
1. View Data
8. RELATIONSHIPS
1. Connect Tables
9. Setup Data Gateway (required to sync local data to Published Power BI Dashboards)
1. https://powerbi.microsoft.com/en-us/gateway/
2. Download and install (Not the personal one)
3. Register Gateway
1. Sign in with office 365 account
2. Gateway clusters are for redundancy, if one gateway fails, it will switch to another in the cluster
3. Add recovery key
4. Enable Gateway
1. Gear>Manage Gateways>Tick all options
2. Create a Cluster
3. Top left above "Gateway Clusters">Click Add Data sources to the cluster
1. For Files, Enter Local path, windows login and password, Privacy level blank
2. If any data sources are incorrectly configured, they all stop working
5. Schedule Data Source Refresh
1. My Workspace>Datasets>Select Dataset>Ellipsis(...)>Settings
2. Gateway Connection>Select Gateway>Use Data Gateway
1. Slide the "Use Data Gateway" option to "On"
2. If you leave this off, the Data Source Credentials and Scheduled Refresh menus are greyed out and disabled
3. Gateway Connection>Select Gateway>Scheduled Refresh
4. Re-Publish your solution after creating the gateway
6. Manual Data Refresh
1. On the left, under My workspace, hover over data set
2. Click ellipsis(...) > Refresh Data
10. Publish your Dashboard
1. File>Publish
2. If you do not have Power BI Pro License, you cannot view shared content
11. Access your Dashboard
1. https://powerbi.microsoft.com/en-us/landing/signin/
2. Select Dashboard
3. Click Share in top right hand corner (Requires Power BI Pro License)
12. Notes
1. TEST ALL REPORTS and Data Connections right through to publishing and scheduling before starting any serious work
2. Data may give trouble to sync
3. Schedules may give trouble to run

LS NAV Demo Mods

• GL
• General Ledger Setup: Set Show Amounts to All Amounts
• P16 Add button for P490 Account Schedules Overview
• Add GL Budgets Shortcut to Bookkeeper Navigation Menu
• Add 2018 budget entries
• Install Excel
• Account Schedules: Create Column Layouts
• Prior Year (-1Y comparison date)
• History (-1M to -6M comparison date)
• By Department dimensions (Department filter on each column for each value)
• P9004 add buttons for P118 General Ledger Setup, P1875 Business Setup, P119 User Setup
• Inventory
• T32 Item Ledger - add description flowfield
• P38 Item Ledger entries - show Description flowfield

Dynamics NAV - Replenishment system - Transfer

• To setup transfers to come up in the requisition worksheet automatically, 
• Setup Stockkeeping units
• Define the specific warehouse that can be used to transfer a specific item to a specific location
• Click Carry out Actions from the Requisition worksheet
• This will generate a Transfer Order

Dynamics GP - Remove or Clear tem Allocations - Manufacturing Picklist

• These items are on open picklists that haven’t been fully picked.
• Delete the Picklist to remove the allocation.
•  From the Item Allocation Inquiry Screen, Select the document holding the allocation, click “Document Number”
•  Navigate to the Picklist
•  Enter the MO number
• Click Delete to delete the picklist. This will release the allocation.

Dynamics NAV - Page or Subpage does not insert data into table immediately. Refresh or next clears the record and does not commit data.

https://community.dynamics.com/nav/f/34/t/203873

• If the subpage is a LIST type, it will not save the record until the "Ok" button is pressed on the parent form
• If the subpage is LISTPART type, it will save the record immediately

LS NAV - Update Jobs not updating by action

Retail Setup>General>Preaction Creation By: Set to Database Triggers

Affects everyone after 11.0

LS NAV - Non-Inventory Items - Miscellaneous Charges, Services - Create an item that does not track stock

Retail Items>Invoicing>Tick No Stock Posting

Core NAV does not have any options to prevent stock posting.
Items>Inventory Value Zero only affects cost posting, but will still track stock
You need to transact directly against a GL account instead of using an item, or use a Service Item
Or periodically adjust stock to 0.

Dynamics GP - Manufacturing - Finished Goods Costs are higher than expected

Scenario:
Receive Raw Material with FIFO costs, Complete an MO using backflush, no labor, no overhead, Finished Goods cost does not match raw material cost

Explanation: