Grouped Data Drill Through

Larry Beasley shared this question 2 years ago
Answered

Hello! In one report I have grouped data. In another report, I have the same exact grouping. It does not seem to be working correctly. How do I make this work? What is the logic? Should I hard code a field in SQL?

Best Answer
photo

The second report I created a group the same as the first report. It appears that what is happening is the value of the group is being passed into the report filter and not the logic. For example my 1st report I have a field called program. I want to group 'Mental Health' and 'Substance Abuse' as 'MH/SA'. The second report, I have the same grouping, however, when I put the field program into the second report's filter section the operation apparently is program='MH/SA' instead of program IN('Mental Health', 'Substance Abuse') which makes sense to me because there isn't actually a 'MH/SA' record. If I create a 'MH/SA' using a case statement in the view then this works fine; which is what I went ahead and did. My case statement in my view looks like:

CASE WHEN program IN('Mental Health', 'Substance Abuse') THEN 'MH/SA' ELSE program END [program group]

Now, I can assign the grouped data field to the second report's [program group] and all works.

Comments (5)

photo
1

Hi Larry,


Can you elaborate a bit with screenshots on what exactly isn't working with the second report's grouping?


Regards,

Nathan

photo
1

The second report I created a group the same as the first report. It appears that what is happening is the value of the group is being passed into the report filter and not the logic. For example my 1st report I have a field called program. I want to group 'Mental Health' and 'Substance Abuse' as 'MH/SA'. The second report, I have the same grouping, however, when I put the field program into the second report's filter section the operation apparently is program='MH/SA' instead of program IN('Mental Health', 'Substance Abuse') which makes sense to me because there isn't actually a 'MH/SA' record. If I create a 'MH/SA' using a case statement in the view then this works fine; which is what I went ahead and did. My case statement in my view looks like:

CASE WHEN program IN('Mental Health', 'Substance Abuse') THEN 'MH/SA' ELSE program END [program group]

Now, I can assign the grouped data field to the second report's [program group] and all works.

photo
1

Hi Larry,

If you create this group at the view level you should be able to cache the resulting values:

df94e7574feda126406a4b934d297b90

I created this in 7.3 so I am not sure if this is applicable to your version, but It makes sense that a grouping defined at the report level would not work in that same report's filters.

Glad you found a workaround though, let me know if htere is anythign else I can do here!

Regards,

Nathan

photo
1

Hi Larry,


Have you had any luck in resolving this?


Regards,

Nathan

photo
1

Hi Larry,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan