Highlighted

Can I Share a value returned from a report function?

Explorer ,
Apr 16, 2015

Copy link to clipboard

Copied

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?

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

TOPICS
Reporting

Views

362

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Can I Share a value returned from a report function?

Explorer ,
Apr 16, 2015

Copy link to clipboard

Copied

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?

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

TOPICS
Reporting

Views

363

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 16, 2015 0
Advocate ,
Apr 16, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 16, 2015 0
Explorer ,
Apr 17, 2015

Copy link to clipboard

Copied

Thanks for your response.

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 17, 2015 0
Cozmo2 LATEST
Explorer ,
Apr 20, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 20, 2015 0