7.4 Beta: Value set relational table

Other discussions about CSPro
Forum rules
New release: CSPro 8.0
Post Reply
htuser
Posts: 631
Joined: December 19th, 2011, 6:26 pm
Location: Silver Spring Area, MD, USA

7.4 Beta: Value set relational table

Post 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,
G.VOLNY, a CSProuser from Haiti, since 2004
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: 7.4 Beta: Value set relational table

Post 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.
htuser
Posts: 631
Joined: December 19th, 2011, 6:26 pm
Location: Silver Spring Area, MD, USA

Re: 7.4 Beta: Value set relational table

Post 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,
G.VOLNY, a CSProuser from Haiti, since 2004
josh
Posts: 2399
Joined: May 5th, 2014, 12:49 pm
Location: Washington DC

Re: 7.4 Beta: Value set relational table

Post 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.
htuser
Posts: 631
Joined: December 19th, 2011, 6:26 pm
Location: Silver Spring Area, MD, USA

Re: 7.4 Beta: Value set relational table

Post 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,
G.VOLNY, a CSProuser from Haiti, since 2004
Post Reply