dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

Dynamic pivot of resultset

Hi,
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,

> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>

Dynamic columns of matrix

I have a matrix, I would like to have a subtotal and total that only
performs the action if the dynamic collumn has a particular value.
I have dynamic column A and B. I only want the subtotal for A not B.
BruceOn Mar 6, 12:31 pm, "Bruce L-C [MVP]" <bruc...@.newsgroup.nospam>
wrote:
> I have a matrix, I would like to have a subtotal and total that only
> performs the action if the dynamic collumn has a particular value.
> I have dynamic column A and B. I only want the subtotal for A not B.
> Bruce
I would suggest using a loop or cursor in the stored procedure or
report query sourcing the report to determine the subtotals and total.
Then in the report use a conditional expression to check for a
particular column value and if it is not the one that needs the
subtotal and total, substitute an empty value for the subtotal and
total. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.

dynamic change of graphs type

We have a requirement where we show a particular simple graph in type columns. we wanted to give the user a parameter like Type: which has some or different charts in it. i would like to give a user a feature of dynamically changing the chart type.

hope you understand me. let me know which is the property i need to assign the parametere value and where i should write that

there is no such property available. For this you need to writhe the function and make it DLL and refer in your report or u can write the code in code window and refer that in your report|||

I have the same problem, I tried the following but did not work.

<Type>=Parameters!ChartType.Value</Type>

|||

Expression-based chart types are not supported at this point. The closest you can get in RS is to define multiple charts with different types. Then through the Visibility.Hidden property you always hide all but the one chart you want to see for a particular parameter value.

-- Robert

|||

OK, just figured out a work around:

1. Create report a report parameter for chart type, say ChartType, add in all the types you want.

2. Use your current chart as a template, make a few copy of it, and paste them in the same report.

3. Change the type property of each chart to a type corresponding to a item in ChartType parameter.

4. Also change the visibility/Hidden property of each chart from False to expression: e.g. for Pie chart, =iif(Parameters!ChartType.Value="Pie",false,true)

Drawback: I guess the Reporting Server may need to create all the charts and Browser may need to load all the charts' images each time when user sees one chart, the other charts are hidden but has been generated/loaded.

Hope this will help before MS fixes the bug in its next release.

|||

I have just had a user ask for this feature, wondering if it is in RS 2005 or planned for the near future?

99

|||

I have the same problem.

I created 5 charts on one report. but i dont want to execute the report for every graph change.

all the charts are using the same dataset.

Can i show /Hide all the charts except the choosen one by clicking on a textbox?

thanks

|||

I have the same problem too,

I wonder how did you solve problem lastly?

And ...

In last 3 months microsoft deliver SP2 of SQL Server 2005. Does it bring any new property to solve this problem?

I will be glad taking your answers about problem...

|||

Hi,

I didnt find a way to choose the graph type by clicking a text box.

i created all the graphs in the report, all connected to the same dataset.

i also created a list control and the user can choose which type he wants.

and by the graphs visibilty propery, i show only the wanted one.

regarding the sp2, i dont familar with it yet. soory.