sql server reporting services: Table Question multiple datasets -- sql server reporting services

Re: Table Question multiple datasets

Josh
8/10/2006 11:47:40 AM

Marc,

I am confused. This sounds like a problem that could be solved with the
right SQL, but I don't understand what the tables look like. Can you
show me what the table(s) that you are working with look like now and
explain again what you are trying to do?

-Josh

[quoted text, click to view]

Re: Table Question multiple datasets

Marc Bumgarner
8/10/2006 2:32:57 PM

Josh

This is the results of dataset1 (2005 data) amd dataset2 (ytd 2006 data)
we collect 15min interval data from various meters and the data is stored at
15 minute intervals, the result set below is the interval data grouped by
month for 2005, same for the data for year-to-date 2006

2005 data:

2005-01-01 00:15:00.000 7523 5681 7443 0 887
2005-02-01 00:15:00.000 7903 6399 7339 0 702
2005-03-01 00:15:00.000 10295 7656 7330 0 660
2005-04-01 00:15:00.000 11673 8250 8356 16 240
2005-05-01 00:15:00.000 10979 8192 6139 32 121
2005-06-01 00:15:00.000 9423 7199 8480 421.371 0
2005-07-01 00:15:00.000 9102 6935 5854 541.2065 0
2005-08-01 00:15:00.000 9619 7151 7503 544.0755 0
2005-09-01 00:15:00.000 9156 7294 8439 380.928 0
2005-10-01 00:15:00.000 11669 8541 10258 74.2855 119.3685
2005-11-01 00:15:00.000 9524 7012 7412 3.30749999999999 312.055
2005-12-01 00:15:00.000 6188 4888 7019 0 743.6435

2006 ytd data (only had data up to may on test server)

2006-01-01 00:15:00.000 6524 5557 6684 0 546.2085
2006-02-01 00:15:00.000 7304 5878 5849 0 619.7395
2006-03-01 00:15:00.000 10581 8043 8045 11.3965 426.826
2006-04-01 00:15:00.000 12998 8560 10077 41.84 87.6215
2006-05-01 00:15:00.000 13504 8667 9353 90 81

what I want to do in the report is show the data for 2005 and ytd 2006 and
by month

example report

Month 2005 col2 2006 col2 2005 col3 2006 col3 2005 col4 2006
col4 2005 col5 2006 col5
jan xxx xxx xxx xxx
xxx xxx xxx xxx
feb xxx xxx xxx xxx
xxx xxx xxx xxx
mar xxx xxx xxx xxx
xxx xxx xxx xxx

What do you do if you can't express the result set as a single dataset?

thanks

[quoted text, click to view]

Re: Table Question multiple datasets

Josh
8/11/2006 5:47:02 AM

Marc,

Much clearer now; thanks. You can do this pretty easily with the right
SQL.

You are going to make several subqueries, 1 for each year, and use
filters to make sure that each subquery has just the data that you
want. Then, you use the MONTH() function to pull out the month number.
Each subquery will join by that month number.

Example:
SELECT
t2005.monthNo,
t2005.value1,
t2006.value1,
t2005.value2,
t2006.value2
FROM
(SELECT
MONTH(dateField) as monthNo,
value1,
value2
FROM
dataTable
WHERE
(YEAR(dateField) = 2005) t2005
LEFT OUTER JOIN
(SELECT
MONTH(dateField) as monthNo,
value1,
value2
FROM
dataTable
WHERE
(YEAR(dateField) = 2006) t2006
ON (t2005.monthNo = t2006.monthNo)
ORDER BY t2005.monthNo

If you want the month name in there, use a CASE statement. The top part
of the SQL will look like this:

Example:
SELECT
t2005.monthNo,
CASE
WHEN t2005.monthNo = 1 THEN 'Jan'
WHEN t2005.monthNo = 2 THEN 'Feb'
WHEN t2005.monthNo = 3 THEN 'Mar'
WHEN t2005.monthNo = 4 THEN 'Apr'
WHEN t2005.monthNo = 5 THEN 'May'
WHEN t2005.monthNo = 6 THEN 'Jun'
WHEN t2005.monthNo = 7 THEN 'Jul'
WHEN t2005.monthNo = 8 THEN 'Aug'
WHEN t2005.monthNo = 9 THEN 'Sep'
WHEN t2005.monthNo = 10 THEN 'Oct'
WHEN t2005.monthNo = 11 THEN 'Nov'
WHEN t2005.monthNo = 12 THEN 'Dec'
END AS monthShort,
t2005.value1,
t2006.value1,
t2005.value2,
t2006.value2 ...

That what you need?

-Josh

P.S. Note that I used a LEFT OUTER JOIN with the first year's data on
the left. If you do an inner year, and your current year's data stops
at May, you will only see 5 months in your result set instead of 12.

[quoted text, click to view]

Re: Table Question multiple datasets

TomT
8/12/2006 7:39:01 AM

Can you union the underlying sources of the data to come up with one dataset?

[quoted text, click to view]

Re: Table Question multiple datasets

Josh
8/12/2006 8:38:08 AM

Unions "stack" tables vertically, while JOINS "stack" tables
horizontally. He wants to put 2005 values next to 2006, so he has to
join them. If he was trying to get a grand total of value1 across all
years, then a Union would be appropriate.

-Josh

[quoted text, click to view]

Re: Table Question multiple datasets

TomT
8/12/2006 1:56:01 PM

what about using a union and pivoting the results?

[quoted text, click to view]

Re: Table Question multiple datasets

Josh
8/13/2006 9:38:26 PM

TomT,

There is almost always more than one way of doing things. I am not
familiar with the approach that you suggest, but it might very well be
another way of solving this problem.

My personal preference is to do as much as I can in SQL. When the
output from a query is ready to be put into a report as-is, it is a
beautiful thing.

Marc, let us know if this issue is resolved. Best wishes.

-Josh

[quoted text, click to view]

Re: Table Question multiple datasets

TomT
8/13/2006 9:58:01 PM

Josh,

I've done things similar to this case, using PIVOT (new T-SQL operator in
SQL 2005) on sql statements, e.g. in this case using a union. I believe in
this case, he would pivot on the month/year.

Or, he could use a matrix in the report, and pivot the data there....

As you say, many was to solve problems,

TomT

[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 in the sql server reporting services group.