Replacing Query?

• govind-211283

  SSC Eights!

  Points: 962

  More actions

  October 24, 2005 at 11:55 pm

  #165764

  How to replace report's Dataset query at runtime, i.e.

  Report have query embedded:

  SELECT EmpID AS , FirstName AS [TITLE], LastName AS [OTHER] FROM Emp

  Now at runtime we want to replace it

  SELECT DeptID AS , DeptName AS [TITLE], NULL AS [OTHER] FROM Dept

  This way we easily have 1 report for such similar simple reports.

  Thanks,

  Govind.

   

• Syed Asim Rizvi

  Ten Centuries

  Points: 1112

  More actions

  October 25, 2005 at 9:18 am

  #599843

  Ok, I'm sure there might be other ways, but the way I do it is like:

  1. Create stored proc for each, lets say rspEMP and rspDEPT (you can even use Queries).
  2. Create a report parameter, (lets say "param", with values 0 for EMP and 1 for DEPT), which will be use to identify user selection.
  3. Create or use shared Dataset (for data source purpose).
  4. In Generic Query Designer, in place of query, use expression, like:

     =IIF(Parameters!param.Value=0, "EXEC rspEMP", "EXEC rspDEPT")

     ^{(Note: Make sure Command Type is TEXT)}

  5. Click … button next to your dataset name. The Dataset dialog box will appear, Select the Fields tab, change the Fields table to match your columns (Like Field Name=CODE, Type=Database Field, Name=CODE, and so on for TITLE and OTHER). Click Ok to exit.
  6. Now in Layout tab, just Drag and drop your fields.

  Hope this will help!

• govind-211283

  SSC Eights!

  Points: 962

  More actions

  October 30, 2005 at 1:46 am

  #601180

  Hello Asim,

  Sorry boss for this late reply.

  This is a nice trick to resolve this but SQLRS must have some native way to achieve the same. I think we can anything (i.e. changing report behavior at runtime but substituting its Proxy classes like QueryDefinition... but i've to check) Anyways, thanks for the reply.

  Regards,

  Govind