pcsoft.us.windevCopyright 2026, PC SOFT4 Jan 2018 23:03:00 Z4 Jan 2018 23:03:00 ZIn December a client requested a number of pivot table reports. Previously, these had been produced in our own FoxPro report writer, using Excel automation code (to extract locally held data and create all the pivot tables). The client required a proof-of-concept before upgrading to our new Windev report writer. I reviewed the available Windev options: - Crosstab report - User edition of Reports & Queries - Pivot table control And compared them to Excel 2016 plus Power query & Pivot. Crosstab reports are an option, but Reports & Queries is a complex report medium; Pivot Table controls are only an option for Windev programmers, and my role (and capability…) is to develop reports for users via reports & queries, or any suitable means. Excel 2016 and its pivot table reports are without doubt the best option from a data content and presentation viewpoint. In conjunction with Power Query, I was able to create user-friendly reports from our local HFSQL database, which only require opening an Excel workbook, entering in report dates, and refreshing the pivot tables. I haven't found any information on Windev forums covering this process, so a brief summary is as follows: 1. Open an ODBC connection to HFSQL (C/Server) See Windev Help - HFSQL connections for details In Excel, go to Data > Get Data > From Other sources > ODBC; select Advanced options, then provide the connection string: DRIVER={HFSQL};Server Name=127.0.0.1;Server Port=4900;Database=dbName; Notes: Replace dbName with your database name Localhost works in place of 127.0.0.1 UID and Password should be omitted here, but are supplied in the next step. An OLEDB connection could be used instead. 2. Provide credentials when requested 3. Select one or more database tables You will then be returned to the Query screen, with selected tables listed on the left (as queries). 4. Create joins on selected tables In Power query this is called merging tables. In this process, two tables and their linked fields are selected, and the join type (left/right/inner) can be specified. The joined table appears as a single column in the primary table; this column can be expanded to select only the fields required. 5. Transform the field content as required This may involve adding new fields, or filtering on selected field values. Formulae can be created using Microsoft’s M-Language. 6. Save the query Choose to save as connection only, or add this data to the data model (possibly suitable for larger data tables). Connection-only queries will take longer to refresh later. Note: Testing of the data model is ongoing, as it could greatly reduce refresh time for the pivot tables. 7. Parameters Multiple parameters such as dates may be stored in a worksheet table, and by using special queries (found after extensive internet searching), made available to the Power Query. The entire query can be viewed as code and edited for this purpose. 8. Pivot tables A pivot table can be inserted to any worksheet, specifying an existing connection as the source, which will be a named power query. Excel Power Query plus Pivot tables represents a flexible user-friendly reporting option. Our data sources usually amount to tables of less than 100K records, so performance is adequate.30WEBDEVen_UShttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/63851-excel-2016-power-query-amp-pivot-tables/read.awpmoderateur@pcsoft.fr (El moderador)webmaster@pcsoft.fr (El webmaster)