Search code examples
reporting-servicesssrs-2012ssrs-2008-r2ssrs-tablix

How to define Row Data group in SSRS if we have two parameters to be considered?


Im new to SSRS reporting. Im trying to edit in *.rdl file, where it has row data grouping to view data for a month.

eg: =Fields!EndOfMonth.Value

Here EndofMonth will return September 30th /October 31st etc. So table is grouped by one month data. I need to edit this for a custom data range.

eg: startdate='2019-09-05', enddate='2019-10-12'

So, report table should display rows between September 5th to October 12th.

I have both startdate enddate parameters defined in the *.rdl file. But I dont know, how i can switch between both date ranges based on condition. that is , if companyid<>50 pick custom range else standard.

eg: I defined like this, but here i could point only one parameter(i used enddate only).So report shows rows between October 1st to 12th only. How can I modify the condition?

=IIF(Parameters!CompanyId.Value<>50,Fields!EndofMonth.Value, Parameters!EndDate.Value)

EDIT

I have 2 datasets defined to switch between companyids.

I modified above condition based on Nicks answer, but i get lookup function can not be used with functions issue. I tried following both way, but getting same rows again and again

=IIF(Parameters!CompanyId.Value<>50,Fields!EndofMonth.Value,
 IIF(Lookup(Fields!TankDateCombo.Value, Fields!TankDateCombo.Value,Fields!Date.Value,"Dataset2")
 >= Parameters!StartDate.Value,Fields!EndofMonth.Value,
Parameters!EndDate.Value))


=IIF(Parameters!CompanyId.Value<>67,Fields!EOM.Value,
 IIF(Day(Lookup(Fields!TankDateCombo.Value, Fields!TankDateCombo.Value,Fields!Date.Value,"OESNeoData"))
 >= Day(Parameters!StartDate.Value), Fields!EOM.Value,
Parameters!EndDate.Value))

MY dataset contains following data; enter image description here


Solution

  • I fixed this by simply deleting that row group and passing start/end dates for both datasets