(RTC-30351)

A new report based on 0016: Store settlement has been implemented. This report has option to multi select stores. The report allows to follow up the store settlement: on the first page, the settlement for all selected workstations is shown. The settlement per each workstation is displayed on following pages. Report is divided in 15 subreports.
The report also shows global localization number of the store (GLN). GLN is imported from both POS Master and Store Service and updated in Reporting data warehouse.
(79.2) The processing of Retail Store dimension has been extended with GLN (Global Location Number).

Web user tracking

(RTC-28181, RTC-28182, RTC-30015)

To provide the data for segmentation on web user activities, the import of web user activities from Marketing Service has been implemented. The imported blob type is: Marketing.Export.TrackingFact.
Activities with no member will be updated with correct member with N-job providing activity with correct member is received (a member lookup from user GUID was implemented to provide more information about member web activities for segmentation purposes).

Expose SegmentationSubscription flag in vMember and vDimMember views

(RTC-30226)

An information about SegmentationSubscription (also known as Data Analysis flag) has been added to dimension views: pub.vMember and pub.vDimMember.

Support for SQL 2022 - fixes

(RTC-30529, RTC-30646, RTC-29676, RTC-30768)

1. Fixes in packages that fetch member variables data from Chain Web (tags) or Bridge (variables). (79.2) Reset sync status for tags script attached.

2. Improvements in the package that fetches item lists from Chain Classic.

3. The SQL Server native client is deprecated for SQL 2022. Therefore, the provider for all SQL connections has been changes in all config files.

4. (79.1) Refactored code in SSIS package which handles processing.

Gift card sales procedure - improvements

(RTC-29305)

Improvements in the procedure for fetching member activities and sales data for member dashboard.

Data warehouse maintenance

(RTC-30131)

The procedure for deleting fact data for specific store and date(s) has been updated with new tables.

Requirements:

78.1: Reset sync status with the following script: Lindbak POS Reporting Upgrade x.82.78.1.sql (available with upgrade package)

*or a separate script (see upgrade guide for details).

Support for SQL 2022

(RTC-27882, RTC-27883, RTC-28746)

Reporting supports SQL 2022 now. The changes mainly concern the SSIS packages, which were altered to be compatible with new SQL version.
78.1: (RTC-30529) SQL 2022: Fixes in packages that fetch member variables data from Chain Web (tags) or Bridge (variables).

Store Settlement: Missing sold DepositRefund (IUP) in RIGAL export

(RTC-28560)

RIGAL codes for DepositRefund IIP, IUP, IA1 - IA5 and IB1 - IB5 are now supported in the export of RIGAL statistics to StoreSettlement.

To set it up, the additional configuration in POS Services worker config is needed.
There are two parameters to set (if not present in the config, copy them from template file):

• StoreSettlementDepositRefundOutItemGroups - set to item group(s) used for outgoing deposit refund (sold DepositRefund). Outgoing DepositRefund is definded as sale from this itemgroup(s) with direction Out,

• StoreSettlementDepositRefundVatRates - a parameter for mapping VAT group to a number between 1 and 5. This is used for IA1 - IA5 / IB1 - IB5.

ReportingDataIntegration: new features and improvements

(RTC-27432) Clean-up of processed rows

Cleanup job will now also delete processed rows in order to improve performance. Improvements are done in LP package ReportingDataIntegration.

(RTC-27139) Import of promotion data from Promotion Management

The import job has been extended with import of promotion attributes from Promotion Management.
NB! This data is not updated in Lindbak POS Reporting DW database.

(RTC-22283, RTC-22286) Item import - extensions

1. Import of nonsale type
   The information about item nonsale type is now imported from Item Management.
   Only nonsale types with valid codes are imported. The valid code is an integer number in the range 0-99. If the code is not a number, or is higher than 99/lower than 0, the nonsale type information is ignored.

2. Import of alternative GTINs
   Import of items from Item Management has been extended to support alternative GTIN (tandems EANs).

Reporting v. 77

Requirements:

*or a separate script (see upgrade guide for details).

Wet stock extensions

(RTC-25037, RTC-25464, RTC-25466, RTC-25712)

The wet stock functionality in Reporting has been extended with additional features:

1. Export of end of day dippings (EOD) for manual stations
   Reporting will now export End of day (EOD) dippings when manual dippings are done. If a manual dipping is registered on the same day, the EOD dipping will be exported at midnight. If a manual dipping is done for earlier day(s), the EOD dipping will be exported immediately.

2. Import of adjustments
   Reporting is able to update fuel adjustments from Chain Classic. The adjustments are exposed in the OLAP cube, as well as in wet stock report.

3. Support for L15 corrected dippings in Reporting
   The Unit of measure dimension in OLAP cube is extended with new member: Liter15 (LTR15).  A liter at 15 degrees is a volumetric measurement for the volume of the fuel at a specific temperature, namely 15 degrees Celsius. In report for Wet Stock and Daily Wet Stock, a support for new unit of measure: Liter15 was added. When dippings are registered in Liter15, the values will be marked with * on the day of the dipping. Changed reports: 0750_WetStock and 0751_DailyWetStock

Import of cashiers from User Management

(RTC-24136)

Reporting is able to update Cashiers from UserManagement module, instead of fetching them for POS Master.
Prerequesite: Export of cashiers to blob (blob type: UserManagement.User.Export)

To set up:

On-premise LIP package ReportingDataIntegration must be installed with jobs StagingCleanup, StagingImport and StagingMerger activated.
SSIS package CashierStaging must be enabled (removed from varDisabledPackages)
SSIS package DimOperator must be disabled (added to varDisabledPackages)

ReportingDataIntegration: improvements

(RTC-24984, RTC-26301)
The following improvements to the integrations with cloud services have been implented:

1. Update of stores from StoreService
   To avoid stops in merger jobs, Reporting supports store name with more than 50 characters.
   When updating stores in on-premise data warehouse, the name is truncated after 50 characters.

2. Update of items from ItemService
   Reporting database and cube has been extended to support items with long names in hierarchy.

Export to Cash Settlement - correction for bag number

(RTC-25635)

When there are several bag numbers available for ISB FTD (store), the highest available value will be exported to file. This is to make sure correct bag number are exported for users using balance per shift.
The bag number for operator and workstation will not be affected.

(RTC-15706)

As a part of exporting data from on-premise Reporting to Azure DW, we can now export on-premise data to file.

New package which runs as part of Rt-job, ExportSales. Package will export data from FactRetailTransactionArticle to file. Package uses syncstatus with objectname ExportSales, It only exports ID's higher than LastSyncVersionCT and only triggers if lastupdate is higher than current timestamp + minutes in varExportLatencyInMinutes.

File is exported to varExportFolder

Use color code from the sale in member segmentation

(RTC-21719)

To plan campaigns better and show customer colors they bought, it is possible to link salesline to color code now.

In Reporting, the color data is stored in a new dimension called DimCustomProperty. All POSLogs containing combination of Color (colorname) and ColorCode in Detail_Article will create a new entry in the dimension.
Note that it is the combination of Color (colorname) and ColorCode that crates new color in dimension. Entering colors in CR without validation can cause unwanted rows in dimension.

To set up:
In LindbakPOSReportingConfigPOSLog.dtsConfig under varBusinessRule. Add ;CustomPropertyType=Color to existing string. In the future, this dimension can be used for other properties than color.

Extend vTransactionRealTime view with line and receipt notes

(RTC-22791)

To follow up on returns better, we now include line note and receipt note on item transactions. LineNote and ReceiptNote are available in existing view pub.vTransactionRealTime

Support bonus amount for discounted items in Reporting

(RTC-22868)

Reporting supports new bonus rule from Chain Web. This makes it possible to include bonus amount for discounted items.
Note that bonus rules in Chain Web are transferred to Reporting only by N-job.

Reporting v. 74

Report sales per shift

(RTC-11322, RTC-12361,RTC-11325, RTC-12285, RTC-11058)

We have implemented a new dimension in Reporting, Shift. Making it possible to report sales per shift.
POSLog contains shiftID and Reporting will link sales to correct Shift dimension in DimShift.
We have also extended Reporting services in POS Services with new method GetBalanceForShift which is used by POS when validating balance for shifts.

Realtime stock information

(RTC-11667)

To access information about realtime stock data, a new view has been created. View is based on FactStockRealTime table.

New view pub.VStockRealTime
pub.vStockRealTime Comment Type Related keys:

• EAN Varchar(50)

