Using an unlinked table in report

[RedHeadedStepITChild]

I have a report showing all the scrap and reject transactions in my company's ERP. This report links "Item Master" (IIM) table to the Item Transaction History" (ITH) by Item Number (IIM.ITEM#)--->(ITH.ITEM#)

This report shows various things like what was scrapped (item number), when, actual cost(per), how many, extended cost of the transaction, and who.

This was all fine....until the Procurement department got ahold of this report

When we reject or scrap material, steel for instance, we give our vendors an opportunity to buy it back, and most times they do. When they buy this material back, a credit is entered into an account in our general ledger table. Procurement wants to add the credited amount to the month group, to get a net amount on scrap and rejects.

There is no way to link the G/L (GGM) table to either the ITH or IIM.

I thought about adding a sub-report using the GGM table.
The GGM table is set up with "periods" GGM.GPD07 would be for the month of January, GGM.GPD08 for February and so on. I just grab the entries out of the period and select by account to show the credits by month. However, if I insert this sub-report into the Month's group footer, won't I get the same sub-report 12 times in the main report, one for each month? I just need the total - added to the gross of scrap/reject.

Report set-up...

Report
Month
Date
Details
Date
Month
Report

Any help would be much appreciated - or if you need more information, let me know

Jason

 

[CR85user]

Could you not edit the subreport linking to link based on account and date entry?

Then in your subreport selection criteria the period selection is based on the linked date period and the specific account.

Maybe even use a formula in the main report to convert the period to one matching the subreport to make it clearer what is happening.

Can these tables not be linked by account id / number of some kind?

Is there no unique field to identify customers? From the way you describe it there possibly is.

'J

 

[lbass]

It is unclear whether you need the credit amounts to add to each month total or whether you just want the total across months added once to some report total.

-LB

 

[RedHeadedStepITChild]

LBASS,

I need the credit amount that is entered into the GGM table for the specified account number to go to the footer of the corresponding month's group.

I have a formula set up for the month group for my main report.

In the month group's footer, I have a summary to total all the transaction extended amounts. In the same footer, I would like to insert the corresponding month's credit amount from the GGM table, so I can subtract the GGM amount for credits from the extended gross of scrap/rejects. For example, to insert GGM.GPD7 in the January footer. For the February group, I would insert GGM.GPD8 so on and so forth. I'm assuming I can just do a selection on the account number I want to be pulled.

In response to CR85User...

No - there is no unique field. The GGM is basically a big check register, and the item master is exactly as it states...all the item numbers with the corresponding information for that item.

 

[lbass]

You need to link the sub to the main on both the account field and the month. How you do this depends upon how the fields are setup. Please show what field you use in the main report for month and what its datatype is. What is the datatype of your fields in the credit table. It sounds like the period is only determined by the name of the field? Is there an account field?

-LB

 

[CR85user]

As stated if you can just clarify a couple of things. I will explain which parts I am confused on:

I thought about adding a sub-report using the GGM table.
The GGM table is set up with "periods" GGM.GPD07 would be for the month of January, GGM.GPD08 for February and so on.

Ok - So part of the subreport linking will be based on a date link. Dependant on format used within each table this can easily be coded.

I just grab the entries out of the period and select by account to show the credits by month.

So you can select by account from the GGM table??

However, if I insert this sub-report into the Month's group footer, won't I get the same sub-report 12 times in the main report, one for each month?

Not if the subreport is linked based on the relevant month.

I just need the total - added to the gross of scrap/reject.

Again, just a bit more clarity and we will be able to help you acheive this.

Perhaps give an example of what data you expect to see where and what data is available in each table.

It does not have to be genuine data, just an example to demonstrate. Then i'm sure we can help get you up and running

'J

 

[RedHeadedStepITChild]

LBASS -

The month group is a group only. It is not a field. The month group is created by a formula based on the ITH.TTDTE field (transaction date) Here is an example of the formula

If ITH.TTDTE >= 20070101 and
ITH.TTDTE <= 20070131 then
"January"
Else
If ITH.TTDTE >= 20070201 and
ITH.TTDTE <= 20070228 then
"February"
Else
If ITH.TTDTE >= 20070301 and
ITH>TTDTE <= 20070331 then
"March.........and so on.

I call this the {@month} formula, and is the top group. The second level group is the date, which is the ITH.TTDTE. Data is numerical. (Using legacy DB2 database on AS400)

CR85User -

"So you can select by account from the GGM table?"

