Report with dynamic columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Report with dynamic columns

Author

Topic

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-10 : 09:07:44

Hi,
I'm trying to create a report with dynamic columns. I mean that the user is able to select if he wants to see a double drill report with client group and product group, or agents with clients, or everything else he needs. I tried to insert some variables in the query that extracts data from report, and using that variables like parameters, but Reporting Services always get the parameter default value and not the correct value i give him.
How can I solve my problem? There is another way to obtain dynamic reports?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 10:56:07

Have you checked if required parameters have been correctly added to report and proper mapping to variables has been done?

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-10 : 11:28:17

Yes, I think..
I set the UniqueName of the column in the default value of Report Parameter and "=Parameters!PARAM.Value" in the query Parameter, where PARAM is the name of report parameter. But the connection between the two kinds of parameters is not applied!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:34:42

quote:
Originally posted by Riccardo-QLT

Yes, I think..
I set the UniqueName of the column in the default value of Report Parameter and "=Parameters!PARAM.Value" in the query Parameter, where PARAM is the name of report parameter. But the connection between the two kinds of parameters is not applied!!!

connection? didnt get that? where?

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-10 : 11:41:33

I think that the report parameters and the query parameters are not connected because if I modify the Parameter in report preview and refresh the report, it always takes the default value and creates extract from the database the same columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:53:00

quote:
Originally posted by Riccardo-QLT

I think that the report parameters and the query parameters are not connected because if I modify the Parameter in report preview and refresh the report, it always takes the default value and creates extract from the database the same columns.

Did you refresh your dataset and went to parameters tab to checked if they are correctly mapped.Also have you double checked the result by running the query directly in SQL management studio with your parameter values.

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-10 : 12:02:31

Yes, I'm sure that the query is correct because with default values it runs without problems. The problem is that changes are not interpreted by the query...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:03:30

quote:
Originally posted by Riccardo-QLT

Yes, I'm sure that the query is correct because with default values it runs without problems. The problem is that changes are not interpreted by the query...

You cant be certain unless you test query for different combinations of parameter values.If you parameter mapping is correct, only possibility i can think of would be some problem in query for combinations of value passed.

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-11 : 03:04:56

But If I change default parameter with everyone I need it always run correctly! The real problem is in execution mode, it not takes the parameter i give it danamically. I can see only the default columns I set by default. The thing I need to kwnow is: is this method correct or this problem can be solved in any other manner?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 03:29:19

Continued here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104583

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 04:15:51

quote:
Originally posted by Riccardo-QLT

But If I change default parameter with everyone I need it always run correctly! The real problem is in execution mode, it not takes the parameter i give it danamically. I can see only the default columns I set by default. The thing I need to kwnow is: is this method correct or this problem can be solved in any other manner?

Can you post the query used?

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-11 : 04:34:36

[code]
SELECT NON EMPTY { [Measures].[BUDGET_QT], [Measures].[DELTA_B],
[Measures].[BUDGET], [Measures].[QT], [Measures].[ANNO_PRECQT],
[Measures].[DELTA_P], [Measures].[DELTAPM_B], [Measures].[DELTAPM_P],
[Measures].[PREZZO_MEDIO], [Measures].[DELTAQT_P], [Measures].[PR_MEDIO_P],
[Measures].[BUDGET_PR_MEDIO], [Measures].[ANNO_PREC], [Measures].[FATTURATO],
[Measures].[DELTAQT_B] } ON COLUMNS, NON EMPTY { (STRTOSET(VALUE1) * STRTOSET(VALUE2) *
[PERIODO].[ANNO - MESE].[Mese Desc].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
STRTOSET(@GEOGRGEOGRAFICA, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@AGENTEAGENTI, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@DIVISIONEDESDIV, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@CLIENTICLIENTI, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@PRODOTTOPRODOTTI, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@PERIODOANNOMESE, CONSTRAINED) ) ON COLUMNS FROM [QLT BI])))))) WHERE (
IIF( STRTOSET(@CLIENTICLIENTI, CONSTRAINED).Count = 1, STRTOSET(@CLIENTICLIENTI,
CONSTRAINED), [CLIENTI].[CLIENTI].currentmember ), IIF(
STRTOSET(@DIVISIONEDESDIV, CONSTRAINED).Count = 1, STRTOSET(@DIVISIONEDESDIV,
CONSTRAINED), [DIVISIONE].[DES DIV].currentmember ), IIF(
STRTOSET(@AGENTEAGENTI, CONSTRAINED).Count = 1, STRTOSET(@AGENTEAGENTI,
CONSTRAINED), [AGENTE].[AGENTI].currentmember ) ) CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE,
FONT_FLAGS
[/code]

where VALUE1 default value is [GEOGR].[AREA].ALLMEMBERS and
VALUE2 default value is [PRODOTTO].[TIPO].ALLMEMBERS

GEOGR and PRODOTTO are Dimensions, and AREA and TIPO are Levels.

If I change VALUE1 and VALUE2 at runtime, nothing happens...

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-06-11 : 16:17:24

First of all,

if you change VALUE1 and/or VALUE2 at sql server management studio in the , did you get the data as you expected?

Riccardo-QLT
Starting Member

9 Posts

Posted - 2008-06-12 : 03:24:23

yes, If I statically change the values of VALUE1 and VALUE2 the report appears correctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources