Reporting v. 79.2
Document status: RELEASED
Date: 79.0 / 79.1 / 79.2
New report for Store Settlement
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
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)
- Fixes in packages that fetch member variables data from Chain Web (tags) or Bridge (variables). (79.2) Reset sync status for tags script attached.
- Improvements in the package that fetches item lists from Chain Classic.
The SQL Server native client is deprecated for SQL 2022. Therefore, the provider for all SQL connections has been changes in all config files.
- (79.1) Refactored code in SSIS package which handles processing.
Gift card sales procedure - improvements
Improvements in the procedure for fetching member activities and sales data for member dashboard.
Data warehouse maintenance
The procedure for deleting fact data for specific store and date(s) has been updated with new tables.
Requirements
Component | Version |
|---|---|
POS Master* | 3.0.0.90 |
POS JournalService | 2.82.xxx.x / 3.82.xxx.x |
POSLog XML | 82 |
*or a separate script (see upgrade guide for details).
Reporting v. 78.1
Document status: RELEASED
Date: 78.0 / 78.1
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
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
- 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. - Import of alternative GTINs
Import of items from Item Management has been extended to support alternative GTIN (tandems EANs).
Requirements
Component | Version |
|---|---|
POS Master* | 3.0.0.62 |
POS JournalService | 2.82.xxx.x / 3.82.xxx.x |
POSLog XML | 82 |
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).
Reporting v. 77
Document status: RELEASED
Date:
Wet stock extensions
(RTC-25037, RTC-25464, RTC-25466, RTC-25712)
The wet stock functionality in Reporting has been extended with additional features:
- 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. - 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. - 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
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:
- 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. - 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
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.
Requirements
Component | Version |
|---|---|
POS Master* | 3.0.0.62 |
POS JournalService | 2.82.xxx.x / 3.82.xxx.x |
POSLog XML | 82 |
*or a separate script (see upgrade guide for details).
Reporting v. 76
Document status: RELEASED
Date:
Sales statistics for bonus check
To better follow up bonus rollout, we are able to get sale statistics for receipt where bonus check was used.
Calculation works as follows: as before, bonus check is redeemed in CR and updated in Member Service. Reporting gets this transaction from loyalty database and stores in in FactMemberTransaction table. Now we also get transactionid. N-job will use this transactionid and calculate salesamount from receipt lines in FactRetailTransactionArticle.
To use this solution new package UpdateMemberTransactionSales must be activated by removing it from varPackagesToDisable in LindbakPOSReportingConfig.dtsConfig
Support for Loyalty endpoint in Reporting Service
To fully support all data required by loyalty dashboard, OLAP is extended with deleted date for member.
Supplier information in DimArticle view
The information about supplier phone number is added to Reporting. The field is exposed in the existing view pub.vDimArticle.
The existing information about supplier phone number is also added to the same view.
Inferred member functionality for workstations and VAT
When processing POSLogs with unknown workstations and / or VAT codes, Reporting will now create new dimension data instead of stopping Rt-job.
It also means that Workstation and VAT dimensions in Reporting are independent of POS Master now.
Reservation statistics for all stores
A relation between Retail Store and Order Line is added to OLAP to get correct values for Order and shipped amount / quantity. This will affect existing method orderStatistics when run for a single store. Change will happen when Reporting is upgraded to v76 or later. This is also used by new method orderStatisticsPerStore, which returns order statistics for all available stores.
Original scan code in Sales view
To better follow up item sales, information about OriginalScanCode in poslog is added to FactRetailTransactionArticle. Existing view pub.vSales is extended with new column OriginalScanCode.
Bug fixes and improvements
(RTC-23305, RTC-23392, RTC-25159)
- The collation issues with ArticleHierarchyMaintenance package were resolved. The package runs successfully, regardless of the database collation settings.
- UpdateMemberTransactions package was updated with the latest BonusCalc (4.9.0.2). This makes it possible to recalculate bonus amount at night including new bonus rules (amount on discounted items).
When looking up members from POSLog, it is possible to link unknown member to member 999999999999999990 instead of creating a new inferred member. Parameter varDisableInferredMemberForMember controls this.
Requirements
Component | Version |
|---|---|
POS Master* | 3.0.0.62 |
POS JournalService | 2.82.xxx.x / 3.82.xxx.x |
POSLog XML | 82 |
*or a separate script (see upgrade guide for details).
Reporting v. 75
Document status: RELEASED
Date:
POSLog version: 82
Export sales from Reporting on-premise to file
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
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
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
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
Document status: RELEASED
Date:
POSLog version: 81
Reserve&Collect orders
(RTC-7635, RTC-8222, RTC-8327)
A new order type: Reservation Order (Reserve&Collect) is supported in Reporting now.
In order to report on time between order is reserved and available to customer for pickup, Reporting database and OLAP have been extended.
To measure picking time, the time between order creation (reservation) and Ready for pickup status is calculated. Hours outside opening hours are not calculated.
Reporting database and OAP have also been extended with Upsale measures: amount, item quantity and receipt count.
It is now possible to measure how much additional money the customer spends in the store when they come to pickup their reserved order.
Extend OLAP with Supplier Order Number
To improve reporting on article level, supplier order number was added to OLAP.
This makes it possible to report sales per supplier.
Reporting v. 73
Document status: RELEASED
Date:
POSLog version: 81
Segment on bonus rollouts
When fetching member activities from Loyalty db, the activities which are bonus rollouts are flagged. This information is used to better follow up bonus rollouts.
Requirements
DimMemberActivity package now uses procedure DimMemberActivity_v2 in Loyalty database. This will be delivered in Lindbak Bridge version 3.10.8. Can also be installed with script vProcedures for Loyalty database_20220218.sql provided by Reporting.
To get rollout data from Chain Web, version 2.10.130 is required.
Reporting v. 72
Document status: RELEASED
Date:
POSLog version: 81
Reporting maintenance
To have consistent item hierarchy in reports we have extended existing package for hierarchy maintenance. Package will now detect and fix articles with same articlegroups but connected to different areas.
Missing index on DimMember
To improve performance of Rt- and N-job, queries related to lookup on member and customer are optimized.
Changes are in 17 different packages using lookup on member or customer.
Implement Promotion API
In order to follow up promotions in Chain Web. Reporting has been extended with a new dimension and new methods in ReportingService API.
This makes it possible to report on promotion and promotion offers.
Picking time is recalculated with wrong ShipmentDate
To calculate pickingtime correctly in customer order dashboard we do not recalculate when orders are updated.
Reporting v. 71
Document status: RELEASED
Date:
POSLog version: 81
Autodetect compressed poslogs
(RTC-4006)
To make it easier start using compressed poslog in reporting we are now detecting poslog compression when reading from queue.
Paramter varEnableMessageCompression is no longer needed and removed from config file (LindbakPOSreportingConfigPOSLog.dtsConfig)
Duplicates in vSales
To avoid duplicate rows in view vSales we have extended join on order lines to also include direction.
Export Rigal
To support export of Rigal data for opening day. POS Services will now export data for last 48 hours when there is no earlier EndOfBusinesSequence to export from. This only applies when StoreSettlementPeriod is set to DateRange.
Require latest version of POS Services 7.80.046
Realtime stock
We have fixed package related to RealTime stock to support ID higher than INT value.
Export Rigal data
To make sure we export all relevant data we made changes to GetStoreSettlement procedures. Procedures handles payin /out transactions with social control.
Handle multiple settlement re-export requests in a short time period
Made changes to support re-export of Rigal file when processing multiple requests in short amount of time.
This fix will only work with POS Services 4.79.04306.0 or later.
Reporting v. 70
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Reporting in Loyalty
We have made improvement in query for updating bonuspoints fro loyalty campaigns in package UpdateMemberBonusPoints. This is done to avoid timeouts when job is running.
Import items from ItemService
In order to not be dependent on POS Master database for dimension data, we import items from servicebus.
Pre condition: ItemService delivers items in Azure blob with blobtype = Gateway.ItemChanges
Reporting LIP-job ReportingDataIntegration listens on servicebus topic batchtoprocess, subscription "ReportingImport". 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.
NB! When enabling ItemStaging package disable following packages (to disable import from master), DimArticle, DimArtLC, NonSaleType and ArticleHierarchyMaintenance
Wetstock reports
To better get a view over wetstock, we made minor chagnes in Report layout.
Changes in Report 0840_WetStock and 0751_DailyWetStock
Replace calculated stock values in report if manual/auto readings are imported
The procedure sp_WetStockMaintenance was fixed to support the following scenario:
If historical tank statuses are imported and calculated stock values already exist, the calculated values should be replaced by the imported values.
The new values will be visible in the report instead of the calculated ones as soon as they are imported and the cube is processed.
Add mixtype and Campaign type to pub v.Sales
To better follow up on campaigns, we are fetching more information about mix in campaigngroup. We have also extended view pub.vSales with more information about campaign and mix from campaigngroups.
New columns in view pub.vSales.
MixGroupNumber - CampaignReference in DimMix
CampaignTypeID - CampaignTypeID in DimCampaign
MixCampaignTypeID - CampaignTypeID in DimMix
CampaignLevel - CampaignLevel in DimCampaign
MixCampaignLevel - CampaignLevel in DimMix
NB! Requires version 3.0.0.44 of POS Master or manually created new DimMix procedure.
Import of items - Item Hierarchy
To fully support receiving items from item master we have extended import to support item hierarchy.
Hierarchy is exported from ItemMaster with blobtype "Item.GroupHierarchy.Export" and updated in DW.registry.ItemHierarchy. This table is used as lookup when updating items from ItemMaster. Lookup is based on subgroup, if item doesn't have subgroup it uses itemgroup.
Workstation for handling shift
In order to have correct reports per shift we now support updating transactions from special Cr number on existing shift.
Cr number is decided by new parameter in LindbakPOSReportingConfig.dtsConfig varDefaultDimShift (default value is 111). When we get transactions from this CR we search for active shifts with same shiftnumber and link transaciton to this shift.
Add mixtype and Campaign type to pub v.Sales v2
To better follow up on campaigns, we are fetching more information about mix in campaigngroup. We have also extended view pub.vSales with more information about campaign and mix from campaigngroups.
New columns in view pub.vSales.
MixGroupNumber - CampaignReference in DimMix
CampaignTypeID - CampaignTypeID in DimCampaign
MixCampaignTypeID - CampaignTypeID in DimMix
CampaignLevel - CampaignLevel in DimCampaign
MixCampaignLevel - CampaignLevel in DimMix
MixTypeInSource - MixTypeInSorce in DimMix. This is Mixmatch type in Chain classic.
NB! Requires version 3.0.0.44 of POS Master or manually created new DimMix procedure.
Reporting v. 69
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Postregistration of customer or member sale
Previously, we updated information about postregistration even if receipt was cancelled. This caused problems in N-job when trying to process this data. This is corrected and cancelled receipts containing posregistrations will be ignored by RT-job.
Labeltext on items
To better report on item sales we have extended article information with labeltext1 and labeltext2 from master database,
This information is also available in Olap.
Import of training mode receipts
Previously, RT-job would reject training mode receipts causing delayed import of normal receipts. This has been corrected and training mode receipt are handled as before.
Export readings - implement
In LIP package ReportingDataIntegration we have created a new job, WetStockTransactionExport, for exporting reading transctions for WetStock.
Job exports transactions to blob with blobtype ReportingWetSTockReadingsXML.
See documentation in job for further information.
Item and store import
In existing LIP-package "ReportingDataIntegration" we have added a new job, "StagingCleanup".
This job will delete all entries in staging.flatbatch and corresponding entry in staging.flatbatchjson which has status 2. It also deletes entries with status 4 if they are older than X number of days (default 60).
Job runs on scheduled time - Default every night at 01:00
See also decoumentation for this job in LIP.
Balance on previous date.
When doing balance on previous date in POS. Reporting will handle re-export of Rigal files even if SettlementPeriodType is set to DateRange in POS Services.
Changes made in package: BalancePreviousDays.
Reporting v. 68
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Dimensions and measures for Wet Stock
(RTC-13530, RTC-13533, RTC-13528 )
To be able to report on fuel sales and fuel stock, the data model is extended to support fuel data.
New dimension in Olap for Nozzle, Tank and TankGroup. New measures in Olap for WetStock sales and WetStock transaction.
Dimensions and transactions are imported from Chain classic.
Sales are updated from POSLog and linked to nozzle on sales line.
Configuration:
Prerequisite: Chain classic exports files. Filelistener (onpremise LIP-package) deliver files to blob.
Reporting: ReportingDataIntegration (on premise LIP-package) set up.
Package WetStockStaging enabled.
Note: More detailed documentation on configuration can be found in Jira: RTC-13528.
View for receipt lines
We have extended existing view pub.vFactReceiptLine with following new columns:
- DirectionId - ID to DimDirection
- StatusID - ID to DimStatus
- OriginalFaceAmountDiscount
- NetAmountDiscount
- QuantityDiscount
- NetCostDiscount
- TimeID - ID to DimTime
Reporting v. 67
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
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.
Configuration:
To set up in Reporting add parameter to LindbakPOSReportingConfigPOSLog.dtsConfig.
Add ";ShiftUniqueness=StoreWorkstation" to varBusinessRules
Realtime stock information
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
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
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.
Report details for Cashier statistic | |
File name | 0904_CashierStatistics |
Data source | Lindbak POS Reporting cube (Olap). |
Parameters | |
pCustomerGroupPersonell | Which customer group personell belongs to. Default value = 100. |
pDiscountReasonCodeType | Which reasoncodetype to include in Manual discount - Default LineDiscount. |
pManualDiscount | Which discount type to include in Manual discount - Default Line. |
Filters | Comment |
Date from | Default today. |
Date to | Default today. |
Retail store | Default all. |
Operator | Default all. |
Columns - one row per operator. | |
Cashier | |
No | Cashier number |
Name | Cashier name |
Returns | |
Qty | Quantity of returned articles. |
Amount | Amount of returned articles. |
Deleted receipts | |
Qty | Quantity of deleted receipts. |
Amount | Amount of deleted receipts. |
Deleted item lines | |
Qty | Quantity of deleted item lines |
Amount | Amount of deleted item lines |
Manual discount (discounts filtered by parameter pManualDiscount and pDiscountReasonCodeType ) | |
Qty | Quantity of manual discounts |
Amount | Amount of manual discounts |
Diff settlement (difference between tender cash and balance cash) | |
Amount | Difference in amount. |
Deleted web orders (orders with order type "WebShop Order" and order status "Deleted") | |
Qty | Quantity of deleted web orders. |
Amount | Amount of deleted web orders. |
New customer orders (orders with order type "Store Order" and status "Created") | |
Qty | Quantity of new customer orders |
Amount | Amount of new customer orders |
Offline coupons (tender coupons with coupon type = "O" | |
Qty | Quantity of offline coupons. |
Amount | Amount of offline coupons. |
Personell discount (discount with customers from customer group in parameter pCustomerGroupPersonell) | |
Qty | Number of receipts containing personell discount. |
Import items from Chain Classic
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 v67)
Improvements
Modules | Description |
|---|---|
Bonus | 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. |
Reporting v. 66
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Improvement
Modules | Description |
|---|---|
Order management | 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. |
Reporting v. 65
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Picking time
(Jira links: RTC-9395, RTC-9396)
In order to track picking time for dispatch orders we are able to calculate time between order line assignment and when order is sent. This change will make it possible to illustrate picking time KPIs in the order management dashboard.
Include opening hours in picking time
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
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
Modules | Description |
|---|---|
Customer order | Customer order with missing delivery type (RTC-10409) 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). |
General | 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 emailsubscription 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
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
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).
Requirements:
Lindbak POS Reporting v. 64 / POS Services 7.76.036 / 7.76.037
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
Modules | Description |
|---|---|
Customer order | Deleted order lines (TFS: 189191) When updating deleted order lines Reporting will take into account all deleted statuses. Earlier some statuses made Reporting reject POSLog causing discrepancies. Wrong data after migration (TFS: 189319) When upgrading to v. 60 or above, all existing customer orders were migrated. We have discovered a bug in migration script used, which results in following error:
To fix this we need to run a script which remove duplicates and sets correct direction on return lines. Scripts should not take many minutes. After script is run we need to process order data in cube. In addition, the view pub.vSales could show duplicates. This is corrected by filtering out deleted lines when joining to FactOrderLine. |
Reports | Support for Vipps and Swish (TFS: 189229) The name for payment card number 33 and 90 to Vipps and Swish is corrected. This is changed so reports will show correct payment card name. |
Reporting v. 63
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
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
Document status: RELEASED
Date:
Prerequisites are located in the menu on the left.
Order management dashboard
(TFS: 182289)
In order to provide data to the cloud (Order management dashboard) from on premise data source (Analysis services cube), a new Cloud Service: "Reporting Service" has been created.
Reporting service in Azure talks to Reporting service in POS Services (on premise) which again talks to the Reporting cube.
For documentation of the service / solution: https://tfs.lindbak.no/tfs/DefaultCollection/DotNetRetailSolutions/_wiki/wikis/DotNetRetailSolutions.wiki?wikiVersion=GBwikiMaster&pagePath=%2FDevelopment%2FReporting%2FReportingService%20API&pageId=243
How to set up service and tenant: https://tfs.lindbak.no/tfs/DefaultCollection/DotNetRetailSolutions/_wiki/wikis/DotNetRetailSolutions.wiki?wikiVersion=GBwikiMaster&pagePath=%2FDevelopment%2FReporting%2FReportingService%20API%2FAdding%20the%20service&pageId=248
How to set up POS Services: https://tfs.lindbak.no/tfs/DefaultCollection/DotNetRetailSolutions/_wiki/wikis/DotNetRetailSolutions.wiki?wikiVersion=GBwikiMaster&pagePath=%2FDevelopment%2FReporting%2FReportingService%20API%2FPOSServices%20configuration&pageId=245
Requirements:
POS Services 7.76.036 / Reporting v. 62
Improvements
Modules | Description |
|---|---|
Accounts receivable | Invoices from Chain Web (TFS: 185079) When fetching invoices from Chain Web, Reporting displayed "total amount" as "due amount". This has been corrected to make sure the "due amount" is shown correctly in reports and views. |
Customer order | Performance Improvement (TFS: 181710) The processing of customer orders in Reporting has been optimized to reduce the amount of time RT-job uses to process realtime data. Unknown customers in customer order (TFS: 183875) If Reporting receives a POSLog with a customer order for a customer that is not yet updated in Reporting, the "unknown" customer is still processed by the cube. This is implemented to avoid RT-job failure if we get sale on customers that are not yet updated in Reporting. |
General | Online returns (TFS:179652) When updating receipts where online return is used. We now link this article line to operatorId and not salesperson as before. This is because salesperson can be cashier from another store. When updating sales lines we use salesperson as before. Dependency on dll's in GAC (TFS: 181222) Reporting will not be dependent on entity framework dll's to be in GAC. Reporting will use dll's located in Libraries folder. This is to make sure other products such as Chain Web, LIP and Bridge who uses the same dll's are not affected. |
Loyalty | Discount on Stamp card items (TFS: 185310, 187005) Bonus calculation supports bonus rule 10. This is used to configure if stamp card items should get bonus or not. |
Sales | Discount details (TFS: 179719) Existing view pub.vSales is extended with columns for showing discount details. Support for new price channels (TFS: 182650) Reporting is updated to receive sales for two new price channels, InStore App and ShopAndGoMobile. |