Invoice form layout

Is there some way to simulate a fixed form layout for an invoice? I want to
have a fixed set of columns with dividing lines and totals boxes at the
bottom, regardless of how many actual rows of data are displayed. I've
tried 2 approaches (using PDF rendering only):
1. overlay a table with a set of manually drawn lines. This sort of works
for a single page, but if a 2nd page is required by the data it goes
completely haywire.
2. Use a table with conditional formatting. This is my current solution, BUT
a) the totals are always immediately after the last row, instead of at the
page bottom,
b) when the data requires just a little more than a single page the whole
table is moved to start on the 2nd page (where it fits in total due to less
header space) instead of starting on page 1 and continuing on page2 (hope
that makes sense).
Is there any published advice on designing this sort of page layout, or is
RS not really suited to the task?
brian smithHi Brian,
have you had any luck with this?
regards
Matt
"Brian Smith" <bsmith@.no_spam.schemiotics.co.uk> wrote in message
news:OElbad7nEHA.2300@.TK2MSFTNGP10.phx.gbl...
> Is there some way to simulate a fixed form layout for an invoice? I want
to
> have a fixed set of columns with dividing lines and totals boxes at the
> bottom, regardless of how many actual rows of data are displayed. I've
> tried 2 approaches (using PDF rendering only):
> 1. overlay a table with a set of manually drawn lines. This sort of works
> for a single page, but if a 2nd page is required by the data it goes
> completely haywire.
> 2. Use a table with conditional formatting. This is my current solution,
BUT
> a) the totals are always immediately after the last row, instead of at the
> page bottom,
> b) when the data requires just a little more than a single page the whole
> table is moved to start on the 2nd page (where it fits in total due to
less
> header space) instead of starting on page 1 and continuing on page2 (hope
> that makes sense).
> Is there any published advice on designing this sort of page layout, or is
> RS not really suited to the task?
> brian smith
>|||Nope, nothing :-(
My feeling is the RS cannot really do this sort of thing - it's really meant
for screen-based reporting.
brian
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ugVGLjSpEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi Brian,
> have you had any luck with this?
> regards
> Matt
> "Brian Smith" <bsmith@.no_spam.schemiotics.co.uk> wrote in message
> news:OElbad7nEHA.2300@.TK2MSFTNGP10.phx.gbl...
> > Is there some way to simulate a fixed form layout for an invoice? I want
> to
> > have a fixed set of columns with dividing lines and totals boxes at the
> > bottom, regardless of how many actual rows of data are displayed. I've
> > tried 2 approaches (using PDF rendering only):
> > 1. overlay a table with a set of manually drawn lines. This sort of
works
> > for a single page, but if a 2nd page is required by the data it goes
> > completely haywire.
> > 2. Use a table with conditional formatting. This is my current solution,
> BUT
> > a) the totals are always immediately after the last row, instead of at
the
> > page bottom,
> > b) when the data requires just a little more than a single page the
whole
> > table is moved to start on the 2nd page (where it fits in total due to
> less
> > header space) instead of starting on page 1 and continuing on page2
(hope
> > that makes sense).
> >
> > Is there any published advice on designing this sort of page layout, or
is
> > RS not really suited to the task?
> >
> > brian smith
> >
> >
>

Invert Rows and Columns in Tables

I have many fields but only a few rows to display. How do I invert the table with Report Services Designer so that Field (Columns) go down the page and the row items go across the page?

The best way to do this is to use Matrix instead of table for more help and examples check those links

http://msdn2.microsoft.com/en-us/library/ms251712(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx

Invert rows and columns (PIVOT)

Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:

SellerID(PK)	Year(PK)	ItemsSoldDPT1	ItemsSoldDPT2	ValueSoldDPT1	ValueSoldDPT2
1	2002	10	20	300.00	400.00
1	2003	13	71	450.00	320.00
1	2004	8	4	350.00	640.00
1	2005	2	15	110.00	680.00
2	2001	3	1	130.00	100.00
2	2005	1	7	190.00	200.00
2	2006	6	9	170.00	500.00
...	...	...	...	...	...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:

SellerID	2001 Items	2001 Values	2002 Items	2002 Values	2003 Items	2003 Values	2004 Items	2004 Values	2005 Items	2005 Values	2006 Items	2006 Values	...
1	(NULL)	(NULL)	30	700.00	84	770.00	12	990.00	17	790.00	(NULL)	(NULL)	...
2	4	230.00	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	8	390.00	15	670.00	...
...	...	..	...	...	...	...	...	...	...	...	...	...	...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

