SSRS REPORTS - 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 REPORTS

Author

Topic

navya krishna
Starting Member

39 Posts

Posted - 2013-01-20 : 00:09:04

Dear all,

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

we have one client he want's to display sales report by customer wise using different databases.for example he is maintaining every year databases in single server .he need to display all sales report by customer wise in single report.how can i display.could you please any one explain clearly how to display that one.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-20 : 01:32:19

duplicate of

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

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

navya krishna
Starting Member

39 Posts

Posted - 2013-02-26 : 17:17:08

Hi dear all,

in ssrs i created one datasource and one dataset .so one table is displaying with that dataset.so i need to display two tables in one report(design form). if i create one more dataset for second table .in excution i am getting error the datasource1 is not found.can any one please tell me how to create two tables in one design form.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 23:22:51

you can create as many tables as you want and also as many datasets inside same report. Are tables linked to correct datasets? or is it like table is linked to one dataset and you're trying to use other dataset's field inside it?

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

navya krishna
Starting Member

39 Posts

Posted - 2013-03-04 : 01:32:56

Hello Visakh ,

i have one problem with below query if i give filter from 01-01-12 to 01-31-12 it is showing correct result .but if give filter 01-01-13 to 01-31-12 in privious result that 01-01-12 to 01-31-12 result getting wrong .see if i gvr for 12 year filter same january month i am getting correct but if i give 13 year filter in privious year this 2012 january gettng wrong result.

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Sales Amount(LCY)]) as ytd2011 FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line] WHERE(['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code] = ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Shortcut Dimension 2 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Item Category Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Product Group Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Shortcut Dimension 1 Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code]) and (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_year_f)+''' AND '''+convert( varchar(12), @pr_year_t)+'''))) AS YTD2011,

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Sales Amount(LCY)]) as month2011 FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line] WHERE(['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code] = ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Shortcut Dimension 2 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Item Category Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Product Group Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code]) and (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Shortcut Dimension 1 Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code]) and (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Invoice Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_month_f1)+''' AND '''+convert( varchar(12), @pr_month_t1)+'''))) AS MONTH2011,

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditytd2011 FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line] WHERE(['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code] = ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 2 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Item Category Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Product Group Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_year_f)+''' AND '''+convert( varchar(12), @pr_year_t)+'''))) AS CREDITYTD2011,

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditmonth2011 FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line] WHERE(['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code] = ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 2 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Item Category Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Product Group Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 1 Code] =['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code]) AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_month_f1)+''' AND '''+convert( varchar(12), @pr_month_t1)+'''))) AS CREDITMONTH2011,

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 04:27:04

how do you expect someone to go through your query and understand? why not you provide some sample data from tables and explain what you're trying to get? thats much better than posting query like this

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

navya krishna
Starting Member

39 Posts

Posted - 2013-03-04 : 07:53:44

Hello visakh,
my problem is if i give date filer 1st jan 2012 to 31 jan 2012 showing one result .same lyk if i gve jan same for 2013 this result also getting correct.but i have one field prevoious year.so if i give 2013 jan i need to show prevoius year field result also .prevoius year means 2012 jan .i am gtng previous year result wrong.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 00:12:59

you need a correlated subquery in that case to get previous year fields

something like


SELECT MonthVal,field1,prevmonthfield1,field2,prevmonthfield2,...
FROM (SELECT DATEADD(mm,DATEDIFF(mm,0,datefield),0) AS MonthVal,sum(field1) as field1,..
      FROM yourtable 
      WHERE datefield >= @StartDate
      AND datefield < @EndDate + 1
      GROUP BY  DATEADD(mm,DATEDIFF(mm,0,datefield),0))t
CROSS APPLY (SELECT SUM(Field1) AS prevmonthfield1,..
             FROM yourtable
             WHERE datefield >= DATEADD(yy,-1,t.MonthVal)
             AND datefield < DATEADD(mm,1,DATEADD(yy,-1,t.MonthVal))-1
            )t1

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

navya krishna
Starting Member

39 Posts

Posted - 2013-03-06 : 00:37:48

Hello visakh,

i have one problem in ssrs.

i created one table in that table one column field credit sales.i need credit sales monthly wise so i created query for jan month.if i give parameter jan1 to jan31 it will be showing correct result.so if i give feb like upto dec if i give any month parameter i need to display that month cradit sales result.but if i give for feb it is not showing.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 01:09:48

As told numerous times before post how your report is currently showing fields and also what your expected output is

Its very difficult to make out from your explanation what you're actually looking out for

The onlt thing i can suggest from your current explanation is to apply a grouping on month

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

navya krishna
Starting Member

39 Posts

Posted - 2013-03-11 : 10:54:45

Hello visakh,

I need help from you i asked regarding this issue already i am not getting correct solution for my probelm.i need to display prevoius year and previous month result along with present year and present month .i will put query below pls modify the query.pls i must need this solution pls help me.

ALTER PROCEDURE [dbo].[ITem4](@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_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 @Cur cursor
DECLARE @DName as NVARCHAR(10)
DECLARE @PrevDName as NVARCHAR(10)
DECLARE @SQLQuery AS NVARCHAR(max)
DECLARE @ListofIDs TABLE(Entry_ integer,DBName VARCHAR(10))
DECLARE @I as Integer

set @PrevDName = '';
set @SQLQuery = '';
set @I = 1;

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

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(YY,-1,@fromdate)
SET @pr_year_t = DATEADD(YY,-1,@todate)

set @Cur = cursor fast_forward for
Select [DBName] FROM [CCIL-2012].[dbo].[CCIL-2012$DBName]
open @Cur
fetch next from @Cur into @DName
while (@@fetch_status = 0)
begin
INSERT INTO @ListofIDs VALUES(@I,@DName);
fetch next from @Cur into @DName

set @I = @I +1;
end
close @Cur
deallocate @Cur

set @I = 1;
set @Cur = cursor fast_forward for
Select [DBName] FROM [CCIL-2012].dbo.[CCIL-2012$DBName]
open @Cur
fetch next from @Cur into @DName
while (@@fetch_status = 0)
begin
SET @PrevDName = (Select DBName from @ListofIDs where Entry_ = @I-1)
if (@PrevDName is null)
SET @PrevDname = @DName

set @SQLQuery = @SQLQuery + 'SELECT ['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code] COLLATE DATABASE_DEFAULT as GlobalDimension1Code,['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code] COLLATE DATABASE_DEFAULT as ItemCategoryCode,['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code] COLLATE DATABASE_DEFAULT as ProductGroupCode,

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditytd2011
FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line]
WHERE(['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code] = ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 2 Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_year_f)+''' AND '''+convert( varchar(12), @pr_year_t)+'''))) AS CREDITYTD2011,

