SSRS SUM IIF With Matrix

Question

I have a problem whereby my query in SSRS has to be SUM IIF to ensure its matching the hard coded string, but when I enter it into my matrix with a column group of period, it will only display for the first column.

I have attached a picture that shows in yellow where it won't show for February onwards that contains the SUM IIF and also in black below it which is how it should look, but this is just the sales field on its own.

If anybody could advise if there's a way to use SUM IIF with the matrix that would be really appreciated.

EDIT

I've attached two images below. The CalHeading expands to more months simply by the parameter selection. The sum of period sales seems to work seamlessly with the periods, but the SUM IIF wont talk to the dates in the matrix. Both row groups are grouped by Customer.

Answer 1

What I've done here is do most of the work in the dataset query. This makes the basic report a 5minute job.

I've taken you sample data and put it into a table variable for simplicity and then produced the final output for the report. The query below is all in the dataset query. It basically produces a row for every combination of customer, category and period along with the Sale value if there is any, if not it returns 0 (You can change this to be NULL if you want blanks rather than 0 in the report by removing the ISNULL() bit).

DECLARE @t TABLE (Customer varchar(20), Category varchar(20), Sale float, [Period] int)

    INSERT INTO @t SELECT 'Customer 1', 'Category 2', 5048, 202201
    INSERT INTO @t SELECT 'Customer 1', 'Category 3', 5945, 202202
    INSERT INTO @t SELECT 'Customer 1', 'Category 4', 2696, 202203
    INSERT INTO @t SELECT 'Customer 2', 'Category 4', 3500, 202205
    INSERT INTO @t SELECT 'Customer 2', 'Category 5', 1100, 202202
    INSERT INTO @t SELECT 'Customer 3', 'Category 1', 2696, 202203
    INSERT INTO @t SELECT 'Customer 3', 'Category 2', 6900, 202204
    INSERT INTO @t SELECT 'Customer 3', 'Category 3', 3670, 202201
    INSERT INTO @t SELECT 'Customer 3', 'Category 4', 2340, 202206
    INSERT INTO @t SELECT 'Customer 3', 'Category 5', 6500, 202202
    INSERT INTO @t SELECT 'Customer 4', 'Category 1', 4013, 202205
    INSERT INTO @t SELECT 'Customer 4', 'Category 2', 3100, 202203
    INSERT INTO @t SELECT 'Customer 4', 'Category 3', 3201, 202206
    INSERT INTO @t SELECT 'Customer 4', 'Category 4', 5300, 202203
    INSERT INTO @t SELECT 'Customer 4', 'Category 5', 2109, 202204
    INSERT INTO @t SELECT 'Customer 5', 'Category 1', 8100, 202203

SELECT 
    x.Customer, x.Category, x.[Period]
    , ISNULL(t.Sale, 0) AS Sale -- remove the ISNULL() if you want no data to de displayed as blanks
    FROM
    (
        SELECT * FROM -- build a list which combines all customers and categories and periods
            (SELECT DISTINCT Customer FROM @t) cu 
            CROSS JOIN
            (SELECT DISTINCT Category FROM @t) cat
            CROSS JOIN
            (SELECT DISTINCT [Period] FROM @t) per
    ) x
    LEFT JOIN @t t -- now LEFT join the original data
        ON x.Customer = t.Customer
        AND x.Category = t.Category
        AND x.[Period] = t.[Period]

Now thre port design is really simple

---

RDL File for testing

---

The complete RDL is here, this contains the dataset query and the report design itself. You will just need to edit the data source (DataSource1) and point it at something valid (change the connection) and then it should work.

https://1drv.ms/u/s!Al1Kq21dFT1ik8V2YJufKnA_t1Pp3w?e=RTwn3Q

The final output looks like this