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)
Query a queried dataset in birt
ash1203
<p>Can we query a dataset in birt which is a result of a query. If yes, how ?</p>
<p> </p>
<p>For example, I have a dataset named "Data Set 1" result of query "select * from table_name". Can we query this "Data Set 1" ?</p>
Find more posts tagged with
Comments
pricher
<p>Hi,</p>
<p> </p>
<p>Can you explain a bit more what you are attempting to do? And for which version of BIRT?</p>
<p> </p>
<p>P.</p>
ash1203
<p>Hi Richer,</p>
<p> </p>
<p>I am using Eclipse Birt Plugin version 4.4.2</p>
<p> </p>
<div>Actually I am generating report from two databases (SQL Server and Oracle).</div>
<div> </div>
<div>SQL server has a table which has a column as "Model".</div>
<div> </div>
<div>Oracle DB has a table whose columns are "Dealer","Model" and "Sales" and can have duplicate Dealers. I have to get sum of Sales group by Dealer which is getable using</div>
<div> </div>
<div>[select Dealer,sum(Sales) from table_name group by dealer],</div>
<div> </div>
<div>but those Models have to be excluded which are not there in SQL Server's table. So when I am including Model in my query </div>
<div> </div>
<div>[select Dealer,Model,sum(Sales) from table_name group by Dealer,Model],</div>
<div> </div>
<div>the dealers are getting repeated as the Models are different for same Dealer. Here the Dealers have to be unique for further report work.</div>
<div> </div>
<div>Thats why I was thinking, if we can get a joint Data Set by writing simple queries for these two tables, joining them on Model, and then grouping the resulted Dataset on Dealer.</div>
<div> </div>
<div>Is this achievable ?</div>
<div> </div>
<div>Thanks,</div>
<div>Ashwini</div>
pricher
<p>Hi,</p>
<p> </p>
<p>Using a Joint Data Set, you will be able to join the two tables coming from different databases on the common field, Model. The result set from that Union Data Set will have only the dealer data where a model exists in SQL Server, if I understand correctly your situation. You can then use that result set in a table, group by Dealer and create an SUM aggregate on Sales to obtain the correct results.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
ash1203
<p>Hi Richer,</p>
<p> </p>
<p>I created a group on Dealer and SUM aggregate on Sales (TY Sales). As this grouping is only hiding the duplicate rows, not removing or omitting the rows completely, the total of Goal and LY Sales are affecting. Its calculating the sum of all the rows (repeated rows too). Please refer the below image. </p>
<div>
pricher
<p>Hi,</p>
<p> </p>
<p>I don't understand exactly what you are trying to accomplish. Can you send a sample data set from your two data sources and a mock-up of the desired output?</p>
<p> </p>
<p>P.</p>
ash1203
<p>Hi Richer,</p>
<p> </p>
<p>Thanks for your time and help.</p>
<p>Asked my vendor to keep SQL server's table to Oracle DB which solved the problem.</p>
<p> </p>
<p>But I didn't get answer to my question " is it possible to query an existing Dataset in Birt ?".</p>
<p> </p>
<p>Thanks.</p>
pricher
<p>Hi,</p>
<p> </p>
<p><span style="color:#b22222;"><em><span style="font-family:'Source Sans Pro', sans-serif;">Is it possible to query an existing Dataset in Birt ?</span></em></span></p>
<p> </p>
<p>Query as in a SQL query? No.</p>
<p> </p>
<p>However, the result sets from queries can be used as sources to a Joint Data Set (in Open Source and Commercial BIRT) or a Union Data Set (Commercial BIRT only)</p>
<p> </p>
<p>Also, Commercial BIRT gives you the possibility to create Data Objects where the result from different queries can be combined into data models. These data objects can then become the data source to your report designs.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
