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 2008 Forums
 Transact-SQL (2008)
 SSRS REPORTS

Author  Topic 

navya krishna
Starting Member

39 Posts

Posted - 2013-01-20 : 00:00:29
Dear all,

i am new for ssrs.i need some clarifications on ssrs reports.

we have one client he is maintaining databases every year sepeatly in single server.so for example he maintained 2008,09,10 ,11,12 lyk that so i need sales report customer wise in single report .i need to display customer name,one column 2008 database,2009,10,11,12 to every column in single report.
how can i display .could you please any one explain the process clearly.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-20 : 01:32:51
duplicate of

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181642

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 02:55:42
Hello Visakh,

could you please tell me how to display top 10 customer detailes in ssrs.i am trying to do i am not getting solution for that please solve my problem.

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:03:08
apply rank over field based on which you need the top 10 and then apply a filter like Rank <=10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 03:32:50
Hello Visakh,

i am not getting idea where can i give rank to field.could you explain clearly.

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:37:13
show me how your dataset is currently in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 04:04:24
Hello Visakh,
see I have taken in dataset customer name and customer amount so i need to dispaly top 10 customer detailes in report.where can i give that rank to field.

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 04:15:51
assuming based on customer amount you want to rank. use

SELECT *
FROM
(
SELECT dense_Rank() over (order by [customer amount] as rnk,*
FROM table
)t
WHERE rnk<=10


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 05:16:30
SELECT *
FROM
(
SELECT dense_Rank() over (order by [Amount] as rnk,*
FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
)t
WHERE rnk<=10


i am getting error "Incorrect syntax near the keyword 'as'".....

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 05:40:21
quote:
Originally posted by navya krishna

SELECT *
FROM
(
SELECT dense_Rank() over (order by [Amount]) as rnk,*
FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
)t
WHERE rnk<=10


i am getting error "Incorrect syntax near the keyword 'as'".....

navya krishna katta


missed a )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-04 : 06:40:54
Hello visakh,

could you please provide your email id .i need to contact with you regular touch.

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 06:44:57
quote:
Originally posted by navya krishna

Hello visakh,

could you please provide your email id .i need to contact with you regular touch.

navya krishna katta


please post your questions here. I will try to answer when I can.
There are lots of experts here and it will ensure any one of them would be able to see and give solutions to questions.
I may not be available always for checking and answering questions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-13 : 02:20:07
Hello Visakh,

I got one error "Expression type decimal is invalid for COLLATE clause " please solve that issue.it's urgent to me.

USE [CCIL12]
GO
/****** Object: StoredProcedure [dbo].[Netrevenue] Script Date: 02/13/2013 11:49:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Netrevenue2](@fromdate date, @todate date)
AS
BEGIN
DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY INT
DECLARE @DATE DATE
/*SET @DAY = 01
SET @MONTH = MONTH(@todate)
set @YEAR = YEAR(@fromdate)
SET @DATE = dbo.fnDateTime2FromParts(@Year , @Month ,@DAY,00,00,00,00)*/


SELECT CCIL11.dbo.[CCIL-2011$G_L Account].No_ COLLATE DATABASE_DEFAULT as [N0_],
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate)))COLLATE DATABASE_DEFAULT AS TEST01
FROM CCIL11.dbo.[CCIL-2011$G_L Account]
WHERE CCIL11.dbo.[CCIL-2011$G_L Account].No_ IN('41120','41130','41140','41150')

union all

SELECT CCIL12.dbo.[CCIL-2012$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_],
CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) COLLATE DATABASE_DEFAULT AS TEST01
FROM CCIL12.dbo.[CCIL-2012$G_L Account]
WHERE CCIL12.dbo.[CCIL-2012$G_L Account].No_ IN('41120','41130','41140','41150')
END




navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 04:49:48
you need to add COLLATE only for varchar fields. dont add it for decimal or other numeric fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-18 : 00:50:03
Hello visakh,

below report just displaying 2011,12,13 db's result in future also we need to use this report only.for example we need to display in future 2014,2015 report .how can we write dynamic query for this below query.


USE [CCIL12]
GO
/****** Object: StoredProcedure [dbo].[Netrevenue4] Script Date: 02/18/2013 11:16:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Netrevenue4](@fromdate date, @todate date)
AS
BEGIN
DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY INT
DECLARE @DATE DATE /*frome date for actual month like 01-10-2011 */

DECLARE @pr_month_f DATE /*prior month from_date in this year only*/
DECLARE @pr_month_t DATE /*prior month to_date in this year only*/

DECLARE @pr_month_f1 DATE /*actual month prior year*/
DECLARE @pr_month_t1 DATE /*actual month prior year*/

