Repeating IDs when multiple customers are selected.
We have a report where we display the Customer, Product, Id, Start/EndDate with some revenues on a week basis. We are facing some issues while selecting multiple customers i.e. the report displays the same IDs for the multiple customers even though the ID is not associated to that customer.
April 22nd, 2011 5:18am

What is the query?Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
Free Windows Admin Tool Kit Click here and download it now
April 22nd, 2011 7:11am

Hi, The datasource used is Oracle. The data comes properly when executed from the dataset but when report is executed we get the repeating IDs. Please find below the query: WITH Calendar AS ( SELECT DISTINCT DC.CNAME AS CustomerName, CASE WHEN IP_Productlevel = 1 THEN DP.LEVEL3NAME WHEN IP_Productlevel = 2 THEN DP.PNAME END AS ProductName, DMD.CustomerHierarchyKey, DMD.ProductHierarchyKey, DMD.Year, DMD.Month, NVL(DMD.Dealid,' ') AS DealID, CAST(DD.DealStartDate AS DATE) AS DealStartDate, CAST(DD.DealEndDate AS DATE) AS DealEndDate, DMD.FACTOR AS DealFactor , DMD.DEALPRICE AS DealPrice , (CASE WHEN SYSDATE < DD.DealStartDate AND DMD.category_flag = 2 THEN SUM(NVL(DMD.NDP,0)) WHEN SYSDATE > DD.DealEndDate AND DMD.category_flag = 1 THEN SUM(NVL(DMD.NDP,0)) WHEN SYSDATE BETWEEN DD.DealStartDate AND DD.DealEndDate THEN (CASE WHEN( CAST(DD.DealStartDate AS DATE) <= (SYSDATE-1) AND DMD.CATEGORY_FLAG = 1) THEN SUM(DMD.NDP) END + CASE WHEN (CAST(DD.DealEndDate AS DATE) >= SYSDATE AND DMD.CATEGORY_FLAG = 2) THEN SUM(DMD.NDP) END) ELSE NULL END) AS NDP, (CASE WHEN SYSDATE < DD.DealStartDate AND DMD.category_flag = 2 THEN SUM(NVL(DMD.DEALVOLUME,0)) WHEN SYSDATE > DD.DealEndDate AND DMD.category_flag = 1 THEN SUM(NVL(DMD.DEALVOLUME,0)) WHEN SYSDATE BETWEEN DD.DealStartDate AND DD.DealEndDate THEN (CASE WHEN (DMD.CATEGORY_FLAG = 1 AND CAST(DD.DealStartDate AS DATE) <= (SYSDATE-1)) THEN SUM(DMD.DEALVOLUME) END + CASE WHEN (DMD.CATEGORY_FLAG = 2 AND CAST(DD.DealEndDate AS DATE) >= SYSDATE) THEN SUM(DMD.DEALVOLUME) END) ELSE NULL END) AS DealVolume, DMD.Week As Week, CON.Description, CON.Config_Value, DD.BOGO, IP_Year AS SPYear FROM DM_Deal DMD INNER JOIN DIM_CustomerHierarchy DC ON DMD.CustomerHierarchyKey = DC.CustomerHierarchyKey AND DMD.Year = IP_Year AND ( DC.LEVEL3KEY IN (select * from table(FN_SPLIT(ip_customer))) OR DC.CustomerHierarchyKey IN (select * from table(FN_SPLIT(ip_customer)))) AND DMD.Year=DC.Year AND DC.DivisionKey = IP_Division INNER JOIN DIM_ProductHierarchy DP ON DMD.ProductHierarchyKey =DP.ProductHierarchyKey AND DMD.Year = IP_Year AND ( DP.LEVEL3KEY IN (select * from table(FN_SPLIT(ip_product))) OR DP.ProductHierarchyKey IN (select * from table(FN_SPLIT(ip_product)))) AND DMD.Year= DP.PYear AND DP.DivisionKey = IP_Division INNER JOIN DIM_Deal DD ON DMD.DealID = DD.DealNumber LEFT JOIN DIM_Configuration CON ON DD.TacticType1ID=CON.Config_ID AND CON.Config_Type='TacticType' AND DMD.Year=CON.Config_Year WHERE DMD.Category_Flag IN (1,2) AND CAST(DD.DealStartDate AS DATE) >= NVL(IP_WeekStart,YearStart) AND CAST(DD.DealEndDate AS DATE) <= NVL(IP_WeekEnd,YearEnd) GROUP BY DMD.Week, DD.DealStartDate, DMD.DealID, DD.DealEndDate, DC.CName, DP.PName , DP.Level3Name, DMD.CustomerHierarchyKey, DMD.ProductHierarchyKey, DMD.Year, DMD.Month, DMD.FACTOR , DMD.DEALPRICE, CON.Description,CON.Config_Value, DD.BOGO,DMD.category_flag ORDER BY DMD.Week, CAST(DD.DealStartDate AS DATE), CAST(DD.DealEndDate AS DATE) ) SELECT DISTINCT NVL(CustomerName,0) AS CustomerName , NVL(ProductName,0)As ProductName, NVL(CustomerHierarchyKey,0) AS CustomerHierarchyKey, NVL(C.ProductHierarchyKey,0) AS ProductHierarchyKey, NVL(C.Year,0) AS Year, DealID, DealStartDate, DealEndDate, DealFactor , DealPrice, NDP, DealVolume, NVL(dnc.FIS_Week,0) As Week, NVL(Description,0) AS Description, Config_Value, BOGO, '1' As DescriptionCount, NVL(SPYear,0) AS SPYear FROM Calendar C Right outer join DimNapCalendar DNC ON C.year = dnc.fis_year and C.month = DNC.fis_MONTHnum AND C.Week = DNC.FIS_WEEK GROUP BY DNC.FIS_Week, CustomerName, ProductName, DealStartDate, DealID, DealEndDate, CustomerHierarchyKey, ProductHierarchyKey, C.Year,SPYear, NDP,DealVolume, DealFactor , DEALPRICE, Description, Config_Value, BOGO ORDER BY Week ;
April 25th, 2011 7:53am

