4 Replies Latest reply on Jul 10, 2006 7:18 AM by LaoD

    CFChart Dynamic Column HELP

    LaoD


      I looked over the posts on CFChart but did not find anything that addresses my specific problem with the cfchart. So if you can help, I'd appreciate it very much!

      What I want to do is take the dynamic name of the column derived from the first query and use that name in the Legend. E.G. A_R1_DIS is the first dataset, A_R1_J is the second dataset, and A_R1_PPLI is the third dataset. Then show a line graph with each dataset based on the first query. The X Axis displays the Date/Time value and the Y Axis displays the number of points for each record of time for each dataset individually. I was able yesterday to get all three datasets to display individually but when I tried to show the legend it either displayed only the first Dataset name "A_R1_DIS" or it displayed "NONE". Currently the code will not display any line on the chart and the legend repeats the A_R1_DIS as many times as there are records in the table (60+).

      I use the following query to get the dynamic column name "SelectedDatasetColName" from the MasterDataList table.

      <CFQUERY name="getSelectedDatasetColName" datasource="#getcfdbname.EventCFDBName#">
      SELECT MDL_TestName + '_' + MDL_DatasetID + '_' + MDL_MeaningfulName as SelectedDatasetColName
      FROM MasterDataList
      </CFQUERY>

      Here is where I'm using the "SelectedDatasetColName" value:

      ******************************************* queries *****************************************************
      <CFQUERY name="DatasetHistogramQry" datasource="#getcfdbname.EventCFDBName#">
      SELECT
      <cfset i = 1>
      <cfloop query="getSelectedDatasetColName">
      #SelectedDatasetColName# as col#i#,
      <cfset i = i + 1>
      </cfloop>
      JDay, TheHour, TheMinute,
      convert(varchar(12), JDay, 114) + ' ' + convert(varchar(12), TheHour, 114) + ':' + convert(varchar(12), TheMinute, 114) as DateTime
      FROM vPositionHistogram
      ORDER BY JDay, TheHour, TheMinute
      </CFQUERY>

      Here is the code for the table; which works correct:

      **************************** Report Code ************************************
      <TABLE>
      <TR>
      <TH>Day</TH>
      <TH>Hour</TH>
      <TH>Minute</TH>
      <CFOUTPUT QUERY="getSelectedDatasetColName">
      <TH>#SelectedDatasetColName# Message Count</TH>
      </CFOUTPUT>
      </TR>

      <CFOUTPUT QUERY="DatasetHistogramQry">
      <TR BGCOLOR="###IIF(DatasetHistogramQry.currentrow MOD 2, DE('DCDCDC'), DE('FFFFFF'))#">
      <TD ALIGN="center">#JDay#</TD>
      <TD ALIGN="center">#TheHour#</TD>
      <TD ALIGN="center">#TheMinute#</TD>
      <CFSET i = 1>
      <CFLOOP INDEX="X" FROM="1" TO="#getSelectedDatasetColName.recordcount#">
      <TD ALIGN="center">#evaluate("col#i#")#</TD>
      <CFSET i = i + 1>
      </CFLOOP>
      </TR>
      </CFOUTPUT>
      </TABLE>

      Here is the output for the table; Dynamic Column Name used to repeat counts for each dataset.
      ********************************* output **************************************************
      Day Hour Minute A_R1_DIS Message Count A_R1_J Message Count A_R1_PPLI Message Count
      37 17 36 0 2 35
      37 17 37 19 6 32
      37 17 38 28 30 33
      37 17 39 40 27 27
      37 17 40 66 64 32

      Here is the problem code. I have tried several variations on this code but currently all it does is display a blank chart.

      **************************** Chart Code ************************************
      <CFCHART FORMAT="flash" CHARTHEIGHT="340" CHARTWIDTH="600" SHOWXGRIDLINES="yes" SHOWYGRIDLINES="yes" SHOWBORDER="no" FONTBOLD="no" FONTITALIC="no" XAXISTITLE="Timeline (Day Hour:Minute)" YAXISTITLE="Message Counts" SHOW3D="no" ROTATED="no" SORTXAXIS="no" SHOWLEGEND="yes" TIPSTYLE="MouseOver" SHOWMARKERS="no">
      <CFOUTPUT QUERY="DatasetHistogramQry">
      <CFSET i = 1>
      <CFLOOP INDEX="X" FROM="1" TO="#getSelectedDatasetColName.recordcount#">
      <CFCHARTSERIES QUERY="DatasetHistogramQry" TYPE="line" ITEMCOLUMN="#DateTime#" VALUECOLUMN="#evaluate("col#i#")#" SERIESLABEL="#getSelectedDatasetColName.SelectedDatasetColName#">
      <CFSET i = i + 1>
      </CFLOOP>
      </CFOUTPUT>
      </CFCHART>

      Please let me know if you need any additional info on my problem and if you can help, it is greatly appreciated!!!!

      - Debra
        • 1. Re: CFChart Dynamic Column HELP
          BKBK Adobe Community Professional & MVP
          Attributes ITEMCOLUMN and VALUECOLUMN should be the names of columns in a database table. The pound(#) signs in #DateTime# and #evaluate("col#i#")# make me suspect these might not be column names. Are they?

          • 2. Re: CFChart Dynamic Column HELP
            MikerRoo Level 1
            Please attach your code. It makes it ever so much easier to read.

            Anyway, the attached should work.

            Regards,
            -- MikeR

            • 3. Re: CFChart Dynamic Column HELP
              MikerRoo Level 1
              Oops, had a typo above.

              Use the attached.
              • 4. Re: CFChart Dynamic Column HELP
                LaoD Level 1
                DateTime is a column in the vPositionHistogram (View). Col#i# is the variable used to loop through the column names derived from the getSelectedDatasetColName query. The view is populated dynamically and the columns in it vary depending on the dataset used to populate the view. The view can have 1 to many columns. That is why I pull the column names from the MasterDataList table and use a variable to get the values from each column. My column names end up being Col1, Col2, Col3, and so on. I use that variable in the loop to get the data from vPositionHistogram for each column in the report/chart. I made some headway on Friday before I called it a day, but I will try the last suggestion and post my findings in a little while. Thanks!