1

It is basically the same question I had in this thread: Adding values to a Report when there is no Data in query SSRS

The only difference now is that I want to extend the same functionality to different Datasets.

Imagine this: I have two Datasets. Dataset1, Dataset2.

Both have the same primary key, in this case: Sales Rep Category

Now in Dataset1 I have the following Data: enter image description here

The idea in that thread was to put "0" Each time a Sales Representative Did not have all the categories, if you see for example Sales Rep on DataSet1, does not have G1,G2 Category so In those cases they have to put 0.

Thanks by the answer of the community this can be achieved by adding a Calculated Field on DataSet1:

=Fields!SalesRep.Value & "-" & Fields!Category.Value

So that will give you for example 11-G1 for the 1st Row. and the expression for each Row (For each category) will be:

=iif(IsNothing(lookup(Fields!SalesRep.Value & "-" & ReportItems!Textbox62.Value,
Fields!Another.Value,Fields!Sales.Value,"DataSet7")),0,
lookup(Fields!SalesRep.Value & "-" & ReportItems!Textbox62.Value,
Fields!Another.Value,Fields!Sales.Value,"DataSet7"))

As you can see, the ReportItems!Textbox62.Value saves the value of the Category so If 11-G1 is nothing (dont exist) put "0".

The idea here is to do the same thing with DataSet2.

The tricky part is that we have to ask if 11-G1 equals 14-G1 because in DataSet2 the SalesRep does not exist for all the categories it must put "0". Both are grouped as SalesRep.

Community
  • 1
  • 1
Nickso
  • 785
  • 1
  • 10
  • 32
  • Remember in my answer to your first question, I hardcoded the categories (G1,G2,G3 and G4) then refereced their values by using `ReportItems`. Did you do the same for `dataset 2`? – alejandro zuleta Oct 26 '15 at 14:34
  • I did the same but now we need to ask if both the Calculated fields have the same values or not. For example if the calculated field Another have the expression: `Fields!SalesRep.Value & "-" & Fields!Category.Value` I have to do the same calculated field for the new dataset. The think is in the example above is finding the same Sales Rep in this case `14-G3` but it needs to compare `11-G1` with `14-G3` – Nickso Oct 26 '15 at 14:47
  • Let me understand better, you want to find `11-G1` from `DataSet1` in `DataSet2` if it matches return the `DataSet2` `Sales` value. Right? – alejandro zuleta Oct 26 '15 at 14:51
  • Exactly, Both Datasets are grouped by Sales Rep, so for Sales Rep number 11 it should show 0 on Sales in Dataset2. – Nickso Oct 26 '15 at 14:57
  • Where you want to put the `0` value, maybe `DataSet2` Tablix? A tablix item can be linked to only one dataset, so from `DataSet2` tablix you can lookup on `DataSet1` then in that tablix put the value fetched from `DataSet1`. – alejandro zuleta Oct 26 '15 at 15:04
  • Yes, the idea is to save 0 on Dataset2 Tablix. If I make a lookup it will bring the 14-G3, Makes sense? because for both Tablix/Datasets 14-G3 exists but it needs to evaluate not the whole dataset but for each row. – Nickso Oct 26 '15 at 15:09
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93376/discussion-between-alejandro-zuleta-and-nickso). – alejandro zuleta Oct 26 '15 at 15:15

0 Answers0