report with matrix

  • Hi all,

     

    I need your idea/suggestion. I don’t really know how to explain it well

    Hope you can understand with my sample below.

     

    I create a store proc and run it for every quarter ( because the data will be available quarterly)

    Right now my store proc is just taken 2 date para ( from and To)

     

     

    I want to create a report which looks like below

     

    Assuming today I run it for  Jan-Mar,

     

    Time Frame

    nurseHome

    Allied health

    Accomo

    Travel

    Jan – Mar  Q1

    45

    5

    10

    5

    April – Jun Q2

    0

    0

    0

    0

    Jul – Sep Q3

    0

    0

    0

    0

    Oct-Dec Q4

    0

    0

    0

    0

    Total

    45

    5

    10

    5

     

     

    then when next quarter I run for April-June

     

     

    Time Frame

    nurseHome

    Allied health

    Accomo

    Travel

    Jan – Mar  Q1

    45

    5

    10

    5

    April – Jun Q2

    15

    10

    15

    8

    Jul – Sep Q3

    0

    0

    0

    0

    Oct-Dec Q4

    0

    0

    0

    0

    Total

    60

    15

    25

    13

     

     

    And so on till Q4

     

    Can I use Reporting service?

     

    How do I do it? What sort of store proc template I should use?

     

    Thanks,

    Susan

  • Hi ,

    I Understand your posting but i need some more information to solve your problem

    This Scenario is enough for how the report should look like But it is not enough to Solve your problem on the functionality

    How is the data returned by stored procedure.

    Is it is returning data for all Quarters?

    There would be no Problem if data is not there for other Quarters

    Give me a sample data how the Sp is returning the data.

    I hope Reporting services has flexibility to do what you want

    You can use IIF Conditions,Filters....

    Thanks and regards

    Raj deep.A

  • Thanks for replying my email.

    here what  I mean

    Q1,Q2,Q3,Q4 need to be taken from the visit date of the client

     

    So for example

    Q1 is all clients who have visit date between (1/1/06 -31/3/06)

    Q2  is all clients who have visit date between (1/4/06 -30/6/06)

    Q3 is all clients who have visit date between (1/7/06 -30/9/06)

    Q4 is all clients who have visit date between (1/10/06 -31/12/06)

     

    I thought I need to put in where.

    I reckon there would be no problem if data is not there for other quarters untill the time comes.

     

     

    Here the sample of my query. If it's possible I would like to write this as Store Proc

     

    SELECT

    CEILING(SUM(CASE

            when se.[Service Type Code] IN (1)

            then se.[Numeric Value]

                    ELSE 0 END)/60) AS '001 Domestic Assistance',

                    SUM(CASE

            when se.[Service Type Code] IN (1)

            then v.TravHour

                    ELSE 0 END)AS TravHour1,

                    SUM(CASE

            when se.[Service Type Code] IN (1)

            then v.TravKm

                    ELSE 0 END)AS TravKm1,

                    (CASE

            when se.[Service Type Code] IN (1)

            then count(distinct v.CNo)

                    ELSE 0 END)AS TotCli1

    FROM [Service Entry] se left join VW_ActVisitClient1 v

    on v.CNo =se.[Client No_]

    and v.AdmitNo = se.[Admission No_]

    and v.VisitNo =se.[Visit No_]

    inner join [Service Type] st on se.[Service Type Code] = st.Code

     

    WHERE

    where VDate1 between '2006/04/01' and '2006/06/30'

    and  Proj ='81'

    GROUP BY [Service Type Code]

    Thanks again

Viewing 3 posts - 1 through 2 (of 2 total)

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