nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Data Cubes - More than one Data Set for Summary Field?

dfeiock

Is there a way to have summary fields in a data cube come from more than one data set? I have the following table structure:
MAJOR_TABLE
MAJORGROUP, COUNT, ID
A, 2, 1

SUB_TABLE
SUBGROUP, COUNT, MAJOR_ID
A, 1, 1
B, 2, 1

Using a crosstab, I want A/A to show 1, A/B to show 2. That is easy, but the tricky part is that I want the total for majorgroup A to show 2, not the sum of A/A and A/B (which would be 3).

If I could have the data cube use a secondary dataset which only returns the majorgroup counts as the total, then it would work.

I've also tried to get all the info in a single query, but couldn't find a way to apply the logic of: return the majorgroup count if it is the first time the major_id is found in the result set, otherwise return 0.

Thanks,
- DF

Find more posts tagged with

Comments

mwilliams

DF,

Based on the data above, can you show what you would want your crosstab to look like? I'll do some testing to see if I can help you do it.

dfeiock

Michael,

Thanks for the help. Attached is a zip of a more accurate representation of what I'm actually dealing with in BIRT 2.5.1. Included is a rptdesign where I have manually created a grid to show what I'm trying to get out of a cross tab.

As for a little further explanation:
There are two metrics. Sent and Received.
Received has all the data that it needs and is straight forward.
Sent is harder. There is a MajorGroup and SubGroup. However, there is a many-to-one relationship between the data (a single MajorGroup could have multiple SubGroups related to it). Unfortunately, this is what I've got to work with.
FromSQL is an example of what my query currently looks like from the database that has all this info. The query currently uses unions since the data cube can only have a single primary data set.

As far as I can tell, there are two options:
1. Create a second data set which has the count for only the Sent-MajorGroup data. However, I could not get the data cube to use a non-primary data set for summary fields (which is the same reason that the FromSQL uses unions).
2. Figure out a way to get the Sent_MajorGroup_Count in the FromSQL data to only populate the count (4 in this case) for the first unique id (SENT_MAJORGROUP_ID), and 0 for any duplicates. I haven't had any luck figuring out a way to do this in SQL.

Hopefully this is clear enough to make sense.

Thanks a ton,
- DF