Looking for ideas/tips to fill data gap. I have set up static labels for the Combo box as 2010, 2011, 2012 (requirement)
For example assume that the following is the source data
2010 Jan 20
2010 Feb 30
2010 Mar 40
2010 Apr 50
2011 Jan 30
2011 Feb 50
2011 Jun 40
I am looking for, if I select 2010, the following values (jan to Jun) are returned
Jan 20
Feb 30
Mar 40
April 50
May 0
Jun 0
When 2011 selected the data returned is
Jan 30
Feb 50
Mar 0
Apr 0
May 0
Jun 40
When 2012 selected (no source data for 2012) the data returned is
Jan 0
Feb 0
Mar 0
May 0
Jun 0
Ideally, you’d go back to your data source, passing the year out each time and grabbing just the relevant data. But this should work if you get it all in one go and tghere isn’t too much of it - it’s not going to be very efficient for hundreds of rows of data but you get the idea…
Debbie,
This solution works fine. Only problem is the number of rows returned is unknown. So the range of cells to apply the forumla need to be estimated, which may miss out some data.
That’s always going to be an issue with getting your data in this way. I always do it via an aspx and grab the last 3 years (as in sysdate-37 months) so the number of rows returned is always the same. Perhaps you could do something similar? Since you are getting one row per month/year, it shouldn’t be too hard to just grab 3 year’s worth of data or whatever.
Or - if your connections are fast enough to work in real-time - pass your year back out to the connection and only pull 12 month’s worth of data each time.
(BOB member since 2008-02-11)
(BOB member since 2005-03-01)