none
SSRS Report with Company Name as filter.

    Question

  • Hi all,

    I am new to SSRS.

    My Query is -

    a) My database contain multiple companies (say 2).

    b) So my tables say (item) is named as companyA_$Item & CompanyB_$Item.

    I want to pass company name as parameter to SSRS Report.

    My query is  - 

    SELECT
      No_
      ,Description
      ,[Base Unit of Measure]
      ,[Unit Price]
    FROM CompanyA_$Item

    What i tired -

    I created a parameter company name which takes input from a another dataset which list down both companies from the database.

    What i Want -

    In above query i want The last line based on parameter.

    Please suggest if anyone knows how to resolve it.

    Regards,

    saurav dhyani 

    Tuesday, October 01, 2013 9:11 PM

Answers

  • Hi ,

    Try like shown below in the dataset query and then add columns No_ ,Description
      ,[Base Unit of Measure] ,[Unit Price] ,

    under Dataset properties - >  Fields - >  Add - > Query Field


    = "SELECT
      No_
      ,Description
      ,[Base Unit of Measure]
      ,[Unit Price]
    FROM "+  Parameters!CNAME.Value

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Wednesday, October 02, 2013 6:46 AM

All replies

  • What you are trying to accomplish is possible with dynamic SQL provided that the desired columns are the same in all of your tables. But, if it's an option, I'd recommend instead getting all your data into a single table with a column to identify the company.  That said, here's a way to accomplish what you've asked by first creating a stored procedure in your database, defining your SSRS dataset to use the stored procedure and binding your report parameters to it:

    CREATE TABLE Cogswell_Cogs (
    	No_ int
       ,Description varchar(128)
       ,[Base Unit of Measure] varchar(16)
       ,[Unit Price] money
    );
    
    CREATE TABLE Spacely_Sprockets (
    	No_ int
       ,Description varchar(128)
       ,[Base Unit of Measure] varchar(16)
       ,[Unit Price] money
    );
    
    INSERT Cogswell_Cogs VALUES
     (1, 'ABCDE', 'cm', 1.25)
    ,(2, 'FGHIJ', 'mm', 1.56);
    
    INSERT Spacely_Sprockets VALUES 
     (1, 'KLMNO', 'in', 0.95)
    ,(2, 'PQRST', 'ft', 5.25);
    
    GO
    
    CREATE PROCEDURE GetCompanyData @company varchar(128), @item varchar(128)
    AS
    BEGIN
    	DECLARE @sqlcmd nvarchar(MAX);
    	
    	SET @sqlcmd = 'SELECT No_, Description, [Base Unit of Measure], [Unit Price] FROM ' + @company + '_' + @item;
    	
    	EXEC sp_executesql @sqlcmd;
    END
    
    GO
    
    EXEC GetCompanyData 'Spacely', 'Sprockets'


    Jason

    Wednesday, October 02, 2013 12:10 AM
  • Another solution would be to create a view something like below

    CREATE VIEW dbo.ALlItem AS 
    SELECT
        'CompanyA' AS Company
       ,No_
       ,Description
       ,[Base Unit of Measure]
       ,[Unit Price]
     FROM CompanyA_$Item
     UNION ALL
    SELECT
        'CompanyB' AS Company
       ,No_
       ,Description
       ,[Base Unit of Measure]
       ,[Unit Price]
     FROM CompanyB_$Item
    

    and then use a SSRS dataset query to query the view like below.

    SELECT
       No_
       ,Description
       ,[Base Unit of Measure]
       ,[Unit Price]
     FROM dbo.AllItem WHERE Company = @CompanyParam -- Connect to SSRS param
    

    -----------------------------------------------------------------

    Please mark as answered if this solve your problem.


    --sIbu

    Wednesday, October 02, 2013 3:17 AM
  • Hi  saurav dhyani ,

    You also try like this - Control Dataset Query Dynamically based on SSRS Report Parameters

    write expression for dataset :

    = "SELECT * FROM  " + Parameters!tablename.Value
    Add the columns under Dataset properties - >  Fields - >  Add - > Query Field

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Wednesday, October 02, 2013 5:10 AM
  • Hi all,

    Thanks for all your replies, but i am really sorry i am unable to resolve the issue.

    Below are the steps that i had taken -

    1) A dataset is created - which list down companies in the database. The dataset is set as source to the parameter CName.

    2) I want to use that CNAME in my another dataset shown below.

    But The Query gives an error message as shown in right.

    Thanks for all your replies but please suggest how i can resolve this issue.

    Regards,

    Saurav Dhyani

    Wednesday, October 02, 2013 6:04 AM
  • Hi ,

    Try like shown below in the dataset query and then add columns No_ ,Description
      ,[Base Unit of Measure] ,[Unit Price] ,

    under Dataset properties - >  Fields - >  Add - > Query Field


    = "SELECT
      No_
      ,Description
      ,[Base Unit of Measure]
      ,[Unit Price]
    FROM "+  Parameters!CNAME.Value

    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Wednesday, October 02, 2013 6:46 AM
  • Hi SathyanarrayananS,

    Thanks for the input but its not working.

    Wednesday, October 02, 2013 7:08 PM
  • Hi SathyanarrayananS,

    Thanks for the input it worked fine.

    Sunday, October 06, 2013 12:19 PM