Beginner in need of advice

  • 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

  • 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... 😛

  • 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...

  • 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