none
Using Lookup function in SSRS Matrix Group.

    Question

  • Can we use Lookup function in SSRS matrix Column Group and Rows group ?

    I have implemented this, its not throwing any error but displaying only first column,  tried searching online for examples, couldent find any, any help much appreciated.

    Thank You.


    Praveen

    Thursday, September 26, 2013 9:11 AM

Answers

  • Hi Praveen,

    In Reporting Services, the Lookup function returns the first matching value for the specified name from a dataset that contains name/value pairs. So in your scenario, report is displaying only first region (X)ignoring the remaining (Y and Z). And if we use Lookupset function, the result will looks like below which is not as your expect:

    In your scenario, if you want to display multiple records of another dataset on the column group, I am afraid there is no such an expression can work around this requirement currently.  The suggestion for your is that join those tables into one dataset on the query.

    Thank you for your understanding.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Monday, September 30, 2013 6:57 AM

All replies

  • Technically I see no reason why not.

    I know I have used it in Group totals for sure.

    Can you show what exactly you did and where something is missing.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, September 26, 2013 9:53 AM
  • I am trying to use Lookup function in Matrix's, its just picking up the first column ignoring the remaining. please look at below example.

    Using two datasets in a Matrix. 

    Dataset One has columns (ProductId, Productname, SalesAmount and Dataset) 
    Dataset two has (ProductId, Region, Count)


    In the matrix I am using all the columns in first dataset and for column grouping using lookup function for region
    =Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Region.Value, "Dataset2")


    For value using  below.
    =Sum(Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Region.Count, "Dataset2"))


    Expecting to see  in report (ProductId, Productname, SalesAmount,ProductId, X ,Y, Z), but report is displaying only first region ( ProductId, Productname, SalesAmount,ProductId, X )ignoring the remaining ( Y and Z ).

    Thank you for your time.


    Praveen

    Thursday, September 26, 2013 10:51 AM
  • am I understanding you right?

    The picture is the desired outcome, and NOT what you are getting?

    Just to make sure.Unfortunately I don't see the problem from here either. Tis should be possible with the Lookup-Function. As long as there is an exact match in the second dataset.

    But maybe the problem is in the details. I am not exactly sure in which columns you put which formula. Could you show that somewhat clearer


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, September 26, 2013 9:36 PM
  • Previous picture was of datasets, now enclosed the Expected Result, Actual Result & Report dev.

    I found, Lookup function is not correct to use in this senario (as Lookup will give output as x,y,z), so i have changed it to Lookupset with Split( may need further corrections to below two expressions)

    Column Group Expression :
    =Split(Lookupset(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Region.Value, "Dataset2"),",")

    Row Group Expression :
    =Sum(Split(Lookupset(Fields!productid.Value, Fields!productid.Value, Fields!countt.Value, "DataSet2"),",")

    I am not sure if SSRS suppports it, but all i want to do is use columns from second dataset(which is not the default dataset of matrix) in matrix's Column group and Row Group expression. . 

    Thank you very much for your time.


    Praveen

    Friday, September 27, 2013 8:47 AM
  • Ok, I see

    I tried to reproduce it quickly, but I am too short on time to figure it out.. sorry

    I have not seen it in an expanding group, but it could work

    you may need to use the Join function first

    maybe those links are of help:

    http://technet.microsoft.com/en-us/library/ee210450.aspx

    http://stackoverflow.com/questions/5158634/vbscript-passing-an-array-into-ssrs-vb-code

    good luck..


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, September 27, 2013 9:22 AM
  • Hi Praveen

    I think you should try other way. Let the source of the Matrix be DataSet2 (where Product Id and Region info is there). Have row group on Product Id and Column Group on Region.

    Then Use Lookup function to get the product name and total sales.

     

    Regards

    Srini

    Friday, September 27, 2013 12:41 PM
  • Hi Srini,

    I have tried that one already and it works,  but cant implement in my report, as there are about 13 datasets with complex business logic, I have to use Lookup/Lookupset function in Column Group Expression & Row Group Expression of matrix.

    Thank you.


    Praveen

    Friday, September 27, 2013 12:48 PM
  • Hi Praveen,

    In Reporting Services, the Lookup function returns the first matching value for the specified name from a dataset that contains name/value pairs. So in your scenario, report is displaying only first region (X)ignoring the remaining (Y and Z). And if we use Lookupset function, the result will looks like below which is not as your expect:

    In your scenario, if you want to display multiple records of another dataset on the column group, I am afraid there is no such an expression can work around this requirement currently.  The suggestion for your is that join those tables into one dataset on the query.

    Thank you for your understanding.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Monday, September 30, 2013 6:57 AM
  • Thank you for the replie Charlie, I will try joining the datasets or look for other alternatives.

    Praveen

    Monday, September 30, 2013 8:09 AM