Skip to main content
C
Cozmo2
Inspiring
April 16, 2015
Answered

Can I Share a value returned from a report function?

  • April 16, 2015
  • 1 reply
  • 655 views

I have two report functions:

1. counts all the occurrences of courses that match a specific school year.

2. gives me a total count of courses

I also need to count the occurrences of  courses that do not match a specific school year. How can I subtract the value from the first report from the second report function?

Is there a better way to do this?

This topic has been closed for replies.
Correct answer EddieLotter

As always, try to do as much as you can on the database side.

For example, in Microsoft SQL Server you could do the following:

SELECT

  schoolID, courseID, additionalFields,

  CASE WHEN CourseYear = SchoolYear THEN 1 ELSE 0 END AS CourseYearMatch,

  CASE WHEN CourseYear <> SchoolYear THEN 1 ELSE 0 END AS CourseYearMismatch

FROM tableName

Then, in Report Builder, you can use simple calculated fields to sum these two fields to get the counts you want.

Cheers

Eddie

1 reply

EddieLotter
EddieLotterCorrect answer
Inspiring
April 16, 2015

As always, try to do as much as you can on the database side.

For example, in Microsoft SQL Server you could do the following:

SELECT

  schoolID, courseID, additionalFields,

  CASE WHEN CourseYear = SchoolYear THEN 1 ELSE 0 END AS CourseYearMatch,

  CASE WHEN CourseYear <> SchoolYear THEN 1 ELSE 0 END AS CourseYearMismatch

FROM tableName

Then, in Report Builder, you can use simple calculated fields to sum these two fields to get the counts you want.

Cheers

Eddie

C
Cozmo2Author
Inspiring
April 17, 2015

Thanks for your response.

Is there a way I can also count the distinct ID's by Level?

C
Cozmo2Author
Inspiring
April 20, 2015

Each student can have multiple courses in the school year and I only want to count them once, how can i do that?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices