Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Grouping Issue


SSRS Grouping Issue

Author
Message
Bill-89778
Bill-89778
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 2506
I have an issue that I can't seem to find a good resolution for. I'm creating a commission report for our Accounting department, and would like to show current period and total year values in a table/matrix. Basically, I'd like to show a 'Current Period' group along with a 'Total Year' group. Here is some sample data:

--===== If the test table already exists, drop it     
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Territory VarChar(20),
FiscalYear Int,
FiscalPeriod Int,
ActualSales Decimal(18,2),
Commission Decimal(18,2)
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, Territory, FiscalYear, FiscalPeriod, ActualSales, Commission)

SELECT '1','Eastern',2010,1,1000.00, 5.00
UNION ALL
SELECT '2','Eastern',2010,2,2000.00, 10.00
UNION ALL
SELECT '3','Eastern',2010,3,3000.00, 15.00
UNION ALL
SELECT '4','Eastern',2010,4,4000.00, 20.00
UNION ALL
SELECT '5','Midwest',2010,1,2000.00, 10.00
UNION ALL
SELECT '6','Midwest',2010,2,3000.00, 15.00
UNION ALL
SELECT '7','Midwest',2010,3,4000.00, 20.00
UNION ALL
SELECT '8','Midwest',2010,4,5000.00, 25.00

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON





The user would fill in parameters for Fiscal Year (2010) and Current Period (4)

The basic format of the table would look like this:

Territory Current Period Year to Date
Sales Commission Sales Commission
Eastern 4,000.00 20.00 10,000.00 50.00
Midwest 5,000.00 25.00 14,000.00 70.00


It seems like there should be some way to group on the Current Period parameter, but nothing that seems obvious to me yet :-)

Thanks.

Bill



Bill-89778
Bill-89778
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 2506
Found a solution...I added a filter to a 'Period' grouping.



veeren4urs
veeren4urs
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 204
Hi,

i am also looking same requirment can u please let me know how to do this...


Thanks in advance...

Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. ;-)
Bill-89778
Bill-89778
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 2506
Yes.

I chose to use a table for these values.

After creating the row groups, I added a column group on the 'Period' value. Under group properties, I just added a filter for 'Period' equal to the period parameter.

The last step is to add a total after the period group. This will be the total year values.



veeren4urs
veeren4urs
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 204
Thank you very much,

But in my requirement i dt have the parameters i have timeinterval group in rowgroup and periods in column groups i need to show the data for a particular period at a time...

I really appreciate if you have any idea on this?


Thanks
Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search