Multivalue Error with Detail variable
I spent number of hours trying to sort this problem out without success. I tried various solutions posted here and none of them work.
I have two queries:
query #1 (Main query) which returns :
Count of Calls Received
Date when those calls where received.
Query #2 returns:
Effective From Date
Effective To Date,
other objects in regards to personal details.
I have merged both queries on LoginID and my objective was to create a report which would list all Logins, dates when they received calles, number of calls received and location.
The problem is with the location. I created Detail variable on LoginID from my second query. And because, 'logins' change locations frequently (lets say London on Monday and Bristol on Tuesday) I get quite a lot of Multivalue Errors for logins that changed location within the reporting period.
How can I correlate Calls Received Date with the Effective From/Effective To dates and my Location variable (detail) from my staff query so that I only pull the location for each agent relevant to when they received calls and avoid multivalue errors?
So if my agent, John received 10 calls on Monday being in London and 10 calls on Tuesday being in Bristol, I would like to show it as:
agentID date location Numberofcalls
John 01.01.13 London 10
John 02.01.13 Bristol 10
John 01.01.13 Multivalue Error 10
John 02.01.13 Multivalue Error 10