-1

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

Ratha
  • 9,434
  • 17
  • 85
  • 163
  • Do you want to filter on the custom date range or group on it? If you just _filter_ on it then you only have one group, and grouping is irrelevant. If you want to _group_ on it, do you want the "leftovers" to appear in different groups? i.e. `2019-09-01` to `2019-09-04` and `2019-10-13` to `2019-10-31` willl be in their own groups. At this stage you need to post some sample input and output data. – Nick.Mc Oct 24 '19 at 04:27
  • @Nick.McDermaid , Yes as I mentioned, i have defined start end date parameters for report. So rowgroup should be defined in those two parameters based on companyid condition. leftoverdates will appear in other groups based o start end date parameter which user selects – Ratha Oct 24 '19 at 04:36
  • You must have an actual date field in your dataset right? Say it's `TransactionDate`, then you need to "Bucket" this correctly. Currently you "Bucket" it by always picking the end of the month. For your new requirements you need to bucket it differently. The expression that evaluates the "Bucket" (the actual grouping column) needs to check if `TransactionDate` is between your parameters. If it _is_, return the parameter end date. If it _isn't_, return the end of month date. That will get you started. Then you just have to cover the case from start of month to the start parameter date. – Nick.Mc Oct 24 '19 at 04:48
  • @Nick.McDermaid Yes I have actual date filed returned by both datasets.(I use 2 datasets. Based on companyid condition I pick values from different datasets, using lookup function)Could you please add this as an answer with example. Sorry, im new to SSRS and trying out different functions . look none working to me – Ratha Oct 24 '19 at 04:50
  • To provide an answer I'll need to go check up on SSRS syntax etc.... The ideal outcome here is that you find a solution based on my comments and add your own answer, as I am unlikely to have the time to double check syntax. Also without actual sample data and column names I'm kinda guessing – Nick.Mc Oct 24 '19 at 04:58
  • @Nick.McDermaid I provided my dataset and the query I try..ANy help? – Ratha Oct 24 '19 at 05:31
  • It helps to post the exact error. Is this your error? https://stackoverflow.com/questions/17318939/lookup-function-in-ssrs-report. It's a while since I've used SSRS but I suggest you try and remove the Lookup (these can usually be replaced by performing a join in the source SQL - Unless you aren't using SQL?). I also suggest you try and build up columns in your source dataset bit by bit to see where the issue is. – Nick.Mc Oct 24 '19 at 05:37

1 Answers1

0

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

Ratha
  • 9,434
  • 17
  • 85
  • 163