I have an application that adjust inventories during runtime. During the run, the program reads some text files and extracts this info into several different tables. I want to run a report after the application runs thru the files that shows what materials where adjusted. For example, the program runs and extracts material A and saves the info in a table. At the same time it extracts material B and saves that to another table. Both tables contain a field called jobid. Everything works great until the report. I created a dataenvironment that connects to the tables. I set the command1 properties with the sql builder. To summarize the sql, it opens to seperate tables and pulls the records where jobpart.jobid=jobinprocessrs!jobid. It should sum the field jobpart.qty. It does the same for the trussplate table. The jobinprocess!jobid is a variable where the jobid is stored in a table. For instance, the jobid="1000", then the sql should pull all records with jobid="1000". It looks like the following:
SELECT jobpart.jobid, jobpart.id, SUM(jobpart.qty) AS Expr2, trussplate.plid, SUM(trussplate.plqty) AS Expr1, trussplate.plqty FROM jobpart, trussplate GROUP BY jobpart.jobid, jobpart.id, trussplate.plid, trussplate.plqty HAVING (jobpart.jobid = 'where jobpart.jobid=jobinprocessrs!jobid') ORDER BY jobpart.id
I then opened the datareport and set the datasource to the dataenvironment and the datamember to the command1. I then retrieved the structure. I then dragged the fields to the datareport sections.
When the report runs I get a blank page. I know there is info in the tables since the report runs only after the info is stored in the tables. Everything is stored in the correct place I just can't get it out.
Any help appreciated.