Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Row wise sub total...

Posted on 2007-07-29
5
Medium Priority
?
488 Views
Last Modified: 2012-05-05
How can i add the individual,row sub total , as I have String Values in Detail Part.  (MATRIX Report)

                                                             
            Sunday      Monday    Tuesday   Wednesday..          p    A    Total
User1      P                 A               P                P                       3     1      4
User2      A                A               A               A                       0     4      4

Like the Above Example, I shall be very thankful to all

0
Comment
Question by:123-imran
3 Comments
 
LVL 13

Accepted Solution

by:
crgary_tx earned 2000 total points
ID: 19588090
Create the following formulas

//@p_sun
if {table.sunday} = 'P' then 1 else 0
//@p_mon
if {table.monday} = 'P' then 1 else 0
..
..
//@a_sun
if{table.sunday} = 'A' then 1 else 0
//@a_mon
if {table.monday}-'B' then 1 else 0
..
..
Now to get the totals
//@p_total
@p_sun+@p_mon+@p_tue...
//a_total
@a_sun+@a_mon+@a_tue...
//@total
p_mon+a_mon+p_tue+a_tue+..

0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19589132
What is the table structure(s). Do you have a single table or is there a user table and DOW table?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19596169
SELECT Sunday, Monday, ...,
    CASE WHEN Sunday = 'P' THEN 1 ELSE 0 END +
        CASE WHEN Monday = 'P' THEN 1 ELSE 0 END +
        ... AS P,
    CASE WHEN Sunday = 'A' THEN 1 ELSE 0 END +
        CASE WHEN Monday = 'A' THEN 1 ELSE 0 END +
        ... AS A,
    CASE WHEN Sunday IN ('A', 'P') THEN 1 ELSE 0 END +
        CASE WHEN Monday IN ('A', 'P') THEN 1 ELSE 0 END +
        ... AS Total
FROM ...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question