Yes, the GGM table has the accounts. For instance, in this report, I am selecting to show only the 7120 account, which is the scrap and credits entries. All of January 2007 entries are entered into GGM.GPD07 field. February 2007 entries are entered into GGM.GPD08 field. March 2007 entries are entered into the GGM.GPD09 field. However, there is no account field in either the ITH or IIM field.

"I just need the total - added to the gross of scrap/reject"

Sorry, not my best piece of writing What I meant to say is that I enter a summary at the date level to total the scrap costs for that particular day. I also enter a summary to total the scrap cost for the month. Lets say for instance, I had $10,000 worth of scrap for November. This summary obviously would be in the Month group's footer. I want to place the GGM.GPD17 (november's general ledger entries) for account 7120 only, in the same footer, so I can create a formula to show the net on scrap and rejects.

I'll try to give an example visually:

NOVEMBER
20071101
Item# Transaction QTY CostPerUnit TransactionTotal
91200 -10 1.83 -18.29
91221 -8 2.89 -23.08
____________________________________________________________
Total This Day: -41.37

**************************************
* Report sohws each days transactions as shown here.
* There are more columns, but this shows the gist of
* the report. All this data comes from the IIM and
* ITH tables, which are linkable by item number.
**************************************

20071131
Item# TransactionQTY CostPerUnit TransactionTotal
91200 -10 1.83 -18.29
91221 -8 2.89 -23.08
____________________________________________________________
Total This Day: -41.37
____________________________________________________________
____________________________________________________________
November Totals: Total Scrap 82.74
Total Buy Back: (GGM.GPD**)
Net Scrap: (@{scrap formula}

I am willing to email you the report if you would like to take a look at it. I don't know how that works without it being connected to my database, but maybe you can get a better idea what I am trying to do.

I appreciate your help!

Jason

 

[lbass]

Please show the name of the account field you are using in the main report and the name of the account field in the GGM table. You say there is no account field in the main report tables, and yet you are able to limit that report to a specific account--so what field are you using to do this?

-LB

 

[RedHeadedStepITChild]

I currently only have 1 report. It was a thought of mine to add a sub-report pulling in the fields from the GGM table.

The account field is GGM.GACC. It was my thinking that I would do a selection so that:
GGM.GACC = 7120

 

[lbass]

You are showing data in your main report for a specific account. How are you limiting your main report to that specific account?

-LB

 

[RedHeadedStepITChild]

It might be better to rephrase my question.

I have *A* report showing all the transactions in my system by SC and RJ (scrap and reject) and the dollar amount that it cost the company to make those transactions. How would I show from a completely different table, the amount that was paid back on these scraps, based on an account that has no relationship back to either the transaction file or the item master file.

The data in the report is coming solely from the ITH table. All the ITH table is, is a record of transactions in our system. I sort by SC and RJ type transactions. I use the IIM table to link to the ITH table, and get the item description as well, since the item description field is not in the ITH table.

The account is solely within the GGM table. The GGM table is the table I can not link into this report. If I try to insert anything at all from the GGM table, the report loops.

 

[lbass]

Okay, so you are trying to say that the account field is irrelevant. So the challenge is how to link the subreport to the main report, so that the only data shown is for the particular month group in the main report. I can't really test this, but try the following. Assuming you don't have a period field in the main report, create a formula {@Period} like this:

select mid(totext({ITH.TTDTE},0,""),5,2)
case '01' : '07' //Jan
case '02' : '08' //Feb
case '03' : '09' //Mar,etc

Then insert a subreport and choose "add command" instead of choosing the table directly. Then set up the command something like this:

Select '07' as Period, GGM.`GPD07` as Value
From GGM
Union all
Select '08' as Period, GGM.`GPD08` as Value
From GGM
Union all
Select '09' as Period, GGM.`GPD09` as Value
From GGM
//etc.

The syntax/punctuation would be specific to your datasource. Then link the sub to the main by linking {@Period} to {Command.Period}. This assumes that the GGM.`GPD##` field contains the values you want in the sub. You can then add a formula in the footer of the subreport:

whileprintingrecords;
shared numbervar sumval := sum({command.value})

Then in the main report in a GF_b section (below the section containing the sub), you can create a formula like this:

whileprintingrecords;
shared numbervar sumval;
sum({table.scrap},{@month})-sumval

-LB

 

[RedHeadedStepITChild]

The account file is only relevant as that I am doing my selection on ONE account only. I am assuming this selection can be made in the sub-report, so would have no bearing on the main report.

Reading through your instructions, it looks like it should work. Let me give it a try.

Once again, thanks so much for your help!