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

grouping in flat data set

koohzi

Hello.

In flat file data source list csv,
for example, the following csv file.

No JOBID

1 1111
2 1111
3 1111
4 2222
5 2222
6 3333

My goal is to create data set which group by JOBID

No JOBID JOBNUM

1 1111 3
2 1111 3
3 1111 3
4 2222 2
5 2222 2
6 3333 1

or

JOBID JOBNUM

1111 3
2222 2
3333 1

in database data source, it is possible to use "count" and "group by" cause in SQL.
is it possible to use "cumputed column" ? or another way ?

Regards,

Find more posts tagged with

Comments

mwilliams

Hi koohzi,

You could do this within a table in your report with grouping and aggregations. Would this work?

koohzi

Hi, Michael.
(I am sorry for my poor English)

thank response.

so after creating jobnum column,

JOBID JOBNUM

1111 3
2222 2
3333 1
4444 3
5555 2
6666 2

using this JOBNUM column,
I want to create the following cross-tab table

JUBNUM count

1 1
2 3
3 2

rownum is the number which group by same JOBNUM

Regards,

mwilliams

koohzi,

You shouldn't need a cross-tab to do this. You could just use a regular table, group it by the JOBNUM computed column you made, delete the detail row of the table and drop an aggregation in the group header next to JOBNUM and use the agg function of COUNT. This should show exactly what you're looking for. Let me know if you have questions.

koohzi

Hi,

sample file attached.

sample.csv flat data set. column is "jobid" and "type"
image.bmp image which I need to create
sampleflat.rptdesign sample report file(using cross tab)
finally, I want to render report like abobe image,
using regular table or cross-tab.

Regard,

mwilliams

koohzi,

What version of BIRT are you using?

cantyman

Can you please tell me what you did to create the JobNum computed column?

Specifically, what aggregation did you use when creating the JobNum computed column?

Thx

mwilliams

koohzi,

What do you want different than what you're doing in your sample report? It seems to do what you're looking for besides putting 0 in the blank cells.

koohzi

birt version is 2_5_1.

>Can you please tell me what you did to create the JobNum computed column?
>Specifically, what aggregation did you use when creating the JobNum computed column?

for now, I do not know how to create jobnum column.
probably, there is no way in standard aggregation.
jobnum is count by jobid.
(in sample.csv, 28 in jobid:1691358, 1 in jobid:1691366, 27 in jobid:1691366, and so on)

>What do you want different than what you're doing in your sample report?
>It seems to do what you're looking for besides putting 0 in the blank cells.

in sample report, jobnum is in summary field(not computed column),
but i am looking for moving jobnum(1, 27, 28 ...) is in groups
and summary field is total count by jobnum(distinct jobid) by type.

mwilliams

koohzi,

I've tried everything I can think of to achieve what you're wanting with no success. I can get a running count of the items per group in a computed column, but can't then filter the rows down to only show the max value of each group. This might be able to be achieved if a table was able to be used as a dataSource for a dataCube, but that's not possible, so it's hard to know for sure.

You might be able to use a scripted dataSource to step through your flat file and manipulate your data in the way you want it. I haven't tried this. If I think of anything else, I'll let you know.

koohzi

mwilliams, 
 
Thank you for everything. 
 
I arranged the scripted data set, and put up jobnum grouping by jobid. 
attached file is sample report. 
 
it is based on the following article. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/491-page-numbers-in-report-layout-/#description'>Page numbers in report layout. - Designs & Code - BIRT Exchange</a> 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/745-transposed-data-sets/#description'>Transposed data sets - Designs & Code - BIRT Exchange</a> 
 
(first article is one you post!) 
 
I think that it is easy if it is use SQL cause(where, group and so on) in flat data set. 
 
thanks. 
 
Regard,