how to print large number of fields in a report

[Nifrabar]

I am having a table with 5 rows about 50 columns.
Each column holds content of a year (2001-2002-2003 etc)

Can I make report showing:

      field1 field2 field3 ...... field10
row 1
row 2
row 3
row 4
row 5

      field11 field12 field13 ...... field20
row 1
row 2
row 3
row 4
row 5

-
-
-
-

      field41 field42 field43 ...... field50
row 1
row 2
row 3
row 4
row 5

TIA
-Bart

 

[Olaf Doschke]

I'd turn the cursor structure the way, that it has ten fields and the data of fields 11-20, 21-30, 31-40 and 41-50 should be put in new records instead of the columns they are in, then you can print fields 1-10 only.

Bye, Olaf.

 

[JRB-Bldr]

"Each column holds content of a year"
But you also say that you have "50 columns"

So are you telling us that you have 50 years of data?

We need to be more clear on what you are trying to accomplish before we can come up with better suggestions.

But for starters, like Olaf suggests above, the table you talk about might not be the best organization for your needs.

Going on a wild guess as to what you want, I'd recommend an alternative original data structure like:
DATE Field1 Field2 Field3 Field4 Field5 .....
or
YEAR Field1 Field2 Field3 Field4 Field5 .....

Continuing to work on the same wild guess - Then prior to your report you can accumulate the data in a transposed manner so that each Column in the report can represent a different YEAR and the Rows can represent the Field values (either summarized or not).

Field1Yr1 Field1Yr2 Field1Yr3 Field1Yr4 Field1Yr5 ...
Field2Yr1 Field2Yr2 Field2Yr3 Field2Yr4 Field2Yr5 ...
Field3Yr1 Field3Yr2 Field3Yr3 Field3Yr4 Field3Yr5 ...
and so on

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Bart,

I agree with Olaf and JRB-Bldr.

In general, where you have a "difficult" report, it's best to design a cursor to fit the report, rather than trying to figure out how to do the report from the existing cursor.

In this case, I'd suggest that, for each record in your original table, you create five new records, each with ten columns. Give each record a sequence number field, 1 to 5.

The report can then show the ten columns across the page, with grouping on the sequence number.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Nifrabar]

I realise my question was not exactly reflection the actual situation.
I do have a table with columns (just in words):
-StartYear (I)
-nNumberOfYears(I)
-nAmount(€)
-nIntendedFor(I)

Than I transferred the contents of this table by:

	CREATE CURSOR CurOntvangen ;
	(nBet_ID I, nSoort I, nStartYear I, nAantYear I,;
	n2001 Y(9),n2002 Y(9), n2003 Y(9), n2004 Y(9), n2005 Y(9), n2006 Y(9), n2007 Y(9),  n2008 Y(9), n2009 Y(9), n2010 Y(9),;
	n2011 Y(9),n2012 Y(9), n2013 Y(9), n2014 Y(9), n2015 Y(9), n2016 Y(9), n2017 Y(9),  n2018 Y(9), n2019 Y(9), n2020 Y(9),;
	n2021 Y(9),n2022 Y(9), n2023 Y(9), n2024 Y(9), n2025 Y(9), n2026 Y(9), n2027 Y(9),  n2028 Y(9), n2029 Y(9), n2030 Y(9),;
	n2031 Y(9),n2032 Y(9), n2033 Y(9), n2034 Y(9), n2035 Y(9), n2036 Y(9), n2037 Y(9),  n2038 Y(9), n2039 Y(9), n2040 Y(9),;
	n2041 Y(9),n2042 Y(9), n2043 Y(9), n2044 Y(9), n2045 Y(9), n2046 Y(9), n2047 Y(9),  n2048 Y(9), n2049 Y(9), n2050 Y(9),;
	nSort I, cAttribuut c(1))

*	USE tbetaling IN 0
	SELECT nBet_ID, nSoort, nStartYear, IIF(tBetaling.nAantYear > 0 , tBetaling.nAantYear , 1) as nAantYear ,;
	IIF(nMutBedrag>0 , nMutBedrag/IIF(nAantYear>0,nAantYear,1),0) as nJaarBedrag,nMutBedrag , dValdat;
		FROM tBetaling ;
		INTO Cursor CurBetaling ;
		WHERE nStatus = 1 AND nMutBedrag <> 0

	SELECT CurBetaling

	SCAN
		lnBet_ID 		= CurBetaling.nBet_ID
		lnSoort	 		= CurBetaling.nSoort
		lnStartYear 	= CurBetaling.nStartYear
		lnAantYear 		= CurBetaling.nAantYear
		lnJaarBedrag 	= CurBetaling.nJaarBedrag
		
		FOR I = 0 TO lnAantYear-1
			lnField = lnStartYear + I
			lcField = 'n'+ALLTRIM(STR(lnField))
			INSERT INTO CurOntvangen ('nBet_ID', 'nSoort','nStartYear','nAantYear', (lcField)) ;
			values(lnBet_ID, lnSoort,  lnStartYear, lnAantYear, lnJaarbedrag)
		ENDFOR
		
	ENDSCAN

