question

CliveWightman-0851 avatar image
0 Votes"
CliveWightman-0851 asked CliveWightman-8621 answered

SSRS Count rows in colmns to get the percentage

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @CliveWightman-0851,

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CliveWightman-0851 avatar image
0 Votes"
CliveWightman-0851 answered CliveWightman-0851 edited

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



performance2.jpg (17.1 KiB)
performance1-li.jpg (705.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @CliveWightman-0851,

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CliveWightman-0851 avatar image
0 Votes"
CliveWightman-0851 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CliveWightman-0851 avatar image
0 Votes"
CliveWightman-0851 answered

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered Zoehui-MSFT edited

Hi @CliveWightman-0851,

We should use add total to get the value you want and the expression should be like:

 =sum(iif(Fields!GOOD_BAD_TIME.value=0,1,0))/(RowNumber("YourDataSet")*100)

80386-tempaa1223.gif

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



tempaa1223.gif (145.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CliveWightman-8621 avatar image
0 Votes"
CliveWightman-8621 answered

Hi Zoe,

Thank you very much for your help, I now have this calculating as I was after.


Regards

Clive

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CliveWightman-8621 avatar image
0 Votes"
CliveWightman-8621 answered

Hi Zoe

Sorry quick one I'm getting
=sum(Fields!C_STATUS_NAME.value="Clearance delay"))/(RowNumber("dst_PERFORMANCE"))

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.