• Quantity Decimal(18,8)

• ReservedQuantity Decimal(18,8)

• OrderedQuantity Decimal(18,8)

• AdjustedQuantity Decimal(18,8)

• StoreNumber Varchar(50)

• StockDate Datetime

• DateLoaded Datetime

• DateUpdated Datetime

• StockLocation Nvarchar(50)

Shift dimension in Olap

(RTC-11324)

A new dimension in Olap is created. With new dimension Shift we can report on Article, Receipt and Tender measure per Shift.
Shift dimension is populated from DimShift table which is again populated from Shift start / stop receipts.

Order history data

(RTC-10866)

In order to report on changes in orders by operator, we have redesigned Orderhistory. Order history is stored in new table FactOrderHistory. Data is also available in Olap.

Note: When upgrading old Orderdatahistory data must be migrated, see upgrade instructions in Jira for further details.

Store data from Cloud

(RTC-9631)

We have extended store information received by StoreService. Reporting fetches store data such as:

• City

• Postal code

• Country

• Organization name and number

Requirement: Update in LIP package ReportingDataIntegration and database (Reporting DW v67)

Cashier statistics report

(RTC-794)

A new cashier statistics report is created to analyze and investigate cashier behavior. In order to create this report, the following changes are implemented in Reporting:

• Order history is stored in new table: FactOrderHistory

• Order history data is added to Olap.

Import items from Chain Classic

(RTC-12022)

In order to not be dependent on POS Master database for dimension data, we import items from servicebus.

Pre condition: Chain classic delivers following file types to azure blob; vare.99900 (item), farge.99900 (color), storr.99900 (size), tandem.99900 (tandem) and register.99900 (article hierarchy). This is done with LIP-job ChainToCloud.

Reporting LIP-job ReportingDataIntegration listens on servicebus topic batchtoprocess, subscription "ReportingImprot". File is downloaded to DW.staging.FlatBatch and unpacked to DW.staging.FlatBatchJson and DW.staging.Item.

New SSIS package ItemStaging (Rt-job) takes item data from DW.staging.item to DW.DimArticle and puts in request for processing.

Data for article hierarchy, color and size are stored in table register.ItemProperty and used for lookup on article hierarchy names when updating items.

NB! When enabling ItemStaging package disable following packages (to disable import from master), DimArticle, DimArtLC, NonSaleType and ArticleHierarchyMaintenance.

Store data from Cloud

(RTC-9631)

We have extended store information received by StoreService. Reporting fetches store data such as:

• City

• Postal code

• Country

• Organization name and number

Requirement: Update in LIP package ReportingDataIntegration and database (Reporting DW v. 67)!

Improvement

Exclude Opening Balance

In procedure GetMemberBonusPointsInPeriod we have excluded transactions with description 'Opening balance'. To include these transactions anyway, procedure can run with new parameter IncrementalImport = 0. Default value for this parameter is 1.

This change is made to correct an issue which caused Retail - and subsequently APSIS/MailChimp - to show more bonus on the member than they actually had when anonymizing old member transactions.

Improvement

Rejected order lines (RTC-10838)

In order to display number of rejected order lines, Reporting imports this status from Retail database.

This is done by existing package FactOrderRetail.

This package require Retail database version 2.10.0.7 because of new procedures.

Note: There are some limitations to this functionality: To get rejected status we need to fetch changes in Retail db and changes in POSLog. In some cases this can result in incorrect status for Order lines.

Case1: We update changes in Retail database in one RT-job, but not changes from POSLog. Status for order line will be ONLY Rejected. Before we update POSLog we will not have assign status for new store.

Case2: We update changes in POSLog in one Rt-job, but not changes from Retail db. In this case Rejected lines will have status "Reassigned" before we update correct status from Retail db.

Case3: We create order, reject it in Chain Web. If we update changes from Retail db before all POSLogs, we will only have reassigned status in Reporting

(RTC-10047)

In order to calculate picking time for dispatch orders more correctly, picking time calculation is based on opening hours for stores.

Opening hours are fetched from Chain Web with a new package StoreOpeningHours, which is part of N-job. Package is default disabled. This means any change in store opening hours will not be reflected in the order management dashboard until the next day, as it is updated each night.

Locating active cashiers from Chain Web

(RTC-3625)

