|
Re: Chart from multiple data sources [message #368346 is a reply to message #368339] |
Fri, 22 May 2009 14:34 |
Eclipse User |
|
|
|
Originally posted by: jasonweathersby.windstream.net
Darek,
Have you tried to create a cube off the two datasets and use a crosstab?
You can turn the crosstab into a chart by selecting the crosstab tab and
right click and select create chart view.
Jason
Dariusz Filipski wrote:
> Hi,
>
> I have to create a chart which takes data from more than one data
> source. Unfortunately I can't create a joint data source as there is a
> subquery to other data sources based on results from the first one (so
> there is no column to join the data but the result from the first query
> is a parameter to subqueries). I managed to get the data into a table
> contains results of the first query and data fields with results of
> subqueries, but I can't use this table as a base for a chart as only the
> data from the main query is accessible. Is there any way to do that in
> BIRT? What I did temporarily is I output the table to XLS and created a
> chart manually there :)
>
> Simplified description of the situation:
>
> Two data tables (each one in separate data sources, unfortunately, so I
> can't create one query which collects all data together):
>
> SNAPSHOTS
> -------------
> ID
> DATE
>
> DATA
> -----------
> ID
> SNAPSHOT_ID -> the same as ID in SNAPSHOTS
> VALUE1
> VALUE2
> ...
>
> So that's a bunch of snapshots of some environment taken e.g. daily. I
> want to compare snapshots one to another and show a chart with number of
> differences between consequent snapshots in one of VALUEx fields.
> So, I have a query which returns set of pairs of IDs of snapshots to
> compare. This list I put into a table in my report. Then I created
> subqueries counting differences between VALUE1, VALUE2 etc for given ID
> of the current and previous snapshots. Those queries (data sources) are
> connected to data fields in the same table and parameters (IDs of
> snapshots) are linked to the proper values from the table.
>
> Is there any other/better way to achieve this? How to create a chart
> from this data?
>
> Regards
> Darek
>
|
|
|
Re: Chart from multiple data sources [message #368433 is a reply to message #368346] |
Thu, 28 May 2009 20:03 |
Dariusz Filipski Messages: 10 Registered: July 2009 |
Junior Member |
|
|
Hi Jason,
Thanks for your quick response and sorry for slow the one of mine :)
> Have you tried to create a cube off the two datasets and use a crosstab?
I've spent some time trying that but I found no way to use subqueries in a
data cube. I created one data source to get pairs of snapshots to compare
(used for one dimension in the cube), one data set for each value to
compare (measures) with two parameters (id's of those snapshots to
compare). I may try to create another fake data source for column names
(second dimension). What I don't know is how to merge this together - how
to pass parameter values from the dimensions to the measures?
Is there some tutorial about those issues?
> You can turn the crosstab into a chart by selecting the crosstab tab and
> right click and select create chart view.
Yes, if I build the cube, the chart should be a piece of cake :)
Regards
Darek
> Dariusz Filipski wrote:
>> Hi,
>>
>> I have to create a chart which takes data from more than one data
>> source. Unfortunately I can't create a joint data source as there is a
>> subquery to other data sources based on results from the first one (so
>> there is no column to join the data but the result from the first query
>> is a parameter to subqueries). I managed to get the data into a table
>> contains results of the first query and data fields with results of
>> subqueries, but I can't use this table as a base for a chart as only the
>> data from the main query is accessible. Is there any way to do that in
>> BIRT? What I did temporarily is I output the table to XLS and created a
>> chart manually there :)
>>
>> Simplified description of the situation:
>>
>> Two data tables (each one in separate data sources, unfortunately, so I
>> can't create one query which collects all data together):
>>
>> SNAPSHOTS
>> -------------
>> ID
>> DATE
>>
>> DATA
>> -----------
>> ID
>> SNAPSHOT_ID -> the same as ID in SNAPSHOTS
>> VALUE1
>> VALUE2
>> ...
>>
>> So that's a bunch of snapshots of some environment taken e.g. daily. I
>> want to compare snapshots one to another and show a chart with number of
>> differences between consequent snapshots in one of VALUEx fields.
>> So, I have a query which returns set of pairs of IDs of snapshots to
>> compare. This list I put into a table in my report. Then I created
>> subqueries counting differences between VALUE1, VALUE2 etc for given ID
>> of the current and previous snapshots. Those queries (data sources) are
>> connected to data fields in the same table and parameters (IDs of
>> snapshots) are linked to the proper values from the table.
>>
>> Is there any other/better way to achieve this? How to create a chart
>> from this data?
>>
>> Regards
>> Darek
>>
|
|
|
Re: Chart from multiple data sources [message #368441 is a reply to message #368433] |
Fri, 29 May 2009 15:02 |
Eclipse User |
|
|
|
Originally posted by: jasonweathersby.windstream.net
Darek,
I do not think the parameter route will work. The cube is suppose to do
the work in this case. For example if I have orders from one datasource
and customer details from another and I make a cube with each of the
above as a dimension and choose something out of the orders table as the
measure the cube will automatically do the intersection for the measure.
If this route want work the only other solution that i can think of is
to load up JavaScript arrays on hidden tables and then the
afterDataSetFilled chart script replace the values with the ones you need.
Jason
Dariusz Filipski wrote:
> Hi Jason,
>
> Thanks for your quick response and sorry for slow the one of mine :)
>
>> Have you tried to create a cube off the two datasets and use a crosstab?
>
> I've spent some time trying that but I found no way to use subqueries in
> a data cube. I created one data source to get pairs of snapshots to
> compare (used for one dimension in the cube), one data set for each
> value to compare (measures) with two parameters (id's of those snapshots
> to compare). I may try to create another fake data source for column
> names (second dimension). What I don't know is how to merge this
> together - how to pass parameter values from the dimensions to the
> measures?
> Is there some tutorial about those issues?
>
>> You can turn the crosstab into a chart by selecting the crosstab tab
>> and right click and select create chart view.
>
> Yes, if I build the cube, the chart should be a piece of cake :)
>
> Regards
> Darek
>
>> Dariusz Filipski wrote:
>>> Hi,
>>>
>>> I have to create a chart which takes data from more than one data
>>> source. Unfortunately I can't create a joint data source as there is
>>> a subquery to other data sources based on results from the first one
>>> (so there is no column to join the data but the result from the first
>>> query is a parameter to subqueries). I managed to get the data into a
>>> table contains results of the first query and data fields with
>>> results of subqueries, but I can't use this table as a base for a
>>> chart as only the data from the main query is accessible. Is there
>>> any way to do that in BIRT? What I did temporarily is I output the
>>> table to XLS and created a chart manually there :)
>>>
>>> Simplified description of the situation:
>>>
>>> Two data tables (each one in separate data sources, unfortunately, so
>>> I can't create one query which collects all data together):
>>>
>>> SNAPSHOTS
>>> -------------
>>> ID
>>> DATE
>>>
>>> DATA
>>> -----------
>>> ID
>>> SNAPSHOT_ID -> the same as ID in SNAPSHOTS
>>> VALUE1
>>> VALUE2
>>> ...
>>>
>>> So that's a bunch of snapshots of some environment taken e.g. daily.
>>> I want to compare snapshots one to another and show a chart with
>>> number of differences between consequent snapshots in one of VALUEx
>>> fields.
>>> So, I have a query which returns set of pairs of IDs of snapshots to
>>> compare. This list I put into a table in my report. Then I created
>>> subqueries counting differences between VALUE1, VALUE2 etc for given
>>> ID of the current and previous snapshots. Those queries (data
>>> sources) are connected to data fields in the same table and
>>> parameters (IDs of snapshots) are linked to the proper values from
>>> the table.
>>>
>>> Is there any other/better way to achieve this? How to create a chart
>>> from this data?
>>>
>>> Regards
>>> Darek
>>>
>
>
|
|
|
|
Re: Chart from multiple data sources [message #368478 is a reply to message #368458] |
Tue, 02 June 2009 21:15 |
Dariusz Filipski Messages: 10 Registered: July 2009 |
Junior Member |
|
|
Hi,
>> If this route want work the only other solution that i can think of is
>> to load up JavaScript arrays on hidden tables and then the
>> afterDataSetFilled chart script replace the values with the ones you need.
> Sounds tempting. :) Even though it's a dirty solution it might work for me
> as the result table is rather small (tens of rows, not more). Is there any
> example of storing data into a JS array and then building some chart based
> on that?
I've created a sample report based on ClassicModels database to explain
the problem. It's just an example and I know that in this case it could be
done with just one query :). However my example contains one main data
set: Orders (just a list of IDs and dates for Orders, limited to 10 items
to make it simple) and two dependent data set, each with subquery with
parameter filled from the result of the main query: Quantity and Price.
Those two simulates subqueries from separate data sources I have in the
original report (with small difference - those two depend on one
parameter, original ones depend on two parameters as I compare two
snapshots in each subquery).
The result of main query is put into a table (called data_table) with
additional data field for each subquery. There is a binding between main
query results in the table row to subquery parameters in data fields. This
way I got a table with all the data I need. Now I want to build a chart
based on that (X-axis: date, multiple series: one for each subquery).
So, in initialize method of the report I created a multi-dimensional array
and a counter for rows, in onCreate method of the date_table row I copy ID
and date to this array, than finally in onCreate method of both additional
data fields I copy values counted by subqueries. To check if my array
contains proper data I added 4 dynamic text fields under the table
displaying content of the array.
Design file of this report is available at:
http://sites.google.com/site/birtsample/Home/multisource.rpt design?attredirects=0
Is it a proper way to do that? Is there any simpler/better solution? How
to build a chart based on this array?
Additionally, I tried to build some chart based on that data.
Unfortunately, even simple binding to first two columns of the table
causes that additional fields are not filled... Is it a bug in BIRT or I
do something wrong? Here is a design file which shows that problem:
http://sites.google.com/site/birtsample/Home/multisource_cha rt.rptdesign?attredirects=0
Regards
Darek
|
|
|
Re: Chart from multiple data sources [message #368486 is a reply to message #368478] |
Wed, 03 June 2009 15:34 |
Eclipse User |
|
|
|
Originally posted by: jasonweathersby.windstream.net
Darek,
Can you email me the two designs?
jasonweathersby at windstream.net
Jason
Dariusz Filipski wrote:
> Hi,
>
>>> If this route want work the only other solution that i can think of
>>> is to load up JavaScript arrays on hidden tables and then the
>>> afterDataSetFilled chart script replace the values with the ones you
>>> need.
>
>> Sounds tempting. :) Even though it's a dirty solution it might work
>> for me as the result table is rather small (tens of rows, not more).
>> Is there any example of storing data into a JS array and then building
>> some chart based on that?
>
> I've created a sample report based on ClassicModels database to explain
> the problem. It's just an example and I know that in this case it could
> be done with just one query :). However my example contains one main
> data set: Orders (just a list of IDs and dates for Orders, limited to 10
> items to make it simple) and two dependent data set, each with subquery
> with parameter filled from the result of the main query: Quantity and
> Price. Those two simulates subqueries from separate data sources I have
> in the original report (with small difference - those two depend on one
> parameter, original ones depend on two parameters as I compare two
> snapshots in each subquery).
> The result of main query is put into a table (called data_table) with
> additional data field for each subquery. There is a binding between main
> query results in the table row to subquery parameters in data fields.
> This way I got a table with all the data I need. Now I want to build a
> chart based on that (X-axis: date, multiple series: one for each subquery).
> So, in initialize method of the report I created a multi-dimensional
> array and a counter for rows, in onCreate method of the date_table row I
> copy ID and date to this array, than finally in onCreate method of both
> additional data fields I copy values counted by subqueries. To check if
> my array contains proper data I added 4 dynamic text fields under the
> table displaying content of the array.
> Design file of this report is available at:
> http://sites.google.com/site/birtsample/Home/multisource.rpt design?attredirects=0
>
>
> Is it a proper way to do that? Is there any simpler/better solution? How
> to build a chart based on this array?
>
> Additionally, I tried to build some chart based on that data.
> Unfortunately, even simple binding to first two columns of the table
> causes that additional fields are not filled... Is it a bug in BIRT or I
> do something wrong? Here is a design file which shows that problem:
> http://sites.google.com/site/birtsample/Home/multisource_cha rt.rptdesign?attredirects=0
>
>
> Regards
> Darek
>
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03243 seconds