AnsweredAssumed Answered

Cross-Tab Reporting - Data is repeating

Question asked by jakebutt14 on Oct 27, 2015
Latest reply on Oct 30, 2015 by user19752

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?