|
navya krishna
Starting Member
India
39 Posts |
 Posted - 02/25/2013 : 17:43:14
|
Hello Dear All,
i am getting this error "The Group expression for the table ‘table1’ refers to the field ‘ContractName’. Report item expressions can only refer to f"ields within the current data set scope or, if inside an aggregate, the specified data set scope" it is working perfect in sql server managemetn studio but i am getting that error in ssrs.
please solve the problem.
USE [CCIL-2012]
GO
/****** Object: StoredProcedure [dbo].[ITem2] Script Date: 02/25/2013 20:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ITem2](@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*/
SET @DAY = 01
SET @MONTH = MONTH(@todate)
set @YEAR = YEAR(@todate)
SET @DATE = [MASTER].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 @DAY = DAY(@todate)
SET @pr_month_f1 = [MASTER].dbo.fnDateTime2FromParts(@Year-1 , @Month ,01,00,00,00,00)
SET @pr_month_t1 = [MASTER].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 [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code],[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code],[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code],
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as ytd2012
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Shortcut Dimension 2 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @fromdate AND @todate)))AS YTD2012,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Sales Amount(LCY)]) as Month2012
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Shortcut Dimension 2 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Invoice Line].[Posting Date] BETWEEN @DATE AND @todate)))AS MONTH2012,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Sales Amount(LCY)]) as ytd2011
FROM [CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line]
WHERE ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Shortcut Dimension 2 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t)))AS YTD2011,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Sales Amount(LCY)]) as month2011
FROM [CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line]
WHERE ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Shortcut Dimension 2 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Invoice Line].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1)))AS MONTH2011,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditytd2012
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 2 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @fromdate AND @todate)))AS CREDITYTD2012,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditmonth2012
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 2 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @DATE AND @todate)))AS CREDITMONTH2012,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditytd2011
FROM [CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line]
WHERE ([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Shortcut Dimension 2 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2011].dbo.[CCIL-2011$Sales Cr_Memo Line].[Posting Date] BETWEEN @pr_year_f AND @pr_year_t)))AS CREDITYTD2011,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditmonth2011
FROM [CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Item Category Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Product Group Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Shortcut Dimension 2 Code] =[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Sales Cr_Memo Line].[Posting Date] BETWEEN @pr_month_f1 AND @pr_month_t1)))AS CREDITMONTH2011,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Sales Amount]) as bdgtytd2012
FROM [CCIL-2012].dbo.[CCIL-2012$Item Budget Entry]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Global Dimension 1 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Analysis Area] = 0)
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Global Dimension 2 Code]=[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Date] BETWEEN @fromdate AND @todate))) AS BDGTYTD2012,
CONVERT(DECIMAL(38,2),(SELECT sum([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Sales Amount]) as bdgtmonth2012
FROM [CCIL-2012].dbo.[CCIL-2012$Item Budget Entry]
WHERE ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Global Dimension 1 Code] = [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Analysis Area] = 0)
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Global Dimension 2 Code]=[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code])
AND ([CCIL-2012].dbo.[CCIL-2012$Item Budget Entry].[Date] BETWEEN @DATE AND @todate))) AS BDGTMONTH2012
From [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry]
where ([CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Entry Type] = 1)
and ([CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code] between '300' and '550')
and ([CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Document Type] in (1,3))
group by [CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 1 Code],[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Global Dimension 2 Code],[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Item Category Code],[CCIL-2012].dbo.[CCIL-2012$Item Ledger Entry].[Product Group Code]
END
navya krishna katta |
 |
|
SSRS REPORTS
