February 17, 2016 at 10:21 am
I know, me again but I am actually getting there.
So, I have the need for an iif statement. What I want to do is if the date in dataset 2 matches the date in dataset 1 (which is also in one of the text boxes in the table) then sum a visits value.
so far I have done this but it's throwing up an aggregate error and it doesn't look right but I cannot put my finger on why:
=iif(First(Fields!Date_of_Visits.Value, "Visits"= fields!THEDATE.Value), sum(Fields!GA_Visits.Value, "Visits"),0)
Any help is appreciated or pointers in the right direction, as I have tried to get the right answer.
February 18, 2016 at 2:37 am
Hi Kazmerelda,
There are two ways of approaching this (excluding doing it in the datasource).
You can use the Lookup function, which essentially works like an Excel vlookup against the dataset. However, like a vlookup, it will only return the first match. So this will work, but only if you have a 1-to-1 match of dates between the datasets.
If you have a 1-to-many, or many-to-many match on dates you use a LookupSet to return all the values that match that date from the second dataset. You do need some extra work to handle the results as it returns an array. A good article on how to sum the results of a LookupSet is (here![/url]).
The other approach is to use the ReportItems collection to reference the value in the other textbox directly. This works but there are restrictions on where you can use it.
February 19, 2016 at 9:12 am
Get me? you are coming up trumps for me! It hasn't entirely worked but I am going to persevere as I reckon it just needs tweaking. I might be back, if not I will come back and at least tell you it worked :).
March 1, 2016 at 10:23 am
Right so, it's still not working but I really do think it is almost there rarrghh. Sorry for delay I was off with this vile bug.
So, the date field matches on each, but dataset 1 has multiples for the same date (as the data is split in tiers) however the data in dataset 2 has one line (so it's the one to many).
So I opted to try all suggestions but the one that got me furthest was this link:
http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/
So I have added the code into the report, I have then added this into the field in the SSRS table:
=Code.SumLookup(LookupSet(Fields!THEDATE.Value, First(Fields!THEDATE.Value, "Visits"), Sum(Fields!GA_Visits.Value, "Visits"))
Now I get an error The Value Expression for textrun has an incorrect number of parameters for the function 'LookupSet'.
I have googled but I am getting lost again. Any help is appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply