Reset variable or result based on Shared Variable

I did try moving the variable...but I couldnt get it to work

trial.rpt

Which variable(s) are we talking about?  Exactly what do you want to do with it(them)?  What did you try?

 If this is connected to the subreport, I have a few observations:

 The subreport just sets the variables with each record (eg. QISS:={InvWhse.MtdQtyIssued}), so if the subreport reads more than one record (which is probably the norm for a subreport), then the variable will just end up with the value from the last record.  If the subreport reads more than one record and you want the total from those records, you could use QISS := QISS + {InvWhse.MtdQtyIssued} in a formula in the detail section, or just use QISS:= Sum ({InvWhse.MtdQtyIssued}) in a formula in the subreport report header or footer.  Then again, two of the variables that are set in the subreport are strings, and you can't total those, so maybe the subreport is just reading one record.

 You have the subreport in a detail section of the main report, so it will be run for each record in the main report.  This is highly inefficient, but if the main report isn't reading too many records, then maybe it doesn't matter.

 James

well...shit.. (sorry i thought this was the most effective way to express my frustration with my lack of knowledge with this stuff)

I copied this from my word doc


The Column QtyIssRemain which is a formula (shown below) needs to be the result of @QtyIssued  which is a formula (shown below) - QtyTo Review and then reset based on the GH1
In the Db section in the far right column QtyIssRemain for the value 'B111' it shows as -340.00 this needs to be shown as 9.00 (first value in that column) - QtytoReview...so the new value should be  -331.00.
In the Db section in the far right column QtyIssRemain for the value 'B112' it shows as -347.00 this needs to be shown as 2.00 (2nd value in that column) - QtytoReview...so the new value should be  -348.00.

@QtyIssRemain Formula
NumberVar QISS:={@QtyIssued}-{UnconfirmedJobs.QtyToReview}

@QtyIss Formula
Shared NumberVar QISS;

Formula from the subreport that creates the Shared Variables
WhilePrintingRecords;
Shared StringVar SCode:={InvWhse.StockCode};
Shared StringVar Whse:={InvWhse.Warehouse};
Shared NumberVar QOH:={InvWhse.QtyOnHand};
Shared NumberVar QISS:={InvWhse.MtdQtyIssued};

I thought that the sub report had to be in the detail section in order to accomplish above... shocker..I am not correct..ho hum

I thought that the sub report had to be in the detail section in order to accomplish above...

It depends on how the data in the main report and subreport are related.

 Your main report is reading UnconfirmedJobs, and you linked the subreport using Component and Warehouse.  If each record in UnconfirmedJobs is for a different Component and Warehouse combination, then you probably do want the subreport in the detail section.  But if you could have multiple records for the same Component and Warehouse, then you might want to have a group on one or both of those fields (depending on your data), and put the subreport in a group header/footer, so that it's only run once for each Component and Warehouse combination.

 OTOH, is the subreport even necessary?  Could you just add InvWhse to the main report and use Component and Warehouse to link the two tables?

 Putting the subreport issue aside for the moment and getting back to your formulas ...

 First, just to try to keep things straight ...

 The @QtyIss formula in your post is named @VarQtyIssued in the report that you posted.  I assume that they are the same formula.

 The @QtyIssRemain formula in your post uses a formula named @QtyIssued.  I assume that that should be @QtyIss (the second formula in your post).  In the report, @QtyIssRemain uses @VarQtyIssued.

 @QtyIssRemain is:
NumberVar QISS:={@QtyIssued}-{UnconfirmedJobs.QtyToReview}

 QISS is also the name of the shared variable that is set in the subreport, and used in @QtyIssued (or @QtyIss, or @VarQtyIssued).  I'm not sure what you were trying to do there, so I'll just point out some things:

 If you just want to output the result of {@QtyIssued}-{UnconfirmedJobs.QtyToReview}, you don't need to use a variable there.  You can just use:
{@QtyIssued}-{UnconfirmedJobs.QtyToReview}

 NumberVar by itself will default to a Global variable (as opposed to Shared), so NumberVar QISS creates a global variable named QISS, which is separate from the shared variable named QISS.  If you want to set a global variable there, you should probably change the name, to avoid confusion.  If you actually want to set the shared variable, add Shared to the variable declaration.

 You said that the figure for B111 should be -331 and B112 should be -348, but I'm not following the math/logic.  I think maybe B111 should be -341 instead of -331?

 As for what you're trying to do there, trying to "combine" the figures from two non-consecutive records (you have one record for B111, then 4 other records, and then another record for B111) is going to be a problem.  It would be much easier if you could group or sort the report by that field (Component?), so that those records were consecutive (B111, B111, B112, B112, B113, B113, etc.).

 FYI, just in case you were wondering ...
 As I said, I can't really view the document file that you posted earlier, but I can look at the pieces of it, including the picture that you had included, so I was able to see your example data that way.  If you don't know, Word's docx files are actually just zip files that contain the various pieces that make up the document (xml files, pictures, etc.).

 James

I am not trying to be hard to understand, but I guess I am...sorry about that.. In the column QtyIssRemain the first Db Records are correct within the GH1.

The issue is the next set of Db records in GH1 for this QtyIssRemain....The value for B111 in the 2nd set of records.. needs to look at the Db value that shows '9.00' in the first set of records and then subtract the value from the sub report..

So for B111 9.00 - 10.00 - 350.00   .. The 9.00 is from the 1st value in GH1 Db ... the 10.00 is the in the 2nd set of records of GH1 Db... the 350.00 is in the QtyToReview for the 2nd set of records of GH1 Db

CrystalPic.PNG

So for B111 9.00 - 10.00 - 350.00   ..

Well, 9 - 10 - 350 would be -351, not -331.

 IAC, what about my suggestions to just eliminate the subreport and add that table to the main report instead, and to group/sort by the B111 field, to get those records together?

 Just to be clear, those are two separate issues.  Eliminating the subreport and reading that table in the main report instead would simplify things and make the report more efficient.  Grouping/Sorting by the B111 field would make "combining" the values (eg. from the two B111 records) much easier.

 James

Math and I.....well..fire and ice...

I need to show the split between the GH1 (the t1 and t2)..How can I change a group.. as you suggest.. but leaving the outlay of the report split as I have in my pic I sent you, and still the results I need?

I am very confused..

any ideas?

