calculation based on data from 2 tables 1

[tandyaw]

I have the following data in my database:
Table 1
AUDIT_ID FACTORY_ID TYPE
10001 12345 AUDIT 1
10002 23456 AUDIT 1
10003 12345 AUDIT 2
10004 34567 AUDIT 1
10005 23456 AUDIT 2

Table 2
AUDIT_ID QUESTIONS_ID
10001 23
10001 45
10001 57
10001 77
10002 34
10002 45
10003 45
10003 98

I need to create a report to show the total number of questions for a factory that were in AUDIT 1 and also in AUDIT 2.
i.e, for factory 12345, there are 4 questions in audit 1, and 2 questions in audit 2.. but only 1 appears in both audit 1 and 2 (question_id = 45). So in my report i will have to show:
factory #of questions(audit1) #of open questions
12345 4 1

I've tried creating a view and access the view data instead, but it didn't work.

How can I do this?

Thanks again in advance,
Lia

 

[griffindm]

Lia,

Your example data does not easily fit into your explanation. Can you give more detail on what each of Tables 1 and 2 represent, and how the audit_id fits into the equation? I'm thinking a self-join view of table 1 back into itself (each with a filter on each portion to get only Audit 1 or Audit 2) would get you the view data that you need, but a little more background on the problem would help.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[tandyaw]

Table 1 represents all the available audits. While table 2 represents all the questions in each audit.

Based on the sample data, Factory 12345 currently has 2 audits available (10001 which is AUDIT 1 and 10003 which is AUDIT 2). AUDIT 2 is the follow up of AUDIT 1. There might be AUDIT 3 which is the follow up of AUDIT 1 and 2.

In the report, I will need to show the total number of questions in AUDIT 1 and the total number of open questions (questions that are re-occuring in AUDIT 2 and probably also in AUDIT 3 - if there's an AUDIT 3 by the time the report runs).
I guess my question is how can I do that ? I can find which AUDIT_ID belongs to which factory. But how can I find which questions are re-occurring? I think I have to compare the question_id somehow, but I don't know what's the best way to do it in Impromptu.

Any suggestions/helps will be very much appreciated.

Thanks,
Lia

 

[griffindm]

Lia,

I think this is possible, if a little complex. Try creating a view that 'collapses' the Factory data in Table 1 into a single row per factory, as in (for SQL Server, for Oracle use DECODES):

CREATE VIEW FACTORY_SUM AS
SELECT FACTORY, MAX(CASE WHEN AUDIT_TYPE = 'AUDIT1' THEN AUDIT_ID ELSE NULL END) "AUDIT1_ID",
 MAX(CASE WHEN AUDIT_TYPE = 'AUDIT2' THEN AUDIT_ID ELSE NULL END "AUDIT2_ID",
 MAX(CASE WHEN AUDIT_TYPE = 'AUDIT3' THEN AUDIT_ID ELSE NULL END) "AUDIT3_ID"
FROM Factory_Audits
GROUP BY FACTORY_ID

This should give you a single row with everything you need to link into the questions table (table 2). Use a single alias and join it to both Audit1_id AND Audit2_id. This should restrict the results to questions that persist across the original and subsequent audits. Use another alias to just get the sum of the total questions in the original audit (returning summary data ONLY).

This should get you pretty close to what you need. You may have other issues trying to extend the reporting logic into the results for Audit_3. Give it a try and see how it goes.

Hope this helps!

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[tandyaw]

Hi Dave,

If I'm understanding you correctly, joining the alias to both audit1_id and audit2_id, won't give me any results because audit1_id and audit2_id will never be the same.

When you said 'single alias' it means an alias of the questions table, right?

Thanks,
Lia

 

[griffindm]

Lia,

The select with joins from the view to get the count of repeating audit questions would be:

select a.factory, count(b.questions_id)
from factory_sum a, questions b, questions c
where a.audit1_id = b.audit_id
and a.audit2_id = c.audit_id
and b.questions_id = c.questions_id
group by a.factory

I haven't built your sample data into tables to test it, but I will later today.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[griffindm]

Lia,

I was able to recreate your data today. The above view allowed me to write a report that shows in open questions remaining from Audit_1, as well as to count the total open questions, as well as the total questions for the factory. I made question 23 open in audit_2 for the factory to make sure it handled more than one open question properly.

The report layout looks like:

[b] Factory      Open Questions  Total Questions [/b]
12345                23               4
12345                45               4
                   --------       ----------
Total for 12345      2                4

I joined the view to table2 three times. Twice to link on Audit_id separatedly into each of the views Audit1_id and Audit2_id columns. I added a report filter to set the question number for each of the above aliases to be equal (i.e. only show questions in both audits. I then added the alias a third time just into Audit1_id to get total questions.

The join strategy would be a little more complex to pick up the net total question for all distinct questions in all audits.

Hope this helps.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[tandyaw]

Hi Dave,

I think I'm not doing this correctly.
This is what I did:
I created the view above.
Then I created an alias of table 2. First, I join the audit_id from the alias to both audit1_id and audit2_id from the view. But I didn't get any results in the report. Then I changed it and make a join with audit1_id from the view. I also created another alias of table 2 and join it with audit2_id from the view.
So I have 2 aliases from table 2.
Now I got some data showing in the report, but it still doesn't show the repeated question.

I'm so confused.

-Lia

 

[griffindm]

Lia,

Here is what my SQL in the Impromptu report looks like:

select T1."FACTORY_ID" "c1", T1."AUDIT1_ID" "c2", T1."AUDIT2_ID" "c3", T1."AUDIT3_ID" "c4", T2."QUESTION_ID" "c5", T3."QUESTION_ID" "c7", count(T4."AUDIT_ID") "c8"
 from "CPREPORTS"."FACTORY_SUM" T1, "CPREPORTS"."TABLE_2" T2, "CPREPORTS"."TABLE_2" T3, "CPREPORTS"."TABLE_2" T4
 where T1."AUDIT1_ID"=T2."AUDIT_ID" and T1."AUDIT2_ID"=T3."AUDIT_ID" and T1."AUDIT1_ID"=T4."AUDIT_ID" and T2."QUESTION_ID"=T3."QUESTION_ID"
 group by T1."FACTORY_ID", T1."AUDIT1_ID", T1."AUDIT2_ID", T1."AUDIT3_ID", T2."QUESTION_ID", T3."QUESTION_ID"
 order by 1 asc, 2 asc, 3 asc, 4 asc, 5 asc, 6 asc

Alias T2 and T3 are used to create the join on repeating open question. T4 is used to get the count of total questions. This is then done as an average for the report (locally by Impromptu, not in the database SQL above) to avoid counting the total multiple times for each open question.

Let me know if this doesn't clear up your questions.

Regards,

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com