A new procedure is created in Reporting that returns cashiers with sale for given day. Procedure is used by Chain Web to fetch cashiers for balance registration.

External order number

(RTC-890)

In view for OrderStatusHistory a new column is added to show external order number from DimOrder.

Reporting version available in Olap

(RTC-10764)

A new hidden dimension is added to Reporting cube. This dimension (registry) contains database version from registry table in DW and server name. This information is used in compatibility handling in Reporting Service in Cloud.

Store data from Cloud

(Jira links: RTC-954, RTC-3477, RTC-728, RTC-781)

Reporting is able to retrieve store and store group data from StoreService in the Cloud.

Solution consists of LIP-job which fetches service bus message and stores them in a local database. SSIS packages merges data from staging to DW tables.

Data flow:

• LIP-job ReportingDataIntegration/StagingImport - Listens for new messages on Azure servicebus. Saves new messages in staging tables.

• LIP-job ReportingDataIntegration/StagingMerger - Merges new data in staging tables.

• SSIS package (Rt-job) StoreStaging and StoreTeamStaging - Takes data from staging tables and updates / creates stores in DW.DimRetailStore and creates / updates team relations in FactRetailStoreTeam.

Improvements

Customer order with missing delivery type (RTC-10409)
Reporting support POSLogs where delivery type is missing in inverted receipts, to avoid the RT-job failing when it occurs.

Default customer order date (RTC-10404)

In cases when default customer order date is set to .net default (0001-01-01). Reporting will convert this to 2000-01-01. This is because 0001-01-01 (datetime2) is not supported by Reporting (only supports datetime).

Support for large partitions (RTC-10036)

Improvements are made in the "create script" for the cube. This is to handle articledistinct partitions over 4GB. This previously caused issues when processing the cube.

Creating partitions in N-job (RTC-3145)

Improvements are done related to creating of monthly partitions in N-job. If N-job doesn't run on the first try, partitions will still be created next time N-job runs.

Loyalty - Update email subscription for member (RTC-10646)

When updating members from Loyalty database we make sure to update emailsubscriptiondate. This will make sure correct data is found in view pub.vMembers for members who has cancelled their email subscription.

Sales - Assortment in view vSales (RTC-9819)

To provide information about sales related to assortment, pub.vSales view is extended with a new column, assortmentID. AssortmentID refers to ID in view pub.vDimAssortment.

Store Settlement - New deleted statuses included in RIGAL export (RTC-762)

When exporting to RIGAL we have included deleted receipts/receipt lines with status DeletedFromUnfinishedReceipt.
These will affect RIGAL code IS1, IS2 and IMB.

Reporting v. 64

Currency information to Cash settlement

(TFS: 188570)

To have better control over currencies, the rigal output is extended with new codes for currency.

Two new parameters in POS Services worker. New parameters needs to be copied to template file.

• StoreSettlementEnablePaymentCardInCurrency - default False. If True we export currency amount for all card types (K01 - K99) in field #8.

• StoreSettlementEnableCashInCurrency - default False. If True we have new codes to split cash in currency amount. Field #6 is currency amount, field #8 is same amount in main currency.

New codes are CEU (Euro), CNO (NOK), CSE (SEK) and COT (Others).

Supplier order number on items

(TFS: 188574)

To support filter on supplier order number, we have extended article information we fetch from Lindbak POS Master.

DimArticle has a new column “SupplierOrderNumber”.

Requirements: Lindbak POS Master 3.0.0.32, or updated DimArticle procedures.

Improvements

Reporting v.63

Released 4th November of 2020

Support for Vipps and Swish

(TFS: 187446)

Two new payment cards are added in Reporting.

PaymentCardID 209, Swish and PaymentCardID 210, Vipps. This is added to allow reporting on Vipps and Swish transactions.

Deposit refund as sale

(TFS: 187496)

It is possible to include received deposit refund as sale in Olap. This is done by changing articletype from DepositRefund to Stock when updating article line in reporting.

Note that nonsale items are not included in sales from Olap.

To set up:

In LindbakPOSReportingConfigPOSLog.dtsConfig, find existing parameter varBustinessRules.

Change DepositRefundAsSale=False to DepositRefundAsSale=True.

Reporting v.62

Released 22nd September of 2020

Requirements:

Expiration date on internal transfer