I have two tables
one table has year,department code, slots available for kindergarten,slots available for highschool
other table has disctrict code , grade level and funding code,student iD,year
I want the report output to be
year||district code|| slots available for kindergarten|| Actual no-of kindergarten students with(funding code=11 and grade level=454)|| slots available for highschool || Actual no-of high school students with(funding code=29 and grade level=300
I am trying to calculate the Actual no-of kindergarten students using count func
count( case when code=11 and grade level=454 then (1) else (0) end ) and similarly for other
But my numbers are not correct
Please help. Thanks.
In your count, you need for [district code], [year].
i.e Count([Grade type] for [district code], [year])
Think the value you are getting is the lumped value of all district codes/ multi year.
Another thing I would do differently is first group the grade level then types of funnding codes. Prompt filter for Grade level/ Funding type used.
Thanks,
RK