none
Unique Values from a Column in a Dataset

    Question

  • Hi,

    I have a dataset that contains assignments for resources and their capacity. I got capacity from another table in the dataset. This was to try and end up with one dataset for the report as I needed capacity information on the tablix also to get resource capacity and utilisation information on one report.

    The issue is that there can be multiple assignments per day and the query returns more than one record for capacity also hence duplicating capacity!

    I tried creating two datasets (capacity and assignments) and used Lookup function, but no difference as the lookup looks at each row in the assignment also and vice versa.

    Is there anyway to get unique capacity values per day and associate it with assignments without duplication?

    Thanks as always!

    SJ

    Friday, July 19, 2013 2:34 PM

Answers

  • When you combine tables, each row should be unique.  So in your sample data above, I see two duplicate lines for Project A--is this a mistake?  Otherwise, if they are identical then this means you need to join these two tables on additional fields.  For example, you may be joining these tables on too broad of an index. 

    To show this in your report the way that you want it, all that you need to do is the following.  If this is always going to be just 4 weeks long, I recommend using a table over a matrix.  A table is a pre-defined, static storage place for data.

    1. Add a parent row group on resource name
    2. Add a child row group beneath this on CAP/Utilization.  (I don't see a Utilization column above).
    3. Add another parent row group on Project
    4. The remaining capacity should be included in this last parent row group if the Project is NULL.
    5. Add all of your columns listed above.

    This should do it!


    Ryan D

    Saturday, July 20, 2013 3:40 PM
  • Thanks Ryan.

    That didn't work for me. I ended up creating a union between two sets of data and got what I needed.

    Thank you,

    SJ

    • Marked as answer by SJ_EPM Tuesday, July 23, 2013 8:37 PM
    Tuesday, July 23, 2013 10:42 AM

All replies

  • You should combine resources and capacity into one dataset.  Then in your tablix, create a row grouping on assignments.  Add the capacity field to this row and it should display capacity/assignment.  You don't need the Lookup function in this case.

    Also, could you post your table structure and datatypes? 

    And post example data of what it looks like?


    Ryan D

    Saturday, July 20, 2013 12:30 AM
  • Hi Ryan,

    Thanks for your response.

    I may not have been clear. I did mention that I have assignment table and capacity table combined already.

    Example below:

    Table A

    RES_UID   RES_ NAME   Capacity_Hours   Time_by_Day

    Table B

    RES_UID   RES_NAME   Assignment_Hours   Time by Day Project_Name

    Combined:

    RES_UID Capacity_Hours  Assignment_Hours TimebyDay Project_Name   RES_NAME

    RES_ABC       8                        4                              2013-07-01   Project A        ABC

    RES_ABC       8                        4                              2013-07-01   Project A        ABC

    RES_ABC       8                        2                              2013-07-01   Project B        ABC

    RES_DEF       7.5                      7.5                          2013-07-02    Project B        DEF

    What I need to show in my SSRS report:

                                                                            By Month

    Resource Name     CAP/Utilisation               WK 1           WK 2             WK 3             WK 4

    ABC                             Capacity                       40                 40                 40                  40

                                      Utilisation (if any)

                                                      Project A    8                    16                20                  20

                                                      Project B   24                   32                20                  20

                                      Remaining Capacity       8                    (8)                0                    0

    Right now it is showing capacity of 56 hours for Week 1 for RES_ABC due to more than one assignment allocated in a day - which is understandable, but I need to get 40 hours in the report and not 56 hours...

      



    • Edited by SJ_EPM Saturday, July 20, 2013 10:48 AM added more comments
    Saturday, July 20, 2013 8:11 AM
  • When you combine tables, each row should be unique.  So in your sample data above, I see two duplicate lines for Project A--is this a mistake?  Otherwise, if they are identical then this means you need to join these two tables on additional fields.  For example, you may be joining these tables on too broad of an index. 

    To show this in your report the way that you want it, all that you need to do is the following.  If this is always going to be just 4 weeks long, I recommend using a table over a matrix.  A table is a pre-defined, static storage place for data.

    1. Add a parent row group on resource name
    2. Add a child row group beneath this on CAP/Utilization.  (I don't see a Utilization column above).
    3. Add another parent row group on Project
    4. The remaining capacity should be included in this last parent row group if the Project is NULL.
    5. Add all of your columns listed above.

    This should do it!


    Ryan D

    Saturday, July 20, 2013 3:40 PM
  • Hi Ryan,

    Thanks for your prompt response. Capacity, Utilisation and remaining capacity are all in one column.

    I will try the above and let you know how I go.

    Thanks again!

    SJ

    • Marked as answer by SJ_EPM Tuesday, July 23, 2013 8:37 PM
    • Unmarked as answer by SJ_EPM Tuesday, July 23, 2013 8:37 PM
    Sunday, July 21, 2013 3:13 AM
  • Thanks Ryan.

    That didn't work for me. I ended up creating a union between two sets of data and got what I needed.

    Thank you,

    SJ

    • Marked as answer by SJ_EPM Tuesday, July 23, 2013 8:37 PM
    Tuesday, July 23, 2013 10:42 AM
  • Glad to hear you have a working solution!

    Ryan D

    Tuesday, July 23, 2013 12:10 PM
  • Thanks Ryan for all your help.

    I can use your suggestion for another type of reporting solution though.

    Thanks again!

    Shazia

    Tuesday, July 23, 2013 8:36 PM
  • Alright, what do you have?  Can you post a new question so that I can possibly receive another Answer for my profile?

    Ryan D

    Tuesday, July 23, 2013 9:15 PM
  • Sorry about the delayed response. I will have few questions...not too worry :-)

    I am starting on another project and will have interesting requirements. Sorry...I am a bit new to SSRS, but your help has been greatly appreciated.

    Kind regards,

    SJ

    Monday, August 19, 2013 10:57 AM