Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam