Metric Reporting from RSView32

[samit]

All,

I am a newbie to RSView32. I am being tasked to generate various reports contaning KPI's like downtime, # boxes produced, start time, downtime per shift etc from RSView32. I know that RSView32 runs on a VB script at the backend. But I don't know much of VBA.

I have in depth knowledge of Excel and SQL but not VBA.

Can you please advise how can I set the reports up (step by step)in the RSView 32 and export them into an excel spreadsheet or an Access Database. I am sure if I am able to set one report up, I can do the others too.

One of the forums suggested that Crystal reports are best way to extract data but I don't know how to set them up either. So please help!!

Any input is appreciated.

Thanks
AS

 

[surferb]

If you're doing too much scripting/programming you're probably going in the wrong direction.

I don't think RSView 32 supports such report generation. Rockwell does have many different applications - I think "FactoryTalk Metrics" would be the one. Those applications aren't cheap.

You wouldn't export reports into an Excel spreadsheet or Access database, although you might export the data in such format, then generate a report externally.

In general, the strategy would be to get your data into an SQL database, then from there generate a report from it. Access might work, but you won't get much of an automated report from a spreadsheet - it's too "free form". From the SQL database you can use Crystal Reports or any of many other packages. These may be integrated in your HMI/SCADA system or separate. The SQL database is key - don't think that any application can generate a report from a programs "tag database".

There are different degrees to which you can "do it yourself" versus use a pre-canned approach for reporting. Remember, any modern approach will use an SQL database, which is a good thing. As I understand the "FactoryTalk" series of the Rockwell Apps use a custom (possibly obfuscated) version of MS SQL Server. There are other approaches using other industrial vendors or shifting more toward commercial reporting solutions.

 

[samit]

Dear Surferb,

Thanks for your quick response. Can you please advise how I can configure a SQL database (like MS Access) with RSView32? What kind of middleware do I need to export the reports?

Thanks
AS

 

[surferb]

There are various approaches. I would recommend investigating two:

1. Contacting Inductive Automation for a web demo. They'll show you an end to end SQL reporting approach in a live web demo. Just tell them that your focus is on reporting.

2. Contact your local Rockwell rep to see what they have to offer. Like I said earlier, I don't think RSView supports much in the way of reports. They will probably show you additional FactoryTalk applications. There is something to be said about vendor consistency.

Your definition is important - you may need something far simpler or different than what I am. When I think of reports, I'm referring to something that's: dynamically generated (can select different date ranges/etc) and printable - typically in .pdf format, although, HTML, image formats, or even Excel could work. Also, I usually think of historical or summary data.

 

[rdrast]

Dear Surferb,

Thanks for your quick response. Can you please advise how I can configure a SQL database (like MS Access) with RSView32? What kind of middleware do I need to export the reports?

Thanks
AS

You don't configure the database with RSView, although, I suppose you could if you really wanted to write a graphical Database tool in view... ugh.

In general, the steps you need to go through are:

1) Create a database for your system.

2) Create your tables, as required for your data.

3) Write a method of populating your tables with data (this can be as simple as building a SQL command, if you are using the VBA and ADODB)

3a) To actually write data to a database (using VBA and ADODB), you must first create a connection object, and open the database.
DIM dbConn as New ADODB.Connection

' easy if you create the database connection with Windows ODBC Manager in Control Panel

dbConn.Open "DSN=MyODBCConnection"


3b) Create an ADODB.Command object, to execute a SQL Statement.

DIM SQLCmd as New ADODB.Command

SQLCmd.CommandText = "INSERT IGNORE INTO MYTABLE [Field1], [Field2], [Field3]... VALUES 12345, 1232, 'Record Name' "

SQLCmd.Name = "AddRecord"
SQLCmd.ActiveConnection = dbConn

3c) Execute the command against the database:
dbConn.AddRecord

------------
I do like Crystal Reports, so I create a report in Crystal, with a very simple selection criteria, usually just a single integer.

To actually print out a report (again from VBA):

1) Add references for Crystal Reports to the VBA app.

2) Create the Interface:
' create an instance of the Crystal Reports engine
Dim crApplication as New CRAXDRT.Application
' create a report viewer object (I use version 9)
Dim CReportViewCtrl = New CRVIEWER9Lib.CRViewer9
' allocate pointer for a report object...
Dim crReport As CRAXDRT.Report

' Pick the report
Set crReport = crApplication.OpenReport("C:\Reports\MyReport.rpt", 1)

' discard any saved data, and Assign the selection formula
crReport.DiscardSavedData
crReport.RecordSelectionFormula = "{MyDBTable.MyIndexField} = 1234"

' Refresh the report control with active data.
cReportViewCtrl.ReportSource = crReport
cReportViewCtrl.Refresh

' Print out the Crystal report:
crReport.PrintOut False, 1, False, 1, 1


---------------------------

Just some very basic VBA/Database interaction, but may be enough to point you in at least one direction.

 

[parky]

If you use RSV logging feature, trigger when you want the data, make it log to an access db (all this can be done in RSV32) then if you use excel to get data & save it as a macro you will see the code used (it's just a sql statement.

you could modify it to add things like date/time so you pick a start time & end time create your sheet with a button that runs the code & then sort the data from the sheet that is populated onto your formatted sheet, I have done this many times, it's not the best but it means that our production staff can get the data when they like just using excel, I have created a number of templates for them to use & they get data with graphs, easy to use as I use the calendar control & time fields they can select.

 

[samit]

If you use RSV logging feature, trigger when you want the data, make it log to an access db (all this can be done in RSV32) then if you use excel to get data & save it as a macro you will see the code used (it's just a sql statement.

you could modify it to add things like date/time so you pick a start time & end time create your sheet with a button that runs the code & then sort the data from the sheet that is populated onto your formatted sheet, I have done this many times, it's not the best but it means that our production staff can get the data when they like just using excel, I have created a number of templates for them to use & they get data with graphs, easy to use as I use the calendar control & time fields they can select.

Dear Parky

Thanks for the response. I think this may work for me. Is there a way you can email me some snapshots of the steps that I need to take to make it log to an Access DB. I can then easily transfer the data into Excel.

Thanks
Amit

 

[parky]

Don't have it handy at the moment as it's on one of our servers, however I will try from memory, go to datalog in the menu tree, create a new one & name it what you want, the setup dialog allows you to choose the type of database.
select MDB with the radio button, go to next tab (think you can skip this one, go to next tab select the trigger either time, or by event (you can trigger the datalogsnapshot in an event i.e. 10:00:00 datalogsnapshot mydata.
then next tab add your tags & save.
I also think you have a button on the setup to create the db.
I will see if I can get some screenshots of the setup process also I will post a simple excel code if I can.

 

[parky]

First, create a new Datalog by going into Datalog setup
Then add log trigger (on change, on demand or timed).
Suggest you use on demand.
Add tags in the model
note that the standard fields are Tag, String, Float
Save then open up events
Create an event or a number of them i.e
DatalogSnapshot "MydataDB" "13:59"
or create a macro to do the datalogsnapshot then run when you want
In excel, go to Sheet3 (suggest you have 3 sheets)
Start Recording a macro, get external data, point to the database
Select the fields, select datetime >= (get first log)
Then <= (last log) then sort by
& complete the data, this will fetch the data & put it on sheet 3.
Stop recording the macro, open up VB for excel, convert macro to vb this will give you the basic code for fetching data, you could then add variables in place of the datetime data & use a calendar control to select the date etc.
Using VB manipulate the data on sheet 3 to sheet 1 using vb or create graphs.
Hope this helps, I did copy some vb code at work & forgot to bring it home to post it.