First of all, what is B111, B112, etc.?  Is that Component?  I'd just like to know what to call it.

 The problem with having the records "split" (B111, B112, B113, etc., and then B111 again) is that you have to save the value from B111 until you get the next B111 record, and the value from B112 until you get the next B112 record, and so on.  IOW, you have to save multiple values at the same time, not just one.

 How many different components(?  B111, B112, etc.) could you have on one report?  If there's a fairly small limit (eg. there will never be more than 5), you could use a different variable for each one (eg. QISS1, QISS2, QISS3, etc.).  If there could be more, it would be easier to use array variables.

 Your example just has 2 of each (eg. two B111 records, two B112 records, etc.).  Could there be more?  If there are more, how do you want to handle that?  Is it some kind of running total, or is it always the previous B111 (for example) minus the current one (and any before the previous one aren't a factor)?  Just trying to better understand the data.


 And, returning to my idea about eliminating the subreport ...

 Have you tried simply adding InvWhse to the main report and linking UnconfirmedJobs.Component to InvWhse.StockCode, and UnconfirmedJobs.Warehouse to InvWhse.Warehouse ?

 If you want to try that, you can temporarily eliminate the subreport by suppressing the section that the subreport is in (DetailsA).  When a section is suppressed, any subreports in that section won't be run.  That way, if you decide that you want to use the subreport again, you can just unsuppress the section.

 James

B111, B112 are Components.. It could be a lot of components.. There could be many more than the example of the 2.. I did your suggestion with eliminating the subreport.. and linking exactly as you have  "UnconfirmedJobs.Component to InvWhse.StockCode, and UnconfirmedJobs.Warehouse to InvWhse.Warehouse"

I copied my original report to 'trial1.rpt'

I guess I am not sure how I can show the data...with the t1 and t2 (representing the current groups) and then show the B111, B112 under these groups (while using your suggestion of eliminating the subreport).. it is important that the outlay of the report looks the way I have sent it to you...

I don't see why you think the value should be 331.

The formulas are working as programmed so perhaps it is the formulas that are incorrect.

I think you need to add the QtyToMake field into the formulas or the QtyToReview into the formulas for Issue Remaining

mlmcc

I am exhausted with this.. I can't figure it out..not sure what to do.. I have spent so much time on it..I am running out of options

Did you try my suggestion?

Can you post the current version of the report?

mlmcc

I guess my math is wrong, and I can't explain it.. I have reviewed this sooo many times I am drunk with it

So here is another attempt I need to have the groups based on Job those are listed on the pictures i have sent...
When I the report runs... I need the components and the warehouse of the Report to link in some fashion to the StockCode and Warehouse in the other table.

When the report is run i need MtdQtyIssued  - QtyToReview ...for each component in the GH1 in this case t1 = 9.00 for  B111
When the next value for gh1 occurs in this case t2, I need the above 9.00 value subtracted (the 9.00) that was for B111 in the t1 value group  subtracted from the QtyToReview in that Gh1 for the t2 value...So actually 9.00-350.00  = -341.00...


If there was another 10 jobs i.e... t3, t4,t5,t6,t7,t8,t9,t10,t11.t12... and they each have a B111...then the same process happens that happened on t2.


and the same process would happen when the next occurrence of a component that matches the Component and Warehouse link.

Hopefully this helps...

I have posted both versions..As I am not sure which one to utilize
trial.rpt
trial2.rpt

I'm going to use Job-Component (eg. t1-B111) as a shorthand for the different "sets" of data.

 It seems that there is only one record for each Job-Component (one t1-B111 record, one t1-B112 record, one t2-B111 record, and so on).  Is that correct?

 I think I'm getting there, but there are still some things that I'm not sure about.

 It looks like for the first job on the report, you want to just subtract UnconfirmedJobs.QtyToReview for the first job from InvWhs.MtdQtyIssued for the first job (since there is no prior job).  Correct?

 Then, for the other jobs, you want to subtract QtyToReview from the remain (not MtdQtyIssued) for the prior job?

 Is it the prior job, or is it always the first job?  IOW, is it t2 - t3, t3 - t4, t4 - t5, and so on?  Or is it just t1 - t3, t1 - t4, t1 - t5, and so on?

 I'm guessing that it's "prior job", but I wanted to check.  In one of your last posts you said to use the "same process" for the other jobs, but, in that context, "same process" could mean "prior job" or "first job".

 I've worked on some formulas to save the values and do the calculations, but I need the answers to the questions above to finish them.


 Also, if you want the records sorted by Job and then Component, I would suggest that you add a sort by Component.  The records appear to be coming out in the order that you want, but you can't necessarily rely on the order if the records aren't explicitly sorted somewhere (either in the report or in the db).  If you sort by Component (in addition to the group on Job), then you know that the records will always come out in that order.

 James

Hi James, I will answer your questions as they are asked in your previous response

"t seems that there is only one record for each Job-Component (one t1-B111 record, one t1-B112 record, one t2-B111 record, and so on).  Is that correct?"..Yes

"It looks like for the first job on the report, you want to just subtract UnconfirmedJobs.QtyToReview for the first job from InvWhs.MtdQtyIssued for the first job (since there is no prior job).  Correct?".. You are correct

"Then, for the other jobs, you want to subtract QtyToReview from the remain (not MtdQtyIssued) for the prior job?"  Correct

 "I'm guessing that it's "prior job", but I wanted to check.  In one of your last posts you said to use the "same process" for the other jobs, but, in that context, "same process" could mean "prior job" or "first job".",, Your assumption is correct
 "Is it the prior job, or is it always the first job?  IOW, is it t2 - t3, t3 - t4, t4 - t5, and so on?  Or is it just t1 - t3, t1 - t4, t1 - t5, and so on?"
There will always be a first job...there may not anything after the first job...the naming of the jobs will be between 20 characters long.. not sure if this answer your question

OK, I've got something that you can try.  It's untested, since I don't have your data, but it looks reasonable to me.  :-)

 I've added two formulas to your trial2.rpt (the one without the subreport).

 init_vars in the report header just declares the variables.  I used Global variables and they just seem to work better if you declare them at the start of the report.  FYI, the "" at the end of that formula serves two purposes.  First, it's just something that I often use in formulas like that, so that the formula doesn't produce any visible output on the report.  But, in this case, the statement before that is a Redim (to set the size of an array), so I had to put something after that.  The result of Redim is an array, and CR formulas can't produce an array as a result, so if the Redim was the last statement in the formula, you'd get an error.

 The other formula is calc_remain, in the detail section.  That does all of the work.  There are comments in the formula, but, basically:
 There are two arrays, one for Component and one for the "remain".

 If Component is not already in the array, then this is the first Job for that Component, so we put Component in the next slot in the array (expanding the array as necessary), and the remain is MtdQtyIssued - QtyToReview.

 If Component is already in the array, we find it and subtract QtyToReview from the old "remain" to get the new "remain".

 Whichever way it calculates the "remain", the last thing that the formula does is put that value in the array, so you'll get that value on the report.

 James

trial2_J.rpt

James is great.. Thank you

You're welcome.  Glad I could help.

 James