Hi All,

I need to write a query for SSRS report. StartDate, EndDate and ChangedField are the input parameters.

Let's consider that we are taking employee details. The table contains lot of fields like emp name,add1,add2,city,state,country,designation,rank etc. We have almost 30 fields are there. These field name are coming as input parameter "ChangedField" . I need to show the records which belong to the start date and end date also data change for the field which is given in the input parameter "ChangedField".  That meand If I give ChangedField="city", we need to check for data which has been updated with new City name in the given time period.

Please suggest.

Regards,

Julie

• Moved by Eric__ZhangMicrosoft contingent staff 3 hours 52 minutes ago more relevant

Hi,

if object_id('tempdb..#Your_Table_Log') is not null DROP TABLE #Your_Table_Log
CREATE TABLE #Your_Table_Log (rec_id int,emp_name varchar(10),add1 varchar(10),add2 varchar(10),city varchar(10),ModifiedDate DateTime)

INSERT #Your_Table_Log
SELECT 1,'emp1','add1','add2','mbr','20150101'
UNION ALL
SELECT 1,'emp1','add1','add2','mbr2','20150102'
UNION ALL
SELECT 1,'emp1','add1','add3','mbr2','20150103'
UNION ALL
SELECT 1,'emp1','add5','add3','mbr2','20150104'
UNION ALL
SELECT 2,'emp2','add1','add2','mbr','20150101'
UNION ALL
SELECT 2,'emp2','addr1','add2','mbr2','20150102'

DECLARE @StartDate varchar(100), @endDate Varchar(100),@chClmn nvarchar(10),@sql varchar(8000)
DECLARE @cnt int

SET @StartDate = '20150101'
SET @endDate = '20150103'
SET @chClmn = 'city'

SET @sql = ' SELECT  DISTINCT '+@chClmn+' FROM #Your_Table_Log where ModifiedDate between '''+@StartDate+''' and '''+@EndDate+''''
exec (@sql)
if @@ROWCOUNT > 1 
BEGIN
	-- This is what you want to see in report --SELECT * FROM #Your_Table_Log WHERE ModifiedDate -- between CAST(@startDate as DateTime) and CAST(@endDate as DateTime)
END
ELSE
BEGIN
	-- This mean that no changes happen--SELECT TOP 0 * FROM #Your_Table_Log -- no changes
END

• Edited by Vitaliy Lukashev 18 hours 40 minutes ago

Hi Julie,

Per my understanding that you have many fields in the table to be displayed in the report, some of the fields will always show and others should be hide and show based on the selection of the parameters which include the columns names, right?

By default, we can't display columns dynamically in the report automatically without insert this column in the report before. So, in your scenario, I will suggest you to add all the columns in the reports first and then add show/hide expression on the column visibility based on the selection of the parameter "ChangedField", when some of the fields selectted they will be display, otherwise they will be hidden. For the fields which you want to be always displayed will not need to add the expression in the column visibility.

Details steps about how to acheive this, please referencet to the  article below:
Displaying Dynamic Columns in SSRS Report

If you still have any problem, please feel free to ask.

Regards,
Vicky Liu