Report Builder 3.0 with SQL Express/FTView SE

[Roksan]

I have a requirement to produce date-range selectable reports from a panel mounted PC running FactoryTalk View SE V9.0. So far I have FactoryTalk logging a mixture of string data and float values to an SQL Server Express database. My first problem arises when I try to use Microsoft Report Builder 3.0 to display the data in date order in a table. MY SQL knowledge is very limited. I can build a dataset that includes 'FloatTable.Val' and 'StringTable.Val', but don't know how to get all float and all string values for a given date and time onto a single row in a report. I guess my query needs to include the 'TagIndex' field somehow from the database to select particular float and string values. Once I (hopefully) get this working, the next task will be to be able to select a date range for the data to be specified at the HMI, and to trigger report generation from the HMI. I assume report viewing could be achieved by opening a web browser on the HMI and specifying the report server URL?
I also don't have any high level language programming skills. I've attached the query I've cobbled together so far in Report Builder, and the resulting output of that query. 

Any help, advice or code examples would be greatly appreciated.

[kaare_t]

I'm not sure what you need help with here, would you please explain what it is you want to read out from your tables?

I get that you have one table for string values and one table for float values, but instead of explaining what is not working, could you explain what you are trying to accomplish? Maybe that helps my understanding?

[Roksan]

Thanks for the reply.  The problem I have is that variables that seem to be available for display in my report are FloatTable.val, and StringTable.val, but for each row in the database and report (i.e. each time/date value), there are several float values, and several string values that each relate to different tags in my application.  I'll try and represent what I wish to display below......

 

| TimeDate1  |  FloatTable.val (where TagIndex = 6) | FloatTable.val (TagIndex = 7) | FloatTable.val (TagIndex = 8) | StringTable.val (TagIndex = 4) | StringTable.val (TagIndex = 4) |

| TimeDate2  |  FloatTable.val (where TagIndex = 6) | FloatTable.val (TagIndex = 7) | FloatTable.val (TagIndex = 8) | StringTable.val (TagIndex = 4) | StringTable.val (TagIndex = 4) |

 

Hope this clarifies a bit.

[kaare_t]

I see, so what you're saying is that you want to replace TagIndex with a name of some sort, to represent the device or the actual equipment? Then, you want to show them kind of "in-line" one after the other? So for example:

TimeDate | Variable1 | Variable2 | Variable3

If that's the case, were closer to a solution. Could you also post how your tables are structured? I see you have a JOIN in your first post, so obviously you have at least two tables. Do you have more than those two (for this scenario), or is it just float/string tables each containing a correct datetime?

[Roksan]

That's correct.  There's just the two tables that I'm interested in - StringTable and FloatTable.  There are eight string entries for each time/date (TagIndex 0 to 5, and 13 to 14), and seven float entries (TagIndex 6 to 12).  I've attached an image showing the column names in these two tables.

[kaare_t]

Understood! I will get back to you but I need to do some investigation first... I'll let you know.

[Roksan]

Thanks - greatly appreciated.

[kaare_t]

OK, so I've been giving this some thought, and I must say I'm a bit in the dark exactly what you want to achieve by getting the data "inline" like you describe. Maybe you could also describe what your "final goal" is, just to make my understanding better, and to shed some light on possible routes?

I mean; the output you are referring to in the pictures is just the raw output right? In your report application you can organize and adjust where you want the data placed?

What I'm trying to point out is that I'm pretty sure you want to do the UI logic in the View application instead of in the SQL query?

Anyway, I've created a query you can use for your question, but please post more information if you can and we'll try to help you accomplish your goal in the "correct way. See code below, works for MS SQL Express server. You can copy-paste this code into your SQL Management Studio to be able to run the query if you want to test (you might have to adjust the DB names etc).

-- Creates a temporary timestamp value (and sets it to NULL in my test) -> Set it to the correct value in your application query
DECLARE @stamp AS DateTime
SET @stamp = NULL;
-- Creates a temporary table that we will use to insert into - note that these are the columns you want
DECLARE @tmpTable AS TABLE
(
	ValDateTime datetime,
	Val1 float,
	Val2 float,
	Val3 float,
	Val4 float,
	Val5 float,
	Str1 nvarchar(82),
	Str2 nvarchar(82),
	Str3 nvarchar(82)
)
-- Inserts into the temporary table
INSERT INTO @tmpTable(ValDateTime, Val1, Val2, Val3, Val4, Val5, Str1, Str2, Str3)
VALUES
	((SELECT TOP (1) DateAndTime FROM FloatTable WHERE DateAndTime LIKE @stamp),
	(SELECT Val FROM FloatTable WHERE TagIndex = 1 AND DateAndTime LIKE @stamp),
	(SELECT Val From FloatTable WHERE TagIndex = 2 AND DateAndTime LIKE @stamp),
	(SELECT Val From FloatTable WHERE TagIndex = 3 AND DateAndTime LIKE @stamp),
	(SELECT Val From FloatTable WHERE TagIndex = 4 AND DateAndTime LIKE @stamp),
	(SELECT Val From FloatTable WHERE TagIndex = 5 AND DateAndTime LIKE @stamp),
	(SELECT Val From StringTable WHERE TagIndex = 1 AND DateAndTime LIKE @stamp),
	(SELECT Val From StringTable WHERE TagIndex = 2 AND DateAndTime LIKE @stamp),
	(SELECT Val From StringTable WHERE TagIndex = 3 AND DateAndTime LIKE @stamp))

-- In the end, just use a SELECT * from the temp table to select all columns
SELECT * FROM @tmpTable

 

Gives the following result (note that in my DB I only created the columns with NULL value in the datetime column. Top line is headers, and bottom line is data.

ValDateTime	Val1	Val2	Val3	Val4	Val5	Str1		Str2		Str3
NULL		1	2	3	4	5	String 1	String 2	String 3

 

Edited March 27, 2018 by kaare_t
EDIT: Needed to apply the datetime in WHERE clause

[Roksan]

That looks like just what I need.  I think the key is the use of the 'where' clause to allow me to access the individual values that I need.  I'm stuck on board a ship off the coast of Israel at the moment, with little spare time to test this out, but as soon as I get chance I'll give it a go and let you know how I get on.  Thanks again for all your help.

[KearyD]

Would you rather use field mapping between the PLC tags and your SQL database fields as opposed to writing Select statements?  Check out the tutorials on www.thedatacommander.com/resources

[Roksan]

14 minutes ago, KearyD said:

Would you rather use field mapping between the PLC tags and your SQL database fields as opposed to writing Select statements?  Check out the tutorials on www.thedatacommander.com/resources

Thanks for the suggestion.  I'll take a look.

[KegsMcSorley]

I have done this many times for reports. Check out "Pivot" in SQL. I embed that into my reports.