| Author |
 Topic  |
|
navya krishna
Starting Member
India
39 Posts |
 Posted - 01/20/2013 : 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
India
47173 Posts |
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/04/2013 : 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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/04/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/04/2013 : 03:32:50
|
Hello Visakh,
i am not getting idea where can i give rank to field.could you explain clearly.
navya krishna katta |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/04/2013 : 03:37:13
|
show me how your dataset is currently in report
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/04/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/04/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/04/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/04/2013 : 06:40:54
|
Hello visakh,
could you please provide your email id .i need to contact with you regular touch.
navya krishna katta |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/04/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/13/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/13/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/18/2013 : 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/
|
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/21/2013 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/21/2013 : 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 |
|
|
|
navya krishna
Starting Member
India
39 Posts |
Posted - 02/21/2013 : 05:13:39
|
Hi Chandu,
I am getting same error Please modify that above report .
navya krishna katta |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/21/2013 : 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 |
|
|
|
Topic |
|
|
|
SSRS REPORTS
