March 29, 2015 at 4:37 am
How can I create a more complex iif statement?
=iif(Fields!row_description.Value = "All Leads", count(Fields!lead_id.Value), "") <--works
need same expression to also evaluate
=iif(Fields!row_description.Value = "Leads Referred", count(Fields!lead_id.Value) / <--doesn't work
iif(Fields!row_description.Value = "All Leads", count(Fields!lead_id.Value)
I am trying to get a count of lead_ids where row_description value is "Leads Referred" and DIVIDE that by count of lead_ids where row_description value is "All Leads" .
is it possible to have conditional aggregation based on the value in the row_description? I really don't want to go back to the SQL and restructure it unless that is ONLY solution.
March 29, 2015 at 6:11 pm
I am trying to get a count of lead_ids where row_description value is "Leads Referred" and DIVIDE that by count of lead_ids where row_description value is "All Leads"
KK,
I did something like this when I was trying to get % of total for drug studies. ("What percent of the population experienced this symptom?") One way of doing this would be to create a data set of leads that's either not filtered or filtered for only the data you want. In other words, it's not filtered by "referred"/whatever.
I did mine this way:
1. Create a report-level variable (numeric/integer) to store the "population count". (The count of ALL leads.)
2. Create a dataset of "all leads" (well, the ones relevant to this report dataset).
3. value of the variable = =CountRows("Referrals_dset") (all the values, regardless of type).
4. the % of Total: =COUNT(Fields!PersonID.Value)/Variables!PopulationSize.Value
Here's a quick dummy report with an embedded recordset...
(Can't tell if it's attached... but that should point you in the right direction.)
March 29, 2015 at 6:15 pm
checking it out. Thanks a lot.
March 29, 2015 at 7:58 pm
Hmm... wondering where my post went... Turns out I did the thing wrong. 😀
The way I got it to work was to create two datasets - one of the standard "referrals" dataset that gets filtered by the @ReferralType parameter, and another that contains as few fields as possible and does not get filtered. (There's no sense in returning a lot of extra data - I only want the count of members.)
Here (hopefully) is the report that actually works. In the unfiltered dataset, I'm just returning a single column - basically, enough to get a count back. I'm using it to assign a value to the variable @PopulationSize, which I can then use to divide the count of the filtered recordset by.
Okay, okay... here is the fixed report. Sorry about that!
Here's the calculation for the variable, PopulationSize:
=CountRows("UnfilteredReferrals_dset")
Then in the report, the expression for the percent of Population Size is:
=COUNT(Fields!PersonID.Value)/Variables!PopulationSize.Value
March 29, 2015 at 11:08 pm
OK. Looks like I need a second dataset (unfiltered). Does it matter whether I create variable? I think not and will try that and report back.
March 29, 2015 at 11:40 pm
I created the variable to store the aggregate value to divide by. If you're doing "% of total" then you would store the total value in the variable, and then just divide by it everywhere you're trying to get a ratio. If you tear apart the report I posted, you can see how it works.
Otherwise I will probably need a report with embedded dummy data to work on.
March 29, 2015 at 11:51 pm
ok, I still think I shouldn't have to use a variable since I can reference the dataset in my expression box like so.
=Switch(Fields!row_description.Value = "Follow-Up Score", AVG(Fields!score.Value),
Fields!row_description.Value = "% Leads Referred", count(Fields!ref.Value, "results") / count(Fields!lead_id.Value, "lead_unfiltered"),
True, Count(Fields!lead_id.Value))
the ref.value is always a 1 or 0, and the lead_id.value is an id....which is why Count against the columns directly should work (IMO). I will mess with it a bit more before I delve into using a variable....seems so close, plus have no idea how to create Variables in SSRS. Would be first time. Have created parameters though.
March 30, 2015 at 12:07 am
ok, now the trouble is that I don't know how to tell the expression that I need the count(lead_id) from the unfiltered set to pivot by month......
I will go to bed an when I awake I will make dummy data, if you would continue to be so kind as to check in again tomorrow. Thanks a lot for help.
Are variables the answer here? Will the counts from unfiltered data set pivot along with the upper dataset if I use a variable?
March 30, 2015 at 2:05 am
To create a variable...
1. Under the Report menu, choose "Report Properties".
2. Click on the Variables Tab (in the left pane).
3. Click the Add button.
4. Give the variable a name.
5. click the FX button to add a calculation
... the rest works like usual.
March 30, 2015 at 5:36 am
pietlinden,
modified and attached the rdl you sent me, having added a date column and a matrix to the right of your table.
row: referral type
columns: date
details: count of personID grouped by referral type
at runtime the matrix should display three groupings pivoted by Jan and Feb. (Attached .png showing desired result.)
External
Internal
External/All Referral Types as Percent <---where am having problem
March 31, 2015 at 10:54 am
Maybe it's the lack of coffee or something, but the basic matrix is simple, but getting the % of Internal at the bottom as a pain. (I'm starting to think of this in terms of a PowerPivot table, because there it would probably be easy!)
The problem is the filtering inside the grouping (Internal only). Not sure how to deal with that yet.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply