grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

Grouping on multiple datasets - ANY suggestions

I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:

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?

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping Data Based On Return From Stored Procedure

I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:

ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)

ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)

ContactNumType3 (From the Tables)
File_Name5 (From the sp)

ContactNumType4 (From the Tables)
File_Name6 (From the sp)

File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)

So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?

-
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....

If I understand you correctly, the problem here is that the argument to the procedure needs come from the results of your two-table join. Question: Are you running SQL Server 2005 or SQL Server 2000? If you are running SQL Server 2005, I would suggest converting your stored procedure into a table function (if possible). If you can do this, then you will be able to use a CROSS APPLY join and pass the arguments to the newly written function based on the results of your two table join.|||The solution involved creating a new stored procedure combining the table dataset and the stored procedure. The one dataset made it much easier to directly throw each dynamic field into a row.

Group two different datasets by date field

I have two (actually, more than 2, but for simplicity's sake,2) datasets
which I need to put into one pivot table or matrix style report. For
example, if I want to create a report on some forum software, and I have two
datasets, one being the sum of new users grouped by signup date, and the
other being the sum of new posts grouped by post date. The report would
look like this:
Date | Number of New Users | Number of new posts
2/8/2006 | 43 | 175
2/9/2005 | 47 | 190
etc...
The problem that I dont understand is how to group these two datasets by two
different fields, even though they are both dates. Make sense?
Thanks,
NickYou can't have 2 or more datasets in the same table or matrix. RS won't
understand it, even if you percieve the sets as comparable. You need to
either create one set that gives you all the data, doing joins and
unions in your sql query, or maybe have a few tables next to each
other. Would rather go for one big query, though.
Kaisa M. Lindahl|||Perhaps something like this would work for you..
Select signupdate as thedate, count(signupdate), count(Postdate) FROM
(select signupdate, signupdate, NULL from sometable
UNION
select postdate,NULL, postdate from someothertable) as a
Group by thedate
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"nick parker" wrote:
> I have two (actually, more than 2, but for simplicity's sake,2) datasets
> which I need to put into one pivot table or matrix style report. For
> example, if I want to create a report on some forum software, and I have two
> datasets, one being the sum of new users grouped by signup date, and the
> other being the sum of new posts grouped by post date. The report would
> look like this:
> Date | Number of New Users | Number of new posts
> 2/8/2006 | 43 | 175
> 2/9/2005 | 47 | 190
> etc...
> The problem that I dont understand is how to group these two datasets by two
> different fields, even though they are both dates. Make sense?
> Thanks,
> Nick
>