Hello
I am attempting to denormalize my data, meaning have one row per Client, in Reports Studio 10.1. All Data is from a SQL Server relational database. Here is what is happening:
I have the following columns:
- [Client ID]
- [Date]
- [Dispatch Type] (containing the following: "PickUp" and "DropOff")
- [Address]
Now, what I would like to do is have one [Address] for [Dispatch Type] of "PickUp" and one [Address] for [Dispatch Type] of "DropOff" per [Client ID] and [Date]. I would like this to all be in one row. So I tried the following calculations:
[vPickUp Address]: CASE WHEN [Dispatch Type] IN ('PickUp') THEN([Address]) ELSE(NULL) END
[vDropOff Address]: CASE WHEN [Dispatch Type] IN ('PickUp') THEN([Address]) ELSE(NULL) END
When I insert these calculations into my report, it is still causing a new line whenever an [Address] is populated.
What I would like it to look like is this:
CLIENT ID DATE vPICKUP ADDRESS vDROPOFF ADDRESS
123 1/1/14 123 ne drive 881 main street
456 1/2/14 550 e toll ln 99 s first street
Instead it is returning this:
CLIENT ID DATE vPICKUP ADDRESS vDROPOFF ADDRESS
123 1/1/14 123 ne drive
123 1/1/14 881 main street
456 1/2/14 550 e toll ln
456 1/2/14 99 s first street
Any help would be GREATLY appreciated.
Thank you
Try setting the aggregate function for vPickUpAddress and vDropOffAddress to maximum.