Generate Report

[BK Singh]

Dear Experts,
Please suggest a logic to generate a date-wise report. In each case, I am capturing the date of the interview and the type of interview. Now I am trying to generate a report having details like:-
Date - Type of interview - Number of interview
01-01-2024 - Students - 25
01-01-2024 - Teacher - 4
01-01-2024 - Principal - 1
02-01-2024 - Students - 25
02-01-2024 - Teacher - 4
02-01-2024 - Principal - 1
03-01-2024 - Students - 25
03-01-2024 - Teacher - 4
03-01-2024 - Principal - 1

Thank you....

[justinlakier]

Hi,

CSPro can generate an HTML templated report file when you go to File -> Add Files -> Report. I would erase the report contents of the basic report example and add something like this.

Code: Select all

<table class="table table-striped table-bordered">
		<thead>
			<tr>
				<th>Date</th>
				<th>Type of Interview</th>
				<th>Number of Interviews</th>
			</tr>
		</thead>
<?
//(list of dates, dateList, and list of types, typeList)
//(included interview database INTERVIEW with fields intvw_date and intvw_type)
do numeric a = 1 while a <= dateList.length()
	numeric tableDate = dateList(a);
	do numeric b=1 while b <= typeList.length()
		numeric tableType = dateList(b);
		numeric dateRoleCount = COUNT(INTERVIEW where intvw_date=tableDate and intvw_type=tableType);
		$.write("<tr>");
		$.write(maketext("<td>%d - </td>", tableDate);
		$.write(maketext("<td>%d - </td>", tableType);
		$.write(maketext("<td>%d</td>", dateRoleCount);
		$.write("</tr>");
	enddo;
enddo;
?>
</table>

The top section makes a table with HTML. The sections between the <?...?> are CSPro rather than HTML, and use $.write to add to that table. Variable names INTERVIEW, intvw_date, intvw_type, dateList and typeList are examples and will likely be different for your implementation. If you don't have a date List and type List, you can generate them easily using a Forcase statement to loop over the dictionary and add every new date or role to their lists.

Hope this helps,
Justin

[BK Singh]

Thanks for the help sir, but can you suggest it for .txt file?

[justinlakier]

The CSPro logic between the <? and ?> symbols is mostly the same for txt files. The difference is that you do not need to run it in an HTML file or use $.write() to write the data to that HTML file. Instead, you can run the CSPro code as a function in a CSPro application, and output the data to a text file using File.write(), opening and later closing a text file as in the example. Since the data is being inserted into a text file instead of into an HTML table with headings already in place, you will have to write the headings (Date, Type of Interview, Number of Interviews) to the file before the loop. You also do not need to write "<tr></tr>" or "<td></td>" to the text file as these are HTML table tags. Instead, you can write "\n" for newlines between rows and "\t" for tabs between columns.

Hope this helps,
Justin

[BK Singh]

Dear Expert,
I am using this code for generating .txt file.. But it is not working correctly.. It is generating .txt file only for last case entered.

Code: Select all

string datafile = "../DATA/" + maketext("%v_%2s_%v.csdb" ,strip(loadsetting("intname")),loadsetting("intcode"), strip(getdeviceid()));

setfile(INTERVIEW_DICT , datafile);
{list string enteredkeys;
keylist(INTERVIEW_DICT , enteredkeys);}
forcase INTERVIEW_DICT do 
list dateList = DOV;
list statecode = S_CODE;
list string typeList = getvaluelabel(MENU);
list visitlist = V_NO;
list string distlist = D_NAME;
list string psulist = PSU_NAME;
	do numeric a = 1 while a <= dateList.length()
			numeric tableDate = dateList(a);
			string tableType = typeList(a);
			numeric tablevisit = visitlist(a);
			string tabledist = distlist(a);
			string tablepsu = psulist(a);
			string reportfile = "../DATA/"+maketext("%v-Report.txt" , sysdate("ddmmyyyy"));
			file repor;
					setfile(repor , reportfile , create);
								open(repor);
								repor.write(maketext(" Date  Visit			District			PSU Name			VisitNo"));
								{repor.write(maketext("%d	%v	%v	%2s	%v", dateList(a) , typeList(a) ,  distlist(a), strip(psulist(a)), visitlist(a)));}[*]
								repor.write(maketext("%d 	", tableDate));
								repor.write(maketext("\t 	"));
								repor.write(maketext("%v 	", tableType));
								repor.write(maketext("%v	", tabledist));
								repor.write(maketext("%v	", tablepsu));
								repor.write(maketext("%d	", tablevisit));
								
								close(repor);
						view(filename(repor));
					
	enddo;
enddo;
end;

[Gregory Martin]

Your forcase loop iterates over every case in your data file. Within that loop, you are creating your report, so that means that you are creating it again and again for every case. Move that file/setfile code before the forcase loop so that it only executes once for your entire data file.

[BK Singh]

Dear Experts,
Thanks for the prompt replies. I tried as you seggested but it is not giving desired result for .txt file but it is working fine in .html formate.
Also please suggest the way to use \n, \t and \f.

Code: Select all

function viewrepo()
	string datafile = "../DATA/" + maketext("%v_%2s_%v.csdb" ,strip(loadsetting("intname")),loadsetting("intcode"), strip(getdeviceid()));
	string reportfile = "../DATA/"+maketext("%v-Report.txt" , sysdate("ddmmyyyy"));
	file repor;
	setfile(repor , reportfile , create);
setfile(INTERVIEW_DICT , datafile);
list string enteredkey;
keylist(INTERVIEW_DICT, enteredkey);
	forcase INTERVIEW_DICT do 
		list dateList = DOV;
		list statecode = S_CODE;
		list string typeList = getvaluelabel(MENU);
		list visitlist = V_NO;
		list string distlist = D_NAME;
		list string psulist = PSU_NAME;

	do numeric a = 1 while a <= dateList.length()
			numeric tableDate = dateList(a);
			string tableType = typeList(a);
			numeric tablevisit = visitlist(a);
			string tabledist = distlist(a);
			string tablepsu = psulist(a);
		
								open(repor);
								repor.write(maketext(" Date  		Visit			District			PSU Name			Visit No"));
								repor.write(maketext("%d	%v	%v	%2s	%v", tableDate , tableType ,  tabledist, strip(tablepsu), tablevisit));
								repor.write(maketext("%d 	", tableDate));
								repor.write(maketext("\t 	"));
								repor.write(maketext("%v 	", tableType));
								repor.write(maketext("%v	", tabledist));
								repor.write(maketext("%v	", tablepsu));
								repor.write(maketext("%d	", tablevisit));
								
								close(repor);
						
					{enddo;
				enddo;
			enddo;
		enddo;}
	enddo;
enddo;
view(filename(repor));
end;

[justinlakier]

\n, \t, and \f are escape characters. During the writing \n will be replaced by a newline and \t will be replaced by a tab. Use them in text where you want to insert a newline or tab rather than writing a large number of spaces. You can print an escape character literally by escaping it with another backslash, so \n will print a newline and \\n will print \n. \f is for form feed, used for a new page when printing. This is detailed more in the File.write() documentation.

Why is what you are getting not the desired result for the .txt file? Is the problem with opening the correct file, or with writing the contents to that file in the correct format? Knowing how the result differs from what you want will help us fix it.

[BK Singh]

Dear Expert,
The code is not writing what I want... Please suggest..
And
Is there any way of writing row number (index) in the first column of table...

[justinlakier]

Yes, you can write the row number in the first column of the table. Since the writes all happen in the loop "do numeric a = 1", the value of the variable "a" will contain the row number and you can write that to the report before writing the tableDate as the first column. You should also include a a label for this column in the top row so it doesn't throw off your existing labels "Date, Visit, District, PSU Name, Visit No". Note that in the HTML report I gave you it loops through both Date and Type to summarize every type of interview on every date. In that case rather than just keeping track of the loops through dateList with "a" or the loops through typeList with "b" you would have to create a new numeric variable "c" outside of both loops, which increments whenever a new row is added and can be printed in that row as the row number.

The code you have put for writing the report to a .txt file looks different from the more functional code used to write to HTML. The code loops through INTERVIEW_DICT and sets "dateList = DOV", however this means that "datelist" isn't actually used as a list at all. The same is true for the other lists. They only contain the value of the from the current case. Instead of setting list dateList = DOV, you should be adding the value to the list with each loop with dateList.add(DOV). The same format goes for the other lists stateCode, typeList, visitList, distList, and psuList. You should only begin looping through dateList after you have finished your loop through the dictionary and are done adding to dateList. The two loops should not be nested, the INTERVIEW_DICT loop is done first to set up the lists and the "do numeric a = 1 while a <= dateList.length()" loop is done second to use the lists.

Hope this helps,
Justin