reporting services - Adding values to a Report when there is no Data in query SSRS -

i have query returns sales representatives number, category, sales.

the result this:

there 4 categories called g1,g2,g3,g4.

as can see sales representative 11 sold 10 each category (yellow rows). representative 12 sold category g3 , g4.

the idea show in report categories , populate 0 did not sell on particular category.

it must grouped sales representative if make tablix grouping sales representatives have this:

but want this:

is there expression use add these?

what did far create group, group of course sales representatives , combine cells column , created row group each category, this:

but if execute report repeat categories g1,g2... each time category exists particular sales representative.

another problem is, how can evaluate hardcoded category in report if not exist in datasource cant make iif("g1" = fields!category.value,fields!sales.value,"0") not comparing g1 null or isnothing, comparing exists.

i think can achieve smoothly using t-sql @ query level. don't know why don't use simplest way apply kind of logic since in t-sql can use almost every logic.

however kind of challenges come possible solution.

this sample dataset:

in ssrs dataset (not in t-sql) i've added calculated field called another

another field set below expression:

=fields!salesrep.value & "-" & fields!category.value 

i've added tablix following data arrangement

as mentioned before category field hardcoded, right column sales set expression:

=iif(isnothing(lookup(fields!salesrep.value & "-" & reportitems!textbox62.value, fields!another.value,fields!sales.value,"dataset7")),0, lookup(fields!salesrep.value & "-" & reportitems!textbox62.value, fields!another.value,fields!sales.value,"dataset7")) 

note: reportitems!textbox62.value corresponds textbox g1 hardcoded. have replace textbox reference corresponding in tablix every category.

it preview below tablix.

let me know if helpful.