7.4 Beta: Value set relational table

Other discussions about CSPro
Post Reply
htuser
Posts: 461
Joined: December 19th, 2011, 6:26 pm
Location: HAITI

7.4 Beta: Value set relational table

Post by htuser » March 30th, 2020, 2:40 pm

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,

josh
Posts: 2140
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: 7.4 Beta: Value set relational table

Post by josh » March 30th, 2020, 7:06 pm

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.

htuser
Posts: 461
Joined: December 19th, 2011, 6:26 pm
Location: HAITI

Re: 7.4 Beta: Value set relational table

Post by htuser » March 30th, 2020, 7:14 pm

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,

josh
Posts: 2140
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: 7.4 Beta: Value set relational table

Post by josh » March 30th, 2020, 9:22 pm

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.

htuser
Posts: 461
Joined: December 19th, 2011, 6:26 pm
Location: HAITI

Re: 7.4 Beta: Value set relational table

Post by htuser » March 31st, 2020, 10:49 am

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,

Post Reply