report with matrix

• Susan S

  SSCommitted

  Points: 1771

  More actions

  July 26, 2006 at 7:45 pm

  #169031

  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

• Raj Deep Alapati

  Ten Centuries

  Points: 1118

  More actions

  July 27, 2006 at 6:43 am

  #651857

  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

• Susan S

  SSCommitted

  Points: 1771

  More actions

  July 27, 2006 at 4:11 pm

  #652035

  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