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

Multiple Charts with Different Data Sets

system_migrated

Hi,

When I create two diffent charts using different data sets, I select the relevant 'Use Data From' option (for each chart), but when I preview the reports, I get multiple results in each chart. What I need is one person's results per chart.

The only thing I could find was if I select 'Inherit Data from Container' in Chart 1 - which subsequently splits the results correctly. But when I add Chart 2, I can only choose the same option in 'Inherit Data from Container'. This doesn't contain the data I require and therefore, I choose the Use Data From option, but again, this produces multiple persons sales in data in one report.

Would I be easier to use one data set and somehow, SUM TOTAL the data in Chart 1 as this would be suffice for all Chart's? - How can I sum data?

When I attempted to sum the data, I was advised that the filter was invalid. I need to the sum the data based upon it's team. Is this possible?

Kind regards,
Andy

Find more posts tagged with

Comments

mwilliams

Hi Andy,

A table can only be bound to one dataSet, so the chart that's inside the table that is bound to the same dataSet is the only one that can use the container. The other would have to be filtered by the outer table's value that you're wanting to filter by. If you were able to join the dataSets and assign the fields to the charts as you want, you could use the container for both.

I'm not sure I understand what you're talking about with the SUM TOTAL thing. Is there any way you can reproduce what you're talking about with the sample database or with a flat file database and describe with it what you're wanting so that I can run the report?

Migrateduser

I now have one data set that feeds three separate charts (all sat within a table that has row and three columns).

I don't get the option to choose inherit data from container and therefore, the reports include multiple salesperson information, instead of just one per page.

Unfortunately, the database contains sensitive information and therefore I cannot provide. Although, I have attached the SQL Query:

SELECT DETAIL.ENTITY, ROUND(SUMMARY.VALUE,2), ROUND(TARGET.TARGET,2)
FROM (
SELECT OS_ENTITY.ID AS ID, CONCAT(OS_ENTITY.ID, " ", "-", " ", OS_ENTITY.FIRSTNAME, " ",OS_ENTITY.LASTNAME) AS ENTITY, OS_ENTITY.X_REGION AS REGION,
(SUM(OS_TRANSACTION.VALUE)+SUM(OS_TRANSACTION.X_CREDITS)+SUM(OS_TRANSACTION.X_ADJUSTMENTS)) AS VALUE
FROM OS_ENTITY, OS_TRANSACTION
WHERE OS_TRANSACTION.PERIODID = 14
AND OS_TRANSACTION.X_MONTHID = 201005
AND OS_TRANSACTION.X_MECODE = SUBSTR(OS_ENTITY.ID,1,2)
AND OS_ENTITY.ENTITYGROUPID in (3,4)
GROUP BY OS_ENTITY.X_REGION, OS_ENTITY.ID) DETAIL,

( SELECT OS_ENTITY.X_REGION AS REGION,
(SUM(OS_TRANSACTION.VALUE)+SUM(OS_TRANSACTION.X_CREDITS)+SUM(OS_TRANSACTION.X_ADJUSTMENTS)) AS VALUE
FROM OS_ENTITY, OS_TRANSACTION
WHERE OS_TRANSACTION.PERIODID = 14
AND OS_TRANSACTION.X_MONTHID = 201005
AND OS_TRANSACTION.X_MECODE = SUBSTR(OS_ENTITY.ID,1,2)
AND OS_ENTITY.ENTITYGROUPID in (3,4)
GROUP BY OS_ENTITY.X_REGION) SUMMARY,

( SELECT OS_ENTITY.X_REGION AS REGION, SUM(OS_RESULT.VALUE) AS TARGET
FROM OS_RESULT, OS_ENTITY, OS_PROCESSINGPERIOD
WHERE OS_PROCESSINGPERIOD.ID = 14
AND OS_PROCESSINGPERIOD.ID = OS_RESULT.PERIODID
AND OS_RESULT.NAME like 'Monthly%Reg%Target%'
AND OS_RESULT.NAME not like '%Part1'
AND OS_ENTITY.ENTITYGROUPID = 1
AND OS_ENTITY.ENTITYTYPE = 'Person'
AND SUBSTR(OS_RESULT.NAME, 9, 3) = SUBSTR(OS_ENTITY.X_REGION, 7, 3)
GROUP BY OS_ENTITY.X_REGION) TARGET

WHERE TARGET.REGION = DETAIL.REGION
AND SUMMARY.REGION = DETAIL.REGION;

mwilliams

Your charts are in the table that is bound to your main dataSet and you don't get an inherit data from container option? Can you by chance reproduce something similar with the sample database?

Migrateduser

Thanks,

The sample database may take some time, but for reference purposes:

PERSON TEAM PERSONAL_SALES PERSONAL_TARGET
MR A NUMBER 1 £100.00 £50.00
MR B NUMBER 1 £150.00 £100.00
MR C NUMBER 2 £200.00 £150.00
MR D NUMBER 3 £250.00 £200.00
MR E NUMBER 4 £300.00 £250.00
MR F NUMBER 5 £350.00 £300.00

The three charts are as follows:

Chart 1 = Personal Sales/Target Summary
Chart 2 = Team Sales/Target Summary
Chart 3 = Total Sales/Target Summary

Therefore, each person should have sight of their own personal sales, theire team sales and the total sales. Each person's reports should be on a single page.

Much Appreciated,
Andy

mwilliams

Andy,

Does the team sales show each person's sales that is on their team? Or just their sales towards the team total?

Same with the overall sales? Does it just show their sales compared to the overall goal? Or does it show everyone's?

Let me know. I might be able to make something up out of this small sample of data. Also, what is your BIRT version?

Migrateduser

Michael,

The team sales and targets show the full value of their team totals (not just their contribution). This is the same for the Overall Sales and Targets.

The purpose of this is to give the individuals an insight into their own performance, their team's performance and the overall performance - all of which they are rewarded against.

I believe that I am using BIRT 2.5.2 at present.

Regards,
Andy

mwilliams

Andy,

Here is a very simple example of having a chart for the total sales, team sales, and individual sales for each person on separate pages. This is using the container as the chart source. There is probably a possibility to use the actual dataSet and filters to not have to put the charts in the headers, but this may work for you. I'll work on it the other way too and post it in here if I get something working.

Migrateduser

Michael,

This is fantastic, thankyou very much!!

I have attempted to transfer this design onto my SQL query and still I cannot inherit the data from the container. Do I now need to? or can I simply use my own data set?

Regards,
Andy

mwilliams

Andy,

The way I did it above uses the container. Can you attach your report design so I can see what might be causing you to not be able to use the container?