Display Non-related group value

[FunkyBunch]

I am currently working on a report that needs to display mounted equipment transfers, additions, and deletions.

This is my formula to check if it is a transfer:
Stringvar MEChanger;
if {user_defined20_fw.archive_status_fw} = 'A' then

MEChanger := 'Deletion'

else if ({Count_access_equip_FW__From_ME_History.count(access_equip_Fw)}>1) and
({user_Defined20_Fw.archive_status_Fw} = 'N') then

MEChanger := 'Transfer'


else

MEChanger := 'Addition'

If it is a transfer and the date removed is null i have to show the unit number it was attached to. if it is a transfer and the date removed is not null i have to show the unit number it was moved to.

Currently i have the report grouped by Company, division, and unit.


EX of what i need to see:
Group1: Company2
Group2: Division2
Group3: Unit2 Make model serial#
Equipment # Make Serial # Transfer from Unit 1

Group1: Company1
Group2: Division1
Group3: Unit1 Make model serial#
Equipment # Make Serial # Transfer to Unit 2


Thus far i have everything showing as above, except for the to unit..... and from unit....

Thanks for all of your help.

 

[lbass]

There is nothing in your post that tells us how to determine the number of the unit transfered from or to.

-LB

 

[FunkyBunch]

I apologize.

each piece of equipment has a number as does each unit.

If the equipment is transferred from a unit, a new record will be created. Everything in the new record will be the same (Equipment id, etc) except for the new vehicle id, date added, and date removed.

If the vehicle is transferred from it will have no date_removed will be null and {count(equip_id)}will be > 1. To find the unit number we just have to:

select top 1 vehicle_id
from equipment
where the equipment ids are the same and date removed is null and date added is not null
order by date removed desc


If the vehicle is transferred to it will have a date removed. To find the unit number we just have to:

select top 1 vehicle_id
from equipment
where the equipment ids are the same and date removed is not null and date added is not null
order by date added desc


It is a really difficult part of my report and i am not sure if it is even possibly to accomplish, but thanks alot for all of your help.

 

[lbass]

But the to and from units appear to be nested in different groups, so how would you access those numbers? It would help to see some sample data of what this might look like.

If the units referenced are from different groups, you might need to use a subreport that doesn't use those groups, but is instead grouped by equipment #. You could then access the unit # from the subreport.

-LB

 

[FunkyBunch]

Good Call,

If i used a sub-report to pass the variable to the main report could i then just suppress my sub-report?

Would that be a valid work-around?

 

[FunkyBunch]

Or would it be smarter to create a view on the database and just use the view?

 

[lbass]

I can't respond to your last question. As to your first, you could do that or you could just display the resulting unit from the subreport.

-LB

 

[FunkyBunch]

What about using an SQL Expression? Is there a possible way to make that function in this scenario?

 

[lbass]

I'd have to understand your data better before I could advise you on that. Also depends upon your version of CR. The SQL expression functionality in XI doesn't currently work with subqueries the way previous versions did. You could probably try using a command, but the minute you link to it, you would be forcing your report to process locally.

-LB

 

[FunkyBunch]

How would i go about obtaining the vehicle number if i were to go about the subreport approach? Is there a particular formula i would use?

Thanks for your help!

 

[lbass]

Please provide some sample detail level data that includes the vehicle number, the date removed, the date added, the equipment ID and which uses your current group structure. Show what you'd expect to see in the transfer to and transfer from fields.

-LB

 

[FunkyBunch]

Here is just a shot of the sample data. The first column is vehicle id, the second column is equip ID, the third is date added and the fourth is date removed.


4 2 1999-12-01 00:00:00.000 NULL
6055 3 2003-03-01 00:00:00.000 NULL
6 4 1999-12-01 00:00:00.000 NULL
10 5 1992-03-01 00:00:00.000 2007-09-06 00:00....
11 6 2001-01-01 00:00:00.000 NULL
11 7 2001-01-01 00:00:00.000 NULL
11 9 2001-01-01 00:00:00.000 NULL
16 12 2000-11-01 00:00:00.000 NULL
16 14 2000-11-01 00:00:00.000 NULL
9590 15 1990-03-01 00:00:00.000 NULL
9591 17 1999-12-01 00:00:00.000 NULL
25 18 2000-11-01 00:00:00.000 NULL
45 23 1996-10-01 00:00:00.000 NULL
49 24 1999-11-01 00:00:00.000 NULL
64 25 1994-03-01 00:00:00.000 NULL
64 26 1994-03-01 00:00:00.000 NULL

Let me know if I can provide you with anything to help you help me.

Thanks

 

[lbass]

Better, but you forgot these critical parts:

which uses your current group structure. Show what you'd expect to see in the transfer to and transfer from fields.

So what groups would these sample records fall under (show this), and what would you expect to see in the transfer to/from column?

-LB

 

[FunkyBunch]

What i would want to show in the transfer to/from field is the unit # of the vehicle that the equipment was last attached to.

For example if the equipment(22) was moved from unit 3 to unit 1. for unit 3 it would show

Unit # Equipment Transferred To/From
3 22 1
1 22 3

Again i hope this helps you help me.

Thanks,

 

[lbass]

Sorry, but I meant for you to provide the group context for your sample data from your previous post and to then show what you would expect to see for the transf to/from column. Your sample data (two posts ago) doesn't show the unit number and your last post doesn't reflect the sample data.

-LB

 

[FunkyBunch]

I think it may be best to just let this one go, I am going to investigate database views to fix this problem. Thanks for your help and i apologize for my communication.