Group two different datasets by date field -- sql server reporting services

sql server reporting services : Group two different datasets by date field

nick parker
2/10/2006 2:58:27 PM

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,

Kaisa M. Lindahl
2/12/2006 1:03:24 PM

You 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

Wayne Snyder
2/13/2006 5:07:27 AM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn