Multiple columns in Report Viewer report

[yolovs]

Hi,

How it is possible to put 5 archive tag values in Report Viewer report in table?
Each of the 5 archive tags got the same time stamp.
In screen switch function, on second step, I add dataset with 5 archive tags.

Regards,
Yolov

[david.cerdeira]

Hello yolovs,

Welcome to the COPA-DATA Forum.

First, I strongly recommend to read the documentation topic Manual > Reporting > Report Viewer.

Aside note: It is possible to use Visual Studio IDE (or others) to create reports, but I strongly recommend to use Microsoft Report Builder 2.0. In the documentation topic Manual > Reporting > Report Viewer > Report definition files you can find the URL to download it. Note that is not possible to change the IDE after a report was created on a specific IDE.

Then, you need to create the RDL (report definition file): In the Editor, in the project tree, select the node Files > Report Viewer and create a new report file. Select the ARCHIVE data set and name it as you wish.

In fact, I believe you want to create a report but not using a "Table" but a "Matrix", so that its Rows correspond to the TIMESTAMP and the Columns to the VARIABLES. The Data field are correspond to the STRVALUE field of the ARCHIVE data set.

In the screen switch function that opens the report, select the respective the RDL file and link the archive variables to the respective data set created and linked before.

Finally, I also recommend you to open the SUPERVISOR7XX demo projects where there are configured report viewer reports.

Please let me know if you need further help.

All the best,
David Cerdeira

[yolovs]

Dear David,

Many thanks for explanations!
I understand what you describe!
I can not understand the following!
Let say that I configure the following archive tags:
- ARCHIVETAG1
- ARCHIVETAG2
- ARCHIVETAG3
- ARCHIVETAG4
- ARCHIVETAG5
in Historian.
In Files/Report Viewer, I create new RDL file - Select data sets for report dialog is opened.
Now I add for example 5 archives named:
- ARCHIVE1
- ARCHIVE2
- ARCHIVE3
- ARCHIVE4
- ARCHIVE5
Next I save the RDL file.
Next I start configuring screen switch function. First step is to select the Report Viewer type screen.
Next step is the Filter dialog.
Here first I select my RDL file and read the empty dataset from it - ARCHIVE1 to 5. Correct?
Next I do the following:
- Configure Time filter
- Select each of the (empty by now) dataset, click over the Edit button at the bottom of dialog and assign real archive tag to the configured dataset! Correct?
Now if I open the RDL file in Report Builder 2.0, I willl have 5 datasets, each of them having the archive tag values, configured in screen switch function.

How to put all 5 dataset in one tablix joined by [TIMESTAMP]? If it was real SQL, I would write INNER JOIN on [TIMESTAMP] to return one set of data.

So how to make five coumns with archive tags data, alligned by theirs common timestam?

Svetozar Yolov

[david.cerdeira]

Dear yolovs,

All the steps mentioned look fine, but just two short notes:

• A report data set can contain more than one archive variable, from the same or different zenon archives.
  
• The report elements such as tables, matrices, etc. cannot be linked to different data sets.

In this case, I suggest to group the variables in one data set (note: the variables can be linked to multiple data sets) in the report viewer screen switch function that are used in the same report element.

Please let me know if you need further information. Good luck!

Best regards,
David Cerdeira

[yolovs]

Dear David,

Thank you for explanation. I did what you advise me and I have got the report!
When you add more than one historian tag in dataset, you get the tags into the reports but ONE BY ONE!
If I add tags:
- ARCHIVETAG1
- ARCHIVETAG2
- ARCHIVETAG3
in dataset names ARCHIVE1, in report viewer window (in RT) I got:

Variable          Timestamp   Status flag   Value   Unit
ARCHIVETAG1  5/29/2017     4325376     45 
ARCHIVETAG1  5/29/2017     4325376     45 
ARCHIVETAG1  5/29/2017     4325376     45 
ARCHIVETAG2  5/29/2017     4325376     45 
ARCHIVETAG2  5/29/2017     4325376     45 
ARCHIVETAG2  5/29/2017     4325376     45
ARCHIVETAG3  5/29/2017     4325376     45 
ARCHIVETAG3  5/29/2017     4325376     45 
ARCHIVETAG3  5/29/2017     4325376     45

I need to have the following
Timestamp                ARCHIVETAG1 Value     ARCHIVETAG2 Value     ARCHIVETAG3 Value
5/29/2017 01:00:00    45                             467                            123
5/29/2017 02:00:00    35                             457                            223
5/29/2017 03:00:00    25                             447                            323
5/29/2017 04:00:00    15                             457                            423

I can do it with T-SQL, but in zenon Documentation is stated, that dataset must not be edited! In addition the dataset is XML!
Is there any chance to use T_SQL, to group by Timestamp and then to go from rows to columns?

Best regards,
Svetozar Yolov

[david.cerdeira]

Dear Yolovs,

That's great. Then we are back to my first reply - it is not a table, but a matrix you want - see there more details.

Please try to apply my suggestion and give me a feedback.

Good luck,
David Cerdeira

[yolovs]

Dear David,

Thank you very much for your cooperation!
I did it (please see attachment).
I configure a matrix, where in the wizard I did:
- row groups -> by timestamp
- column groups -> by variable name
- Sum values -> by variable value

... and it works!!!! I have to read again the Understanding Groups (Report Builder and SSRS).

Best regards,
Svetozar Yolov

[david.cerdeira]

Dear Yolovs,

That's cool!!!

No problem at all!

Best regards,
David Cerdeira