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

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...