Add additional fields to table for SAP Report

JoshW — Tue, 28 Dec 2021 19:42:17 GMT

We are pulling data through the use of SAP Reports. These are to be used daily to gather data but I'd simply like to attach a date field when loading. Every time I add a field, I get the "mismatch detected" error. Is there another way I can do this?

LET KEYDATE = Date(Today(), 'YYYYMMDD'); Load [Field1_] as [RDATA], Date($(KEYDATE)) AS [DATE] SELECT SAPREPORT (REPORT (Z_3_REPO), ROWS_PER_RECORD(1), FIELD_AUTO_COLUMNS(6), OPTIONS ( (SELNAME P_FDATE, KIND P, SIGN I, OPTION EQ, LOW $(KEYDATE)) ));

What I'd ultimately like to do is write a script that cycles through a set of dates and pull the daily reports and then add those to a table along with the date associated with the report.

One idea was to cycle through a loop and pull data into the table. From there I can concate this data into another table that includes the date from he for loop. Any ideas on how to get this going?

Re: Add additional fields to table for SAP Report

JoshW — Fri, 08 May 2020 18:34:16 GMT

I resolved my issue with the following script. I'm not 100% sure if it is the easiest way but it works. This code will cycle through by a start and end date, create a temporary table from SAP Report connector and then create another temp table adding in the dates specified. Once this is done, I save it all to a QVD file as it is a daily report pull. I hope this helps someone out there.

// STORAGE PATH LET vQVDPath = 'lib://QVDFiles (user)/'; // RUN THROUGH TODAY LET KEYDATE = Date(Today(), 'YYYYMMDD'); // START ON THIS DATE LET STARTDATE = Date('01/01/2020', 'YYYYMMDD'); // LOOP TIME. LET day = 1; DO WHILE day < (KEYDATE - STARTDATE) LET CURRENTDATE = Date(STARTDATE + day, 'YYYYMMDD'); // CONNECT TO SAP REPORT CONNECTOR LIB CONNECT TO 'SAPReportCon (user)'; // SET ERROR MODE IN THE CASE THAT THE REPORT ISN'T FOUND FOR THIS DATE // AND ALSO TO IGNORE ERROR FROM LIBRARY NAME SET ErrorMode = 0; Set errormode = 0; TempTable: Load [Field0_] AS [RDATA]; Select SAPREPORT (REPORT (ZSD_39_COMMISSION_STMT), ROWS_PER_RECORD (1), FIELD_AUTO_COLUMNS (6), OPTIONS ( (SELNAME P_FDATE, KIND P, SIGN I, OPTION EQ, LOW $(CURRENTDATE)) )); // CREATE TEMPORARY TABLE WITH DATE ADDED TempTable2: NoConcatenate LOAD RDATA, $(KEYDATE) as PULLDATE, $(CURRENTDATE) as REPORTDATE Resident TempTable; // Not sure if this is needed but errors thrown if I don't have it. drop fields PULLDATE, REPORTDATE from TempTable; // Drop the SQP Report Table drop table TempTable; // IF FILE EXISTS, CONCATENATE if NOT IsNull(QVDCreateTime('$(vQVDPath)RDATA.qvd')) then Concatenate(TempTable2) LOAD * FROM [$(vQVDPath)RDATA.qvd](qvd) WHERE [REPORTDATE] <> $(CURRENTDATE); end if // STORE FILE if NoOfRows('TempTable2') > 0 then STORE TempTable2 INTO [$(vQVDPath)RDATA.qvd](qvd); end if drop table TempTable2; SET ErrorMode = 1; Set errormode = 1; // INCREMENT DAY day = day + 1; // Sleep performed due to overload on SAP causing errors. Brief pause. Sleep 500; LOOP; // LOAD THE REPORT INTO A TABLE TO USE [RDATA_REPORT]: Load * FROM [$(vQVDPath)RDATA.qvd](qvd);

topic Re: Add additional fields to table for SAP Report in Connectivity & Data Prep

Add additional fields to table for SAP Report

Re: Add additional fields to table for SAP Report