March 28, 2018 at 7:28 am
I'm using the lookup function in ssrs.
I have three records in dataset 2. Total population 1, total population 2, and total population 3.
I'm using total population to calculate % in my report which is being grouped into country1, country2, and country3.
The % of the report showing country 1 is correct, but the % of country2 and country3 is using country1's total population.
I think it's because I have country 1 as the first record in my dataset2.
The good thing is that in all three groups (country1, country2, and country3) I'm seeing correct respective populations, but don't know how to make the groups use their own populations not country1's population for calculating %.
I'm using following formula in my expression to get the total population:
=Lookup(Fields!country_group.Value, Fields!country_group.Value, Fields!popul_ct.Value, "dataset2")
I'm using following formula in my expression to calculate %:
IIF(First(Fields!popul_ct.Value, "dataset2") > 0, Fields!distinct_pop.Value/
IIF(First(Fields!popul_ct.Value, "dataset2") > 0, First(Fields!popul_ct.Value, "dataset2"), 1), 0)
I know the problem is due to using First, but when I remove it I get error. How can I modify this expression.
Any help would be greatly appreciated.
Thank you!
March 28, 2018 at 2:21 pm
Just gonna throw this out there...
What if you created a few variables in your report and grabbed the population size from somewhere? Then you can just divide by that? (Super easy that way, especially if you need to reuse the value).
March 29, 2018 at 10:26 am
I have to wonder why these calculations are being done in the report. Not enough detail here to know whether or not the alternative of doing them in the dataset's stored procedure or query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 30, 2018 at 11:40 am
pietlinden that's exactly what I did.
I created a variable and stored the value of the total population. Then it was easy for me to use the variable else where in the report. This solved the problem.
sgmunson, since I'm using the value of two different datasets (based on two different datasources) I had to do this calculation on the report.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply