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; } } }

Aggregate cross tab data

Bharath406

Hi , I have generated a cross tab with the following structure:

| | Sev 1 | Sev 2 | Sev 3 |
| Dec 1, 2009 | 0 | 1 | 0 |
| Dec 3, 2009 | 0 | 1 | 2 |
| Dec 4, 2009 | 2 | 1 | 0 |
| Dec 6, 2009 | 2 | 1 | 0 |
| Dec 7, 2009 | 2 | 1 | 0 |
| Dec 8, 2009 | 1 | 0 | 0 |

I would like to aggregate the row data by week. So, Dec 1, 2009 to Dec 6, 2009 should be single row with a cumulative count. Something like this

| | Sev 1 | Sev 2 | Sev 3 |
| Dec 1, 2009 | 4 | 4 | 2 |
| Dec 7, 2009 | 3 | 1 | 0 |

How can I achieve this?

Thanks,
Bharath

Find more posts tagged with

Comments

mwilliams

Hi Bharath,

Is your date field a string data type or an actual date data type? If a date, you should be able to use the grouping feature of BIRT to group by week. If it's a string, you could create a computed column that changes the string date to an actual date, then figured the integer week from that and you could group on that value. Let me know if you have any questions.

Bharath406

Thanks for the reply Michael. My date field is an actual Date type. But, I dont see a way to use grouping on cross tabs. I may be missing something. Please shed some light.

Thanks,
Bharath

mwilliams

Bharath,

When you add a date as a dimension in a dataCube, it should automatically pop up a box asking what date groupings you'd like to display, i.e. year, month, day of month, day of year, etc.

When the date is displayed like in your example above, it's usually a string date. That's why I asked.

Bharath406

Ah.. Thanks Michael. It worked. 
 
One more question regarding the cross tabs. I have a joint data set which is built on a main data set and a missing date generator (<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/1006-add-missing-dates-to-chart-category-table/#description)'>Add missing dates to chart category / table - Designs & Code - BIRT Exchange</a>. When I try to build a cross tab with this joint data, I get an extra column in the cross tab with all zeroes in it. 
 
Any idea? 
 
Thanks, 
Bharath

mwilliams

Bharath,

Do you have a column dimension that the "missing date" rows don't have a value for in your dataSet, so it returns a null column with 0's? In other words, what does the data in your joint dataSet look like, and what are your column dimensions in your crosstab?

Bharath406

Michael,

Thats right. In the joint data set I have empty rows for some of the missing dates I generated. So that should be the issue then right?

As I can see , on the cross tab, I have an extra row and column with null value.
Is there a way to suppress this?

--Bharath

Bharath406

mwilliams

Bharath,

Can you post a bit of your data and let me know what you're using for your crosstab dimensions? Be sure to include some of the date rows that don't have data, so I can see what you're doing there. Thanks.

Bharath406

Michael, I have attached two files, one that shows the preview of cross tab result and the other one is the data I used to generate cross tab data. I just provided a subset of actual data. There are more records than specified there.

mwilliams

Bharath,

Both the extra row and column are caused by having null values in your dimension fields. If you're using a "count" for your measure value, you'll want to create a new computed column that assigns a value of 1 if it was an original row and a 0 if it's a computed date. You'll use this as your measure as a sum. For your date, you'll then be able to include all dates in your dimension field, so you don't have a null row. Also, for your "computed severity" column, you may need to add a dummy value of like "4-Normal" if it's a computed date, so that you don't have the null column. This won't mess up your count because of the change of the measure to sum the 1's and 0's instead of counting the values. This should work I would think if I'm understanding correctly how you're setting things up.

Bharath406

Michael,

Even if I use a dummy value in the computed severity for generated dates it still displays an extra column with that dummy severity which I feel should be suppressed.

mwilliams

Bharath,

Can you set up a sample report how you would have it set up with a larger sample of data in a csv file and attach it in here, so I can edit it and run it for testing purposes? This way I can see if I can send you what you're looking for.

Bharath406

Hi Michael,

Not sure if I'm providing exactly what you have asked for. I have attached a zip file which contains sample data and the report that I used to generate it and a screenshot of the crosstabs.

Please check it and get back to me if you want anything else.

--Bharath

mwilliams

Bharath,

I meant a rptdesign file that already runs off of the .csv file so that I can run and test on it. If you can modify your report design to work off of csv data so that I can see exactly how you're running things and be able to modify and test it myself, that'd be great! Thanks.

Bharath406

Hi Michael,

I made the change you asked for. I provided you the design file which uses a CSV data source. The zip file also contains the sample data in a csv file.

As a side point, the "Date Ranger Dataset:: DateRange" field is a String type and you'll be unable to aggregate on that by date or week which I do it on my original design file.

--Bharath

mwilliams

Bharath,

For this example, you just need to put a value in your "computed severity" column for each row. Since your joint computed count for these rows is 0, you can just put in, say, "1-Blocker" for any empty value. It will not change your crosstab measure values, but you won't get the blank column full of 0's anymore. Hope this helps.

Bharath406

Ah.. got that Michael. It worked. Thanks a lot.

Now, the initial issue I posted in this topic. Though I'm able to aggregate the cross tab data by week, it shows the week number of that month. But is there a way to display the starting date of that week, instead of the week number.

--Bharath

mwilliams

Bharath,

I don't think there's a way to just tell BIRT to display the first day of the week. You could create computed columns to make any dimension you would like, though. You could use javaScript to write an expression that figures the first day of the week for each date, make it a string and use it as your dimension. If you'd still like to do year and month above that, you could create columns in your dataSet that just return the Year and the text month and use those as well.

Hope this helps.

Bharath406

Thanks Michael. I'll try that.

--Bharath

Bharath406

Hi Michael,

A quick recap. I added missing date generation script to my report and it was showing an extra empty column in the cross tab which I eliminated by applying your logic of using an existing column name for the empty rows generated by the script.

Now another issue, when I use the same Joint data set for the Chart, it is showing a severity value(which is added to avoid the extra column in cross tab) as a legend, though there is 0 count for it. How can I avoid this.

Check the screenshot to get a clue. Please get back to me if I didn't make any sense.

Thanks,
Bharath

mwilliams

Bharath,

Can you go back to using the non-computed columns for the chart? Or does that introduce another problem of having a "null" value?

Bharath406

Yup.. If I use non-computed columns I have issues with null. In the sense, on the legend I just get an extra icon without any name(which again is not what I need :-( ).

mwilliams

Bharath,

What if you filtered out those values, uncheck the "is category axis" checkbox and just use the x-axis scale to provide every date?

Bharath406

Sorry Michael, I didn't get that?

mwilliams

Don't use the joint dataSet. Just use the one with the dates that have values. In the chart editor, deselect the checkbox for "Is Category Axis" in the x-axis section of the format chart tab. Then, at the bottom of the x-axis section, there is a button for "scale". If you click on that, you can modify the scale.

Bharath406

This did remove the duplicate legend, but it doesn't show up the complete period I selected. For example, I'm trying for New defects created from Jan 1 2010 to Feb 20 2010.

I unchecked "Is Category Axis" and in the scale I used a step size of 2 Days. But the chart shows only till Feb 3 2010, and the values from Feb 3 to Feb 20 are lost.

mwilliams

Bharath,

You ran it in the web viewer, correct? So there was not a chance that the "preview" screen just wasn't showing the data? I've not seen a chart cut off axis values. The range is set by the data in the dataSet. If anything, I've seen it extend too far.

Bharath406

Yes Michael, I did run it on the Web viewer. Please check the screenshot, I see the same issue. May be I'm missing something.