how to show one record per one page

I am using reporting services to build a report to show customer's balance statements. I need each page only show one customer and related transactions and balance etc.

Now it shows several customers' balance in one page, how can i change it to show only one record per one page, and also one customer per one page when print it out.

how can i achieve that?

cheers

place all your controls in a list control

in the properties for the list control

group by customer

check the "page brake before" option

How to set up related tables

I have a bunch of data related to a property. On that property, we have
several measurement requirements to perform several calculations. For
instance, I may have data that currently am planning to set up as follows:
PropertyInformation table:
PropertyAddressID, ResidentialPropertyTypeID, PropertyOwner,
CorporatePropertyTypeID
PropertyMeasurement table
PropertyInformationID
Measurement1 (related to Corporate property only)
Measurement 2 (related to Corporate property only)
Measurement 3 (related to Residential property only)
Measurement 4 (related to Residential property only)
Measurement 5 (related to both Corporate and Residential)
Measurement 6 (related to both Corporate and Residential)
Calculation table:
PropertyMeasurementID (FK)
Total (result of Measurements 2, 4, 5, 6)
Date (date calculation done)
In order to perform the final calculations, we must have one residential
property type and one corporate type (which comes from other lookup tables.
I originally was going to base the 2 latter tables on the
PropertyInformation; however, we may not have any property information
whatsoever when the measurements are recorded, all we know is we have one
ResidentialPropertyType and one CorporatePropertyType, but we may not even
know what that type is. My thought is to force an "Unknown" for property
type for the ResidentialPropertyTypeID (the CorporatePropertyTypeID is the
only information we collect on the property, all other fields in that table
relate to the ResidentialProperty, but the combination of the Corporate and
Residential measurements are required for the calculations). And we may
have Property data but no calculations are being done at this time.
My question is not really what to do with the PropertyInformation table
(although I'm open to suggestions), it's what to do with the Measurement
information -- should I leave all measurement information in one table,
requiring 2 fields for Measurements 5 and 6 because there will be
information on both, or should I split out the CorporateMeasurements and the
ResidentialMeasurements? I'm concerned that down the road the measurement
information will change, and it will become more important what properttype
that came from. If I split it out, then my calculations become more
complicated -- do I relate each calculation to the ID of both tables, or do
I fall back on the PropertyInformation to begin with? Or should I split out
only the duplicated fields, then the calculation table would still need to
pull information from both measurement tables...?
I hope this all makes sense, I suspect I may simply have to choose one way
and then stick with it. Any thoughts?--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'd set up the PropertyMeasurement table like this:
CREATE TABLE PropertyMeasurements (
PropertyInformationID INTEGER NOT NULL
REFERENCES PropertyInformation ,
MeasureTypeID TINYINT NOT NULL
REFERENCES MeasureTypes ,
Measure SMALLINT NOT NULL ,
CONSTRAINT PK_PM PRIMARY KEY (PropertyInformationID, MeasureType)
)
I'm not sure where the PropertyInformationID came from, but guessed the
PropertyInformation table. The PK allows only one MeasureType per
PropertyInformationID. If you want to allow more than one MeasureType
per PropertyInformationID you will have to add another attribute to the
table, like a date column.
You didn't say what type of metric unit would be used for the Measure so
I just put SMALLINT. Change to whatever data type you require.
To allow the further expansion of measurement types you should have a
table of MeasureTypes:
CREATE TABLE MeasureTypes (
MeasureTypeID TINYINT IDENTITY(1,1) UNIQUE ,
MeasureTypeName VARCHAR(20) NOT NULL PRIMARY KEY
)
I wouldn't have a calculation table; rather, I'd have a view or stored
procedure to return those calculations.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjCstIechKqOuFEgEQIO+ACbBGgH6AnoXuC1
kmWwAvoJ1uzEQX0Aniuj
3NTuWjMlvl+f8EDFzU9op+bA
=Wmkm
--END PGP SIGNATURE--
Iams wrote:
> I have a bunch of data related to a property. On that property, we have
> several measurement requirements to perform several calculations. For
> instance, I may have data that currently am planning to set up as follows:
> PropertyInformation table:
> PropertyAddressID, ResidentialPropertyTypeID, PropertyOwner,
> CorporatePropertyTypeID
> PropertyMeasurement table
> PropertyInformationID
> Measurement1 (related to Corporate property only)
> Measurement 2 (related to Corporate property only)
> Measurement 3 (related to Residential property only)
> Measurement 4 (related to Residential property only)
> Measurement 5 (related to both Corporate and Residential)
> Measurement 6 (related to both Corporate and Residential)
> Calculation table:
> PropertyMeasurementID (FK)
> Total (result of Measurements 2, 4, 5, 6)
> Date (date calculation done)
> In order to perform the final calculations, we must have one residential
> property type and one corporate type (which comes from other lookup tables
.
> I originally was going to base the 2 latter tables on the
> PropertyInformation; however, we may not have any property information
> whatsoever when the measurements are recorded, all we know is we have one
> ResidentialPropertyType and one CorporatePropertyType, but we may not even
> know what that type is. My thought is to force an "Unknown" for property
> type for the ResidentialPropertyTypeID (the CorporatePropertyTypeID is the
> only information we collect on the property, all other fields in that tabl
e
> relate to the ResidentialProperty, but the combination of the Corporate an
d
> Residential measurements are required for the calculations). And we may
> have Property data but no calculations are being done at this time.
> My question is not really what to do with the PropertyInformation table
> (although I'm open to suggestions), it's what to do with the Measurement
> information -- should I leave all measurement information in one table,
> requiring 2 fields for Measurements 5 and 6 because there will be
> information on both, or should I split out the CorporateMeasurements and t
he
> ResidentialMeasurements? I'm concerned that down the road the measurement
> information will change, and it will become more important what properttyp
e
> that came from. If I split it out, then my calculations become more
> complicated -- do I relate each calculation to the ID of both tables, or d
o
> I fall back on the PropertyInformation to begin with? Or should I split o
ut
> only the duplicated fields, then the calculation table would still need to
> pull information from both measurement tables...?
> I hope this all makes sense, I suspect I may simply have to choose one way
> and then stick with it. Any thoughts?

how to set column name dynamically?

HI chaps

my scenario is that, i have a table in which i have column such as col01,col02,col03....col31

i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col?') and i have to do integer increment in last two character (?01...?31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?

waiting for your reply

regards

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

|||

fafnir wrote:

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

thanx for your reply....

yes i can do it that way but still it will be string (varchar) can you tell me how to execute that statement? i can print the statement but i dont know how will I able to execute a query which is stored in the varaible

|||

sp_executesql @.query

|||

i have written the follwoing script

DECLARE @.CURRENTDATE DATETIME

DECLARE @.STARTDATE DATETIME

DECLARE @.LOOPDATE DATETIME

DECLARE @.NOOFMONTHS INT

DECLARE @.NOOFDAYS INT

DECLARE @.DAYCOUNTER TINYINT

DECLARE @.I INT

DECLARE @.J TINYINT

DECLARE @.DAYNUMBER VARCHAR(6)

/*INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR(BPCODE)

SELECT DISTINCT LTRIM(RTRIM(CZMMCU)) FROM STAGING.DBO.F0007*/

SET @.CURRENTDATE = (SELECT CONVERT(DATETIME,CAST(DATE AS VARCHAR(10))) FROM PRESENTATIONEUROPE.DBO.CALENDAR

WHERE CURRENTDAY = 0 )

--Check for first time execution and set start date accordingly

IF (SELECT MAX(DATE) FROM PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR) IS NOT NULL

SET @.STARTDATE = CONVERT(DATETIME,(SELECT CONVERT(VARCHAR(8),MAX(DATE)) FROM

PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR))

ELSE

SET @.STARTDATE = (SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4),(CAST(MIN(CZYR)AS INT) + 2000))+'-'+

CONVERT(VARCHAR(2),MIN(CZMT))+'-01') FROM STAGING.DBO.F0007 WHERE CZYR = (SELECT MIN(CZYR) FROM STAGING.DBO.F0007))

--GET THE NO OF MONTH DIFFERENCE

SET @.NOOFMONTHS = DATEDIFF(MM,@.STARTDATE,@.CURRENTDATE)

SET @.I = 1

SET @.LOOPDATE = @.STARTDATE

DECLARE @.LOOPDATE2 INT

DECLARE @.QUERY VARCHAR(500)

WHILE (@.I<=@.NOOFMONTHS)

BEGIN

SET @.LOOPDATE2 = (CONVERT(VARCHAR(8),CONVERT(DATETIME, CONVERT(VARCHAR(4),YEAR(@.LOOPDATE))

+'-'+ CONVERT(VARCHAR(2),MONTH(@.LOOPDATE))+'-01'),112))

SET @.J = 1

WHILE (@.J<=31)

BEGIN

IF (@.J<10)

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+CONVERT(VARCHAR(8),@.LOOPDATE,112)+'),

CZTD0'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

ELSE

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+ CONVERT(VARCHAR(8),@.LOOPDATE,112) +'),

CZTD'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

SP_EXECUTESQL @.QUERY

SET @.J = @.J+1

END

PRINT @.LOOPDATE

SET @.I = @.I + 1

END

and getting follwing error

Msg 102, Level 15, State 1, Line 62

Incorrect syntax near 'SP_EXECUTESQL'.

|||

use:

exec sp_executesql @.query