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

filtered counts in a Cross Tab

buchenberg

I have an Event data set that returns the following:

occured_date,type,user_pk,firstname,lastname,active,fullName
1/1/2010 6:06,login,38,JOHN,DOE,Y,John Doe
1/1/2010 8:10,login,77,JOE,SCHMO,Y,Joe Schmo
1/1/2010 8:13,entityIntelAdded,77,JOE,SCHMO,Y,Joe Schmo
1/1/2010 8:16,entityIntelAdded,77,JOE,SCHMO,Y,Joe Schmo
1/1/2010 8:22,entityIntelAdded,77,JOE,SCHMO,Y,Joe Schmo
1/1/2010 10:46,login,38,JOHN,DOE,Y,John Doe

In the report, I would like to list each user (fullName) as a single row in the first column. For each user I want to show total logins. So the first record might be "John Doe, 2". While I can group by fullName and get a count based on row["type"] == "login", I cannot seem to find a way to format as a spreadsheet with each user on a single line. I think a Cross Tab is what I need but can't seem to figure it out. Any Ideas?

Find more posts tagged with

Comments

mwilliams

Hi buchenberg,

You could use a crosstab with a count measure or you could simply group your report by first and last name and put an aggregation in the group header that counts the rows with type, "login". Then you'd just delete the detail row because you're only interested in the summary information. I can make an example of both ways with your data above if you'd like. Or if you provide a larger sample to use, it might show better results.

buchenberg

Thanks for the prompt reply. I've been able to get it to work putting the aggregation in the group footer and deleting the details. I didn't think of using the header. As long as it exports like a usable spreadsheet, I'm golden. 
 
However, I would like to know how to use a COUNT in a crosstab. I didn't find a way in the designer. I can use COUNT as a measure, but didn't see where to put the filter expression. I assumed I would have to get into the XML and customize the script. Can you explain how I might use COUNT as measure filtered by the string in Event.type? When I choose COUNT in the Summary Field Properties I get a single expression field without a filter field. 
 
Unfortunately, I would have to alter any data that I posted here so a large dataset isn't practical. If you can use those few records in an example, I sure I could apply it to my larger dataset. 
 
Thanks, 
 
Gregory Buchenberger 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="70667" data-time="1290529294" data-date="23 November 2010 - 09:21 AM">
Hi buchenberg, 
 
You could use a crosstab with a count measure or you could simply group your report by first and last name and put an aggregation in the group header that counts the rows with type, "login". Then you'd just delete the detail row because you're only interested in the summary information. I can make an example of both ways with your data above if you'd like. Or if you provide a larger sample to use, it might show better results. </blockquote>

mwilliams

Gregory,

What version of BIRT are you using? In 2.6.1, a crosstab measure can be of type count and have a filter statement.

buchenberg

I'm using 2.3.1 to match the runtime that is installed on the application I'm enhancing. I've been a bit hesitant to update BIRT in the event it broke something. You think it's worth the upgrade? BTW, I was able to use the group header for my aggregates and removed the details. Thanks for the tip. Your help is greatly appreciated. I was able to get some much needed sleep :wacko: !

mwilliams

You could download the newer version, copy your workspace over and test the reports in the new version. If they all work as expected, upgrading should be painless for you. If there are issues, you'll have to consider what they are. There have been improvements from 2.3.1 to 2.6.1 though, so if you can upgrade, it might be worth it.