on 11-16-2012 7:53 PM
Hi,
I have left outer joined resources.resources_id to jobs_has_dispatch.resources_id.
The right table has two date fields (start and end) representing a date range that a particular resource_id is dispatched for.
I am building a calendar style report that will indicate if a resource_id has been dispatched for the calendar date. I have already solved this.
My challenge is: I need to also show on the calendar report the resource_id values that ARE NOT dispatched for the particular date. (I will then highlight them in red so people have a visual of how many unique pieces of equipment (resource_id) are not dispatched in a given month). All resource_id values are in the left table (resources.resources_id) but there isn't a record with a date range in the right table to say when the resource_id is not dispatched.
For example, resource_id "X2" is dispatched from November 1 to November 10, 2012. I can successfully show "X2" on each calendar date of the report Nobember 1 to November 10, 2012. "X2" is also dispatched from November 15 to November 20, 2012. I can successfully show that on the calendar report.
However, I would like to also show resource_id "X2" on each of November 11, 12, 13, 14 even though "X2" has not been dispatched for those dates. I will then highlight those dates in red so the user has a visual of how much red is on the monthly calendar report.
How to I pull "X2" for the November 11, 12, 13, 14 dates if there is no records in the right table?
thanks in advance!
Mark
Hi Mark,
Here's what I think can be done:
1) Setup a separate subreport that puts all equipments in a array
2) Make a copy of the subreport that shows equipments that fall in a certain date range
3) Pass this array to the this 2nd subreport
4) So, right-now both the Subreports will show the same result isn't it?
5) I'm sure you have a formula in the Report Footer of this subreport that shows equipments concatenated together
6) All you need to do is, check if the equipment exists in the array. If it does, then skip and show the ones that do not exist.
So, basically it will show everything else that is not in the concatenated string and I guess this is what you want?
The Left-outer join route won't really work here, since it's already showing you X02 from the left table. There is no way it will show you another X02 record with Null dates. So what I'm trying to say is it is already giving you all the equipments from the left table since their dispatch dates exist.
Anyway, let me know if the array method works.
- Abhilash
Follow us on Twitter
Got Enhancement ideas? Try the SAP Idea Place
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Abhilash,
Writing arrays steps into programmer world. I'm not up on writing arrays. If you can provide an example of what it would look like I might then be able to see how it flows and then input my specific values. This particular example looks like I need to set up a couple of arrays/subreports and then subtract commone ones.
Appreciate your help!
Mark
Hi Mark,
Here's the idea:
1) Create a subreport with the resources table as its datasource. Place this somewhere on the Report Header
2) In the Subreport create a group on the Equipment name
3) Create an array formula with this code:
whileprintingrecords;
shared stringvar array arr;
redim preserve arr[groupnumber];
arr[groupnumber] := {Equipment_name};
Place this formula on the Group Header. This shared array will hold all the Equipment Names
4) Create a formula in each of the Subreports that checks if the Equipment exists in the array.
shared stringvar array arr;
stringvar equip_list;
equip_list := join(arr,",");
Place this in the Report Header of the Subreport.
5) Create another formula to check if the Equipment exists in this string:
whileprintingrecords;
stringvar equip_list;
equip_list := replace(equip_list,{Equipment_Name}&",","");
Place this on the Equipment Name group
6) Then, create another formula to display the new string:
whileprintingrecords;
stringvar equip_list;
Let me know how this goes!
-Abhilash
Hi Abhilash...looks like that works!
BUT....it only works if the first subreport, with the array formula, (number 3 above) is not suppressed and can grow. Yes, even if I don't check the "Can Grow" box I get a different result in my subreport....very weird. Obviously I don't want the inital comprehensive list of all equipment to show.
Any ideas?
thx,
Mark
Hi Mark,
I didn't want to confuse you with all the steps and I thought we'll take it one step at a time. Glad that you got this far so soon!
The Subreport/section that holds the Subreport that brings in the list of equipments cannot be suppressed.
Here's what you can do though:
1) Suppress all the sections of the Subreport
2) Go back to the Main Report and reduce the size of the Subreport to 'bare minimum'. It shouldn't appear bigger than a dot. You would also need to remove its borders.
Place it somewhere at corner of the Report Header.
That should do the trick!
-Abhilash
User | Count |
---|---|
74 | |
26 | |
10 | |
10 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.