SSRS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

SSRS

Author

Topic

babulyusuf
Starting Member

6 Posts

Posted - 2009-10-29 : 06:42:29

I have a query which gives me spend data of all the vendor with fields vendorname and vendornumber i want to use a parameter which gives me top 10 or 15 vendor names with there spend query i am using is
select vendorname,vendornumebr, sum(invoice) from table1..
I want to have a box in which user enter a value 10 or 15 or 20 and get top 10, 15 or top 20 vendor order by invoice desc

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-29 : 07:41:01

Create dataset for variable for report called @TopVar:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

Then use in main SQL as

Select TOP @TopVar vendorname,vendornumebr, sum(invoice) from table1..

John
It's a small world (but I wouldn't want to paint it)

babulyusuf
Starting Member

6 Posts

Posted - 2009-10-29 : 07:50:50

Thanks can you please provide me the code how to create the dataset?

quote:
Originally posted by JCirocco

Create dataset for variable for report called @TopVar:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

Then use in main SQL as

Select TOP @TopVar vendorname,vendornumebr, sum(invoice) from table1..

John
It's a small world (but I wouldn't want to paint it)

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-29 : 08:09:56

I assume you are using reporting services because you posted it to that forum.

And I did post the code... It would be:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

John
It's a small world (but I wouldn't want to paint it)

babulyusuf
Starting Member

6 Posts

Posted - 2009-10-29 : 08:16:26

Yes i am using SSRS but i am new in this how can i create such dataset i can create dataset from the shared datasource using a query but how to create such dataset please help

quote:
Originally posted by JCirocco

I assume you are using reporting services because you posted it to that forum.

And I did post the code... It would be:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

John
It's a small world (but I wouldn't want to paint it)

babulyusuf
Starting Member

6 Posts

Posted - 2009-10-29 : 08:29:12

I created the dataset topvar but when i used it in main sql it gave me error incorrect syntax near @top var i am using beloq query

select to @topvar * from (table 1 where table 1.value=@value) new order by table1.sales order by desc

quote:
Originally posted by JCirocco

I assume you are using reporting services because you posted it to that forum.

And I did post the code... It would be:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

John
It's a small world (but I wouldn't want to paint it)

babulyusuf
Starting Member

6 Posts

Posted - 2009-10-29 : 08:51:28

This is my exact query

select top @TopVar * from (select bob.vendorname,bob.vendornumber , bob.vendorparent, Sum(bob.InvoiceAmountUSD) As 'InvoiceAmount' from invoice bob inner join businesscommoditydim bcd on bcd.BusinessCommodityID = bob.BusinessCommodityID inner join timedim td on td.timeId = bob.timeId inner join executivedim ed on ed.profitcenterId = bob.profitcenterId inner join diversityindicatordim di on di.diversityindicatorid=bob.diversityindicatorid inner join salesgeographydim sgd on sgd.salesgeographyId = bob.salesgeographyId inner join ProgramTypeDim pt on pt.ProgramTypeId = bob.ProgramTypeId where bcd.categorygroup=@categorygroup Group by bob.vendorname,bob.vendornumber , bob.vendorparent )new
order by invoiceamount desc

quote:
Originally posted by JCirocco

I assume you are using reporting services because you posted it to that forum.And I did post the code... It would be:
select 10 AS TopVar
union
select 15 AS TopVar
union
select 20 AS TopVar

John
It's a small world (but I wouldn't want to paint it)

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-29 : 09:21:54

Not sure what all the joins are for but a simplified version:



select	top @TopVar
	invoice.vendorname,
	invoice.vendornumber,
	invoice.vendorparent,
	Sum(invoice.InvoiceAmountUSD) As 'InvoiceAmount'

  from	invoice

order by invoiceamount

John
It's a small world (but I wouldn't want to paint it)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources