Showing posts with label invoice. Show all posts
Showing posts with label invoice. Show all posts

Tuesday, March 20, 2012

Repeating report

I want to do the following with RS:
I have a invoice that I want to print three times and at the end of the
report I want to print a letter refering the invoices. I could do that making
2 reports, calling the first with three copies for the invoices and the
second report would be the letter.
But that doesn't seems to be a pretty way to do things.
Anybody can help me'
Thanx.You could do this with a report and subreport. Make a report for your
invoice and an report for your letter. Then add to your letter report: two
subreports that both point to the invoice report. It should optimize pretty
well.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TheRSBeginner" <TheRSBeginner@.discussions.microsoft.com> wrote in message
news:9CC3661E-F5D1-4D9E-8CF9-7F918403EB1A@.microsoft.com...
>I want to do the following with RS:
> I have a invoice that I want to print three times and at the end of the
> report I want to print a letter refering the invoices. I could do that
> making
> 2 reports, calling the first with three copies for the invoices and the
> second report would be the letter.
> But that doesn't seems to be a pretty way to do things.
> Anybody can help me'
> Thanx.
>
Posted by originatorrtyg at 1:51 AM 0 comments
Labels: , , , , , , , , , , ,

Monday, March 12, 2012

Repeating data regions on every page

Hi all, I want to create an invoice that will repeat numbers dates and
shipping info on every page that the list of items grows on to. You know,
to simulate a standard preprinted invoice page but fully generated by
reporting services.
Any assistance is appreciated.
Raul RubioThe sample report, at the end of this posting, should point in the direction
you need to go.
Since you will have a fairly regular structure I suggest that you use a
table instead of lists.
I think you will find them much easier to work with than a List which
contains a number of textboxes.
Note the use of the table group on OrderID.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raul R" <roller8@.hotmail.com> wrote in message
news:e%23qgdx$nEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Hi all, I want to create an invoice that will repeat numbers dates and
> shipping info on every page that the list of items grows on to. You know,
> to simulate a standard preprinted invoice page but fully generated by
> reporting services.
> Any assistance is appreciated.
> Raul Rubio
>
SampleInvoice.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Quantity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderDate">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>OrderDate</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!OrderDate.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Country.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="OrderIDGroup">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Quantity_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>ca33ccc8-f580-468b-ae00-bdebbfd7a5f0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CompanyName, Customers.City,
Customers.Country, Orders.OrderID, Orders.OrderDate, [Order
Details].UnitPrice, [Order Details].Quantity,
Products.ProductName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4d305298-f219-4b15-ab25-32b10f38c465</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Posted by originatorrtyg at 8:47 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Saturday, February 25, 2012

Renumbering Invoice Nos. through query ?

How can I renumber the following Invoice Nos. through Query.
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1
Posted by originatorrtyg at 4:27 AM 0 comments
Labels: , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)