Dynamic Formula for Instr

[LHSCNaved]

Hi All,

Environment: CRXI and SQL Server 2005

I am looking for a formula which can count some tests out of
thousands using instr.

I have thousands of Lab results need to grouped and counted.

For example:

NM THERAPY ENDOC. MALIG. LN*C 46 Records
NM THERAPY ENDOC. MALIG. LN*S 46 Records
NM THERAPY ENDOC. MALIG. LN*T 46 Records

Through using instr I was to able to group them like
NM THERAPY ENDOC. MALIG. LN (before *) but it should be counted 46 instead of 138 records as per requirement. There are many more tests other then mentioned above, so I need something dynamic.

Thanks all in advance.
Naved Altaf

 

[lbass]

You need an inner group just on the field itself. Then insert a count on any non-null field at this new inner group level.

-LB

 

[LHSCNaved]

Hi,

Thanks Lbass for your quick response, I did created the inner
group and do have 46 records per inner group but would like to have 46 records NM THERAPY ENDOC. MALIG. LN as upper group total.

Is is possible that we count number of inner group so that I can divide Upper group total counts with number of inner group counts. (46+46+46) = 138/3 = 46 Records.

Thanks again.

 

[lbass]

If the count is always the same for all subgroups, then you could simply add the following formula (not inserted summary) to your higher order group:

count({table.record},{table.subgroup})

This formula, placed in the higher order group, will always pick up the value of the first subgroup summary.

-LB

 

[LHSCNaved]

Your solution has worked perfectly and I have achieved first milestone. The small issue which I have is that I cannot use this formula in group sort expert for top 10 tests.

Thanks.

 

[lbass]

It is important to lay out the report goal in your initial post. I wouldn't have recommended that approach had I known you wanted to do a group sort. What is the name of the field that contains the "*"? Does this field always return the same characters to the left of the "*"? I.e., does it always start with: NM THERAPY ENDOC. MALIG. LN?

-LB