Sorry, I'm having a hard time coming up with a meaningful title.
I'm trying to prepare data for a report. I use Report Builder 2.0. I have a data set which contains a Service field and a Sub Category field.
My query filters out the services I don't want and includes any records with "VPN" as the Sub Category.
AND (I.Service IN (N'Desktop - Hardware', N'Desktop - Software', N'Desktop - Property', N'User Provisioning')
OR I.Subcategory = N'VPN')
This leaves me with a data set that includes the enumerated Services PLUS all items where Sub Category ="VPN" regardless of the service. This part works.
What I have is a data set that includes the records where the Sub Category = "VPN" that have a variety of Services associated with them as well as records that match my "chosen" Services.
I want to group my results by the service OR the VPN Sub Category. My expectation is that all the values associated the "chosen" Services will be grouped by Service. This would exclude any values where the record has a "chosen" Service but has "VPN" as the SubCategory.
I created a calculated field as such:
=iif( Fields!SubCategory.Value = "VPN", "VPN",Fields!Service.Value)
I put this in the "Group by" position for a Tablix row group and it sort of works.
I get something that looks like the following:
So, how do I get all those VPNs to roll up and behave like a single service row? I want to group any records with the "VPN" Sub Category and group the rest by the "Service".
Thanks!
Tim