June 12, 2014 at 8:36 am
I've got a dataset of properties, customers, and distances of the two from each other. Next, I've made a sql query that'll retrieve the property closest to a given customer (using a windowing function with row_number(), ordering in asc order inside cte and then taking row=1 outside cte), however I've decided to expand it to include all customers within a 25 mile radius of a given property.
I want to leave it up to the user of the SSRS report to decide whether to limit the report to the closest property, the closest, two etc., or all properties within 25 mile radius.
Dataset looks like this.
custID, Property, distance_miles
112, Laguna, 2
112, Laguna, 4.5
112, Laguna, 6
890, Monterey, .5
890, Capistrano, 1
890, Lodown, 10
As you can see the data is grouped by custID and ordered by distance_miles.
In SSRS I know how to sort results by custID and distance_miles = done.
But, I want to configure SSRS to now display only the closest property ie Top N = 1 to the customer.
In Tablix Properties dialogue box I've tried this:
expression: min(Fields!distance_miles.Value)
operator: Top N
Value: 1
Datatype: float (also tried int)
The error I get is:
"an error occurred during local report processing. The definition of the report "<name of report>" is invalid. A FilterExpression for the dataset 'DataSet1' includes an aggregate function. Aggregate functions cannot be used in dataset filters or data region filters"
My query is like this (no aggregates)
;WITH CTE AS
(SELECT
row = ROW_NUMBER() OVER(PARTITION BY custID ORDER BY (Geography::Point(geo.latitude, geo.longitude, 4326).STDistance(geography::Point(prp.latitude,prp.longitude, 4326))/1609.34) asc),
ld.custID
prp.property_name,
distance_miles = Geography::Point(COALESCE(geo.latitude,0), COALESCE(geo.longitude,0), 4326).STDistance(Geography::Point(COALESCE(prp.latitude,0),COALESCE(prp.longitude,0), 4326))/1609.34
FROM
<a bunch of joins>
)
SELECT
distinct
custid,
property_name,
FROM cte
WHERE 1=1
AND row > 0
AND distance_miles <= 25
Can someone help me to configure SSRS to group by customerID and retrieve the row with Min(distance_miles) per customer?
June 12, 2014 at 9:13 am
I think you have to do the TOP in the stored procedure, not in SSRS. If you can do it in SSRS, I have no idea how.
SELECT TOP (@NumRecords) {FieldList}
FROM...
June 12, 2014 at 9:35 am
Can you explain your wsy in a little more detail? Looks like you're suggest ing parameterizing this and that looks promising!! Are you saying to parameterize the row_number column? (I'm totally new to ssrs.)
June 12, 2014 at 9:46 am
Yes. Instead of just this:
;WITH CTE AS
(SELECT
row = ROW_NUMBER() OVER(PARTITION BY custID ORDER BY (Geography::Point(geo.latitude, geo.longitude, 4326).STDistance(geography::Point(prp.latitude,prp.longitude, 4326))/1609.34) asc),
ld.custID
prp.property_name,
distance_miles = Geography::Point(COALESCE(geo.latitude,0), COALESCE(geo.longitude,0), 4326).STDistance(Geography::Point(COALESCE(prp.latitude,0),COALESCE(prp.longitude,0), 4326))/1609.34
FROM
...
You add a parameter to prompt the user for a number of values, like this... note the parentheses around the parameter - the parameter won't work without it.
DECLARE @NumValues INT
;WITH CTE AS
(SELECT TOP (@NumValues)
row = ROW_NUMBER() OVER(PARTITION BY custID ORDER BY (Geography::Point(geo.latitude, geo.longitude, 4326).STDistance(geography::Point(prp.latitude,prp.longitude, 4326))/1609.34) asc),
ld.custID
prp.property_name,
distance_miles = Geography::Point(COALESCE(geo.latitude,0), COALESCE(geo.longitude,0), 4326).STDistance(Geography::Point(COALESCE(prp.latitude,0),COALESCE(prp.longitude,0), 4326))/1609.34
FROM...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply