Web Report - dynamic data - display as many templates of data as there are number of dynamic rows

[NGM]

Hi,
I’m trying to produce a report that will display x number of template of data (formatted in a container).

The data is from a mysql query and x is the number of rows returned.

I have tried

• using the inbuilt generators - the table doesn’t allow me to present the data in a useable way, the form generator seems to be for posting data, the paging generator seems close but it requires the user to select the page rather than seeing them all
• tried using Repeat and Repeat Children but couldnt get to work

I would appreciate any thoughts on how i might be able to do this.

Many thanks,

[Teodor]

Why not build your “formatted data container” and put it in a repeat region, so that it repeats as many times as your records are?

[NGM]

Hi Teodor,

Sounds perfect , is there any documentation or examples of how to do this,

Thanks

[Teodor]

Maybe explain what are you trying to do?
You can just use the Bootstrap structure, build your static layout in acontainer> row > col structure and then apply the repeat region the row and apply dynamic bindings inside …

[NGM]

Many thanks, i wasnt aware /used the repeat region before, but looking at it and with your comments i think i know how to proceed.

Regards

[ben]

Cards are an ideal candidate for such a container

Cards

[NGM]

Thanks Ben, I am making some progress with the repeat region but will also look at the cards option.

The problem I now have is getting all the data into a single expression to use for the repeat. The specific problem is that when using the add columns property if i use the expression seen in the attached screenshot I seem to get nested results and not just the single answer expected. If i change the add column set value to NumOfQs[0].NumOfAns then for each repeated row i just get the first row answer. Whilst not really a surprise i am trying to figure out how i can just get the specific value only in each row - see screenshot where i highlight QID 1186 - hope this makes sense… probably a simple solution but when you dont know you dont know … Each of the queries being added have a common column QID
Many thanks

[ben]

@NGM

NumOfQs[0]

This is an array. Therefore it should be placed this in a repeat area.

Edit: I get the impression that you are making too much of this. I do not understand the number of (custom) queries. Surely one query should give you the results that you want. Giving us a better view of the database tables, should allow us to help you better.

[NGM]

Hi Ben,
Quite possibly the application creates questionnaires and then it gives some analysis of all the different answers. One analysis is to compare a specific persons answers to x questions to a company wide view of the same questions (the person is part of that company) and compare to all the answers we have (all our customers) to give a benchmark view.

• Each Question can have upto 5 answers
• so… I have 5 queries for each of these (person, company and all) which count the number of answers for each question
  screenshot 1 - shows a query that returns the count of answers = 3 (or actually >2 and <=3) for customer
  screenshot 2 - shows the same for answers = 4

I report the data in two ways -

1. interactive where it shows the current questions view and the user can go to next or previous question - see screenshot3
2. for a pdf report which shows all the answers, hence the need for the repeat region or possibly cards to get all the data !
   
   
   
   (upload://a3q7qkWdpV5l1ftAAqJeuz83h7c.jpeg)
   

Ben, I would be very grateful if you could point to ways in which i can combine some or all of these datasets into 1 or fewer number of queries.

Thanks

[ben]

It is getting late down under. Please give me a bit of time so that I can sleep on it.

Ben

[NGM]

Hi Ben, Very kind of you, thanks.

I’ve also attached a screenshot of the key table Questionnaireresponse which contains all the answers to the questionnaires:
UQnID - is the unique questionnaire ID which states which Questions (QID) are included in it
QUserAns - is the button the person pushed which can be from between 2 and 5 answers

Other Tables
Questionnaireids - contains the link between the Customer ID (CID) and the questionnaires (UQnID)
Questions - which contain the questions, answers etc.

[NGM]

Hi Ben, did you manage to think of any way to either reduce the number of queries to 1 or how to use join, add columns to create a single query.
No problem if you haven’t… I need to decide to either continue trying to find a solution or to rethink how I could present the data in another way.
All the best

[ben]

Apologies for the late reply, still trying to figure out what you are doing.

NumOfQs[0].QID and getcid[0].CID both return the first entry in an array. Is that the aim? Where are these values defined?

Also trying to figure out why you have not assigned a value of 3 instead of

[NGM]

Hi Ben,
many thanks for trying to help.
I’m trying to create the attached report

The question template is in a repeat region and as you can see it properly contains the question and answer options. i need to add in the answer data which are all the queries using Add columns to the initial query GetQStat (which successfully contains all the Question+answer option data that you can see in the attachment).
In the Add Column i dont seem to be able to add in the array of answer details - if i use {{GetEStat[0].EAns}} then i only get the first row , if i use {{GetEStat.EAns}} then i seem to get a nested array which the repeat region doesnt recognise. What i want is

for the first question e.g. QID 914 to add the answer data associated with 914 to the GetQStat query… but however i try and use add column or the Join command i cant get what i need for the nested repeat to work !!

So i think the questions are

1. How can i add the queries GQCA1-GQCA5, GCCA1-GCCA5, GECA1-GECA5 to GetQStat so that the resulting query aligns the queries so i achieve the above
2. Can i create a single query that does all this so i havent got to try and manipulate the data use Add Column, Join etc functions.

Regarding your questions

• …[0]… - the aim is to get all the entries added to GetQStat matched to the QID
• …<=3 & >2 at one point QUserAns could have had values like 2.3 but i think i have changed that so i probably can change to what you suggest, but my bigger issues are the creation of the single query with all the data that the repeat region can use.

Best regards.

[ben]

Have a look at

Nested Repeat Regions

In this tutorial we will show you how to nest database queries. This is useful when you have two separate tables, one containing your categories and another one containing your products. You can nest the products query in the category query and filter it by the category ID. Other usage case could be a dynamic navbar with dynamic dropdowns, so you nest the dropdown items under each parent item. Let’s start with the server action. Open the Server Connect panel: [Screenshot_40] Add new server …

where the questions can be the categories in the tutorial and the answers can be the products.

I may still have misinterpreted the problem, in which case please come back.

[NGM]

thanks