Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Group by a computed column
Alec
Hi All,
I am trying to group by a computed element ..
Anybody knows how to do it?
Thanks,
Find more posts tagged with
Comments
mwilliams
Do you create the computed binding in your table or in the dataSet? Either way, you should be able to add a grouping to your table and use the computed field as the one to group on.
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80937" data-time="1312302383" data-date="02 August 2011 - 09:26 AM"><p>
Do you create the computed binding in your table or in the dataSet? Either way, you should be able to add a grouping to your table and use the computed field as the one to group on.<br /></p></blockquote>
I created the computed column from the data set edit.<br />
but dont know how to group by this column?<br />
may you please give more instruction.<br />
Thanks,
mwilliams
Ok, once you've dragged the dataSet into the report to make the table, select the table, right click on the detail row tab that appears around the table, and then choose insert group. You can also select the table then go to the grouping section of the property editor to add a group. Hope this helps. Let me know if you need further instructions.
Alec
Thank you but I am still not getting the group by as i want to do..
Let me explain more to you so you may help me
I have the following Query in the data set
select mgr_num,
CASE
WHEN mgr_num = 6670 THEN 'Web Site'
WHEN mgr_num = 5261 THEN 'Drury Group'
WHEN mgr_num = 5349 THEN 'HP Group'
WHEN mgr_num = 6704 THEN 'HP Group'
WHEN mgr_num = 5728 THEN 'Cisco Group'
WHEN mgr_num = 6651 THEN 'SUN Group'
WHEN mgr_num = 6688 THEN 'IBM Group'
WHEN mgr_num = 6689 THEN 'Storage Group'
WHEN mgr_num = 6614 THEN 'Asset Recovery Group'
WHEN mgr_num = 6705 THEN 'Asset Recovery Group'
WHEN mgr_num = 6706 THEN 'Drury Group'
WHEN mgr_num = 6149 THEN 'Stezzi Group'
WHEN mgr_num = 6698 THEN 'Stezzi Group'
WHEN mgr_num = 6121 THEN 'Wing Group'
WHEN mgr_num = 6699 THEN 'Wing Group'
WHEN mgr_num = 6700 THEN 'Elan Group'
WHEN mgr_num = 6107 THEN 'Elan Group'
WHEN mgr_num = 6701 THEN 'Wade Group'
WHEN mgr_num = 6050 THEN 'Wade Group'
WHEN mgr_num = 6702 THEN 'Swanson Group'
WHEN mgr_num = 6671 THEN 'Swanson Group'
WHEN mgr_num = 6669 THEN 'Willis Group'
WHEN mgr_num = 6702 THEN 'Willis Group'
ELSE 'Unknown'
END ,CAST(sum(smn_amt) as INT)
from reports.tvsales
where mgr_num!=6614
and mgr_num!=6705
group by mgr_num
ORDER BY sum(SMN_AMT) desc
here I am grouping by the mgr_num..while i really need to group by the titles in the case above..
any ideas
mwilliams
In your BIRT design, the dataSet has the field with the text, correct? If so, doing what I said above should do what you're wanting. You'd just remove the "group by" from the query. If you would like to reproduce this with the sample database and show me what's not working for you with the grouping within BIRT, I'll try to help!
Alec
the dataSet has the field with the text??Mmm
Not sure if yo mean the computed column?!
mwilliams
If the computed column that shows the text values you want it to is in your dataSet, when you add the table to the report, you should be able to add the grouping the way I told you.
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80962" data-time="1312309326" data-date="02 August 2011 - 11:22 AM"><p>
If the computed column that shows the text values you want it to is in your dataSet, when you add the table to the report, you should be able to add the grouping the way I told you.<br /></p></blockquote>
That's true..what I am saying is that I am not getting what I want from it.<br />
if you please look at the query I posted I am selecting mgr_num and then changing the value to a titles in having a case in the query. like if the Sales_num is 6702 then Swanson Group.<br />
now I want to group these titles..the computed column didnt work. I thought if I create a computed cloumn it would pick up the new values..appearntly it is not..<br />
Do you have any solution to group by the new values.<br />
Thank you so much for your help!
mwilliams
What is your BIRT version?
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80966" data-time="1312311107" data-date="02 August 2011 - 11:51 AM"><p>
What is your BIRT version?<br /></p></blockquote>
Version: 2.5.2.v20090925-3417w31211318
mwilliams
Maybe this will help. I do the same query as you essentially and then group the table in the report by the new field.
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80969" data-time="1312312493" data-date="02 August 2011 - 12:14 PM"><p>
Maybe this will help. I do the same query as you essentially and then group the table in the report by the new field.<br /></p></blockquote>
I have tried that..it is pulling up the original values not the new values...<br />
If I group by the original values ..it wont work.<br />
like you know if I have <br />
WHEN mgr_num = 5261 THEN 'Drury Group'<br />
WHEN mgr_num = 6706 THEN 'Drury Group'<br />
so thiese are different mgr_num ..I want to group them by the new title
mwilliams
The report I sent you does exactly that. You see the customer numbers listed under each "new title"? Those are now grouped under the title. Not sure what else you'd want it to do with the grouping. Can you explain what output you'd like to see?
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80972" data-time="1312313301" data-date="02 August 2011 - 12:28 PM"><p>
The report I sent you does exactly that. You see the customer numbers listed under each "new title"? Those are now grouped under the title. Not sure what else you'd want it to do with the grouping. Can you explain what output you'd like to see?<br /></p></blockquote>
like you know if I have <br />
WHEN mgr_num = 5261 THEN 'Drury Group'<br />
WHEN mgr_num = 6706 THEN 'Drury Group'<br />
I need to see Drury Group for the last two raws and I should not see it twice.. i want to group y this value.<br />
Thanks
Alec
<blockquote class='ipsBlockquote' data-author="'Alec'" data-cid="80973" data-time="1312313469" data-date="02 August 2011 - 12:31 PM"><p>
like you know if I have <br />
WHEN mgr_num = 5261 THEN 'Drury Group'<br />
WHEN mgr_num = 6706 THEN 'Drury Group'<br />
I need to see Drury Group for the last two raws and I should not see it twice.. i want to group y this value.<br />
Thanks<br /></p></blockquote>
So far I see the the group by with Drury Group but I see it twice because the mgr_num is different 5261/6706 <br />
so I am seeing it twice..
mwilliams
Have you looked at the report I sent? I have included your same "computed column" information in the query. Drury group is applied to two different customers in the example. In the grouped table in the design, you will only see Drury group one time and the two customers that are in the group listed below it. I am not understanding what you're seeing is wrong with this. If you can explain, that would be fantastic. If you have not ran the report I sent you, please do. Thanks.
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80976" data-time="1312315713" data-date="02 August 2011 - 01:08 PM"><p>
Have you looked at the report I sent? I have included your same "computed column" information in the query. Drury group is applied to two different customers in the example. In the grouped table in the design, you will only see Drury group one time and the two customers that are in the group listed below it. I am not understanding what you're seeing is wrong with this. If you can explain, that would be fantastic. If you have not ran the report I sent you, please do. Thanks.<br /></p></blockquote>
Sorry to confuse you and thank you for your help.<br />
to give you an idea about my report.<br />
it is pulling the sales person number as every sales person has his own sales Nr.<br />
every group contain several sales persons.<br />
I am trying to build a report that shows sales by group.<br />
and show it for example as below:<br />
Drury group $ 150.000<br />
IBM Group $ 4.023<br />
and so on.<br />
doing what you told me is still pulling up the group name twice.<br />
let's assume we have sales person X from Drury group made $10.000<br />
and sales person Y from the same group made 2000.<br />
it will show as <br />
Drury group 10.000<br />
Drury group 2.000<br />
while what I needed to show is <br />
Drury Group 12.000
mwilliams
Ok. Do this. With the table grouped, drag the group name element into the group header, then delete the detail row. Now, drag an aggregation in from the palette and choose to SUM over the sales figure number for the group. This will get you exactly what you're wanting based on the data you have. Let me know if you need a better explanation.
Alec
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80979" data-time="1312317096" data-date="02 August 2011 - 01:31 PM"><p>
Ok. Do this. With the table grouped, drag the group name element into the group header, then delete the detail row. Now, drag an aggregation in from the palette and choose to SUM over the sales figure number for the group. This will get you exactly what you're wanting based on the data you have. Let me know if you need a better explanation.<br /></p></blockquote>
I probably need more explaination.<br />
What is the group header? and where to drag the aggregation in?
mwilliams
If you added a group to your table the way I told you above, a group header and footer would be added into your table automatically with the field you grouped by in the group header. You can find an aggregation element in the palette that you can drag into your table. This will all work if you've grouped your table. I'll send you the example I made earlier with the same thing done, only with the customer's credit limits.
mwilliams
Run this report and see how it's done.
