0

I have two datasets and I'm using Lookup to get one result, but the total is only from one dataset. I've tried and tried, but I'm having no luck.

First Dataset is called MedCond

This is the Data:

  • Drug_Name
  • Start_Date
  • Stop_Date
  • InmateID
  • Drug_Indication
  • Created
  • ID

Second Dataset is called ProblemList

This is the Data:

  • Medical_Condition
  • Inmate_ID
  • Created
  • ID

Drug Indication and Medical Condition are the same. I want to get a combined total of both.

This only gives me the count of the Drug Indications (which I have them grouped on) =Count(Lookup(Fields!Drug_Indication.Value,Fields!Medical_Condition.Value,Fields!Medical_Condition.Value, "ProblemList"))

I feel like I've tried everything under the sun. I'm extremely exasperated. I'm trying to get a total of each of the conditions/Indications that come from each dataset. For instance, One condition/Indication might be Addiction. There may be four addictions in from the Drug_Indication in the MedCon dataset and five addictions from the Medical_Condition in the ProblemList. I want to see Addictions 9 on the table and so and so forth for each Drug Indication/Medical Condition.

Thanks in advance for your help! Save my sanity. :) I'm sure it's probably something simple?

Tara

Thank you. I've tried using the Inmate_ID and InmateID as the key to join, but I still end up with only one of counts of either Medical_Condition or Drug_Indication.

As an example, there might be 10 addictions in one and 15 addictions in the other. I need them to be grouped under the title addiction (and whatever other titles there might be) with the total being 25. It would look something like this.

Example Look

Something like this is close, but the counts aren't quite right. =Count(Lookup(Fields!InmateID.Value, Fields!Inmate_ID.Value, Fields!Medical_Condition.Value, "ProblemList")) + Count(Fields!Drug_Indication.Value)

Maybe it's the way I'm grouping? How do you group on a combination of values such as Medical_condition and Drug_Indication?

Thanks again! Tara

TYOzzy
  • 5
  • 1
  • 6

1 Answers1

0

I think you used the Lookup() wrong. When I look at your two datasets (for me) the key to join the two datasets would be Inmate_ID. 

=Lookup(Fields!InmateID.Value, Fields!Inmae_ID.Value, Fields!Medical_Condition.Value, "SecondDatasetName")

This would result in a table like this (The last column comes form the lookup above):

Drug_Name | Start_Date | Stop_Date | InmateID | Drug_Indication | Created | ID | Medical_Condition

Now you can just get the total per column:

Drug_Name | Start_Date | Stop_Date | InmateID | Drug_Indication | Created | ID | Medical_Condition

                                                    Total1                           Total2

To sum Total1 and Total2 you can add a new tablix and reference to the textbox totals like this:

=ReportItems!Total1TextboxName.Value + ReportItems!Total2TextboxName.Value
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • The question was updated. You may have further input. – niton Apr 18 '19 at 17:57
  • There may be data in the InmateID that is not in Inmate_ID. How does lookup handle that? I basically just want totals from the Drug_Indication and the Medical_Condition. I'm not concerned if they have the same Inmate_ID or InmateID. If the Drug_Indication data has 10 addictions in it and the Medical_Condition has 20 addictions, I'd like for the row column to say the total Addictions 30. – TYOzzy Apr 19 '19 at 17:17
  • In this case, take a look at this answer: https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/36143175#36143175 – Strawberryshrub Apr 23 '19 at 04:36
  • Unfortunately the counts still aren't right. I don't get why it's so hard to add these two together. I'm definitely open for suggestions. I'm at my wits end. – TYOzzy Apr 24 '19 at 21:26