CONVERT(Decimal(38,2),(SELECT SUM(['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) as creditmonth2011
FROM ['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line]
WHERE (['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code]=['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Shortcut Dimension 1 Code])
AND (['+@PrevDName+'].dbo.['+@PrevDName+'$Sales Cr_Memo Line].[Posting Date] BETWEEN '''+convert( varchar(12), @pr_month_f1)+''' AND '''+convert( varchar(12), @pr_month_t1)+'''))) AS CREDITMONTH2011,

FROM ['+@DName+'].dbo.['+@DName+'$Item Ledger Entry]
where (['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Posting Date] between '''+convert( varchar(12), @fromdate)+''' AND '''+convert(varchar(12), @todate)+''')
group by ['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 1 Code],['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Global Dimension 2 Code],['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Item Category Code],['+@DName+'].dbo.['+@DName+'$Item Ledger Entry].[Product Group Code]
'

fetch next from @Cur into @DName
if (@@fetch_status = 0)
set @SQLQuery = @SQLQuery + ' UNION ALL '
set @I = @I +1;
end

--print CAST(@sQLQUERY AS NTEXT)
exec(@sqlquery)
close @Cur
deallocate @Cur
END

navya krishna katta

navya krishna
Starting Member

39 Posts

Posted - 2013-03-11 : 10:57:49

HELLO visakh,

I am getting wrong result visakh only for previous year and previous month please don't mine solve my problem.

navya krishna katta

navya krishna
Starting Member

39 Posts

Posted - 2013-04-12 : 08:35:03

Dear All,

I have created one ssrs report i gave parameters to that report item no,item catagery code and branch name. it is working good.but i need to give multiple selection for that parameters......can anyone please help me....in that parameters option i applied right click and give option multiple selection but it is showing error incorrect syntax near",".....please solve the issue.

navya krishna katta

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-12 : 08:55:28

quote:
Originally posted by navya krishna

Dear All,

I have created one ssrs report i gave parameters to that report item no,item catagery code and branch name. it is working good.but i need to give multiple selection for that parameters......can anyone please help me....in that parameters option i applied right click and give option multiple selection but it is showing error incorrect syntax near",".....please solve the issue.

navya krishna katta

Same as this? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179834

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources