After all my attempts i find that Cfchart actually has transpose and grouping functions built into the webcharts3d interface ARGGGGG!
Thanks for all the help Heree is an example using the XML style atribute http://www.webintegrity.us/aclsatracking/Untitled-1.cfm altho id like for the mean line to be 3 connected dots across each domain so it doesnt appear to be a trend line. On 9/18/07, Chris Terrebonne <[EMAIL PROTECTED]> wrote: > > Richard, > > A simple way to do this is to use a "Tally" table. A "Tally" table > contains one column, ID, that contains numbers from 1 to [x]. You can > then do joins against this table to do what you are looking for. Here's > an example: > > SELECT t.ID, > a.Taken, > a.assessment_ID > FROM Tally as t > LEFT OUTER JOIN assessment a on > YEAR(a.Taken) = YEAR(DATEADD(YEAR,-(t.ID - > 1),now())) > WHERE t.ID <= 3 > AND youth_id = 'X' > > That will output NULLs for years that don't exist. Let me know if you > have any questions. > > Regards, > Chris > > > -----Original Message----- > From: Richard Dillman [mailto:[EMAIL PROTECTED] > Sent: Monday, September 17, 2007 8:13 PM > To: CF-Talk > Subject: Re: Pivot table confusion > > OK maybe I need to take a step back and give you some more detail. > Or maybe I'm just not going to comprehend what you guys are telling me. > > I have a single table called assessment with these fields. > > [assessment_id] [int] IDENTITY(1,1) NOT NULL > [youth_id] [varchar](26) NOT NULL > [org_id] [varchar](26) NOT NULL > [Taken] [datetime] NOT NULL > [assessment_type] [int] NOT NULL > > Ive tried any number of answers from googling, and Ive been on this all > day > and I simply don't follow what your doing. But this is what I'm after. > > I'm only going to display 3 years so > > #Year(dateadd("yyyy",-2,now()))# Through #year(now())# > > but the part i get lost at is creating the empty cells for the values > that > Don't exist. > > -------------------2005--2006--2007--Total > ------------------------------------------ > assessment_type 1-----0-----5----10-----15 > assessment_type 2-----3-----0----11-----14 > assessment_type 3-----5-----5-----9-----19 > ------------------------------------------ > Grand Totals----------8----10----30-----48 > > > I really appreciate all the help but you would think their was an easy > way > to do this with SQL. Seems it would be something you would need rather > often. What I keep coming up with is the following table with the type > repeated if theirs more than one year. > > -------------------2005--2006--2007--Total > ------------------------------------------ > assessment_type 1-----------5----10-----15 > assessment_type 2-----3----------11-----14 > assessment_type 3-----5------------------5 > assessment_type 3-----------5------------5 > assessment_type 3-----------------9------9 > ------------------------------------------ > Grand Totals----------8----10----30-----48 > > ----Totally Confused---- > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289311 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