The result now is the cursor which I wrongly mentioned in the start of this threat as table.
This cursor I use on a form to make the values available for the user.
I have to tailor the contents for them a bit by:

	SELECT * FROM CurOntvangen ;
	LEFT JOIN tTarief ;
	ON CurOntVangen.nSoort = tTarief.nSoort INTO CURSOR CurOntVangen_Voor

	SELECT ;
	NVL(SUM(n2001),0) as n2001, NVL(SUM(n2002),0) as n2002, NVL(SUM(n2003),0) as n2003, NVL(SUM(n2004),0) as n2004, NVL(SUM(n2005),0) as n2005, NVL(SUM(n2006),0) as n2006, NVL(SUM(n2007),0) as n2007, NVL(SUM(n2008),0) as n2008, NVL(SUM(n2009),0) as n2009, NVL(SUM(n2010),0) as n2010,;
	NVL(SUM(n2011),0) as n2011, NVL(SUM(n2012),0) as n2012, NVL(SUM(n2013),0) as n2013, NVL(SUM(n2014),0) as n2014, NVL(SUM(n2015),0) as n2015, NVL(SUM(n2016),0) as n2016, NVL(SUM(n2017),0) as n2017, NVL(SUM(n2018),0) as n2018, NVL(SUM(n2019),0) as n2019, NVL(SUM(n2020),0) as n2020,;
	NVL(SUM(n2021),0) as n2021, NVL(SUM(n2022),0) as n2022, NVL(SUM(n2023),0) as n2023, NVL(SUM(n2024),0) as n2024, NVL(SUM(n2025),0) as n2025, NVL(SUM(n2026),0) as n2026, NVL(SUM(n2027),0) as n2027, NVL(SUM(n2028),0) as n2028, NVL(SUM(n2029),0) as n2029, NVL(SUM(n2030),0) as n2030,;
	NVL(SUM(n2031),0) as n2031, NVL(SUM(n2032),0) as n2032, NVL(SUM(n2033),0) as n2033, NVL(SUM(n2034),0) as n2034, NVL(SUM(n2035),0) as n2035, NVL(SUM(n2036),0) as n2036, NVL(SUM(n2037),0) as n2037, NVL(SUM(n2038),0) as n2038, NVL(SUM(n2039),0) as n2039, NVL(SUM(n2040),0) as n2040,;
	NVL(SUM(n2041),0) as n2041, NVL(SUM(n2042),0) as n2042, NVL(SUM(n2043),0) as n2043, NVL(SUM(n2044),0) as n2044, NVL(SUM(n2045),0) as n2045, NVL(SUM(n2046),0) as n2046, NVL(SUM(n2047),0) as n2047, NVL(SUM(n2048),0) as n2048, NVL(SUM(n2049),0) as n2049, NVL(SUM(n2050),0) as n2050,;
	1 as nSort,'N'as cAttribuut;
	FROM CurOntVangen_Voor WHERE UPPER(cVoor) = 'VW' ;
	union ; 
	SELECT ;
	NVL(SUM(n2001),0) as n2001, NVL(SUM(n2002),0) as n2002, NVL(SUM(n2003),0) as n2003, NVL(SUM(n2004),0) as n2004, NVL(SUM(n2005),0) as n2005, NVL(SUM(n2006),0) as n2006, NVL(SUM(n2007),0) as n2007, NVL(SUM(n2008),0) as n2008, NVL(SUM(n2009),0) as n2009, NVL(SUM(n2010),0) as n2010,;
	NVL(SUM(n2011),0) as n2011, NVL(SUM(n2012),0) as n2012, NVL(SUM(n2013),0) as n2013, NVL(SUM(n2014),0) as n2014, NVL(SUM(n2015),0) as n2015, NVL(SUM(n2016),0) as n2016, NVL(SUM(n2017),0) as n2017, NVL(SUM(n2018),0) as n2018, NVL(SUM(n2019),0) as n2019, NVL(SUM(n2020),0) as n2020,;
	NVL(SUM(n2021),0) as n2021, NVL(SUM(n2022),0) as n2022, NVL(SUM(n2023),0) as n2023, NVL(SUM(n2024),0) as n2024, NVL(SUM(n2025),0) as n2025, NVL(SUM(n2026),0) as n2026, NVL(SUM(n2027),0) as n2027, NVL(SUM(n2028),0) as n2028, NVL(SUM(n2029),0) as n2029, NVL(SUM(n2030),0) as n2030,;
	NVL(SUM(n2031),0) as n2031, NVL(SUM(n2032),0) as n2032, NVL(SUM(n2033),0) as n2033, NVL(SUM(n2034),0) as n2034, NVL(SUM(n2035),0) as n2035, NVL(SUM(n2036),0) as n2036, NVL(SUM(n2037),0) as n2037, NVL(SUM(n2038),0) as n2038, NVL(SUM(n2039),0) as n2039, NVL(SUM(n2040),0) as n2040,;
	NVL(SUM(n2041),0) as n2041, NVL(SUM(n2042),0) as n2042, NVL(SUM(n2043),0) as n2043, NVL(SUM(n2044),0) as n2044, NVL(SUM(n2045),0) as n2045, NVL(SUM(n2046),0) as n2046, NVL(SUM(n2047),0) as n2047, NVL(SUM(n2048),0) as n2048, NVL(SUM(n2049),0) as n2049, NVL(SUM(n2050),0) as n2050,;
	2 as nSort,'N'as cAttribuut;
	FROM CurOntVangen_Voor WHERE UPPER(cVoor) = 'GR' ;
	Union ;
	SELECT ;
	NVL(SUM(n2001),0) as n2001, NVL(SUM(n2002),0) as n2002, NVL(SUM(n2003),0) as n2003, NVL(SUM(n2004),0) as n2004, NVL(SUM(n2005),0) as n2005, NVL(SUM(n2006),0) as n2006, NVL(SUM(n2007),0) as n2007, NVL(SUM(n2008),0) as n2008, NVL(SUM(n2009),0) as n2009, NVL(SUM(n2010),0) as n2010,;
	NVL(SUM(n2011),0) as n2011, NVL(SUM(n2012),0) as n2012, NVL(SUM(n2013),0) as n2013, NVL(SUM(n2014),0) as n2014, NVL(SUM(n2015),0) as n2015, NVL(SUM(n2016),0) as n2016, NVL(SUM(n2017),0) as n2017, NVL(SUM(n2018),0) as n2018, NVL(SUM(n2019),0) as n2019, NVL(SUM(n2020),0) as n2020,;
	NVL(SUM(n2021),0) as n2021, NVL(SUM(n2022),0) as n2022, NVL(SUM(n2023),0) as n2023, NVL(SUM(n2024),0) as n2024, NVL(SUM(n2025),0) as n2025, NVL(SUM(n2026),0) as n2026, NVL(SUM(n2027),0) as n2027, NVL(SUM(n2028),0) as n2028, NVL(SUM(n2029),0) as n2029, NVL(SUM(n2030),0) as n2030,;
	NVL(SUM(n2031),0) as n2031, NVL(SUM(n2032),0) as n2032, NVL(SUM(n2033),0) as n2033, NVL(SUM(n2034),0) as n2034, NVL(SUM(n2035),0) as n2035, NVL(SUM(n2036),0) as n2036, NVL(SUM(n2037),0) as n2037, NVL(SUM(n2038),0) as n2038, NVL(SUM(n2039),0) as n2039, NVL(SUM(n2040),0) as n2040,;
	NVL(SUM(n2041),0) as n2041, NVL(SUM(n2042),0) as n2042, NVL(SUM(n2043),0) as n2043, NVL(SUM(n2044),0) as n2044, NVL(SUM(n2045),0) as n2045, NVL(SUM(n2046),0) as n2046, NVL(SUM(n2047),0) as n2047, NVL(SUM(n2048),0) as n2048, NVL(SUM(n2049),0) as n2049, NVL(SUM(n2050),0) as n2050,;
	3 as nSort,'N'as cAttribuut;
	FROM CurOntVangen_Voor  WHERE UPPER(cVoor) = 'URN';
	Union ;
	SELECT ;
	NVL(SUM(n2001),0) as n2001, NVL(SUM(n2002),0) as n2002, NVL(SUM(n2003),0) as n2003, NVL(SUM(n2004),0) as n2004, NVL(SUM(n2005),0) as n2005, NVL(SUM(n2006),0) as n2006, NVL(SUM(n2007),0) as n2007, NVL(SUM(n2008),0) as n2008, NVL(SUM(n2009),0) as n2009, NVL(SUM(n2010),0) as n2010,;
	NVL(SUM(n2011),0) as n2011, NVL(SUM(n2012),0) as n2012, NVL(SUM(n2013),0) as n2013, NVL(SUM(n2014),0) as n2014, NVL(SUM(n2015),0) as n2015, NVL(SUM(n2016),0) as n2016, NVL(SUM(n2017),0) as n2017, NVL(SUM(n2018),0) as n2018, NVL(SUM(n2019),0) as n2019, NVL(SUM(n2020),0) as n2020,;
	NVL(SUM(n2021),0) as n2021, NVL(SUM(n2022),0) as n2022, NVL(SUM(n2023),0) as n2023, NVL(SUM(n2024),0) as n2024, NVL(SUM(n2025),0) as n2025, NVL(SUM(n2026),0) as n2026, NVL(SUM(n2027),0) as n2027, NVL(SUM(n2028),0) as n2028, NVL(SUM(n2029),0) as n2029, NVL(SUM(n2030),0) as n2030,;
	NVL(SUM(n2031),0) as n2031, NVL(SUM(n2032),0) as n2032, NVL(SUM(n2033),0) as n2033, NVL(SUM(n2034),0) as n2034, NVL(SUM(n2035),0) as n2035, NVL(SUM(n2036),0) as n2036, NVL(SUM(n2037),0) as n2037, NVL(SUM(n2038),0) as n2038, NVL(SUM(n2039),0) as n2039, NVL(SUM(n2040),0) as n2040,;
	NVL(SUM(n2041),0) as n2041, NVL(SUM(n2042),0) as n2042, NVL(SUM(n2043),0) as n2043, NVL(SUM(n2044),0) as n2044, NVL(SUM(n2045),0) as n2045, NVL(SUM(n2046),0) as n2046, NVL(SUM(n2047),0) as n2047, NVL(SUM(n2048),0) as n2048, NVL(SUM(n2049),0) as n2049, NVL(SUM(n2050),0) as n2050,;
	4 as nSort,'N'as cAttribuut;
	FROM CurOntVangen_Voor  WHERE UPPER(cVoor) = 'DIV';
	into CURSOR totaal
	SELECT totaal

	SELECT ;
	SUM(n2001) as n2001,SUM(n2002) as n2002,SUM(n2003) as n2003,SUM(n2004) as n2004,SUM(n2005) as n2005,SUM(n2006) as n2006,SUM(n2007) as n2007,SUM(n2008) as n2008,SUM(n2009) as n2009,SUM(n2010) as n2010,;
	SUM(n2011) as n2011,SUM(n2012) as n2012,SUM(n2013) as n2013,SUM(n2014) as n2014,SUM(n2015) as n2015,SUM(n2016) as n2016,SUM(n2017) as n2017,SUM(n2018) as n2018,SUM(n2019) as n2019,SUM(n2020) as n2020,;
	SUM(n2021) as n2021,SUM(n2022) as n2022,SUM(n2023) as n2023,SUM(n2024) as n2024,SUM(n2025) as n2025,SUM(n2026) as n2026,SUM(n2027) as n2027,SUM(n2028) as n2028,SUM(n2029) as n2029,SUM(n2030) as n2030,;
	SUM(n2031) as n2031,SUM(n2032) as n2032,SUM(n2033) as n2033,SUM(n2034) as n2034,SUM(n2035) as n2035,SUM(n2036) as n2036,SUM(n2037) as n2037,SUM(n2038) as n2038,SUM(n2039) as n2039,SUM(n2040) as n2040,;
	SUM(n2041) as n2041,SUM(n2042) as n2042,SUM(n2043) as n2043,SUM(n2044) as n2044,SUM(n2045) as n2045,SUM(n2046) as n2046,SUM(n2047) as n2047,SUM(n2048) as n2048,SUM(n2049) as n2049,SUM(n2050) as n2050,;
	5 as nSort,'V'as cAttribuut;
	from totaal into cursor sums
	SELECT sums


	SELECT * FROM totaal UNION select * FROM sums INTO CURSOR xxxxx ORDER BY nSort

	SELECT xxxxx

	.xgrid1.RecordSource='xxxxx'
	FOR i = 1 TO 50
		WITH .xgrid1.Columns[i] 
			.header1.caption = ALLTRIM(STR(2000+i))
			.inputmask = '9,999,999'
			.format = 'Z'
			.Width = 50
			.Dynamicfontbold = [cAttribuut = 'V']
		ENDWITH
	ENDFOR
	FOR i = 51 TO 52
		WITH .xgrid1.Columns[i] 
			.visible = .F.
		ENDWITH
	ENDFOR

Maybe this gives more access for your estimeeded help.

KR
-Bart