Report Viewer show dynamic data

Ignition
1

Hi, I'm brand new to Ignition / databases and I'm trying to figure out reports in Vision. I've searched quite a bit but I haven't been able to figure out how to create a Report Viewer that will show data using a text box or list. And most of what I have found seems to be over my head at the moment.

I'm trying to create a batch report. I'd like the Report Viewer to show data from a database named "batching" when a batch ID has either been entered in a text box or selected from a list.

I've set up a transaction group that triggers when a batch is complete and stores the values of all of the ingredients. That works okay and I can query the data. I'm a little (or very) confused about how to setup the Report and Report Viewer. I have a few Data Sources I've been playing around with. One is a Tag Historian Query and I can successfully use that to get the ingredient values I'm looking for onto the report in the Preview tab with StartDate and EndDate parameters. But I don't want this to be date / time based.

I got some advice from a colleague stating:

"To make dynamic you need to add parameters.
Add a parameter called "batchid" and then bind that to a tag that you'll make called batchid.
Add a datasource that queries the batch data that you want using the parameter "batchid" that you just made.
Make a table or use text in the design tab of the report.
Add the datasource that you made to the table or text.
You should see data in the preview window.

Make a window and put a report viewer in it linking to your report.
Use a dropdown or text component for people to enter batchid's.
Bind the report parameter "batchid" to the component that people will enter text to."

I haven't been able to get this to work and I'm mostly guessing. From top to bottom:

I've created a new parameter called "batchid" but I do not understand what to bind it to.
I've tried a few different datasources but I'm unsure which to use and how to query the data using the parameter "batchid".
I understand how to create the text box but I don't understand how to add the datasource to it. I see Parameters in the Key Browser, but no datasources.

I'm able to create the Report Viewer and link it to my report.
I can create the text box to enter the batchid.
I don't understand how to bind the report parameter "batchid" to the text box.

I apologize if anything is vague, I'm very new to this. I'd appreciate any help and if any other information is needed, please let me know.

2

So, there's two different layers here.

Let's start with the report.
Reports in Ignition are static documents. You pass them parameters, some querying is done by the gateway, and you get a static document back - essentially a PDF. It's a one-time snapshot.

So what you need to do in the report, is:

  1. Add your 'batchid' parameter.
  2. Create a SQL Query datasource that looks at the table your transaction group is creating and pulls in data for the supplied batch id. There's no way to get around learning a little SQL for this; it'll be something as simple as SELECT column, column2, column3 FROM yourTransactionGroupTableName WHERE batchidColumnName = ? - once you add the ?, a little area at the bottom of the editor should allow you to select the batch ID parameter you already have.

Then you'll set up your report to do something with that data.

Then, once you've got a report you're happy with, you need to go back to Vision to do the other half of things.

You need to take a Report Viewer component, and use a binding on the component to 'feed in' a batch ID from your text field/dropdown/whatever into the report viewer. The report viewer will automatically see that this property has changed and re-run the report, running your SQL query again and returning a new report to be displayed.

3 Likes
3

PGriffith

Thank you very much for the response. I agree, lack of SQL and Python knowledge isn't helping me here. I've setup the SQL Query as this:

image
image577×743 48.9 KB

I'm unsure what to put in the batchid parameter. I currently just have the tag I created from the PLC, but I'm guessing that's not correct.

{[default]xxx/xxx STATUS/xxx STATUS/MinorsBatching/Batch_2_ABatch_ID}

Here's what the Design tab looks like:

image
image626×232 3.64 KB

But the preview is just showing N/A:

image
image650×257 2.73 KB

I'm unsure if that's correct because nothings telling it to pull in the data or if I just made a mistake.

For the second half in Vision, I'm unsure what I'd be binding. I think I understand that I'm binding something from the Report Viewer to the Text components text property so that when a Batch ID is entered, the Report Viewer references it. But I'm unsure which parameter in the Report Viewer I'd bind that to.

4

Alright, I was able to get this working correctly. I just had to change the Parameter Type from String to Long. Now when I type a Batch ID into a text box, it populates the Report Viewer with the correct data.

Thank you very much for the detailed explanation. I appreciate the help.

1 Like
5

Nice, glad you got it working.