SSRS 'where clause'

Question

I've got a table that contains sales information for several companies. Each sales transaction the company makes is stored in the table, and the week of the year (1-52) that the sale took place within is stored also. Here's a small example of the database table that I'm querying to produce the SSRS report.

|---------------------|------------------|------------------|
|      Company        |         Week     |Sales_Transaction |
|---------------------|------------------|------------------|
|        Alpha        |         20       |      1.00        |
|---------------------|------------------|------------------|
|        Alpha        |         20       |      2.00        |
|---------------------|------------------|------------------|
|        Beta         |         20       |      9.00        |
|---------------------|------------------|------------------|
|        Alpha        |         21       |      5.00        |
|---------------------|------------------|------------------|
|        Coolbeans    |         21       |      5.50        |
|---------------------|------------------|------------------|
|        Alpha        |         22       |      2.00        |
|---------------------|------------------|------------------|
|        Alpha        |         22       |      2.00        |
|---------------------|------------------|------------------|
|        Coolbeans    |         22       |      3.00        |
|---------------------|------------------|------------------|

I have a matrix with a row group which produces a line in the matrix for each company. The matrix has 52 additional columns for each week of the year. Here's a condensed version of the matrix and data I want to see.

|--------------|---------------|----------------|----------------|
|   Company    | # Sales Wk 20 |  # Sales Wk 21 |  # Sales Wk 22 |
|--------------|---------------|----------------|----------------|
|   Alpha      |       2       |      1         |      2         |
|--------------|---------------|----------------|----------------|
|   Beta       |       1       |      0         |      0         |
|--------------|---------------|----------------|----------------|
|   Coolbeans  |       0       |      1         |      1         |
|--------------|---------------|----------------|----------------|

To count the number of sales transactions for each week for each company, I'm using this expression like this for each column:

=Count(IIF(Fields!Sales_Week_Number.Value = "20", Fields!Sales.Value, 0))

Using the example expression above which I'm placing in the # Sales Wk 20 matrix column, the problem is that instead of counting ONLY the transactions that occurred in week 20, it counts transactions for all weeks for the company. The result is that in column # Sales Wk 20, it shows a 5 for Alpha, a 1 for Beta, and a 2 for Coolbeans.

What do I need to do to make it only count the sales transaction from the specific week?

Side Note: Regarding the 52 columns for each week of the year, I intentionally did not use a column group for this b/c I need to do some other calculations/comparisons with another matrix which doesn't play nice when column groups are used. I did, however, use a row group for the companies.

Use PIVOT if you use SQL Server. docs.microsoft.com/en-us/sql/t-sql/queries/… — Muhammad Vakili
I should have been more clear. What I provided as the example of the database i'm pulling from, is actually the result set of the sql query I run. The second thing in my question that looks like a db result set is actually supposed to be a representation of the matrix in SSRS that I want to see. Is there a way to write an expression in SSRS to get what I'm wanting without having to modify the query/result set? I can't modify the query that produced that result set b/c it is used for a lot of other things in the same report. — Aaron

niktrs niktrs · Accepted Answer · 2018-06-15T10:48:56

Your expression should use SUM instead of count

=SUM(IIF(Fields!Sales_Transaction.Value=0,0,1))

SSRS 'where clause'

3 Answers