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

SUM based row

hellyson · 2016-04-20T14:25:22+00:00

Hello friends, I have the following table in my DB: Site | License =============== Brazil 10 Brazil 15 Mexico 1 Brazil 1 Mexico 50 …

hellyson

<p>Hello friends,</p>
<p> </p>
<p>I have the following table in my DB:</p>
<p> </p>
<div>Site<span> </span>| License</div>
<div>===============</div>
<div>Brazil<span> </span>10</div>
<div>Brazil<span> </span>15</div>
<div>Mexico<span> </span>1</div>
<div>Brazil<span> </span>1</div>
<div>Mexico<span> </span>50</div>
<div>Brazil<span> </span>10</div>
<div> </div>
<p>I would like to create the following report with the table that sum the values based on location:</p>
<p> </p>
<div>Site<span> </span>| License</div>
<div>
<div>===============</div>
<div>Brazil<span> </span>36</div>
</div>
<div>Mexico<span> </span>51</div>
<p> </p>
<p>Is it possible?</p>

Find more posts tagged with

Comments

jfranken

<p>Try using the following query (replace "tablename" in the query with the name of the table in your database):</p>
<p> </p>
<p>Select Site, SUM(License) as License</p>
<p>from tablename</p>
<p>group by Site </p>

hellyson

<blockquote class="ipsBlockquote" data-author="jfranken" data-cid="143387" data-time="1461177931">
<div>
<p>Try using the following query (replace "tablename" in the query with the name of the table in your database):</p>
<p> </p>
<p>Select Site, SUM(License) as License</p>
<p>from tablename</p>
<p>group by Site </p>
</div>
</blockquote>
<p> </p>
<p>jfranken, I have a "problem", I use the BIRT with a specific tool (BMC ITSM) and my dataset is a conection directly in the db table, in these case, I cannot use sql.</p>
<p> </p>
<p>My unique option is using these connections</p>

jfranken

<p>If you can make a data set with all of the rows from the table, use that data set to create a table on the report. When you select the table, you will see a tab labeled "Groups". Select that tab and add a new Group. Choose the Site column to group on in the group editor. A group will be created on the table. On the palette, find the Aggregation element and drag it to the report to add an aggregation. Choose "SUM" as the type and choose the License field to aggregate on. That will give you the numeric sums you need. Then just format the table to display the Site and the aggregated sum for each group. </p>
<p> </p>
<p>Regards,</p>
<p>Jeff</p>

jfranken

<p>Attached is a sample report based on the Classic Models database. I turned off the visibility of the detail row so what you are seeing when you run the report is the grouped and aggregated data. </p>

hellyson

<blockquote class="ipsBlockquote" data-author="jfranken" data-cid="143389" data-time="1461191237">
<div>
<p>If you can make a data set with all of the rows from the table, use that data set to create a table on the report. When you select the table, you will see a tab labeled "Groups". Select that tab and add a new Group. Choose the Site column to group on in the group editor. A group will be created on the table. On the palette, find the Aggregation element and drag it to the report to add an aggregation. Choose "SUM" as the type and choose the License field to aggregate on. That will give you the numeric sums you need. Then just format the table to display the Site and the aggregated sum for each group. </p>
<p> </p>
<p>Regards,</p>
<p>Jeff</p>
</div>
</blockquote>
<p> </p>
<p>Hi Jeff,</p>
<p> </p>
<p>It worked perfectly, thank you</p>