Problems with running totals

[Former Member]

I'm having some trouble getting the correct number when a drive has multiple incentives associated with the drive.  And to explain as fully as I can, every drive has a projecture procedure count and when complete, a performed procedure count associated with the drive.  And when a drive has an incentive assigned to it, I would like to count the drive's projected and performed procedure count to the incentive count.  In the screen shot, there are five drives with four each having one incentive to the drive. The incentive count in which only one incentive is assigned to one drive is correct, but the Fandango pre-drive and post-drive counts are off because it is not counting the 12 and 15 that were collected on the bottom drive.

For my running total, I am summing the ProceduresPerformed field, evaluating on the change of field of DriveID (each drive) and reseting on the change of field for EquipmentID (each incentive has its own unique ID).

What would be the best way to handle drives with multiple incentives to get the correct count?

[JWiseman · Accepted solution]

hi Trey,

have a look at the attached report. i've created an array builder formula which creates 3 arrays. one for the descriptions, and one for each of the numbers you require. the arrays are then used in the cross-tabs. have a look at the array builder formula on the report as well as the Display String properties for the cross-tab summaries and grand totals.

the values after a quick check seem to be adding up correctly but let me know if those aren't the values you're looking for.

-jamie

OBI_IncentiveReport_Test_2.txt.zip

[Former Member · Accepted solution]

Hey Jamie,

That seems to have done the trick and I've trying to figure out how exactly you did it, but I believe it's above my head right now.

One last question about this, would it be possible to use this array to then calculate a cost for the summarized incentives?

So the totals are correct in the screenshot, but how would I then add a column to the right that would pull from another field in the DB {OBI_EquipmentMaster.UnitCost} to multiple the summary number times the unit cost to get a total cost for the inventory?

For instance, if the units costs are as follows:

Double Fandango = 10

Fandango = 5

Gift Card $10 = 10

Movie Ticket = 5

I would like to cross tab to look like the attached picture.  Would this be possible?

[JWiseman · Accepted solution]

hey Trey, glad it works.

the arrays are all created in the array builder formula so that's the one to look at to see how this is done. the display string properties are just used to get the values out of the arrays. there may be other ways of doing this but it seemed like a job for multiple arrays.

in order to keep things separate and easier for other forum users to find, please post the new question as a new discussion as per the forum rules.

-jamie

[Former Member]

Dear

For running total follow this link

Regards

[Former Member]

Hi Trey,

As my understanding try the below some points:

1. evaluating on the change of field of Zip

2. I think your working on crosstab i.e need to add summarized fields with formulas

3. How to differentiate drives?

can you please share more information....

Note: Donot create duplicate threads as per SCN RULES THIS IS NOT SUFFICIENT

Thanks,

DJ

[Former Member]

Hello DJ,

Let me see if I can explain more clearly to help you understand.

I think the most important thing is that each drive is unique and will have one projected procedure number and one performed procedure number.  But each drive may have multiple incentives assigned to each drive. Where I run into trouble is when I have more than one incentive on a drive.

I'll try to illustrate below:

Drive	Projected	Performed	Incentive(s):
A	10	12	Movie Tickets
			Gift Card
B	5	10	Movie Tickets
			Beach Towel
C	20	25	Gift Card

Based on these entries, what I'm trying to have summarized should look like this:

	Pre-Drive (Sum of Projected)	Post-Drive (Sum of Performed)
Movie Tickets	15	22
Gift Card	30	37
Beach Towel	5	10

So if an incentive is assigned to a drive, I would like to have all of the projected and performed numbers summarized that the particular incentive appears on.  Hopefully this makes sense.

And one last screen shot here of a query I ran against the database to illustrate how a drive can have multiple incentives on the drive.

[Former Member]

Hi Trey,

I hope we have try some options like that:

1. In query level need to summarized the two  values based procedureprojection and procedureperformed. i.e sum(procedureprojection) etc.

2. Display Limited Incentives right

3. you can sum the values based on driverid or try to create running total based procedureprojection and procedureperformed.

4. In query level you remove last column i.e description after sum the values.

Need more updates.. Sorry i will go for any wrong way!

I hope the information might be helpful or give some idea

--DJ

[abhilash_kumar]

Hi Trey,

Are you trying to show this as a Crosstab?

If yes, then use the 'Description' field as the Row of the crosstab and the Projected column as the summarized fields (with summary set to 'Sum').

Create another crosstab for the performed column.

-Abhilash

[Former Member]

Yes, I am trying to use a crosstab. I believe I had already created the crosstab the way you are suggesting with a row of EquipmentMaster.Description and my summarized field was the running total I created for the the incentive projection.  Doing it this way was not capturing all of the data with drives that had multiple incentives.

So I changed the summarized field in the crosstab to be a summary of DriveProjectionandCollectedTotals.ProcedureProject and the Incentive that appears on drives with more than one incentive is still off.

Double Fandango is 12 and Correct.

Gift Card $10 is 15 and Correct.

Movie Tickets is 12 and Correct.

Fandango is listed as 72 when it should be 42.  Not sure where the extra 30 is coming from.  Maybe it's counting the top two drives twice?  I am not sure.

[Former Member]

Hi Trey,

Can you attach the report with sample data?

regards,

Naga.

[Former Member]

Sure, here is a link to the report with data.

https://drive.google.com/file/d/0B8ErGi0v2xIJc1BubzczbkVYalE/edit?usp=sharing

[Former Member]

Hi Trey,

When I observed your report the last group "Movie tickets" and "Fandango" display as single group. The Fandango doesn't display any values.

I need one clarification, Does the values displayed in Group1 is correct or not?

if those are  correct please verify the screen shot and let me know is that your requirement or different?

if it is different can you please explain that with Group1 data.

regards,

Naga.

[Former Member]

Thanks Naga,

I think I need to explain what is happening here better.  Under the Incentives column, that values are being pulled in from a sub-report called "Incentives".  I am not using these values in the cross-tab however because I could not figure out how to pass the variables back out of the subreport.

So I created another grouping on OBI_EquipmentMaster.EquipmentID and dropped the Description field in this grouping and planned on hiding it for a couple of reasons: (1) Placing the Description field in the GH #1 for DriveID will not display multiple incentives and (2) not using a sub-report and leaving the Incentives displayed on the GF # 4 works but does not look the way the user would like it by not displaying/"printing" of the Incentives on the same line as the report of the report.

So my goal was to use the sub-report to keep the report cosmetically looking the way the user would like but calculating the values based on the hidden "GF #4". The report I've put on Google Drive shows what I'm looking to do.

The items in red at the incentives being listed along with the drive procedure projection/performed.  These are the values I'm trying to calculate my values in the crosstab off. I've added a second incentive to one drive and it has thrown off the numbers now. I am just not sure how to get around how to handle the summary of incentives when drives have multiple incentives associated with it.

Hopefully this makes sense. If not, please let me know.

https://drive.google.com/file/d/0B8ErGi0v2xIJRHJTdVlVX2JPY2s/edit?usp=sharing