Expression that compares two Datasets with different amount of rows

Question

I have two Datasets,

These two Datasets have different amount of rows.

I have two Tablix. Each Tablix point to these two different Datasets

In one column I want to compare both DataSets row by row.

So the Idea of what I want is this:

These are my two Tablix with my Datasets, as you can see Student number 10,15 and 23 does not exist on Dataset2.

When this happens I want to write a 0.

If I use a Lookup expression it will give me an error as it compares the rows that exists for each Datasets:

=IIF(Fields!Student.Value <> Lookup(Fields!Student.Value,Fields!Student.Value,Fields!Student.Value,"Dataset1"),"0","1")

That expression will give me an error.

The idea (I think) is to persist the Student 21 and compare for each student on Dataset 1 and when they are equal write 1 and 0 while they are not equal but for each row.

I cant touch the Datasets, I mean the queries.

Let me know if you need more info.

EDIT: To give you more info, what I want to do is to replicate what my code behind in my web application does in the Data Layer:

            ReportsDataSet.MyDatasetRow drSchool = (ReportsDataSet.MyDatasetRow)repDset.MyDataset.Rows[0];
            string Student = drSchool.Student;
            bool firstone = true;
            bool hayApproved = false;
            bool hayNotApproved = false;
            bool hayNeedMoreStudy = false;
            bool hayFail = false;
            bool hayG5 = false;
            bool hayNC = false;

            if (repDset.MyDataset.Rows.Count > 0)
            {
                int Count = repDset.MyDataset.Rows.Count;

                for (int i = 0; i < Count; i++)
                {
                    ReportsDataSet.MyDatasetRow dr = (ReportsDataSet.MyDatasetRow)repDset.MyDataset.Rows[i];

                    if (dr.tipo.Trim() == "TOTAL" && firstone)
                    {
                        Student = dr.Student;
                        firstone = false;

                        switch (dr.categoria.Trim())
                        {
                            case "Approved":
                                hayApproved = true;
                                break;
                            case "NotApproved":
                                hayNotApproved = true;
                                break;
                            case "NeedMoreStudy":
                                hayNeedMoreStudy = true;
                                break;
                            case "Fail":
                                hayFail = true;
                                break;
                            case "G5":
                                hayG5 = true;
                                break;
                            case "N/C":
                                hayNC = true;
                                break;
                        }
                    }
                    else
                    {
                        if (dr.tipo.Trim() == "TOTAL" && dr.Student == Student)
                        {
                            switch (dr.categoria.Trim())
                            {
                                case "Approved":
                                    hayApproved = true;
                                    break;
                                case "NotApproved":
                                    hayNotApproved = true;
                                    break;
                                case "NeedMoreStudy":
                                    hayNeedMoreStudy = true;
                                    break;
                                case "Fail":
                                    hayFail = true;
                                    break;
                                case "G5":
                                    hayG5 = true;
                                    break;
                                case "N/C":
                                    hayNC = true;
                                    break;
                            }
                        }
                    }

                    if (dr.Student != Student || i == (Count-1) )
                    {
                        if (!hayApproved)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "Approved", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayNotApproved)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "NotApproved", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayNeedMoreStudy)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "NeedMoreStudy", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayFail)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "Fail", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayG5)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "G5", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayNC)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "N/C", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        if (!hayApproved && !hayNotApproved && !hayNeedMoreStudy && !hayFail && !hayG5 && !hayNC)
                        {
                            repDset.MyDataset.AddMyDatasetRow(Student, "TOTAL", "TOTAL", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0");
                            Alreadyfilled++;
                        }

                        repDset.MyDataset.AcceptChanges();

                        hayApproved = false;
                        hayNotApproved = false;
                        hayNeedMoreStudy = false;
                        hayFail = false;
                        hayG5 = false;
                        hayNC = false;
                        firstone = true;

                        if (i != (Count - 1))
                        {
                            Student = dr.Student;
                            i--;
                        }
                    }
                }
            }

EDIT 2:

Let me give you more Info:

As you can see in the last image, Student 11, 16 etc does not exist on Dataset 2 in those cases I have to put "0". But what Im doing with Lookup is comparing 21 with 11,21 with 11 again,22 with 11 and so on. And thats not the point, the point is to put 0 for each time the Student that exists in Dataset1 does not in Dataset2.

It should be something like, I save the value of the Student in Dataset 2 and compare for each Student on Dataset1, when they are not equal, put 0 when they are equal, put 1.

A Function maybe? save all the students of Dataset1 on a list and Students of Dataset2 on another lists and compare them on a foreach inside a custom code?

Mike D. Mike D. · Accepted Answer · 2015-10-21T14:22:49

The Lookup() function returns Nothing if there is no match. It shouldn't give you an error, but you can check for nothing and return 0 in that case. I think you're just comparing against the wrong thing in your IIF expression.

=IIF(
    IsNothing(Lookup(Fields!Student.Value, Fields!Student.Value, Fields!Student.Value, "Dataset1")),
    0,
    Lookup(Fields!Student.Value, Fields!Student.Value, Fields!Student.Value, "Dataset1")
)

Unless you really want to only show a 1 when the lookup works. In which case:

=IIF(
    IsNothing(Lookup(Fields!Student.Value, Fields!Student.Value, Fields!Student.Value, "Dataset1")),
    0,
    1
)

Return

Returns a Variant, or Nothing if there is no match.

Lookup Function (Report Builder and SSRS)

Walk-through

I'm going to do this a bit differently than what exactly you're trying to accomplish so you can see what is going on with the lookup function.

First I've created two simple data sets that each contain a list of IDs and labels. The second data set is only a subset of the first (some rows are missing but all rows in DataSet2 exist in DataSet1).

DataSet1

 id  |  label
-----+-----------
  1  |  Student1-a
  2  |  Student2-a
  3  |  Student3-a
  4  |  Student4-a
...
 25  |  Student25-a

DataSet2

 id  |  label
-----+-----------
  1  |  Student1-b
  5  |  Student5-b
 10  |  Student10-b
 12  |  Student12-b
...
 25  |  Student25-b

Then I create two tablix on the report. Both of the tablix have the datasource set to DataSet1 because that contains the rows I will be checking DataSet2 for using the lookup.

I set the first tablix to just display the ID and label in the two columns. The second tablix displays the ID (from DataSet1) and uses an expression to look that ID up in DataSet2 and display a 0 if it doesn't exist and show the label from dataset2 if it does.

The expression I've used is:

=IIF(
    IsNothing(Lookup(Fields!id.Value, Fields!id.Value, Fields!label.Value, "DataSet2")),
    0,
    Lookup(Fields!id.Value, Fields!id.Value, Fields!label.Value, "DataSet2")
)

The output I get is this, notice how all of the rows appear in the second tablix (because it is fed with the dataset that contains all of the rows) but the label column shows a 0 when the row doesn't exist in dataset2.

Expression that compares two Datasets with different amount of rows

1 Answers

Return

Walk-through