none
Using a Variable to count rows within an array variable

    Question

  • Greetings,

    I have a 'newbie' question for getting a count on My SSRS 2012 Report.

    I use the logic from following variable to get an array for a couple of my report columns (Meds and Problems) which it displays just great:

    = Join (LookUpSet (Fields!PATIENT.Value, Fields!PATIENT.Value, Fields!MEDS.Value, "DataSet1"), ",")

     and then a second variable...

    = Join (LookUpSet (Fields!PATIENT.Value, Fields!PATIENT.Value, Fields!Problems.Value, "DataSet1"), ",")

    PATIENT              PROBLEMS                                             MEDS

    John                   Short of Breath, Hard of Hearing,              Albuterol, Aspirin, Tylenol

                              Heart Failure

    Mary                  Diabetes                                                 Cortisone, Insulin

    I need to add 2 more variables (report columns)...  one variable to count the problems for each distinct patient and the other to count the meds for each distinct patient.

    Can I use the variable that currently presents an array (Problems and Meds) to somehow get the count of their contents?

    Ultimatelly I am going to summarize these counts to develop a risk score. But for now, I am wondering how I can get a count per patient of there problems and then of their meds. 

    Thanks for your help

    Wednesday, August 28, 2013 4:24 PM

All replies

  • Hi,

    Try this link http://social.technet.microsoft.com/wiki/contents/articles/17703.counting-occurrences-in-a-concatenated-string.aspx


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Wednesday, August 28, 2013 6:02 PM
  • Ok...thanks much. I may be in over my head a bit though.

    Here what I have done. I created a function with this code:

    Function SumOccurrence(s As String, f As String) As Integer

    Dim rtn As Integer = 0

    Dim occ As String() = s.Split(",")

    For Each el As String in occ

    If el = f Then

    rtn = rtn + 1

    End If

    Next

    Return rtn

    End Function

    Then I added a new column in my SSRS Report (TextBox7) and added the following expression to that column:

    =Code.SumOccurrences(join(Lookupset(Fields!PATIENT.Value, Fields!PATIENT.Value, Fields!MEDICATION_NAME.Value, "DataSet1"),","), "MEDICATION_NAME")

    When I attempt to run I get a preview failed message:

     "The Value expression for the textrun ‘Textbox7.Paragraphs[0].TextRuns[0]’ contains an error: [BC30456] 'SumOccurrences' is not a member of 'ReportExprHostImpl.CustomCodeProxy'."

    Wednesday, August 28, 2013 7:11 PM
  • Hi DaveDVF,
     
    According to your description, you want to add two variables, one variable is used to count the problems of each distinct patient and the other is used to count the meds for each distinct patient. Just as you post, in order to achieve the goal, we can create a function in custom code and then use it in an expression.
     
    As the error message, we can infer that there are something wrong with the expression. Compare the two functions name, we can find that there is an extra "s" after SumOccurrence in the expression.
     
    Besides, the function which you created in custom code is used to calculate the number of later string which appears in previous string. So you can’t achieve your goal in this function. After testing in my own environment, you can refer to the following code instead your code:
    Function SumOccurrence(s As String) As Integer 
    Dim rtn As Integer = 0 
    Dim occ As String() = s.Split(",") 
    For Each el As String in occ 
    rtn = rtn + 1 
    Next 
    Return rtn
    End Function

    And then modify your expression to like this:
    =Code.SumOccurrence(join(Lookupset(Fields!PATIENT.Value, Fields!PATINET.Value, Fields!MEDICATION_NAME.Value, "DataSet1"),","))
     
    If you have any other questions, please feel free to ask.
     
    Thanks,
    Katherine Xiong
    Thursday, August 29, 2013 2:01 PM
  • It worked! So awesome! Thanks for taking the time to test in your environment and make this work for me. I have learned much and I am most grateful! The only problem now is that my scores are the same and both count variables (one for meds and one for problems) and appear to reflect the largest row count per patient. Example 

    PATIENT              PROBLEMS                                             MEDS                                                     PROBLEM COUNT              MED COUNT

    John                   Short of Breath, Hard of Hearing,              Albuterol, Aspirin, Tylenol                                  4                                 4

                              Heart Failure, COPD

    Mary                  Diabetes                                                 Cortisone, Insulin                                               2                                  2

    I think this is happening because the underlying query presents the data in this fashion (below ...nulls). It must be including the 'nulls' in its row count per patient regarless of my variable code calling for MEDS or Problems.  The quest for unique counts continues to challenge me! 

    Patient          Problem                   MEDICATION_NAME

    John              Short of Breath         Albuterol

    John              Hard of Hearing        Aspirin

    John              Heart Failure            Tylenol

    John              COPD

    Mary              Diabetes                 Cortisone

    Mary                                           Insulin                                        


    • Edited by DaveDVF Thursday, August 29, 2013 9:37 PM error
    Thursday, August 29, 2013 9:33 PM
  • Is it possible to edit your underlying query?  I've used custom code in SSRS reports and it can be fraught with pitfalls.  I prefer to avoid the custom code wherever possible. 

    This is how I would approach it.

    SELECT
    PatientName,
    ProblemDescription,
    MedicationName,
    CASE WHEN ProblemDescription IS NULL THEN 0 ELSE 1 as ProblemCount,
    CASE WHEN MedicationName IS NULL THEN 0 ELSE 1 as MedicationCount
    FROM Table1

    I would then do a simple sum of the counts in the SSRS group footer.


    Martina White

    Monday, September 23, 2013 10:52 PM