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
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
sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.
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
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.TextRuns’ contains an error: [BC30456] 'SumOccurrences' is not a member of 'ReportExprHostImpl.CustomCodeProxy'."
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.
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
Mary Diabetes Cortisone
- Edited by DaveDVF Thursday, August 29, 2013 9:37 PM error
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.
CASE WHEN ProblemDescription IS NULL THEN 0 ELSE 1 as ProblemCount,
CASE WHEN MedicationName IS NULL THEN 0 ELSE 1 as MedicationCount
I would then do a simple sum of the counts in the SSRS group footer.