SSRS Count rows in colmns to get the percentage

Clive Wightman 66 Reputation points
2021-03-18T10:47:21.42+00:00

Hi

I could do with some help in getting find the expression that will,

Count the total number of Shipment_ID's in the column / by the number of 0 in GOOD_BAD_TIME * 100. to calculate the percentage. (Delivered on Time)

Aim is to calculate the percentages across the top of a report for the success rate along with % of fails with the reason (so 2% might be stuck in customs)

Regards

Clive

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,106 questions
No comments
{count} votes

8 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2021-03-19T05:57:33.983+00:00

    Hi @Clive Wightman ,

    Couldn't fully understand your requirement just with words.

    Could you please share the design sample for us to do more analysis.

    You may also use the excel's screenshot to show what's your report data like and what's the result you want.

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    No comments

  2. Clive Wightman 66 Reputation points
    2021-03-19T10:39:27.407+00:00

    79652-performance2.jpgHi Zoe,

    Thank you for responding I have attached some sample output of the data we have. along with how I see the output of the report working.
    to Calculate the success of a shipment meeting it's transit time, its the GOOD_BAD_TIME with value of 0 / total of rows *100
    Then for the next is STATUS_NAME = Clearance delay or Approaching agents deadline with the GOOD_BAD_TIME with value of 1 / total of rows *100

    I hope this helps to make sense of what I'm trying to achieve.
    79661-performance1-li.jpg

    No comments

  3. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2021-03-22T07:20:37.977+00:00

    Hi @Clive Wightman ,

    Thanks for your information, please refer below expression in SSRS report.

    Count rows of GOOD_BAD_TIME where value=0 :

    =sum(iif(Fields!GOOD_BAD_TIME.value=0,1,0))  
    

    Count rows of GOOD_BAD_TIME where value=1 :

    =sum(iif(Fields!GOOD_BAD_TIME.value=1,1,0))  
    

    If I misunderstand your needs, please incorrect me.

    Regards,
    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    What can I do if my transaction log is full?--- Hot issues November

    How to convert Profiler trace into a SQL Server table -- Hot issues November

    No comments

  4. Clive Wightman 66 Reputation points
    2021-03-22T10:56:16.057+00:00

    Hi

    I have tried this but it just gives me 1 or 1 on all the rows not the total 1 or 0 for that column,

    Thanks

    No comments

  5. Clive Wightman 66 Reputation points
    2021-03-22T11:04:52.53+00:00

    I get the total if I do add_total as well, so going on from that, I tried this but it fails.
    =sum(iif(Fields!GOOD_BAD_TIME.value= "0" ,1,0) / CountRows("GOOD_BAD_TIME") *100)

    No comments