none
Question: Two Sets of Data, One Chart

    Question

  • Can you do this in Reporting Services... Can you have two sets of data inserted into a single chart? In Excel you can do this but I am unable to find a way in RS.

    What I am doing is displaying data from our Internet servers. Transactions are grouped by how many seconds they take. That is one set; then I want to graph how many user web hits there were. It's apples and oranges for a single chart but it makes sense to have a visual on performance versus actual use. This is not the same as creating more than one series.

    Thanks!
    Tuesday, September 15, 2009 10:23 PM

Answers

  • Which version of SSRS are you running in case we need to continue the thread further?

    You can only have one dataset connected to a chart.  You will need to join the results in your query. 
    Program Manager, SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/seanboon http://www.twitter.com/ssrs
    Tuesday, September 15, 2009 11:24 PM
  • Mwokee,
    In SSRS, one data region (List, Table, Matrix, Chart etc) can be only bounded to one dataset. To join the two sets of data into one, you can utilize LEFT JOIN, RIGHT JOIN, JOIN or even CROSS JOIN in your T-SQL query. However you need to first check if the combined output is correct or proper for being used in your report. For example, if you have a table A with 2 rows and a table B with 3 rows. Their definitions are as following:
    A
    =====
    AID
    ANAME

    B
    =====
    BID
    BNAME
    AID

    If you want to keep the rows align with B's, you can use the following T-SQL:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A RIGHT JOIN B ON A.AID = B.AID
    This will return 3 rows according to B's even if there are no matched AID rows in A.

    Similarly you can use LEFT JOIN to change the above query to retrieve 2 rows according to A's.

    If you want to get the records with same AID in both A and B, you can use inner join like:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A JOIN B ON A.AID = B.AID
    The result is to be determined according to how many same AIDs between A and B.

    If you want to get a cross join result, 3*2=6 rows, you can run the following:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A, B

    You can first test the query result in SSMS to see if they are validated for your chart report. If not workable, I suggest that you produce two chart reports since currently Multiple y-axis are not supported through the built-in charts in Reporting Services 2005. You may also refer to this thread:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a08bcd20-d151-4694-aff3-c3e143cdd2b5

    Thx.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, September 17, 2009 4:03 AM
    Moderator

All replies

  • If possible, the best way to do this is to return both resultsets in a single dataset, which can usually be done in T-SQL. If you post some same SQL, I might be able to help you do that.
    Tuesday, September 15, 2009 10:26 PM
  • How about this:

    X-Axis is Jan, Feb, Mar, Apr, etc.

    Y-Axis is 0-100% for one set of data, the second set of data ranges from 100 to 1,000,000.

    So you'd have something like Field1, Field2, Field3, Field4 which are percentages that total 100% and then you have HitCount which can be 100 to 1,000,000.

    How do you get that to work in a RS chart?
    Tuesday, September 15, 2009 11:15 PM
  • Which version of SSRS are you running in case we need to continue the thread further?

    You can only have one dataset connected to a chart.  You will need to join the results in your query. 
    Program Manager, SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/seanboon http://www.twitter.com/ssrs
    Tuesday, September 15, 2009 11:24 PM
  • It looks like we have RS 2005. Can you provide a demo of how to JOIN the two sets of data? Why not just Field1, Field2, Field3, HitCount as one set of results?
    Tuesday, September 15, 2009 11:35 PM
  • Mwokee,
    In SSRS, one data region (List, Table, Matrix, Chart etc) can be only bounded to one dataset. To join the two sets of data into one, you can utilize LEFT JOIN, RIGHT JOIN, JOIN or even CROSS JOIN in your T-SQL query. However you need to first check if the combined output is correct or proper for being used in your report. For example, if you have a table A with 2 rows and a table B with 3 rows. Their definitions are as following:
    A
    =====
    AID
    ANAME

    B
    =====
    BID
    BNAME
    AID

    If you want to keep the rows align with B's, you can use the following T-SQL:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A RIGHT JOIN B ON A.AID = B.AID
    This will return 3 rows according to B's even if there are no matched AID rows in A.

    Similarly you can use LEFT JOIN to change the above query to retrieve 2 rows according to A's.

    If you want to get the records with same AID in both A and B, you can use inner join like:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A JOIN B ON A.AID = B.AID
    The result is to be determined according to how many same AIDs between A and B.

    If you want to get a cross join result, 3*2=6 rows, you can run the following:
    SELECT A.AID, A.ANAME, B.BID, B.BNAME FROM A, B

    You can first test the query result in SSMS to see if they are validated for your chart report. If not workable, I suggest that you produce two chart reports since currently Multiple y-axis are not supported through the built-in charts in Reporting Services 2005. You may also refer to this thread:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a08bcd20-d151-4694-aff3-c3e143cdd2b5

    Thx.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, September 17, 2009 4:03 AM
    Moderator