It is best to use the standard SQL approach of using GROUP BY and CASE expressions. Pivot can only be used to pivot one set of values. You have to write more complex query if you want to just use PIVOT operator and performance will be bad for those approaches.

select t.SellerID

, sum(case t.Year when 2001 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2001 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2002 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2002_Values

, sum(case t.Year when 2002 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2002_Values

...

from tbl as t

group by t.SellerID

|||Thank you for your interest and your time.
I already thought at this solution (it was the first approach I used) but this means that I must build a query that contains a couple of "sum(...) as ..." for each year I want to consider.. and I know neither how many different years are contained in the table nor how big is the date interval..
Moreover I discarted this approach because I can only replace the initial and final year value in the SQL query (something like a "tag replace"). We use a particular reporting tecnology that accepts queries at design time and replaces variables at runtime (with typed parameters).

So I have to write something like
SELECT.... xxx...xxx..xxx
WHERE...
and Year>=[%shortInitialYear] and Year<=[%shortFinalYear]

Even if I could use the PIVOT keyword I have a similar problem because the pivot keyword assumes that the user has to specify each value to pivot "IN ([value1], [value2], [value3])" and not a range (something like "BETWEEN [%shortInitialYear] and [%shortFinalYear]" )|||

hi

i understand exactly what you mean.

here is a short description on what i did to solve the problem, i apologize for not having the time to go into more detail but you'll figure this out quickly

first do a query where you would get the distinct date information with the amounts (sum it if you want), in a view would be best

then when you have got the distinct dates create a new temporary table, dynamically of course. i created the tabel and in a cursor added the columns (which was a result from the view)

then insert into your dynamic table the values into columns corresponding to the view you have created.

this whole approach works if your dynamic sql logic is sound, best is that you don't have to specify any thing other than what you want in the original view.

hope this helps

|||

It is a known annoyance :) You can use pivot by flattening out the set: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry but like Umachandar said, the "classic" style is easier/faster. If you want to help change this, vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071

The easiest thing to do is to just make your SUM clauses dynamic:

create table year
(
year char(4) primary key
)
insert into year
select 2001
union all
select 2002
go
declare @.query varchar(8000)
select @.query = 'select t.SellerID ' + char(10) + (
SELECT distinct
', sum(case t.Year when ''' + year + ''' then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as ' + year + '_Values' + char(10) +
', sum(case t.Year when ''' + year + ''' then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as ' + year + '_Values' + char(10)
AS [text()]
FROM
year y
FOR XML PATH('') ) + char(10) + ' from tbl as t group by t.SellerID'

select @.query

I used 2005 syntax to build the sum columns, since I think you have 2005, in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488720&SiteID=1 there is another full example.

sql

Invalid Primary Key error during table linking

Hi,

Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data--Link Tables'. I am getting an error (no surprise!):

" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".

When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.

About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'

I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index.

I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @.objname is ambiguous or the claimed @.objtype (INDEX) is wrong.

Any ideas on what went wrong and what I can do to fix it?

Thanks,
Lorihave fixed the problem by creating the table anew. thanks anyway.

Invalid descriptor index

Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:

> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks

Invalid Cursor State - Column Rename/Move

Hi,
I am trying to rename columns or move column in a simple table that has data
in it.
Each time I am getting an "Invalid Cursor State" error message.
I am doing that all the time in Access 97/2000/2002/2003. Why is it not
working in SQL Server 2000?
Any idea?
Thanks
vbdev
OK. Forget it. Found the issue:
The Hotfix that you have to install to get the report services on your
server is causing this odd behavior.
Solution is to install Hotfix 878...
vbdev

Invalid Cursor State - Column Rename/Move

Hi,
I am trying to rename columns or move column in a simple table that has data
in it.
Each time I am getting an "Invalid Cursor State" error message.
I am doing that all the time in Access 97/2000/2002/2003. Why is it not
working in SQL Server 2000?
Any idea?
Thanks
vbdevOK. Forget it. Found the issue:
The Hotfix that you have to install to get the report services on your
server is causing this odd behavior.
Solution is to install Hotfix 878...
vbdev

invalid column name ref: calculated columns

hello

we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
we are trying to use a already selected feild in a calculation to return another feild or column as they like to be known. this as we have found is not allowed!! is there anyone who knows of a clever work around so we dont have to write the calculation out 10000000000... times..
below is the code with the problem areas separted ta
cq

Alter Procedure AXACCBodereaux

(
@.ReportsPrintMenuHoldCoveredText73 datetime
)

As

SELECT QuoteRegister.QuoteNumber, QuoteRegister.PolicyNumber, QuoteRegister.Datecoverfrom, QuoteRegister.Datecoverto,
QuoteRegister.Broker, QuoteRegister.CLNTName, CLNTMaster.CLNTAddress1, CLNTMaster.CLNTAddress2, CLNTMaster.CLNTAddress3,
CLNTMaster.CLNTAddress4, CLNTMaster.CLNTPostcode, CLNTMaster.CLNTTradelongname,
Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end AS Type,
QuoteRegister.[BuildingSI] + QuoteRegister.[Total ContentsSumInsured] AS [Material Damage], Rating.TotalBI, Rating.PLIndemnitylimit,

percentnonliab = Case When Rating.TotalExcludingliabs <>0 Then Rating.KeyednonliabsdiscAmount / Rating.TotalExcludingliabs Else 0 end,

percentliab = Case When Rating.Totalliabilities <>0 Then Rating.KeyedliabilitiesdiscountAmount / Rating.Totalliabilities Else 0 end,

(Rating.[Total MD Premium] + Rating.TotaGITPrem + Rating.TotalmoneyPrem + Rating.DeteriorationofstocklimitPrem ) AS sum1,

MDPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then ( sum1 - ( sum1 * percentnonliab))
Else sum1 - sum1 *( Rating.NonliabsdiscountPercent/100) end ,

TOTBIPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then Rating.TotalBIPrem - Rating.TotalBIPrem * percentnonliab
Else Rating.TotalBIPrem - Rating.TotalBIPrem * (Rating.NonliabsdiscountPercent/100) end ,

PLPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem) * percentliab Else (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem)*(Rating.Totalliabilitiesdiscou ntPercent/100) end ,

ELPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then Rating.TotalELPrem-(Rating.TotalELPrem*(percentliab))
Else Rating.TotalELPrem - Rating.TotalELPrem*(Rating.Totalliabilitiesdiscoun tPercent/100) end ,

QuoteRegister.[Total Premium], (QuoteRegister.[Total Premium]*0.275) AS Commission,
(QuoteRegister.[Total Premium]*0.05) AS IPT, ([Total Premium]+IPT-Commission) AS NIA,
0 AS Finalised, QuoteRegister.Proposalreceived, QuoteRegister.CreationDate, QuoteRegister.CLNTCode,
QuoteRegister.DateonHoldCoveredBDX, Rating.KeyedliabilitiesdiscountAmount, Rating.KeyednonliabsdiscAmount
FROM (QuoteRegister INNER JOIN CLNTMaster ON QuoteRegister.CLNTCode =
CLNTMaster.CLNTCode) INNER JOIN Rating ON (CLNTMaster.CLNTCode = Rating.CLNTCode) AND
(QuoteRegister.QuoteNumber = Rating.QuoteNumber)
WHERE (((Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end ) <> 'MTA') AND ((QuoteRegister.DateonHoldCoveredBDX)=
@.ReportsPrintMenuHoldCoveredText73) AND ((QuoteRegister.ReasonForIssueCode) <> 'MTA') AND
((QuoteRegister.Insurer)='AXACC') AND ((Rating.QuoteNumber)=QuoteRegister.QuoteNumber) AND
((CLNTMaster.CLNTCode)=QuoteRegister.CLNTCode) AND ((Rating.ReasonForIssueCode)<>'MTA'));
/* set nocount on */
returnOriginally posted by colonelquinn
hello

we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.

You consider that a slight problem?

Also, I'm not sure I follow the problem...of course yuo can have derived data...what's the issue?|||derived data??
sorry dont know what that is only started doing this on friday..
to simplify the problem if you do this

select a ,b, c , a+b+c as d
or
select a,b,c, d+e as f , f+g as h
errors fly out saying any pre referenced value are invalid column names.
of course i could type out the calculation in every place but when i come to change the calc i am bound to forget to do it in every place.
i have tried doing two sql queies within the procedure but that gives the same error.
any ideas
#
thanks very much
cq|||Yeah You can't do that...

USE Northwind
GO

SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
FROM Orders|||surely there is a work around??!!!?!?!?!?!?|||USE Northwind
GO

SELECT A + ' Can be done!' FROM (SELECT CustomerId + Convert(varchar(10),EmployeeId) As A FROM Orders) As xxx

But it looks like a lot of rework...since you're doing it anyway...|||just define a view with as many of the combinations as you wish -- a+b+c, d+e, etc.

then query the view instead of the table

rudy
http://r937.com|||thankyou gentlemen
there are no errors so far...
now i wait till we have the data|||...or create calculated columns for the combinations you need.

blindman