Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dynamically adding condition to metrics

Dynamically adding condition to metrics

Dynamically adding condition to metrics

Hi all,
I have a user requirement which goes like this. In one table I have 3
fields which contains Spec_Code,Param_name,Param_priority,Result.

Spec_Code,Param_name,Param_priority these three fields make a unique
record. I have to generate a report on the basis of Spec_Code. User
will first choose a Spec_code and the report will have the following

parameter_name,Yield (%), Total_In_records

In this report based on the priority (which is a number field) I have
to calculate the yield percentage.For priority number 1 I have to
calculate the total records passed which will come under the heading
Total_In_records for that parameter and for that spec.

for priorty number 2 the value for Total_In_records will be based on
the passed records (consider pass=0 in the table) from the parameter
where priority was 1. It means Total Pass from first priority becomes
Total_In_Records for the next priority.

Can anybody help me in creating the metrics for this report.


RE: Dynamically adding condition to metrics

what is total pass and yield defined as?
Also if you were to handwrite the sql, what would it look like?

RE: Dynamically adding condition to metrics

Here is the SQL, which is giving me the desired result.

select    distinct a11.spec_cd , a11.RR_PARAM_NM,
                 a12.PRTO_PRITY  ,
                 DECODE(a12.PRTO_PRITY,1,(select count(param_value) from met_fact where rr_param_nm= a11.RR_PARAM_NM and spec_cd = a11.spec_cd),
                 (select count(t1.param_value)    
from    MET_FACT t1,     SPEC_PARAM t2
where    t1.RR_PARAM_NM = t2.RR_PARAM_NM
and        trim(t1.SPEC_CD) = trim(t2.SPEC_CD)
and        trim(t1.SPEC_CD) = trim(a11.spec_cd)
and        t2.prto_prity  = (a12.prto_prity - 1 )
and        t1.rslt_flg=1   
                 )) as Total_IN,
(select count(param_value) from met_fact where rslt_flg=1 and met_fact.spec_cd=a11.spec_cd and met_fact.RR_PARAM_NM=a11.RR_PARAM_NM) as Passed,
(select count(param_value) from met_fact where rslt_flg=0 and met_fact.spec_cd=a11.spec_cd and met_fact.RR_PARAM_NM=a11.RR_PARAM_NM) as Failed
from    MET_FACT a11,     SPEC_PARAM a12
where    a11.RR_PARAM_NM = a12.RR_PARAM_NM
and        a11.SPEC_CD = a12.SPEC_CD
and        a12.prto_prity is not null
and        trim(a11.SPEC_CD) = '3126702'
group by a11.spec_cd ,a11.RR_PARAM_NM,a12.PRTO_PRITY   

RE: Dynamically adding condition to metrics

I don't speak english.  I speak a bad english(I read bad,so).It's possible, I didn't understand the problem.
Probe with this:
- Create a "Virtual Fact' with value=1 for all the records.
- Create a Metric,grouping at the level that you need (spec_code,Param_Priority and ReportLevel, I think its that).
- Then, probe with a 'RunningSum' over the virtual fact.

Good Luck.
If it isn't the solution, I'm sorry for the lost time.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close