0

I am trying to find out how can I write the below logic in SSRS:

The structure of my report is:

Structure

All the rows and columns are grouped together.

The New Week Status has 2 fields: Current Week and Previous Week.

Current Week ranges from 30th Jan - 5th Feb

Previous Week ranges from 23th Jan - 29th Feb

The Gross Sales is in %.

I need to display only those restaurants where at least 5 days in Current Week and Previous Week is above 2% Gross.

Example:

Example

In the above example, R1 has more than 5 days > 2%, so it will be displayed

R2 has less than 5 days > 2%, so it won't be displayed

How can the logic be implemented for this?

Shivang
  • 231
  • 1
  • 5
  • 17

1 Answers1

0

If you are not calculating the Gross Sales in the report this could work for you.

Select the whole Restaurant row and right click it, select Row Visibility... option:

enter image description here

Select Show or hide based on an expression: and use this expression:

=IIF(SUM(IIF(Fields!Gross_Sales.Value> 0.02,1,0))>=5,False,True)

Now only rows with at least 5 dates that have more than 0.02 (2%) Gross Sales are visible.

UPDATE: Adding examples.

I've recreated your dataset and the matrix, so having this structure:

enter image description here

Using the expression I posted above it hides the R2 row as expected.

enter image description here

UPDATE:

=IIF(
  SUM(IIF(Fields!Total_Cost.Value/Fields!Sales_Gross.Value>0.02,1,0))>=5
  or
  SUM(IIF(Fields!New_Week_Status.Value = "Current Week" and Fields!Total_Cost.Value/Fields!Sales_Gross.Value>0.02,1,0))>=3
,False,True)
halfer
  • 19,824
  • 17
  • 99
  • 186
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • There is a grouping of New Week Status and Full Date...so the mentioned method doesn't work. – Shivang Feb 09 '17 at 04:52
  • @Shivang, I've recreated your dataset as mentioned in my update. Check you are using the exact same configuration shown in my example. – alejandro zuleta Feb 09 '17 at 14:53
  • I just got to see that my Gross Sales are calculated using TotalCost/SalesGross. How can I still achieve the solution? – Shivang Feb 09 '17 at 23:20
  • @Shivang, just use the expression that calculates the `Gross Sales` inside the expression. – alejandro zuleta Feb 10 '17 at 01:43
  • I used: =IIF(SUM(IIF( SUM(Fields!Total_Cost.Value)/SUM(Fields!Sales_Gross.Value) > 0.02,1,0))>=5,False,True) But now nothing is displayed – Shivang Feb 10 '17 at 02:51
  • @Shivang, I've updated my answer give it a try. Good luck – alejandro zuleta Feb 10 '17 at 02:56
  • Hi, It works thanks a lot... Now the only last thing remaining is using another condition where 3 days in Current Week is above 2% Gross.. So either this or the above one (the one you gave me solution for) should be displayed in same tablix – Shivang Feb 10 '17 at 03:04
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135337/discussion-between-shivang-and-alejandro-zuleta). – Shivang Feb 10 '17 at 03:08
  • @Shivang, just add an additional condition as shown in my last update. – alejandro zuleta Feb 10 '17 at 03:16
  • Thanks a lot,, your solution did the trick! Very much appreciated – Shivang Feb 10 '17 at 03:24