Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Drew Rosen]

I have a sql stored procedure which is attached that has the following fields in a table:

Branchgroup, correct result,………..
Merch Fay              correct
Merch Fay                null
Merch Jax                 null
Merch NB              correct
Pat Fay                    null
Pat Fay                    null

In my SSRS I have created a row group for Branchgroup I would like to get the report to show on the last page of each group the total number of records and also the total number of records that the correctresult field equals correct within each branchgroup.  For example at the bottom of the Merch Fay group the total would be 2 and the correct would be 1.  Merch Jax total would be 1 and correct would be 0 etc.  thanks 
Drew G. Rosen
9102213561

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————

[John Arnold]

Hi Drew,

I created a test SSRS report and used this query for the data:

SELECT ‘Merch Fay’ AS BranchGroup, ‘correct’ AS Result UNION ALL
SELECT ‘Merch Fay’, null UNION ALL
SELECT ‘Merch Jax’, null UNION ALL
SELECT ‘Merch NB’, ‘correct’ UNION ALL
SELECT ‘Pat Fay’, null UNION ALL
SELECT ‘Pat Fay’, null UNION ALL
SELECT ‘John’, ‘incorrect’

In SSRS, create a Calculated field named   Correct   that has a value of:
=Iif(IsNothing(Fields!Result.Value) Or (Fields!Result.Value <> “correct”), 0, 1)

The Correct field will have a 1 or 0 base on the value of the   Correct   column.

In the page group, if you can add two fields:

Records = [COUNT(Correct)]
Correct = [SUM(Correct)]

This should give you the results you are looking for.  If you have any questions or problems, just give me a yell!

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[Ken Allgood]

Think there might be an easy solution to this.  At the end of the grouping, just add a row inside group, and in the column where you want the group count, do an expression of  CountRows(“Branchgroup”).  I think that should be it.  For the “Correct” count, I would change it in the stored procedure so that instead of “correct”, it returns an int of 1.  Then, on the same row as your countrows, you can do a sum(“Correct Result”).  If you want it to still say “correct” on the lines, you can do an expression on that column to be IIF (1, “Correct”, “null”) or something along those lines, my syntax may be slightly off.

Also, one thing that may make things easier, get something like notepad++ and download the  Poor Mans t-sql formatter plug-in.  It formats SQL into a really nice layout to make it easier to read, and you can set the language to SQL so it highlights syntax keywords

——————————
Ken Allgood
Saratoga Technologies, Inc.
Johnson City TN
——————————
——————————————-

[Blair Christensen]

?From experience, I try to do as much as possible in SQL before I send it to SSRS.  That being said, when looking at your code you are using the same WHERE clause over and over again with fixed values.  It will run more performantly if you take the values and put them in a WITH clause (or several) like this:

WITH movenames as (select 'COD Local' as movename
UNION ALL 'local move cont' as movename
UNION ALL 'N/A Local' as movename
UNION ALL 'O&I Main' as movename
UNIO ALL 'O&I Sub' as movename
UNION ALL 'N/Amilita local' as movename)

select...
    from @fields
   left outer join movenames on @fields.movename = movenames.movename
 where @fields.movename IS NULL
...

This will save the compiler from continuously re-evaluating the same conditions for each of your where clauses and should speed up your query results.

For even better performance (and maintainability), I’d actually recommend creating a static table in your database with all the values you want to exclude and joining against it (as shown).  Given that I counted at least three variations in your code, you could even add a few fields to that table as flags to indicate which “movename” set you wanted for a particular WHERE clause and if you index on those flags, it should fly.  The major benefit is that if you ever add exclusions, its a simple table insert and you’re done – no need to go back in and re-code then test the procedure.
?

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Drew Rosen]

?Thank you very much for all the comments.  I got the report working.  Drew Rosen

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————
——————————————-