Merge Problem

[system]

I do have 2 pieces excel local data resources. You can take a look at atttachment.
If you look at the datas you might not understand What I wanna say.
However, I will explain what i mean. First, I do wanna merge same spaces,
and after that the revunes are written following names, months and years (in order) by themselves
also , the blank values should have seen on chart. i do wanna use vertical table. could you please help me on this topic.

I view this table;

Name------Month------Year------Revenue
John------------1------ 2009------ 1100
John------------3 ------ 2009------ 1500
John------------8 ------ 2009------ 1700
John------------1 ------ 2010------ 2100
David-----------1------ 2009------ 2000
David-----------4 ------ 2009------ 2500
David-----------8 ------ 2009------ 2850
David-----------1 ------ 2010------ 3150
Samm----------1------ 2009------ 800
Samm-----------5------2009------ 1100
Samm-----------7------2009------ 1500
Samm-----------2------2010------ 1800
Gary ------------2------2009------ 2000
Gary------------ 6------2009------ 2500
Gary ------------9------2009------ 2750
Gary ------------1------2010------ 3000

But I want to see these results;

Name------ Month------Year-------Revenue
David------ -----1-------2009-------2000
David------ -----2-------2009 -------
David------ -----3-------2009 -------
David------ -----4-------2009 -------2500
David------ -----5-------2009 -------
David------ -----6-------2009 -------
David------ -----7-------2009 -------
David------ -----8-------2009 -------2850
David------ -----9-------2009 -------
David------ -----10-------2009------
David------ -----11-------2009------
David------ -----12-------2009------
David------ -----1-------2010 -------3150
David------ -----2-------2010 -------
David------ -----3-------2010 -------
David------ -----4-------2010 -------
David------ -----5-------2010 -------
David------ -----6-------2010 -------
David------ -----7-------2010 -------
David------ -----8-------2010 -------
David------ -----9-------2010 -------
David------ -----10------2010-------
David------ -----11------2010-------
David------ -----12------2010-------
Gary------------1 -------2009-------
Gary------------2 -------2009-------2000
Gary------------3 -------2009-------
Gary------------4 -------2009 -------
Gary------------5 -------2009 -------
Gary------------6 -------2009 -------2500
Gary------------7-------2009 -------
Gary------------8 -------2009 -------
Gary------------9 -------2009 -------2750
Gary------------10------2009-------
Gary------------11------2009-------
Gary------------12------2009-------
Gary------------1 -------2010 ------3000
Gary------------2 -------2010 -------
Gary------------3 -------2010 -------
Gary------------4 -------2010 -------
Gary------------5 -------2010 -------
Gary------------6 -------2010 -------
Gary------------7 -------2010 -------
Gary------------8 -------2010 -------
Gary------------9-------2010 -------
Gary------------10------2010-------
Gary------------11------2010-------
Gary------------12------2010-------
John------------1-------2009------- 1100
John------------2-------2009-------
John------------3-------2009-------2009
John------------4-------2009-------
John------------5-------2009-------
John------------6-------2009-------
John------------7-------2009-------
John------------8-------2009-------1700
John------------9-------2009-------
John------------10------2009-------
John------------11------2009-------
John------------12------2009-------
John------------1-------2010-------2100
John------------2-------2010-------
John------------3-------2010-------
John------------4-------2010-------
John------------5-------2010-------
John------------6-------2010-------
John------------7-------2010-------
John------------8-------2010-------
John------------9-------2010-------
John------------10------2010-------
John------------11------2010-------
John------------12------2010-------
Samm----------1-------2009-------800
Samm----------2-------2009-------
Samm----------3-------2009-------
Samm----------4-------2009-------
Samm----------5-------2009-------1100
Samm----------6-------2009-------
Samm----------7-------2009-------1500
Samm----------8-------2009-------
Samm----------9-------2009-------
Samm----------10------2009-------
Samm----------11------2009-------
Samm----------12------2009-------
Samm----------1-------2010-------
Samm----------2-------2010-------1800
Samm----------3-------2010-------
Samm----------4-------2010-------
Samm----------5-------2010-------
Samm----------6-------2010-------
Samm----------7-------2010-------
Samm----------8-------2010-------
Samm----------9-------2010-------
Samm----------10------2010 -------
Samm----------11------2010 -------
Samm----------12------2010-------

Thanks for the answer…
MonthandYear.xls (17.0 KB)
Field.xls (18.0 KB)

---

YaGCi (BOB member since 2010-03-05)

[system]

BO can’t show data that’s not there. If David doesn’t have a row of data for — 1----2009 then nothing will show for that period in your report.

if your datasource is Excel as you say, and you want to see period information where David didn’t get any revenue then just create the missing lines in your excel source file.

---

SteveD (BOB member since 2009-11-02)

[system]

Of if your table was a cross-tab then you could try what’s described in this Reporter’s FAQ:

FAQ: Reporter

How can I include data for all months in a crosstab, even if a particular month has no data? One way to do this is to create a second data provider using only the months. Then link (or “merge”) the second data provider (query) with the first data provider. Use the month column from the second data provider in the cross tab. This should bring in all months even if there were no data for that month. However this technique does not work if you have sections in your report. Dave Rathb…

---

Marek Chladny (BOB member since 2003-11-27)