Invoice Sample

Anyone know where I can find a sample RDL that is styled like an invoice
form?
Header - contains Sold To/Ship To
Detail - Invoice Line Items
Footer - contains invoice totals
TIA
TinaTina,
Reporting Services installs sample reports. There is one particular report
you could probably use as template.
The report is called Sales Order Detail report. It resembles an invoice. You
should be able to access this report in http://<server>/ReportServer in the
SampleReports folder. You can access the design of this report in Visual
Studio .Net.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Anyone know where I can find a sample RDL that is styled like an invoice
> form?
> Header - contains Sold To/Ship To
> Detail - Invoice Line Items
> Footer - contains invoice totals
> TIA
> Tina
>|||Thanks David, I'll have a look at the sample.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||That's a start but I need to repeat the BillTo & ShipTo on every page.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||Tina,
You can put the Bill To and Ship To information in the Table Header and have
it repeat.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> That's a start but I need to repeat the BillTo & ShipTo on every page.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Tina,
>> Reporting Services installs sample reports. There is one particular
>> report
>> you could probably use as template.
>> The report is called Sales Order Detail report. It resembles an invoice.
> You
>> should be able to access this report in http://<server>/ReportServer in
> the
>> SampleReports folder. You can access the design of this report in Visual
>> Studio .Net.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Anyone know where I can find a sample RDL that is styled like an
>> > invoice
>> > form?
>> >
>> > Header - contains Sold To/Ship To
>> > Detail - Invoice Line Items
>> > Footer - contains invoice totals
>> >
>> > TIA
>> > Tina
>> >
>> >
>>
>|||That's what I'm working on now but I loose the free-form ability when
working in a table.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Tina,
> You can put the Bill To and Ship To information in the Table Header and
have
> it repeat.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> > That's a start but I need to repeat the BillTo & ShipTo on every page.
> >
> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> >> Tina,
> >>
> >> Reporting Services installs sample reports. There is one particular
> >> report
> >> you could probably use as template.
> >>
> >> The report is called Sales Order Detail report. It resembles an
invoice.
> > You
> >> should be able to access this report in http://<server>/ReportServer in
> > the
> >> SampleReports folder. You can access the design of this report in
Visual
> >> Studio .Net.
> >>
> >> Dave
> >>
> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Anyone know where I can find a sample RDL that is styled like an
> >> > invoice
> >> > form?
> >> >
> >> > Header - contains Sold To/Ship To
> >> > Detail - Invoice Line Items
> >> > Footer - contains invoice totals
> >> >
> >> > TIA
> >> > Tina
> >> >
> >> >
> >>
> >>
> >
> >
>|||Welcome to Reporting Services. I am a Crystal Reports developer myself but
starting to understand Reporting Services.
Tip: You can select multiple cells and merge them. That is a huge help.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:exmn0TP9EHA.3588@.TK2MSFTNGP10.phx.gbl...
> That's what I'm working on now but I loose the free-form ability when
> working in a table.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Tina,
>> You can put the Bill To and Ship To information in the Table Header and
> have
>> it repeat.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
>> > That's a start but I need to repeat the BillTo & ShipTo on every page.
>> >
>> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
>> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> >> Tina,
>> >>
>> >> Reporting Services installs sample reports. There is one particular
>> >> report
>> >> you could probably use as template.
>> >>
>> >> The report is called Sales Order Detail report. It resembles an
> invoice.
>> > You
>> >> should be able to access this report in http://<server>/ReportServer
>> >> in
>> > the
>> >> SampleReports folder. You can access the design of this report in
> Visual
>> >> Studio .Net.
>> >>
>> >> Dave
>> >>
>> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >> > Anyone know where I can find a sample RDL that is styled like an
>> >> > invoice
>> >> > form?
>> >> >
>> >> > Header - contains Sold To/Ship To
>> >> > Detail - Invoice Line Items
>> >> > Footer - contains invoice totals
>> >> >
>> >> > TIA
>> >> > Tina
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

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

Intersection of N sets

Imagine a table that enumerates membership of items to some set:
create table sets
(
setId int not null,
itemId int not null
);
some data:
set 1 = {1,3,5,7,9}
set 2 = {1,2,3,4,5}
set 3 = {4,5,6,7}
translated to this model:
setId, itemId
1,1
1,3
1,5
1,7
1,9
2,1
2,2
2,3
2,4
2,5
3,4
3,5
3,6
3,7
Consider another table containing an enumeration of sets to calculate the
intersection of:
create table setIntersection
(
setId int not null
);
If the setIntersection table contains the records {1,2,3}, I'd like to calcu
late
the intersection of the items contained in the sets 1, 2 and 3.
In the example above, this would be the {5}.
While this is trivial to do in a typical imperative fashion (looping inside
an SP), I'm wondering if it's possible to perform this operation in a single
query, perhaps using an CTE.Essentially, it would be a join on the the tables, each representing a set.
SELECT t1.itemid -- or t2.itemid or t3.itemid
FROM tbl t1,
tbl t2,
tbl t3
WHERE t1.itemid = t2.itemid
AND t2.itemid = t3.itemid
AND t1.setid = 1
AND t2.setid = 2
AND t3.setid = 3 ;
Anith|||Taras Tielkes (taras.tielkes@.gmail.com) writes:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
>...
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to
> calculate the intersection of the items contained in the sets 1, 2 and
> 3. In the example above, this would be the {5}.
SELECT itemId
FROM sets s
WHERE EXISTS (SELECT *
FROM setIntersection sI
WHERE sI.setId = s.setId)
GROUP BY itemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM setIntersection)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah, but I meant to ask the question in a generic way: "how can I writer a
query that will perform the required operation for an arbitrary number of
sets in the example model. Say 100 defined sets :-)

> Essentially, it would be a join on the the tables, each representing a
> set.
> SELECT t1.itemid -- or t2.itemid or t3.itemid
> FROM tbl t1,
> tbl t2,
> tbl t3
> WHERE t1.itemid = t2.itemid
> AND t2.itemid = t3.itemid
> AND t1.setid = 1
> AND t2.setid = 2
> AND t3.setid = 3 ;|||In that case, Erland's response should help. You may also want to search the
archives for "relational division" to find some related examples.
Anith|||Taras Tielkes wrote:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
> translated to this model:
> setId, itemId
> 1,1
> 1,3
> 1,5
> 1,7
> 1,9
> 2,1
> 2,2
> 2,3
> 2,4
> 2,5
> 3,4
> 3,5
> 3,6
> 3,7
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to cal
culate
> the intersection of the items contained in the sets 1, 2 and 3.
> In the example above, this would be the {5}.
> While this is trivial to do in a typical imperative fashion (looping insid
e
> an SP), I'm wondering if it's possible to perform this operation in a sing
le
> query, perhaps using an CTE.
It's relational division:
sets/setIntersection