1 Reply Latest reply on Oct 30, 2015 1:31 AM by user19752

    Cross-Tab Reporting - Data is repeating

    jakebutt14

      I am trying to create a cross-tab report that sorts on a field which is the concatenated IDs of several other fields. Basically, there is one main field per record that will always have data, and then up to 3 other fields which may or may not have data. Here's an example:

       

      Field1: Text field that will always be present

      Mod1: Text field that may or may not be present

      Mod2: Text field that may or may not be present

      Mod3: Text field that may or may not be present

       

      So my concatenated field which I am sorting on is a number field that would look like this:

       

      ID_Field1 & ID_Mod1 & ID_Mod2 & ID_Mod3

       

      My thought process is that if only Field1 is present, the sort field will be only the ID of the contents of Field1. But, if I have any number of modifiers, those modifier IDs will be added on to the Field1 ID, which I thought would make it a unique item in the sub summary section. For example:

       

      Record #1: Field1 = "Meeting", ID_Field1 = 5 (Sort field = 5)

      Record #2: Field1 = "Meeting", ID_Field1 = 5; Mod1 = "Morning", ID_Mod1 = 1 (Sort field = 51)

       

      Let's say that my columns are days of the week, where the data in the body of the report is simply a "Y" (for Yes) if that event is happening that day. For Sunday, Record #1 is present ("Y" for the "Meeting" row), and for Monday, Record #2 is present ("Y" for the "Meeting, Morning" row). So I would expect that a "Y" would ONLY be present for the "Meeting" row for Sunday and "Meeting, Morning" row for Monday. However, I am getting a "Y" for both rows for both days.

       

      I'm not sure where my misunderstanding lies, though I feel like it almost has to be with the way the sorting is being handled. It's strange that I'm getting the sub-summary rows for each individual combination of Field1 and Mod# fields, but that the body data is present for all rows, even if it should be specific to only one row/record.

       

      The above scenario is a very simple example of the issue that I'm having, not the exact type of data I'm trying to report, but I think that this gives a good indication of the issue I'm running into. Anyone have any ideas why I am getting this duplicate data?