June 25, 2008 at 8:00 am
Hi,
I'm trying to create a chart based on four queries, but I can't get it to work. As I'm new to both SQL and to RS I'm not even sure if it's possible to combine data more than one dataset in one chart (the queries in all four datasets are almost identical, they all return a number for every month this year). I could make four independant charts, but as I want to compare the data for statistic purposes that wouldn't be enough.
The scripts look like this:
select count(e.id) as count, month(e.type1_date) as type1_month
from example as e
where year(e.type1_date) = 2008
and e.type1_date is not null
group by month(e.type1_date)
order by month(e.type1_date)
select count(e.id) as count, month(e.type2_date) as type2_month
from example as e
where year(e.type2_date) = 2008
and e.type2_date is not null
group by month(e.type2_date)
order by month(e.type2_date)
etc.
The columns e.type1/2_date are set to null by default, and when the data I'm after is added, a date is added to that particular row and column
Is there any way I can use different datasets in the same chart or combine these queries into one (ie. giving me one column for each month, one with the amount of type1 for each month, one for type2 etc.)? I've tried to work out how to do that but I just can't seem to get it right.
Thankful for any advice,
Jonatan
June 26, 2008 at 8:41 am
Why are you creating different dataset.
You can create one dataset by combining all the results using UNION ALL
e.g.
select count(e.id) as count, month(e.type1_date) as type_month
from example as e
where year(e.type1_date) = 2008 and e.type1_date is not null
group by month(e.type1_date)
UNION ALL
select count(e.id) as count, month(e.type2_date) as type_month
from example as e
where year(e.type2_date) = 2008 and e.type2_date is not null
group by month(e.type2_date)
order by month(e.type2_date)
Now you need to drag and drop type_month in "Data Category" section and count in "Data Field" section.
Try it... 😛
June 27, 2008 at 1:33 am
thanks a lot, that really helped, never thought of UNION :rolleyes:
I've got a problem now though that RS is complaining about me having ORDER BY items which are not included in my select list. The weird thing is that they do and that it works perfectly fine in SQL management studio...
June 27, 2008 at 1:49 am
Hi,
You can create stored procedure of view for your result and use the same for your report.
If you create a procedure, apply order by condition inside procedure.
If you create a view, apply order by condition while you are creating DataSet
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply