Chart Control

[RickyRelouw]

Hello all,
I was really excited when alpha five released the Chart Control which is to be used in the dialog. But I must be missing something, I am wanting to setup my DBA with dashboards using chart control.

But i cant seem to understand how to develop a chart from a TABLE that will give me useful information. I have watched the videos, but they use Static data in a series. Which is not much use if your building a dashboard appearance as the static data does not give a updated graph by itself.

So I have tried developing a report from a single table it hold thousands of records -

The idea of a dashboard will tell the user how many records belong to them and how many of these record are "completed" or "open"

But when using the data I need to summarize the Records by how many are "completed" and how many are "open".

E.g. A pie graph so when the user looks at their graph it shows percentage of records that belong to them that are "completed" and how many are "open" respectively.

But I must be missing something as I cant seem to work this out? Any ideas or answers would be appreciated.

Regards

Rick.

[Andy]

Re: Chart Control

100% agree - I posted on this months ago -there is nothing in any video or manuals/wiki that explains how this is achieved, though I was told it can be done!

[Foodtester]

Re: Chart Control

Hi

You probably need to use custom series based on one or more sql queries. See Videos : FP11_DLGCHARTS 3 How to use custom series.

Robin

[Andy]

Re: Chart Control

Hi

You probably need to use custom series based on one or more sql queries. See Videos : FP11_DLGCHARTS 3 How to use custom series.

Robin

I watched each video at least 3 times each, I would love to hear from anyone who can give me step by step instructions how to achieve this using a SQL database (not DBF)

[Foodtester]

Re: Chart Control

Andy

In my case I have a single line chart which utilises various options and presents test results. Most of it hangs on 2 xbasic functions which contain the SQL. I will post these two functions so you can see whats involved- I will have to abridge them first because they are rather long (due to the number of options).

This first function sets up the data for the line graph series: x = Result of Test, Y (or Category) = Date + Lab Ref No

function getData as c (e as p)

'This function gets info from the dialog to build the appropriate query.
	
Dim MyShift as C
MyShift = e.dataSubmitted.pCode   'Product Code
Dim MyClient as C
MyClient = e.dataSubmitted.pClient  'Client Code
Dim sDate as D
sDate = e.dataSubmitted.StartDate   'StartDate
Dim eDate as D
eDate = e.dataSubmitted.EndDate  'Enddate
Dim MyTest as C
MyTest = e.dataSubmitted.TestToGraph  'testfield
Dim MyDescription as C
MyDescription = e.dataSubmitted.ProdDescr
Dim TestDescription as C
Dim MyBasis as C
MyBasis = e.dataSubmitted.Base_On


DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::LabPortal")
if flagResult = .f. then 
	
	end 
end if

Dim Args as sql::Arguments
Args.Set("VShift",MyShift)
Args.Set("vClient",MyClient)
Args.Set("vsDate",sDate)
Args.Set("veDate",eDate)
Args.Set("vTest",MyTest)
Args.Set("vDescr",MyDescription)
					


'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t. 
dim sqlStatement as c 

Dim MyCaseSelect as C
MyCaseSelect = MyTest + MyBasis



Select
'This is a set of micro statements   (There are many selects- so I just show a few here)
Case MyCaseSelect = "M1xCodeasis"  ' use select to define sql used

sqlStatement = <<%sql%
SELECT DATE_STARTED,SAMPLE_No,Round(Log10(M1),2) FROM sqllablog  WHERE searchcode = :vShift AND CLIENT_CODE = :vClient AND DATE_STARTED BETWEEN :vsDate AND :veDate AND M1 IS NOT NULL
%sql%



Case MyCaseSelect= "M1xCodeLike"

sqlStatement = <<%sql%
SELECT Date_Started,Sample_No,Round(Log10(M1),2) FROM sqllablog  WHERE searchcode LIKE CONCAT('%',:vShift,'%') AND CLIENT_CODE = :vClient AND DATE_STARTED BETWEEN :vsDate AND :veDate AND M1 IS NOT NULL
%sql%



Case MyCaseSelect = "M1xDescrLike"

sqlStatement = <<%sql%
SELECT Date_Started,Sample_No,Round(Log10(M1),2) FROM sqllablog  WHERE DESCRIPTION LIKE CONCAT('%',:vDescr,'%') AND CLIENT_CODE = :vClient AND DATE_STARTED BETWEEN :vsDate AND :veDate AND M1 IS NOT NULL
%sql%


end select


'Execute the Query which was chosen by the select statement


flagResult = cn.Execute(sqlStatement,Args)
if flagResult = .f. then 
	
	cn.close()
	end 
end if 


dim rs as sql::ResultSet
rs = cn.ResultSet
'Now, dump the contents of the ResultSet to a variable txt

'The data series is separated by pipes where data(1)=datestarted, Data(2)= SampleNo Data(3)= Result.
' In the chart definition these txt is Series1 and the sub series are eg: MyDate,MySample,MyResult


dim txt as c "" 
Flag = rs.nextRow()

while flag
	
	txt = txt +rs.data(1)+"|" + rs.data(2) + "|" + rs.data(3) + crlf()
	
	flag = rs.nextRow()
	
	end while


'Now, close the connection 
cn.close()





getData = txt



	
end function

The Second function is similar but defines another series which allows control of the chart title there is no SQL involved, just data from the dialog:

function getTitles as c (e as p)

'This function builds Series2 which is the information used in the graph title. It gets information from the dialog

Dim MyShift as C
MyShift = e.dataSubmitted.pCode   'Product Code
Dim MyClient as C
MyClient = e.dataSubmitted.pClient  'Client
Dim sDate as D
sDate = e.dataSubmitted.StartDate   'StartDate
Dim eDate as D
eDate = e.dataSubmitted.EndDate  'end date
Dim MyTest as C
MyTest = e.dataSubmitted.TestToGraph  'testfield
Dim MyDescription as C
MyDescription = e.dataSubmitted.ProdDescr
Dim TestDescription as C
Dim MyBasis as C
MyBasis = e.dataSubmitted.Base_On

Dim MyBrand as C
MyBrand = e.dataSubmitted.Brand_or_Type




Select
Case MyTest = "M1"
MyTest = "Total Count (Log cfu/g)"

Case MyTest = "M3"
MyTest = "Coliforms (Log cfu/g)"



end select


'Defining graph titles

getTitles = "Graph of: " + MyTest + " For: "+MyBrand+" " + MyDescription +"(Prod Code: "+ MyShift +")"



end function

Hope that makes sense

[RickyRelouw]

Re: Chart Control

Hi, back to my original question! I am using alpha dba not SQL!

I actually created a new summary table to give me the summary info from the original table! This works as expected, but the summary table is only accurate for when the created! Is there a way to create a summary table that automatically updates???

Or a way to run the code to recreate the summary table prior to viewing the dashboard???

The chart control is great and works well, but unjust haven't worked out how to use to my advantage!

Any ideas or suggestions??

Regards

Rick!

[Foodtester]

Re: Chart Control

Been thinking!

Taking your original Alpha dbf table you need a count of Total Records and a count of completed records for a given client. I think you should use a custom series and you will need to write an xbasic function which returns your series. Something like this:

function mydata as c (e as p)

Dim Totalrecords as N
Dim CompletedRecords as N
Dim MyClient as C = Session.YourClient


TotalRecords = dbcount("YourDBFTable", "Customer_Id", MyClient)
CompletedRecords = dbcount("YourDBFTable", "Customer_ID .and. Completed = .T.", MyClient)? (Not sure of syntax or if works - maybe there is a better A5 function/method) Count(lookup)?


MyData = 100*CompletedRecords/TotalRecords +"|" + (100-(100*CompletedRecords/TotalRecords) (This would be your 2 part series say Series1 subseries complete,notcomplete Complete|NotComplete


end function

Think this would avoid creating a summary table, then refresh the chart periodically.

Robin

[rnoguera]

Re: Chart Control

In the Dialog component, go to Properties > Data Series and create the series definitions that access your data.

The Chart component will ONLY display your data, but will not sumarize or analyze it.

In other words, you should start by having a SQL query (a view if you use SQL Server) that shows the info you need in the way you need:

Record Status    Record Quantity
Open                     1234
Closed                  635

From this series, the Chart component will (easily) create a display.

The Cart component itself, has no sumarizing or totalizing features.

[RickyRelouw]

Re: Chart Control

Thanks for the reply...

Thankyou "Foodtester" - I will have to play with this idea and see if it works. (I like your thinking)...

Also thanks for the reply "Rnoguera" but I'm using Alpha dba not sql. As far as i know you cant do this in alpha dba? thats whey I was playing with the idea of creating a summary table, but couldn't find a way to work this as the summary table needs to be recreated everytime, in order to gain "up to date" record summary.

I will try some of the suggestions above and see how it work out.

[RickyRelouw]

Re: Chart Control

Hello I'm back again.

I have been playing around with the above suggestions, but cant seem to work it out. I have come up with the follwoing code:


function mydata as c (e as p)
DIM TestClosed as C
DIm TestOpen as C
DIm TestOngoing as C
Dim MyData as C
DIM Totalrecords as C
Dim Location as C

if eval_valid("e.rv.V.R1.Facilityname")=.f. then
Facilityname = "House1"
else
Facilityname = e.rv.V.R1.Facilityname
end if

Location = Facilityname

Totalrecords = dbcount("cip_tbl","Fname_C",Location)

TestClosed = dbcount("cip_tbl","Status_C","Closed")

TestOpen = dbcount("cip_tbl","Status_C","Open")

TestOngoing = dbcount("cip_tbl","Status_C","Ongoing")

MyData = TestClosed+"|"+TestOpen+"|"+TestOngoing+"|"+Totalrecords

end function

This is sort of what I'm trying to acheive, but still missing the mark. I would somehow really like to return the following from the CIP table.

Total | Status | FacilityName
20 open House1
10 Closed House 1
2 Ongogin House 1
30 open HOUSE2
0 Closed HOUSE2

etc.

I know Im a bit off with my function, but I just learing and playing around with ideas, but unfortualey have come toa full stop.....

Any suggestions woudl be thankfully appreciated.

Reagrds

Ricky

[Foodtester]

Re: Chart Control

Just a couple of things:

1. You dimmed your variables as C but i think dbcount will return a number.

Try TestOngoing = Str(dbcount("Whatever")) to return a string.


Also try putting debug(1) before your end function. Then add button to your dialog to call you function- if you press it you will then be able to see what your function is doing which may guide you.

Robin

[RickyRelouw]

Re: Chart Control

Thank you Foodtester.

I will make the alterations and keep trying..

[RickyRelouw]

Re: Chart Control

I now have the Following,------


function mydata as c (e as p)
Dim TestClosed as N
Dim TestOpen as N
DIm TestOngoing as N
Dim Totalrecords as N
Dim Location as C
Dim MyData as C

if eval_valid("e.rv.V.R1.Facilityname")=.f. then
Facilityname = "Admin Aged"
else
Facilityname = e.rv.V.R1.Facilityname

end if

Location = Facilityname

Totalrecords = dbcount("cip_tbl","Fname_C",Location)
TestClosed = dbcount("cip_tbl","Status_C","Completed")
TestOpen = dbcount("cip_tbl","Status_C","Open")
TestOngoing = dbcount("cip_tbl","Status_C","Ongoing")

debug(1)

end function

MyData = TestClosed + "|" + TestOpen + "|" + TestOngoing + "|" + Totalrecords

What I woud liek to do is filter the DBCount by the "LOCATION" as well as wehter they are "Compelted, Open or Ongogin"

eg I have tried this but it wont accept - TestClosed = dbcount("cip_tbl","Status_C.and.Fname=Location","Completed")

Any ideas how I can furhter filter the Dbcount???

[RickyRelouw]

Re: Chart Control

Hello all, HORRAY I finaly have this working....

Thank you all for your suggestions, it has taken a little while but each step I got a little closer...

Just in case anyonw is interseted this is the code I came up with. I swapped Dbcount for Tablecount and was able to get my formula right.

I am now addicted... this has been so much fun. :-)

The Code.

function mydata as c (e as p)

Dim TestClosed as N
Dim TestOpen as N
DIm TestOngoing as N
Dim Totalrecords as N
Dim Location as C
Dim MyData as C

if eval_valid("e.rv.V.R1.Facilityname")=.f. then
Facilityname = "Red Ward"
else
Facilityname = e.rv.V.R1.Facilityname

end if

Location = Facilityname

Totalrecords = dbcount("cip_tbl","Fname_C",Location)

TestClosed = tablecount("cip_tbl","Status = 'Completed'.and.Facility_Name = '" + Location + "'")
TestOpen = tablecount("cip_tbl","Status = 'Open'.and.Facility_Name = '" + Location + "'")
TestOngoing = tablecount("cip_tbl","Status = 'Ongoing'.and.Facility_Name = '" + Location + "'")



MyData = TestClosed + "|" + TestOpen + "|" + TestOngoing + "|" + Totalrecords



'debug(1)

end function

thanks...