Page 1 of 1
7.4 Beta: Value set relational table
Posted: March 30th, 2020, 2:40 pm
by htuser
Dear CSPro Developer Team,
I would like that relational CSDB have value set table. This is very important to have it, since right now, with sqlquery we would be able to fill templated reports and other with labeled item.
Please let's know!
Best Regards,
Re: 7.4 Beta: Value set relational table
Posted: March 30th, 2020, 7:06 pm
by josh
The problem is keeping the value sets in the csdb file in sync with the dictionary. Every time the data file is opened we would have to compare the all value sets in the dictionary with the all the value sets in the database. Value sets are often very large (profession and industry codes can have thousands of entries). This could significantly slow down opening data files for everyone even though few users would use them.
Re: 7.4 Beta: Value set relational table
Posted: March 30th, 2020, 7:14 pm
by htuser
You're right about slowing the Csdb/Csdbe files. However, for labellisation, i don't see how to combine getvaluelabel with a sqlquery and extracting data with theirs labels in setreportdata. Please do you have some idea about?
Best,
Re: 7.4 Beta: Value set relational table
Posted: March 30th, 2020, 9:22 pm
by josh
You can use the case statement in your query to add in labels:
Code: Select all
SELECT first_name, last_name, CASE sex when 1 then 'Male' when 2 then 'Female' END AS sex from person_rec
You could even generate the case statements from the value sets in CSPro logic.
Code: Select all
string query = "SELECT first_name, last_name, CASE sex";
do numeric i = 1 while i <= SEX_VS1.length()
query = query + " when " + maketext("%d", SEX_VS1.codes(i)) + " then '" + SEX_VS1.labels(i) + "'"
enddo;
query = query + " END AS sex from person_rec";
Alternatively you could do something similar to generate the valueset tables yourself.
Ultimately though the best way to get reports with value set labels in them is really not with sql but just to improve setreportdata() so that it supports using labels when passed dictionary items/records. We have discussed doing this in a future release but haven't scheduled it yet.
Re: 7.4 Beta: Value set relational table
Posted: March 31st, 2020, 10:49 am
by htuser
Very well and thanks a lot Josh.
This is a very good solution. When the size of the questionnaire is small, we can create valueset table and use them in query.
But, when size is huge, we can use example you posted, who solve lot of using (among other, in showarray). Also, we can see how flexible and powerful CSPro is when allowing sqlquery on questionnaire data.
Ultimately though the best way to get reports with value set labels in them is really not with sql but just to improve setreportdata() so that it supports using labels when passed dictionary items/records. We have discussed doing this in a future release but haven't scheduled it yet.
I'm agree with you that, improving setreportdata() is the best way for labellisation of record and items in reports. Hope you already put it on the
To Do List!
Best regards,