Hi Reports Developer, Please understanding that we mainly troubleshooting reporting services issue on this forum, the issue you have got is beyond our specialty, I suggest discussing this issue in our Transact-SQL forum: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. There are many experts focus on it, and hope you can get the issue resolved soon with helps from appropriate team. If you have any question, please feel free to ask. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2011 4:45am

Hi Eileen, I understand what you are trying to say. But the issue comes only at the report level, i.e. even when executed from the dataset we get the correct data(no repeating IDs). When the report is previewed for multiple customers the report displays the same IDs for the multiple customers even though the ID is not associated to that customer. Thanks!
April 26th, 2011 6:15am

Hi Reports Developer, I am sorry for misunderstanding. From your scenario, I suspect that you are having a parameter in the report, when you pass multiple values to the report, there will display repeat IDs, right? Please correct me if my understanding is wrong. If in that case, I think the issue is caused by the parameter in your dataset, but I cannot see the parameter in the query you have provided above. Please take the following query as a sample for your reference: Select * from [Table] where customer in (@customer) If the issue still exists, could you please supply more details about the issue, such as step by step you have taken to design the report, how to add parameters in dataset and so on. And then we could help you better. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2011 4:13am

Hi Eileen, Yes the IDs are repeating. Ya, we have passed the parameter while doing the inner join [e.g. ( DP.LEVEL3KEY IN (select * from table(FN_SPLIT(ip_product))) OR DP.ProductHierarchyKey IN (select * from table(FN_SPLIT(ip_product))))] We have created a matrix where Customer,Product and Year are displayed as columns along with rowgrouping. IDs,with startdate and some revnues are shown as rows. This report is shown across the weeks and so a column grouping on the week. when a single customer and product is selected the data comes fine. But when two customers are selected the IDs displayed for the first customer repeats for the second customer also...:( Any thoughts on this would be of great help. Thanks!
April 28th, 2011 6:25am

Hi Reports Developer, Thanks for the clarification. I still have some confusion with your scenario, could you please supply your dataset structure, and tell us which are column groups and row groups of your matrix. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 5:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics