Efficiency issue with Parameters

Hi all.

I am new to reporting services and I am having an efficiency problem when loading my report.

I would like to know how Reporting Services handles its datasets.

1: Lets say I have 3 parameters. All set to retrieve data from the same dataset. Does reporting services execute the Query 3 times to get the results for each parameter ? If so, is there a way around this ? I am having a great performance hit with this if it is the case.

2: I am also having an issue with a data processing extensions, when my multi-valued parameter reads the fields from the dataset.. it inserts duplicates and not distinct values, Do i need to explicitly select distinct values in the data processing extension or should Reporting Services automatically do this ?

Any help is greatly appreciated.
Regards,
Neil

1. No, AFAIK this is the behaviour by design. The dataset will be executed several times.

2. I did not get you with that one, could you explain this a bit more ?

Jens k. Suessmeyer.

http://www.sqlserver2005.de

|||Hi Jens K,

thanks so much for the reply.

To explain my 2nd question.
When i retrieve a query lets say [Select names from Name] and it retrives
- Neil
- Jens
- Neil
- Jens

The multi-valued parameter should only contain Neil and Jens .. i.e not show repeate values.
This seems to be the case when I call the query from a standard sql query in the query designer

But when executing the same command in a data processing extension (I return a dataTable with the values) the parameter contains i.e ( Neil , Jens, Neil, Jens)

Does this sound right ? I could be doing something wrong ... just not sure..

Regards,
Neil
|||Yes, and this is also by design. Imagine a situation where you have several Names but different id associated with the names, then you probably want the names to appear, although they are the same. If you only want to use a single instance of each name, you will have to find a way to get only one instance per name like using DISTINCT, Group by or anything else on the basequery. if you already have a datatable you could use following hints:

Creating a DataTable from a DataView
http://msdn2.microsoft.com/en-us/library/73kk32zz(vs.80).aspx

http://support.microsoft.com/kb/325685/en-us

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens K.

Really appreciate your help!!
|||Hi Jens K.

I have a follow up question quickly relating to my first question asked.

If reporting services executes the dataset multiple times. Say I set the available values of a parameter to Dataset A and the Default values to Dataset A. Does this mean the dataset is executed twice ? This seems highly inefficient....

once again, much appreciate all your help.

Regards,
Neil
|||Yes, this is (sort of) by design as the resultsets could be different based on (non-)determinsitic parameters.

Jens K. Suessmeyer.

http://www.sqlserver2005.de