DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92
DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92
Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:
Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190
or
Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190
Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%
I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:
= count((Fields!sale_number.Value,"DS2"))
This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.
If I try something fancier such as:
=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))
The report won't even run.
I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).
I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.
Thank you,
cmk8895Joining datasets at a report level is not currently supported. It is on the wish list for a next release. Why don't you create the combo dataset at the data source level?
No comments:
Post a Comment