Home » Archived » BIRT » Data Cube Count Distinct missing(Using count for a summary field in a data cube yields the wrong result, why is count distinct missing?)
Data Cube Count Distinct missing [message #1031941] |
Tue, 02 April 2013 11:44 |
Marius Lessiak Messages: 6 Registered: September 2012 |
Junior Member |
|
|
I have a dataset which returns rows of stories along with some data, see attached csv:
----------
storyid, date, category, views
1, 2013-01-01, news, 30
1, 2013-01-02, news, 465
..
2, 2013-01-01, sport, 65
2, 2013-01-02, sport, 423
..
3, 2013-01-01, games, 5
3, 2013-01-03, games, 54
----------
I want to analyze the data in regard to the categories and the time periods: How many views has a category per month and how many stories are published in a category in a month? So I create a data cube and add these dimensions as groups. As summary fields I pick the views (sum) and the storyid (count). See attached rptdesign.
Now everything is fine except the number of stories per category per month: Since the only option is to use COUNT as function in the summary field for storyid too many stories are counted. That happens because there are multiple rows for the same storyid containing data for different dates.
I assume a COUNTDISTINCT would work but it seems that function is not available for a cube summary field. Am i right? If yes (why oh why?) is there a way to circumevent this problem?
[Updated on: Tue, 02 April 2013 11:46] Report message to a moderator
|
|
|
Re: Data Cube Count Distinct missing [message #1032231 is a reply to message #1031941] |
Tue, 02 April 2013 18:51 |
|
You could probably create a computed column in your dataSet to keep track of this for you, then, use this in your cube. In this example, would all of the counts be 1, since no category has multiple ids?
Michael
Developer Evangelist, Silanis
|
|
| |
Re: Data Cube Count Distinct missing [message #1034582 is a reply to message #1031941] |
Fri, 05 April 2013 16:10 |
|
My thoughts were very similar to that on how I'd do the computed column. Here is the computed column code I had that works the same:
monthYear = Formatter.format(row["comp_date"], "MMYY");
temp = reportContext.getPersistentGlobalVariable(row["storyid"] + row["category"] + monthYear);
if(temp == null){
reportContext.setPersistentGlobalVariable(row["storyid"] + row["category"] + monthYear, "set");
1;
}
else{
0;
}
Then, you just use this in your cube as a summed measure.
Michael
Developer Evangelist, Silanis
[Updated on: Fri, 05 April 2013 16:12] Report message to a moderator
|
|
| | | | |
Goto Forum:
Current Time: Sun Dec 08 15:37:54 GMT 2024
Powered by FUDForum. Page generated in 0.03951 seconds
|