ssrs reports

Question

Dear all,

i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report ...

year from 2012 year to 2013 date filter 01-01-12..31-12-13

Customer No. Sales -2012 Budget-2012 Variance-2012 Variance %-2012 Sales -2013 Budget-2013 Variance-2013 Variance %-2013 

i need details like this in one report using multiple databases.please anyone help me.


navya krishna katta

Answer 1

SSRS provides several LookUp functions that allow you to retrieve 1 or more values from another dataset where a specified key field in each dataset (source and other) match. Check out the LookUp, LookUpSet and MultiLookUp functions. The 2012 aggregate functions reference is here: http://technet.microsoft.com/en-us/library/dd255275.aspx. You can switch it to 2008 or 2008 R2 as well.

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 2

Hello Tim,

                If i write code for multiple database i am getting error could you  please solve the issue.

SELECT No_ FROM [Demo Database NAV (6-0)].dbo.[CRONUS International Ltd_$Customer]
UNION ALL
SELECT Name FROM CCIL12.dbo.[CCIL-2012$Customer]

I am getting below error

"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.".

Answer 3

Hello Dear All,

                      I wrote one query for that query i need to put filters  item catagory code ,branch,item no in ssrs.i am not getting idea how to modify query and how to put filter .could you please check the query.is this query correct or not i am able to understand.

USE [CCIL-2012]
GO
/****** Object:  StoredProcedure [dbo].[MonthlySalesTrendNet]    Script Date: 03/26/2013 09:53:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MonthlySalesTrendNet] @StDate date,@EndDate date


AS
BEGIN
       
        DECLARE @MonthList TABLE(Entry_ integer,Month_ varchar(20),StDate date,EdDate date)
        --DECLARE @StDate DATE
        DECLARE @SQLQuery AS NVARCHAR(max)
        Declare @I Int
        Declare @TotDB Int
        Declare @FromDate date
        Declare @ToDate date
        Declare @FromDateLY date
        Declare @ToDateLY date
        DECLARE @ListofIDs TABLE(Entry_ integer,DBName VARCHAR(30))
        Declare @Cur cursor
        DECLARE @DName as NVARCHAR(30)
        DECLARE @Serial as Int
     
        declare @J Int
        declare @K Int

        SET @SQLQuery = ' '
        SET @Serial = 0


         set @J = month(@StDate)
        set @k = month(@EndDate)

       SET @I = 1
        while (@I < 13 )
          BEGIN
           INSERT INTO @MonthList
                       VALUES(@I,' ','01/01/01','01/01/01')
                SET @I = @I + 1
          END
          
        SET @I = @J
        
        while (@I <= @k )
          BEGIN
           update @MonthList
                  set Month_ = DATENAME(month,@StDate),
                  stDate = @StDate,
                  EdDate= DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,@StDate)+1,0))
                  
           where Entry_ = @I 
           Set @StDate = DATEADD(DD,DATEDIFF(DD,0,DATEADD(dd,-(DAY(DATEADD(mm,1,@StDate))-1),DATEADD(mm,1,@StDate))),0)       
                 SET @I = @I + 1
          END

        -- End of Month calculation
        Set @TotDB = 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
           INSERT INTO @ListofIDs VALUES(@TotDB,@DName);
           fetch next from @Cur into @DName
           set @TotDB = @TotDB +1;
        end
        close @Cur
        deallocate @Cur

        -- End of finding total databases

        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 @I = 1
          while (@I < 13 )
BEGIN
 SET @Serial = @Serial + 1
                  SET @FromDate = (Select StDate from @MonthList where Entry_ = @I)
                  SET @FromDateLY = DATEADD(YYYY,-1,@FromDate)
                  SET @ToDate = (Select EdDate from @MonthList where Entry_ = @I)
                  SET @ToDateLY = DATEADD(YYYY,-1,@ToDate)
                  
                  SET @SQLQuery = @SQLQuery + 'Select '''+''+ (Select Month_ from @MonthList where Entry_ = @I ) +''' as Month_,'
+'Convert(int,'+convert(varchar,@I)+') as Sort,
                                               CONVERT(Decimal(38,2),(SELECT SUM(['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Sales Amount(LCY)]) FROM ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line] WHERE ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Customer Price Group] IN (''CASH'',''ROUTE'',''CREDIT'',''EXPORT'') 
                                               and ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Posting Date] between '''+convert(varchar(12),@FromDate)+''' and '''+convert(varchar(12),@ToDate)+''')) AS SalesInv ,
                                               CONVERT(Decimal(38,2),(SELECT SUM(['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) FROM ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line] WHERE ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Customer Price Group] IN (''CASH'',''ROUTE'',''CREDIT'',''EXPORT'') 
                                               and ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Posting Date] between '''+convert(varchar(12),@FromDate)+''' and '''+convert(varchar(12),@ToDate)+''')) AS CMCashRoute ,
  CONVERT(Decimal(38,2),(SELECT SUM(['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Sales Amount(LCY)]) FROM ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line] WHERE ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Customer Price Group] IN (''CASH'',''ROUTE'',''CREDIT'',''EXPORT'') 
  and ['+@DName+'].dbo.['+@DName+'$Sales Invoice Line].[Posting Date] between '''+convert(varchar(12),@FromDateLY)+''' and '''+convert(varchar(12),@ToDateLY)+''')) AS SalesInvLY ,
                                               CONVERT(Decimal(38,2),(SELECT SUM(['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Sales Amount(LCY)]) FROM ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line] WHERE ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Customer Price Group] IN (''CASH'',''ROUTE'',''CREDIT'',''EXPORT'') 
                                               and ['+@DName+'].dbo.['+@DName+'$Sales Cr_Memo Line].[Posting Date] between '''+convert(varchar(12),@FromDateLY)+''' and '''+convert(varchar(12),@ToDateLY)+''')) AS CMCashRouteLY ' 
                                               
                                               
                                                                                               
                  
                  IF @I != 12
                    SET @SQLQuery = @SQLQuery + ' UNION ALL '
                  SET @I = @I + 1
          END
          fetch next from @Cur into @DName
          IF (@@fetch_status  = 0)
                Set @SQLQuery = @SQLQuery + ' UNION ALL '
        END
        close @Cur
        deallocate @Cur
        -- End of dynamic query

        --print CAST(@SQLQuery AS NTEXT)
        exec (@SQLQuery )
END

Answer 4

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.

Answer 5

Naveen, you should post this in a separate thread. This one is marked as answered so it is being passed over by people that want to answer questions. A new post will get the attention you need.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.