locked
Only showing labels based on a limited list of departments and hide the other ones. RRS feed

  • Question

  • Hi,

    I want to show a graph with departments and events. There are about 50 departments. I want to make a sorted list of all departments but only showing labels from a other dataset. so there are two datasets. In my opinion i've to iterate over the other dataset and check whether the departments is equal and then show it else mhide it.

     

    Could some one give me a clue?

     

    Hennie

    Thursday, June 16, 2011 7:51 AM

Answers

  • Hi Hennie7863,

    This requirement is impossible if you are using SQL Server 2008, one possilbe workaround is to achieve this requirement in MDX just like you mentioned.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Hennie7863 Wednesday, June 29, 2011 1:27 PM
    Wednesday, June 29, 2011 7:33 AM

All replies

  • Hi Hennie,

    Thanks for your question, However, you could not display different dataset’s records on one chart prior to SSRS 2008 R2, if you are using SSRS 2008 R2, You could also make use of LookUp function in SSRS 2008 R2 to merge different datasets into one dataset based on the same field. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region.

    After retrieve the label field in one dataset, then to chart's label field, you can use an expression such as: =IIF(Fields!Category.value=Fields!Label.value, Fields!Label.value, nothing) Note: Category is your original filed in Category area, Label is the field to show on the chart's X-Axis.

    For more information, please see Lookup Function (Report Builder 3.0 and SSRS): http://msdn.microsoft.com/en-us/library/ee210531.aspx

    If you have any question, please feel free to ask.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Rakesh M J Friday, June 17, 2011 3:30 AM
    Friday, June 17, 2011 3:06 AM
  • Hi Challen Fu


    Thanx for your answer. First, We are using SQL Server 2008. Seems that we can't use the lookup function. Are there other options?

     

    Hennie

    Monday, June 20, 2011 11:02 AM
  • Hi Hennie,

    We can only show one dataset's fields on one data region prior to SSRS 2008 R2 version, this is by design. One possible workaround is that you can change your T-SQL query in database to join these two dataset into one dataset, if you have any question about T-SQL, welcom to T-SQL forum at this link http://social.technet.microsoft.com/Forums/en-US/transactsql/threads to receive more specific support.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 21, 2011 3:20 AM
  • I don't want to show multiple datasets in a data region. i just want to use a lookup to another dataset. Based on the dataset that is looked up,  i want to build a expression that checks whether the department is present in the other dataset. Are we talking about the same thing here?

    Using MDX here! If it's not possible i have to build a MDX query that would have an indicator show/not show based on a subquery (TSQL terminology). i've to investigate this.

    Greetz,

    Hennie

    Tuesday, June 21, 2011 7:59 AM
  • Hi Hennie7863,

    This requirement is impossible if you are using SQL Server 2008, one possilbe workaround is to achieve this requirement in MDX just like you mentioned.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Hennie7863 Wednesday, June 29, 2011 1:27 PM
    Wednesday, June 29, 2011 7:33 AM