How to show only one row for each plan

[cognos74]

please see below post for the corrected one

Hi I have plan, subplan, date achived columns in my query.

I have to create a report like this:

Plan       Subplan       Plan Completed(Y/N)

PlanA      Sub1       Y
PlanB      Sub2       B

using this below example:  if date achived column filled with dates for all ot the subplans then say 'Y'
if the subplan has some 0s and some dates for dateachived then  say 'N'.
it shoule be only 1 line for the PlanA and PlanB with Y or N like above example.

Plan     Subplan    Date Achived
PlanA    Pai1        7/01/2016
       Pai2        7/29/2016
       Pai3        7/15/2016
       Pai4        7/22/2016

planB   sub1        0
      sub2        0
      sub3        0
      sub4        8/12/2016
      sub5        0
Please can anyone help me out in this

[cognos74]

here is the corrected one:
Hi I have plan, subplan, date achived columns in my query.

I have to create a report like this:

Plan              Plan Completed(Y/N)

PlanA            Y
PlanB            N

using this below example:  if date achived column filled with dates for all ot the subplans then say 'Y'
if the subplan has some 0s and some dates for dateachived then  say 'N'.
it shoule be only 1 line for the PlanA and PlanB with Y or N like above example.

Plan     Subplan    Date Achived
PlanA    Pai1        7/01/2016
            Pai2        7/29/2016
            Pai3        7/15/2016
            Pai4        7/22/2016

planB   sub1        0
           sub2        0
           sub3        0
           sub4        8/12/2016
           sub5        0
Please can anyone help me out in this

[Corrigon]

Hi - is your report based on a relational datasource? I'm going to assume it is.

From my inexperienced eye, this looks like a tough ask. Because you eventually want to end up with each Plan and all the dates of all the Subplans on the same row (so you can apply a logical statement to determine the 'Yes'/'No' result), I can't see any other way to achieve this other than doing a LOT of Joins.

If each Plan has the same number of Subplans, and if there is a standardised convention for numbering them (i.e. the first Subplan always starts with '1', the second with '2', etc) then this could be doable. You would create your main query with Plan information only. Then you would create another query looking at Subplans, that includes the parent Plan number (this will be your Join Key with the first query) and the Achieved Date. You would filter it by using something like substring([Subplan], 1,1) = '1'.

This would give you a list of all the First Subplans in your database. Join that to your First Query using the Plan data item as the join key. This would give you a list of every Plan, and the Achieved Date of the First Subplan on the same row of the Joined Query. Call the Achieved Date [Achieved_Date_1] or something as you will be bringing in other Achieved Dates from the other Subplans later.

Then, you would rinse wash and repeat to create filtered queries for Subplan 2, 3, 4, etc, and joining them to your Plan Query. Once they are all joined, you can then use a data item with a case statement that checks all of the Achieved Dates to see if they are null. If all are not null, then you get 'Yes', else 'No'.

If each Plan can have a variable number of Subplans, but there is a maximum number they are allowed to have, then you would use Outer joins on each Query up to that maximum, so that you can cover all eventualities e.g. your maximum might be 10 Subplans, but Plan A only has 6 Subplans.

But if its variable with no maximum number, or the maximum is something unmanageable (100), or you don't have a standardised convention for identifying the First Subplan, Second Subplan etc. then I got nothing, and hopefully someone more experienced will offer you a better solution.

Even if you do have all of the necessary components in play it's an ugly process and I can tell you from sad, sad experience that this will be tedious to accomplish, and the end result might not be slow to produce output.

BEST OF LUCK!

[cognos74]

Hi thanks you for your time and reply.
I have code colum in my query so
can I say something like this:

if Dateachived  is null for Code then 'N'
if Dateachived is not null for Code then 'Y'

something like this works??

Thanks,

[Corrigon]

Hi again - yes that should work if you have only 1x DateAchived field. But you would have to expand the If statement using the AND clause to include multiple DateAchived fields, should they be in your query. You want to know if All of the Subplans have a value in DateAchived, right?