CREATE TABLE [Store](
 [StoreId] [int] IDENTITY(1,1) NOT NULL,
 [LOC_Name] [varchar](50) NULL,
 
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
 [StoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]


  Insert into [dbo].[Store]
  (
[LOC_Name]
  )
  select 'Virginia'
  union all
  select 'Chicago'
   union all
  select 'Dallas'

 

CREATE TABLE [Employee](
 [StoreId] [int] NOT NULL,
  [Emp_NAME] [nvarchar](70) NULL,
 ) ON [PRIMARY]


    Insert into [dbo].[Employee]
  (
[StoreId]
      ,[Emp_NAME]
  )
  select 1,'daniel'
  union all
  select 1,'jack'
   union all
  select 1,'roger'
  union all
  select 1,'matt'
  union all
  select 2,'sam'
  union all
  select 2,'henry'
  union all
  select 3,'eston'
   union all
  select 3,'robert'
    union all
  select 3,'nadal'



CREATE TABLE [Customer](
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 [StoreId] [int] NOT NULL,
 [CUST_NO] [nvarchar](11) NULL,
 [Emp_NAME] [nvarchar](70) NULL,
  
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 


set identity_insert [CCC_STAGE].[dbo].[Customer] on

    Insert into [CCC_STAGE].[dbo].[Customer]
  (
[CustomerId]
      ,[StoreId]
      ,[CUST_NO]
	  ,[Emp_NAME]
  )
  select 201,1,897456,'daniel'
  union all
   select 202,1,974652,'daniel'
  union all
  select 203,1,276294,'matt'
  union all
  select 204,1,612348,'jack'
  union all
   select 205,2,187906,'henry'
  union all
  select 206,2,289123,'henry'
  union all
  select 207,2,427403,'sam'
   union all
  select 208,3,591654,'robert'
  union all
   select 209,3,904563,'robert'


-------------- Query to retrieve In each location each employee is working on how many customers--

 select  [LOC_NAME],
 B.[Emp_NAME], 
 COUNT(distinct C.[CUST_NO]) TOTAL_CUSTOMERS

FROM [CCC_STAGE].[dbo].[Store_TEST] A
join [CCC_STAGE].[dbo].[Employee_TEST] B
on A.StoreId=B.StoreId
join [CCC_STAGE].[dbo].[Customer_TEST] c
on B.StoreId=C.StoreId
and B.[Emp_NAME]=C.[Emp_NAME]
   group by [LOC_NAME]
 ,B.[Emp_NAME]
 ORDER BY  [LOC_NAME],B.[Emp_NAME]

Hi everyone,

Can any one help me in this

Drill down report

can any one help me in this

If we click on drill down of Chicago, we should be able to see all the employees of Chicago in the same column,  like that if we click on drill down on each loc_name, we should be able to see employees related to that location under the loc_name column, not in the different column. when i try am getting in different column.

How to do this in SSRS? i did this using grouping and visibility feature hidden by toggle, but am able to see employee names, but on top of them am not able to get employee as heading.

Below I am sending the code (creating the tables/inserting the data/ and retrieving the data). please try to run the query with the data I sent. you will see the same data as it is in the below image.

Hi ,

You can try below Simple Steps ;
(generate using Wizard)

1. On Solution Explorer -> Right-click on Reports -> Select Add New Report

2.Click on the Next Button -> Give a Data Source name and Click on the Edit Button
3.Select Server Name -> Select "Use SQL Server Authentication" Radio Button
4.Give User name and Password -> Select Database Name and Click on the "OK" button.
5.Press the Next Button -> type your Sql Query -> NExt
6.Select Tabular Option and Click on the Next Button
7.Select [LOC_NAME],[Emp_NAME] for the Group list and the TotalEmployee is for Details -> Next
8.Select Stepped option -> Enbale Drilldown -> NEXT-> Finish(you can use this option if only you require Drilldown option in your report else you an ignore this or simply click next.)
9. Delete EmployeeName Column  -> Paste Emplyee Name Next to LocName As shown in Image.
10 . Delete table1_Details_Group in Grouping Pane
11. Type =Sum(Fields!TOTAL_CUSTOMERS.Value) for Total Customer
12. Right Click on Emp_Name Row -> Insert Row -> Outside Group Above -> TYpe Employee

For your Reference Final Report will looks like this If you will not use Enable Drilldown Option ;

Else (using Drilldown option)

Prajapatineha and  Katherine Thank you so much for your response.

can you please tell me how we can do this with out wizard, don't have drill option if we don't use wizard.

can you please suggest me how to do this kind of drill down with out wizard?

Thanks

Vishu

Hi Vishu,

If you want to add the drill-down option without using wizard, we can refer to the folloiwng steps:

1. Right-click the third row to open the Row Visibility, select Hide.
2. Then enable "Display can be toggled by this report item" with textbox name which contains [LOC_NAME].
3. Use the same method to set the fourth row visbility.

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

Katherine Xiong Thank you for your response.

I understand how to set row visibility, but I need to show employee under loc_name column. If I expand loc_name, I need to see all the employee's of that location under the loc_name. how can we do this with out wizard? can you please suggest.

Thanks
Vishu

Hi,

you can create this without using Wizard also ;

Step1: take one tablix ->delete Header also -> delete 2 columns and Details Group from Row Group  with delete group only .
Step2: Now Group by this data cell with Location Name
step3 :right click on this cell ->Insert row --inside group below
Step4: Rigth cilck on LocationName column -> delete Column only
Step 5: Insert Tablix in second cell and location expreesion in first cell (see in image)
Step6: in this second tablix Merge header cell -> Bind empName and TotalCustomer .
Step 7: on right hand side take one placehoder to contian totla number of customer and use =Sum(Fields!TOTAL_CUSTOMERS.Value) in value of placehoder.
Step 8: for drilldown option select area as mentioned in image;
         Row visibilty-> hide -> toggle on Location


Thanks

Output Images ;

Image 2

Hi Vishu,

My latest reply is used for tell you how to create the drilldown option based on LOC_NAME field. Generally, drilldown option is used for control some items' visibility, so we should achieve this goal by control the visibility.

The following document about Add an Expand/Collapse Action to an Item (Report Builder and SSRS) is for your reference
http://msdn.microsoft.com/en-IN/library/dd220405.aspx

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong