Link to home
Create AccountLog in
Avatar of Barry Cunney
Barry CunneyFlag for Ireland

asked on

SSRS 2008 Report Builder 2.0 Sort on Calculated Field does not see, to work

Hi I have a calculated field on my dataset in Report Builder 2.0.
It returns the correct values required in the matrix.

However I have also specfied this calculated field as the sort field in Tablix properties Sorting, but it does not appear to actually do the sorting

Calculated Field
=LTrim(CStr(IIF(Fields!Country_Name.Value IS NOTHING, "", Code.GetCountryArea(Fields!Country_Name.Value))))

This calculated field makes a call to a Custom Code function which returns a 3-4 charcter string which is the area code associated with the country

I want the results to be sorted by Area A-Z(ascending)
e.g.
APAC
APAC
LAT
NAM
NAM
WSE
WSE

Please see print screen of results being returned.
Ignore the first field AreaSort in the print screen - that was a workaround using Asc to get ascii code which I was trying, but this did not work either.

I am wondering is there something I need to do different so as a global sort is done after all the rows have their values - maybe what is happenign now is that the sort is being done but just on each detail row or something.
but this is where I am hoping to get the the experts advice to talk me through this and give any other advice.

Let me know if any further details is required


 User generated image
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

You need to add a group of Area then sort the group
Avatar of Barry Cunney

ASKER

Hi planocz,
I tried this but the grouping appears to not work on Area, most probably because this is a calculkated field which gets it's value from Custom Code based on the value of the Country_Name field in the underlying dataset.
Attached is a print screen of various tests I did to try and display and understand what works and what does not work. The print screen shows 5 different matrices with different grouping and non groupiings - Let me know what you thing

Thanks  User generated image
Have you tried placing this code =Code.GetCountryArea(Fields!Country_Name.Value) in your Area group under SORT this should go thru an cal. each field then sort for you before displaying the report.
Yes I have tried this and it does not work - This is what I did for the tablix furthest to the right in the print screen above. I think that because it is calculating the value for the Area field for each row, this is too late for the Sort, or something like that.
I also tried adding this as a calc field on the actual dataset, but it did not work either.

I could not find any documentation or examples indicating that grouping or sorting could be done on a calculated field which gets it's values from a custom code function.

Background Info: I have no direct control over the server side database/report model(it would take 6 months for a feature request because of red tape in the organization) . I need to have a report which summarizes data by geographical region APAC, NE, WSE, EMEA etc. using the country as the basis to decide what gets included in what region. The country is in the underlying report model so I can include this  as normal in the dataset, but there is no geographical region data in the underlying report model. So my workaround has been to store these regions with the region-country relationship in a hashtable in Custom Code in the report. So in simple terms I am storing static data in my report which is not available in the Report Model. And this workaround does work, i.e I can now get regions displayed on the report - There is a one time maintenance to set these up in the code and they should rarely change so the maintenance overhead is economical enough - but my challenge now is on grouping and sorting these, as explained above. Maybe its just the case that grouping/sorting cannot be done using  calculated field/expressions which gets it's values from a call to a Custom Code function?
Hi
Instead I implemented this in a calculated field using Switch function, on the dataset as follows:
=Switch(Fields!Country_Name.Value="ARG","LAT",
Fields!Country_Name.Value="BHS","LAT",
Fields!Country_Name.Value="BRB","LAT",
Fields!Country_Name.Value="BMU","LAT",
Fields!Country_Name.Value="BOL","LAT",
Fields!Country_Name.Value="BRA","LAT",
Fields!Country_Name.Value="CAN","NAM",
Fields!Country_Name.Value="CHL","LAT",
Fields!Country_Name.Value="COL","LAT",
Fields!Country_Name.Value="CRI","LAT",
Fields!......................

and it is possible to successfully group and sort on this
Sorry I have been busy it work could not get back to you sooner.
You might having something there. Let me know if it works.
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So I guess this solved your problem?
Avatar of Mike McCracken
Mike McCracken

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.