Dynamic report creation or merging of reports

Hi,
I created a generic report that displays the information for one machine, and I would like to instance it multiple times as I have several machines (but depending on the plant I’m working on, there can be different number of machines, that’s why I would like to create the report “dynamically”). Then, I thought about two solutions, but didn’t find how to perform them :

  1. Firstly, I wanted to create dynamically the report within a script in the Reporting Module, in which I would “import” the individual machine report as many times as needed, one after the other. Then, it would be simple to display it in the reportViewer or to send it in an email as it would generate an unique PDF.
  2. As the first idea seems to be unfeasible, I thought about another one where I would write a script in a Perspective view, to perform a loop where I launch several times the reportViewer and then print all the reports in the same one. But as I also have to send the report by email, I would have to do quite the same thing with the executeAndDistribute() function, but I’m not sure about how to do it, because if I just call the function several times, I think it will just send several emails instead of one with an unique report.

I hope it is clear.

Do you think one of these ideas is feasible ?

I saw some posts on the subject on the forum but the situations where not really the same.

Thanks in advance,

Carla

I suspect that trying to combine reports is a losing battle. I would create the “single machine report” inside an unstructured table data row. You can feed the table a dataset that has the machines for a particular report (using nested queries as necessary).

1 Like

Yes, I think too ! It seems simple but in fact it doesn’t.
Maybe I’m tired because of the day, but I don’t really see what it would look like. You are meaning that I should put all the elements of my “single machine report” in a table? But I have several pages for this report.

Without seeing some examples of what you’re trying to repeat, I don’t have any specific suggestions. Consider reading about table groups and unstructured rows.

https://docs.inductiveautomation.com/display/DOC81/Report+Tables
https://docs.inductiveautomation.com/display/DOC81/Table+Groups
https://docs.inductiveautomation.com/display/DOC81/Table+Rows#TableRows-StructuredvsUnstructuredRows

You can loop through using the executeReport function. You'll get the bytes of the report, which you can save and send as attachments in the email.

1 Like

I’ve been thinking about your solution and finally I think it effectively is a greate idea. When reading the documentation I understood that I have to retrieve the data with nested queries, but I’m struggling with it…

First I’m going to explain a little bit more what I have, and what I would like to do.

For the moment, I have something like that (I just put one data so that it is easier to understand, but I have almost 10 in fact) :

As you can see, I have several parameters, that I’m using in the tag historian query path to retrieve the right data. But in this configuration, when I’m calling the report, it shows the data of only one machine (whose name is given in the parameter filtrationName).
So now, I would like replace the general parameter filtrationName with an array containing all the filtration names (but it is not possible to give an array as parameter so I have to find something else), and create as many nested historian queries as necessary to have one for each machine and then replace the parameter filtrationName with its value within the array. The problem is that when I create a nested historian query, it opens a similar page where I also have to give the entire paths to access the data, where I would like just to replace the value of the parameter filtrationName. Therefore I’m stucked…

I don’t know if it is clear, don’t hesitate to tell me if it isn’t.

The top-level query should be where you compile your list of parameters. In the example below I’ve basically typed my list statically, but if you have a list in SQL you can perform a real query.

QUERY (This returns one row called machines with values 1, 2, 3)

You then create a sub-query. Change the type of the sub-query to Tag Historian Query. The tag path will contain a parameter in curly braces that matches the column name from the parent query. In my case, the path is: %PATH_REDACTED%/EQUIPMENT_{machineNum}_PV. It’s important that only the path have a parameter. The Data Key Alias should not have a parameter.

My report design looks like this:

the preview looks like this (there’s another page with a chart for machine 3):

Notes: If you need to set up more than one dynamic parameter, you can accomplish this in two ways. You can increase the depth of your nest or you can select more than one column in your initial query. Keep in mind, if you don’t carefully manage the data you’re querying, nested queries can quickly start pulling in system-crashingly large amounts of data. Deeper nests are going to make this worse faster, so I’d recommend flatter nests wherever they serve your purpose.

1 Like

It works great, thanks.
I’m trying the method of zacht too to compare, if it allows me to have only one PDF if would be great.

Ok I think I get it, thank you very much for your detailed explaination.
To apply it to my application, I’ve another question (sorry) : do you think that if I have a dataset parameter it is possible to retrieve its data with a SQL query ? I tried different syntaxes but it doesn’t seems to work because the SQL query only seems to search for existing tables in a database.

You would need to use a script data source to pull data from a dataset parameter.

Or use my view() expression function from my free Simulation Aids module.

4 Likes

Well… I can’t figure out how to make it work. Don’t know how to manage nested queries. The idea seemed promiscing but maybe too complicated for me for the moment.

hello @zacht, thank you for this solution, in this example, how many tags did you create in the sub_query? only one or the 3?

thanks again

Just one with a parameter.

The query is run once for each row of the parent table, which gave me three results, but I only configured one tag in the historian query.