Creating a Table in the Database in Pervasive Control Center to run ODBC reporting

[ChristinaOgle]

Hi,
I am hoping someone can assist me. I have established a connection in Windows 10 to Pastel 17 via ODBC, and I am able to run and generate most of the standard data into excel for reporting. However, I seem to be missing the InventoryMaster table. I am looking to get the information such as Qty bought this month (and past 11 months), Qty adjusted and Qty sold, Qty on hand, On order, etc.
I also make use of Intelligence reporting, and am able to get the Inventory Master information from there, but the data is grouped into quarters, and I need the individual months.

How do I add the InventoryMaster into the PCC tables, to get this via ODBC?

Can anyone please help.

[Neville Bailey]

The information you are looking for is in the MultiStoreTrn table.

[ChristinaOgle]

Hi Neville,
I found it, Thank you very much for your assistance.

[ChristinaOgle]

Hi Neville,
I managed to create my MultistoreTrn table in excel. I do however notice that my OpeningQty of my stock does not correspond to my current period open quantity directly in Pastel. The other columns quantities is correct, such as sales per periods, reorder minimums and maximums, that is all accurate, it is only the OpeningQty that is incorrect. I have checked it against all periods, and it does not match to any other period in this finyear either. How do I correct this?
Regards
Christina.

[Neville Bailey]

Hi Christina,

The OpeningQty field represents the opening quantity at the beginning of Last Year. You need to also include the following fields in the data table, i.e. QtyBuyLast, QtyAdjustLast, QtySellLast into the calculation. You will find towards the extreme right of the data table.

[ChristinaOgle]

Thank you very much Neville!