DECLARE @pr_year_f DATE /*prior year from_date*/
DECLARE @pr_year_t DATE /*prior year to_date*/

DECLARE @pr_month2_f DATE /*prior month of prior month from_date in this year only*/
DECLARE @pr_month2_t DATE /*prior month of prior month to_date in this year only*/

DECLARE @pr_year2_f DATE /*prior year prior month Eg: 01-09-2010*/
DECLARE @pr_year2_t DATE /*prior year prior month Eg: 31-09-2010*/


SET @DAY = 01
SET @MONTH = MONTH(@todate)
set @YEAR = YEAR(@todate)
SET @DATE = dbo.fnDateTime2FromParts(@Year , @Month ,@DAY,00,00,00,00)


SET @pr_month_f = DATEADD(MM,-1,@DATE)
SET @pr_month_t = DATEADD(MM,-1,@todate)

SET @pr_month2_f = DATEADD(MM,-1,@pr_month_f)
SET @pr_month2_t = DATEADD(MM,-1,@pr_month_t)

SET @pr_year2_f = DATEADD(yyyy,-1,@pr_month_f)
SET @pr_year2_t = DATEADD(yyyy,-1,@pr_month_t)

set @DAY = DAY(@todate)
SET @pr_month_f1 = dbo.fnDateTime2FromParts(@Year-1 , @Month ,01,00,00,00,00)
SET @pr_month_t1 = dbo.fnDateTime2FromParts(@Year-1 , @Month ,@DAY,00,00,00,00)

SET @pr_year_f = DATEADD(YYYY,-1,@fromdate)
SET @pr_year_t = DATEADD(YYYY,-1,@todate)




SELECT CCIL11.dbo.[CCIL-2011$G_L Account].No_ COLLATE DATABASE_DEFAULT as [N0_],
/*YTD actual from_date to to_date */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01,
/* YTD actual from_date to to_date budget */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test1
FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate)))AS TEST02,
/*month actual Eg: 01-10-2011 to 31-10-2011*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test2
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03,
/*month actual Eg: 01-10-2011 to 31-10-2011 budget */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test3
FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate)))AS TEST04,
/*prior month in this year only */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test4
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05,
/*budget*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Amount]) as Test6
FROM CCIL11.dbo.[CCIL-2011$G_L Budget Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t)))AS TEST06,
/*prior month of prior month in this year only*//*
CONVERT(decimal(38, 2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test41
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051,*/

/*prior month from_date last year only Eg: last year september*/
CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test7
FROM CCIL10.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052,

/*Prior year Eg: 01-01-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test7
FROM CCIL10.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07,

/*prior year same month Eg01-10-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL10.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test8
FROM CCIL10.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL11.dbo.[CCIL-2011$G_L Account].No_= CCIL10.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL10.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08

FROM CCIL11.dbo.[CCIL-2011$G_L Account]
WHERE CCIL11.dbo.[CCIL-2011$G_L Account].No_ IN('41120','41130','41140','41150')


UNION ALL /*union with another db 2013*/


SELECT CCIL12.dbo.[CCIL-2012$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_],
/*YTD actual from_date to to_date */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01,
/* YTD actual from_date to to_date budget */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test1
FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate))) AS TEST02,
/*month actual Eg: 01-10-2011 to 31-10-2011*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test2
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03,
/*month actual Eg: 01-10-2011 to 31-10-2011 budget */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test3
FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate))) AS TEST04,
/*prior month in this year only */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test4
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05,
/*budget*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Amount]) as Test5
FROM CCIL12.dbo.[CCIL-2012$G_L Budget Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST06,
/*prior month of prior month same year*//*
CONVERT(decimal(38, 2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test4
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051, */

/*prior month from_date last year only Eg: last year september*/
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test6
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052,

/*Prior year Eg: 01-01-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test6
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07,
/*prior year same month Eg01-10-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL11.dbo.[CCIL-2011$G_L Entry].[Amount]) as Test7
FROM CCIL11.dbo.[CCIL-2011$G_L Entry]
WHERE(CCIL12.dbo.[CCIL-2012$G_L Account].No_= CCIL11.dbo.[CCIL-2011$G_L Entry].[G_L Account No_])
AND (CCIL11.dbo.[CCIL-2011$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08

FROM CCIL12.dbo.[CCIL-2012$G_L Account]
WHERE CCIL12.dbo.[CCIL-2012$G_L Account].No_ IN('41120','41130','41140','41150')

UNION ALL /*union with another db 2013*/


