Using multiple data sources in a single template?

Adam Richards

I've managed to figure out a lot of things in the week I've been learning jsreport, but I can't figure out how to access multiple data sources in a single report.

I can choose a single data source for my template, but I really want to drill down to a second data source for my report to pull related data. I have a key in both sets of data for looking up, but no idea how to access the secondary data.

Can anyone help? Am I missing something simple?

bjrmatos

hi! to be able to use other data source you will need to use scripts, scripts feature will let you add custom code that you can process before your report render (beforeRender hook), in there you can fetch data from other sources (make http request to other server that returns your data, connect to a database an query some data, etc) and finally merge all of these values in req.data object that your report is going to use to render. you can see an example of this here, in which the report gets data from an external http api.

Adam Richards

Thanks for the reply. What about when both data sources are json files in the studio, is it still possible?

jan_blaha

The custom script can also load entities which you see in the studio.

https://jsreport.net/learn/scripts#query-an-entity-from-script

const jsreport = require('jsreport-proxy')
async function beforeRender(req, res) {
  const data1= await jsreport.documentStore.collection('assets').findOne({name: 'data1'})
  const data2= await jsreport.documentStore.collection('assets').findOne({name: 'data2'})
  req.data.data1 = JSON.parse(data1.content.toString())
  req.data.data2 = JSON.parse(data2.content.toString())
}

Adam Richards

Thanks for this. I'm really a novice, so does this mean I could reference it like this?

{{#each data1.code}}
  <p>Record {{data1.name}} contains the following units:</p>
  {{#each data2.code}}
    {{#if data2.code == data1.code}}
      <p>{{data2.name}}</p>
    {{/if}}
  {{/each}}
{{/each}}

So that in theory would cycle through each unique code/identifier in data1, look for a match for each in data2, and then output the 'name' field from each record in data2 where the code matched the code in data1.

Am I on the right track? Would I be able to reference those two data assets the way I think, with data1. and data2.?

(edit: I'm getting an error telling me jsreport-proxy isn't there - Error while executing templating engine. Unable to find module jsreport-proxy)

(edit 2: by changing to camel notation and requiring jsreportProxy I got past that error, but now I get an error template input in request contain values that does not match the defined schema. schema validation errors: template.data should be object which makes me think I'm trying to get the data in my template doesn't work the way I guessed at above)

bjrmatos

so does this mean I could reference it like this?
So that in theory would cycle through each unique code/identifier in data1, look for a match for each in data2, and then output the 'name' field from each record in data2 where the code matched the code in data1.

{{#each data1.code}}
  <p>Record {{data1.name}} contains the following units:</p>
  {{#each data2.code}}
    {{#if data2.code == data1.code}}
      <p>{{data2.name}}</p>
    {{/if}}
  {{/each}}
{{/each}}

hmm no, there are some mistakes here, probably because you are not getting how handlebars's #each works, . the TL;DR is that when you use handlebars #each the context inside the body changes, so if you use <p>Record {{data1.name}} inside the #each it will not work, because inside the body the new context is the element being iterated, so data1 does not exists there, if you want to access data1 again i think you can use this. <p>Record {{@root.data1.name}}, the rest of your code is also affected by how #each works, so the condition and the other iteration is also wrong. you need to check how handlebars each works and update your code.

template input in request contain values that does not match the defined schema. schema validation errors: template.data should be object

i think this error is because some mistake in your code, i will need to use what are you doing in order to tell you how to fix it. if you can upload a simple version of your code into a github repository i can check it and update it to help you.

Adam Richards

Thanks for this reply, I think i understand a little better. I can't test it yet though, as the JS I used from above seems to be tripping me up. If I change my data source in the studio and remove any handlebars in my template and try to run it, I get this error:

Error while executing templating engine. Unable to find module jsreport-proxy
Searched paths: 
jsreport-proxy
jsreport-proxy
C:\Users\ar206747\jsreport-proxy
C:\Users\ar206747\jsreport-proxy
C:\Users\ar206747\node_modules\jsreport-cli\lib\jsreport-proxy
. 

> 1 | const jsreport = require('jsreport-proxy');

Is it possible I don't have jsreport-proxy installed, and if so, how would I get it? As far as I know I did a complete normal npm install of jsreport.

Edit: I fixed the above problem by copying jsreportProxy.js to \jsreport-cli\lib (is that a bug needs reporting, or my bad installation/config?). I'll try working some code in now and see how I get on.

Adam Richards

Should it work if I don't assign a data source in the studio? I'm happy to upload my code/etc if you let me know the easiest and best way to do it, but as soon as I deselect a data source in the studio, it falls over with the Error: template input in request contain values that does not match the defined schema. schema validation errors: template.data should be objecterror, even with nothing included in the template.

Adam Richards

Out of interest, should I be able to accomplish this with Resources extension? I've installed it, it lets me choose which data sources I want, but using the notation {{$resource.course.CourseName}} I still get the error. I get the error in the reply above whenever I don't choose a data source from the drop-down in the studio.

Adam Richards

I just wanted to update to say I think the problem must be in my template somewhere, and I don't know where. I copied the HTML out to a new template, didn't choose a data source and set the resources up the same way and it works. It seems that it didn't like having a data source chosen and then removed?

jan_blaha

It seems that it didn't like having a data source chosen and then removed?

Thank you. I can replicate it. Ugly bug, working on fix now.

Error while executing templating engine. Unable to find module jsreport-proxy

I was not able to replicate this so far. This happens when rendering from jsreport studio? Do you have the latest version installed? The proxy is installed from v2 by default. No other step needed.

Out of interest, should I be able to accomplish this with Resources extension?

Yes this works. If you attach two "data" items as resources. The course should be name of that data item. And courseName a property inside.

Adam Richards

Hi, thanks, yes I got that working now.

If I can be a pain and ask, is it possible to iterate over two nest each loops to match data in the two sources? As per my original idea.

So I have a data source called course, which has a list of associated modules, held in array, with an identifier 'modulecode'. I have a second data source called modules which have the same 'modulecode' identifier, but then extended information like name, duration etc.

What l'd like to do is to step over each module code in courses, then loop over the array of modules and output the name for example on a match. Is it possible?

I understand that for each loop of the each, I can refer to the current record as 'this.', but what happens when it's nested? I need the equivalent of this pseudocode:

{{#each $resource.course.module}}
  <p>Module {{11}}: </p>
  {{#each $resource.modules.modules}}
    {{#if this.modulecode **meaning the modules data source** == parent.modulecode  **is there a concept of parent for nests?**}}
      {{this.modulename}}
    {{/if}}
  {{/each}}
{{/each}}

Does that make sense? Is it achievable without merging the datasets? Is it using @root as above?

jan_blaha

I understand the main problem is reaching the parent scope inside handlebars template...
Usually you find the best answers when googling: handlebars access parent scope. This leads you to something like:

{{#each items}}
    <div style="font-size:{{../itemSize}}px">{{this}}</div>
    {{#if this.items.someKey}}
       <div style="font-size:{{../../itemSize}}px">{{this}}</div>  
    {{/if}}
{{/each}}

So in other words you can reach parent scope using ../. Hope this helps.

Adam Richards

Thanks, it really does. The problem I'm having now is that when I try to each loop with a different resource as the source the nested loop, nothing happens.

 <tr>
                <td>{{#each $resource.course.Modules}}
                     {{this.[Module Code]}}
                        {{#each $resource.modules.modules}}
                         <p>1</p>
                        {{/each}}
                    {{/each}}
                </td>
            </tr>

The <p>1</p> is in there for my own testing. it never gets printed/output, like the inner loop just isn't happening.

Adam Richards

Ignore that, I fixed it by changing {{each $resource.modules.modules}} to {{each ../$resource.modules.modules}}. I didn't realise that I'd have to be that explicit when using $resource.

jan_blaha

Yes, the $resource is top level prop on input data. So you cannot use it inside #each. This is the part where you should use the ../