SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SSRS REPORTS
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

navya krishna
Starting Member

India
39 Posts

Posted - 02/25/2013 :  17:43:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/26/2013 :  00:58:05  Show Profile  Reply with Quote
the error is not in sql code. The error clearly suggests you're using ContractName field inside grouping properties and ContractName is not in the scope for the dataset or dataregion applied.

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

Go to Top of Page

lucyming
Starting Member

Austria
4 Posts

Posted - 04/30/2013 :  03:02:39  Show Profile  Reply with Quote
thank you for share


__________________________________________________
Diablo 3 Gold;Aion Kinah;Diablo 3 Gold Kaufen;Guild Wars 2 Gold
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/30/2013 :  04:11:23  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000