where to store Data source Connection details

where to store Data source Connection details

• Glider Guider

  use shared data source for all your reports. in that way using report manager you can change connection settings for all the dependencies at one place.
  
  -geeks
  
  

• Phildotnet

  Maybe a derived table
  
  Select SUM(cnt_modid) Cnt_Modid, Month_mm
  FROM (
  

  SELECT    
  count(SiteMods.ModID) as Cnt_Modid, MONTH(Tracking.ActualDate) as Month_mm
  FROM
  SiteMods
  INNER JOIN ModDetail ON SiteMods.ModID = ModDetail.ModID
  INNER JOIN ModTypeLK ON ModSubDetail.ModTypeRID = ModTypeLK.ModTypeRID
  INNER JOIN Tracking ON SiteMods.ModID = Tracking.ModID
  WHERE Tracking.TaskID = 41
  AND MONTH(Tracking.ActualDate) IN (select Month_mm from testMonthLK)
  AND ModTypeLK.ModTypeDesc like @ModTypeDesc
  group by MONTH(Tracking.ActualDate)

  ) view1
  

  GROUP by Month_mm
  
  
  

• PaoloTCS

  If I understand the problem correctly, you want to return 12 months of data to the report   Could you not rewrite the procedure to pass a null value in to the month field, and allow the full 12 months of data to be returned   (using something like WHERE COALESCE(@Month,[Month]) = [Month]
  
  Not sure about limits of DataSets in RS (haven't hit it yet) but the less of them the better.\
  
  cheers,
  Andrew
  
  
  

• Chris Brandsma

  Thanks. But i need 12 values for 12 months. ( also ic ouldn't use COALESCE as this is a nullable field)

  the report is like

   Jan Feb Mar ... Dec
  Type 1
  Type 2
  Type 3
  ..
  ..

  I re-wrote the SP, to take the Type as Parameter and created a lookup table testMonthLK
  for the 12 months.

  
  CREATE PROCEDURE dbo.testRS
   @ModTypeDesc varchar(255)
  AS
  SELECT    
  count(SiteMods.ModID) as Cnt_Modid, MONTH(Tracking.ActualDate) as Month_mm
  FROM
  SiteMods
  INNER JOIN ModDetail ON SiteMods.ModID = ModDetail.ModID
  INNER JOIN ModTypeLK ON ModSubDetail.ModTypeRID = ModTypeLK.ModTypeRID
  INNER JOIN Tracking ON SiteMods.ModID = Tracking.ModID
  WHERE Tracking.TaskID = 41
  AND MONTH(Tracking.ActualDate) IN (select Month_mm from testMonthLK)
  AND ModTypeLK.ModTypeDesc like @ModTypeDesc
  group by MONTH(Tracking.ActualDate)

  
  For eg, if SP returns 2 rows for @ModTypeDesc = "Type1"

  Cnt_Modid Month_mm
  3  4
  2  8

  In the report i have the fields with Expr
  =IIf(Fields!Month_mm.Value="04",Fields!Cnt_Modid.Value,"")
  and
  =IIf(Fields!Month_mm.Value="08",Fields!Cnt_Modid.Value,"")

  the Values gets displayed correctly for April and August but it displays in 2 separate rows.
  Any idea how to bring all the values in a single row for Type1

  Thanks.

  
  
  

• abhi11209

  thanks it helped. I got it.
  
  I have another issue for which i need help.
  
  I'm creating a report that does some calculation for all the months. I wrote a Stored Proc that does it and takes Month as a Parameter and created a dataset for it. But i have to execute the same SP for all 12 months, means 12 times in the same report with different month as a Parameter. Does it mean i have to create 12 datasets with different Report Parameters Is there any better way to do it like automating Parameters in a single dataset
  
  also i would like to know How many datasets are allowed to use in a report
  
  Thanks.
  
  
• where to store Data source Connection details