Dynamically adding condition to metrics

[vikassharma]

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
Headings.

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.

Vikas

 

[nlim]

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

 

[Guest_imported]

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

 

[Guest_imported]

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.