Post reply

SSRS Grouping Issue

  • March 5, 2010 at 8:08 am

    #217493

    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

  • March 5, 2010 at 9:00 am

    #1129239

    Found a solution...I added a filter to a 'Period' grouping.

  • March 5, 2010 at 11:56 am

    #1129371

    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. 😉

  • March 5, 2010 at 12:05 pm

    #1129380

    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.

  • March 5, 2010 at 1:09 pm

    #1129425

    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. 😉

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply