Jump to content
  • 0

Question

Hi there,

I have a tabular report datapage, that I am using to display calculations from my table.

I want to show week number, and what has happened during that week.  So far I have 2 columns other than week number, number of cards updated, and total number of updates.  The calculations I am using are working fine, but it's showing each number as a separate row.  So if the calculation for week 6 is 7 updates, it shows 7 rows.  See attached screenshot.  Below is my code.  Anyone know how I only show each line once (so there's only 1 row for each week number)

Number of cards updated
SELECT COUNT (DISTINCT [@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]

Total number of updates
SELECT COUNT([@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]

 

2018-03-23.png

Share this post


Link to post
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Since you are doing the calculation using SELECT statement, you can create a lookup table with one field: "Week". Then use this lookup table as the source of your report DataPage.

Share this post


Link to post
Share on other sites
  • 0

I have a similar issue, could you explain the solution a little more in depth?  Here is my example:  I have a Teacher, Enrollment, and course tables.  The enrollments tie to teacher with teacher ids and courses with course ids.  The teacher may have multiple enrollment for one course.  I want to create a view to be used in a datapage that would list once the Teacher and Course.  How would I go about this?

Here is what it looks like now and want it to only have 1 record for Bob sanders, but because he has 2 enrollments in his class, it lists twice.  Any Ideas?

Courses_Course                                                      Personnel_Full_Name

English Language Arts, Grade 7                     Bob Sanders

English Language Arts, Grade 7                     Bob Sanders

English, Language and Composition, AP   Christopher Baker

 

Share this post


Link to post
Share on other sites
  • 0

As I understand you have these tables:

Teacher: Teacher_ID

Course: Course_ID

Enrollment: Teacher_ID, Course_ID

to not get repeated info, what you can have is to create a report based on your Course table. Create a view to join Enrollment and Course so you can get the name of the teacher, we call it: 

CourseEnrollment

And then use this formula: 

 

SELECT TOP 1 Teacher_Full_Name FROM _v_CourseEnrollment WHERE Teacher_ID = target.[@field:CourseEnrollment_Teacher_ID] ORDER BY Teacher_Full_Name DESC

 

But this will only give you one teacher per teacher, so if you have more teachers for the same course it will only show you the top 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×