SELECT CCIL13.dbo.[CCIL-2013$G_L Account].No_ COLLATE DATABASE_DEFAULT as [No_],
/*YTD actual from_date to to_date */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test
FROM CCIL13.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01,
/* YTD actual from_date to to_date budget */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test1
FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @fromdate AND @todate))) AS TEST02,
/*month actual Eg: 01-10-2011 to 31-10-2011*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test2
FROM CCIL13.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @DATE AND @todate))) AS TEST03,
/*month actual Eg: 01-10-2011 to 31-10-2011 budget */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test3
FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @DATE AND @todate))) AS TEST04,
/*prior month in this year only */
CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test4
FROM CCIL13.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST05,

/* CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Entry].[Amount]) as Test4
FROM CCIL13.dbo.[CCIL-2013$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Entry].[Posting Date] BETWEEN @pr_month2_f AND @pr_month2_t))) AS TEST051,*/

/*prior month from_date last year only Eg: last year september*/

CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test6
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_year2_f AND @pr_year2_t)))AS TEST052,
/*budget*/
CONVERT(decimal(38, 2),(SELECT SUM(CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Amount]) as Test5
FROM CCIL13.dbo.[CCIL-2013$G_L Budget Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[G_L Account No_])
AND (CCIL13.dbo.[CCIL-2013$G_L Budget Entry].[Date] BETWEEN @pr_month_f AND @pr_month_t))) AS TEST06,
/*Prior year Eg: 01-01-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test6
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t))) AS TEST07,
/*prior year same month Eg01-10-2010 to 31-10-2010 */
CONVERT(Decimal(38,2),(SELECT SUM(CCIL12.dbo.[CCIL-2012$G_L Entry].[Amount]) as Test7
FROM CCIL12.dbo.[CCIL-2012$G_L Entry]
WHERE(CCIL13.dbo.[CCIL-2013$G_L Account].No_= CCIL12.dbo.[CCIL-2012$G_L Entry].[G_L Account No_])
AND (CCIL12.dbo.[CCIL-2012$G_L Entry].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1))) AS TEST08

FROM CCIL13.dbo.[CCIL-2013$G_L Account]
WHERE CCIL13.dbo.[CCIL-2013$G_L Account].No_ IN('41120','41130','41140','41150')
END

navya krishna katta
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 00:55:27
it seems you're using separate tables for holding each years data. in that case you can make it dynamic in a straightforward way.
One solution is to create a view over these group of tables and use view instead in the above procedure

The view still has to be modified to add latest set of tables in each year


I would have scrapped this design and would have come up with single table with a year or date column to indicate period of transaction and kept the entire data in same table itself to avoid these complications.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-18 : 01:07:41
Visakh,


could you please provide a query how to write. am not getting idea how to write it.
pls modify that query into dynamic query or you are telling way.

navya krishna katta
Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-21 : 04:18:55
Hello Visakh,

I am getting Below error how can i solve that problem."Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

USE [CCIL-2012]
GO
/****** Object: StoredProcedure [dbo].[ITem] Script Date: 02/21/2013 12:47:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[ITem1](@fromdate date, @todate date)
AS
BEGIN



SELECT [CCIL-2012].dbo.[CCIL-2012$Item].No_ ,
(SELECT [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code] as Prdgrp
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_)as Ctgry,
CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as Test1
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)and ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST01,

CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as Test2
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)and ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @fromdate AND @todate))) AS TEST02


FROM [CCIL-2012].dbo.[CCIL-2012$Item]
end

navya krishna katta
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 04:42:28
The reason is your first subquery might return more than one row...

Are you having same value for Ctgry?
i.e.
If [Product Group Code] for [CCIL-2012$Item].No_ is same, then you can use as below:
SELECT MAX([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code]) as Prdgrp
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_)as Ctgry,

--
Chandu
Go to Top of Page

navya krishna
Starting Member

39 Posts

Posted - 2013-02-21 : 05:13:39
Hi Chandu,

I am getting same error Please modify that above report .


navya krishna katta
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 05:34:47
Try this....

SELECT
[CCIL-2012].dbo.[CCIL-2012$Item].No_ ,
(SELECT MAX([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code]) as Prdgrp
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
where [CCIL-2012].dbo.[CCIL-2012$Item].No_ = [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_
)as Ctgry,
CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as Test1
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)
and ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @fromdate AND @todate)
)
) AS TEST01,
CONVERT(Decimal(38,2),(SELECT SUM([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as Test2
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].No_= [CCIL-2012].dbo.[CCIL-2012$Item].No_)
and ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @fromdate AND @todate)
)
) AS TEST02
FROM [CCIL-2012].dbo.[CCIL-2012$Item]



--
Chandu
Go to Top of Page
    Next Page

- Advertisement -