Dynamic SELECT Command in SqlDataSource

I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid.

I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e.SelectCommand="usp_reports_shortages" to"usp_reports_shortagesbyID" and"usp_reports_shortagesbyDate"

depending on the value selected in the dropdownlist. So, is there any way to do this??
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ConnectionStrings:TESTDrivercommunication%>"

SelectCommand="usp_reports_shortages"SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ControlParameterControlID="lblDriver"Name="date1"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="lblTODate"Name="date2"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="DDlDriver"Name="driver"PropertyName="SelectedValue"

Type="Int32"/>

<asp:SessionParameterName="week"SessionField="s_week"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

Numerous ways.

Change the value in the dropdown's selectedindex changed event.

Change it in SqlDatasource's selecting event.

Change the select command to an if. "IF @.DDlDriver=1 THEN EXECusp_reports_shortages @.date1,@.date2,@.weekIF @.DDlDriver=2 THEN EXEC usp_reports_shortagesbyID @.date1,@.date2,@.week IF @.DDlDriver=3 THEN EXECusp_reports_shortagesbyDate @.date1,@.date2,@.week".

Make a stored procedure that takes all 4 paramters and encapsulates the IF's.

sql

Dynamic Row Level Security

Hi,
Is it possible to configure Reporting Services 2005 so that the same report
will apply different data filters depending on the user running the report ?
i.e A German user will only see German data and an English user will only
see English data even if they enter a parameter for 'All Europe'.
Thanks.RS supports a property User!UserID which returns the identity of the
interactive user (assuming Windows authentication). You can pass the user
identity to the data source as a query parameter to implement data filtering
at the data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
"Duncan Allen" <DuncanAllen@.discussions.microsoft.com> wrote in message
news:B76E2612-99F1-490B-AFBF-E11B5280BE43@.microsoft.com...
> Hi,
> Is it possible to configure Reporting Services 2005 so that the same
> report
> will apply different data filters depending on the user running the report
> ?
> i.e A German user will only see German data and an English user will only
> see English data even if they enter a parameter for 'All Europe'.
> Thanks.

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date fields), that is sum(amount) in Jan05,Feb05,Mar05......depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

i am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

What is the output of stored procedure? It should return date, category, Quantitiy/Number (appears in dataset)

If you return those three values from stored procedure then you can use Table grouping feature in the Report desinger to build the report.

Thanks
Murthy

|||

I am getting values from the Stored procedure and also able to see the results in the Dataset on the report designer DATA page, but when it comes to laying out the Report on the report designer LAYOUT page and see the PREVIEW, that is where my problem comes.

I am able to display the Department name column but not the Amount column which i have grouped by Year and month.

Any help is appreciated.

Thanks,

vnswathi.

|||

If you get into Dataset then you will surely have those values in Layout/Preview pages too.

Can you verify textbox properties in the layout screen and see are you able to get dataset values there or not? Also check visibility attribute of those text fileds too?

As a simple test, you can verify this by placing a textbox and set those dataset values and see the preview.

Thanks
Murthy

|||I have a other feeling that you may doing something wrong in the Grouping section. Are you building drill-down report? If yes you have to check toggle items?|||

Ok, i was doing it right, but the only thing which i was doing wrong was using a tabluar layout.

I have used Matrix layout and it worked.

Thanks,

vnswathi.

|||Great...any idea what is going wrong with the tabular report?

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date columns), that is sum(amount) in Jan05,Feb05,Mar05........depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

I am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

hi ,

i guess i came across one such report and what i did was instead of using strings i used the BeginDate and Enddate Parameters and feed them into a function which would rip off the months and the sum up the data corresponding to the month.

month(@.BeginDate) would get u the month and then you can use Case statement to actually sum up the amount for that particular month.

i guess it more of a tedious job, but should work.

|||

yes, it works for months, but how about years.

because we know that there would be 12 Months, but you don't know the Date range.

Instead i have used Matrix layout and it worked.

Thanks,

vnswathi.

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.

Dynamic image in my header

HI ,

I need to change the image in my header and footer depending on the sectionid. Is it possible ? how ?

thanks in advance

Chk out this blog on loading images

http://technoblab.blogspot.com

.........................................................................................

remember to "Mark As Answer" if the post helped you

|||

you do this easily in reporting service bu IIf statement check this article for complete example

http://blog.toddtaylor.com/PermaLink,guid,b1517702-ad4b-48e1-a6e3-b736e8a982cb.aspx

Dynamic Filtering Expression

I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:

If SuppressZero = 1
Filter where Quantity <> 0

If SuppressZero = 2
No Filter

If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"

How can I do this in my report?

In general its better to filter the data in the query than the report. The report filters are per report object not the entire report so in the case of mutiple objects you may need to set multiple filters. Assuming you have a single table report you can set a filter on the that table filter property. The filter porperty has three fields; expression, operator, value. One solution is

Expression: iif((Paramters!SupprssZero=1 and Fields!Quantity=0) or Parameters!SuppressZero=2 or (Parameters!SuppressZero=3 and Fields!Quantity=0 and InstockFamily="N"),1,0)

Operator: =

Value = 1

|||assuming you pass the SuppressZero param to the stored procedure, you need to add a WHERE clause something like

WHERE
(@.suppressZero = 1 and quantity <> 0)
OR
(@.supressZero = 2)
OR
(@.supressZero = 3 and (quantity <> 0 OR instockfamily <> 'Y')

Dynamic Dataset for Chart in Report

Is it possible to dynamically set the dataset for a chart on a report?

What I'm trying to do is generate a single chart on a report. Depending on what items a user selects as parameters would determine the dataset used. Unfortunately, one dataset comes from Oracle the other from SQLServer.

Thanks!

You could have two charts - one per dataset. Set the chart.Visibility property so that only one chart is visible depending on the selected parameter value.

-- Robert

Dynamic database tables

How does one make a database table dynamic?
I need to have a single report display data from different SQL tables
depending on which client is viewing the data.Here's an example:
1. Run the following static SQL in data pane of report designer: select
name, xtype, crdate from sysobjects
2. Switch to Layout view. This creates the fields in fields window.
3. Go back to the data pane and pase the following query into it: ="select
name, xtype, crdate from " & iif(LCase(User!UserID) = "redmond\ravimu",
"pubs", "northwind") & "..sysobjects where type = 'u'". This will
dynamically source the data from diferent table depending on the current
user.
Notes:
1. Assumption: Table schemas are the same.
2. If the target tables have same number of columna but different names, you
can map each column name to a unique alias as follows: select name As Col1,
xtype As Col2, crdate As Col3 from sysobjects
3. If you have many users, you may want to replace iif() with Switch()
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html
/vafctswitch.asp)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"jimmy stewart" <jims@.wolfmagick.com> wrote in message
news:u9$uYmQbEHA.3944@.tk2msftngp13.phx.gbl...
> How does one make a database table dynamic?
> I need to have a single report display data from different SQL tables
> depending on which client is viewing the data.
>

Dynamic Data Connections

Hello,
I am designing a SRS integration where the data source for a report must be
changed depending on which database the user chooses. There are any number
of databases, all having the same schema. The user can select which database
they want to query in a drop-down list in the web application, and then
select the report to run.
Any ideas on the best way to dynamically set the connection at run-time?
I see that in RDL there is a tag for <ConnectionString> so I could set that.
However, I think with lots of people accessing the report this may cause
problems. Perhaps I need to make a .NET assembly that modifies the
connection string tag at run-time?
I am accessing the report via an internet shortcut to the URL, but it is
possible for me to modify the URL before it is called. Another possibility
is to include the connection string as a parameter and pass it in the URL.
This just seems a bit clunky to me, since all reports would have to have
this parameter. It would make it slightly more difficult to allow users to
design their own reports.
Any suggestions?
:-D Maliksee this:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
or search for "dynamic connection" in this newsgroup
Antonio
"Abdul Malik Said" wrote:
> Hello,
> I am designing a SRS integration where the data source for a report must be
> changed depending on which database the user chooses. There are any number
> of databases, all having the same schema. The user can select which database
> they want to query in a drop-down list in the web application, and then
> select the report to run.
> Any ideas on the best way to dynamically set the connection at run-time?
> I see that in RDL there is a tag for <ConnectionString> so I could set that.
> However, I think with lots of people accessing the report this may cause
> problems. Perhaps I need to make a .NET assembly that modifies the
> connection string tag at run-time?
> I am accessing the report via an internet shortcut to the URL, but it is
> possible for me to modify the URL before it is called. Another possibility
> is to include the connection string as a parameter and pass it in the URL.
> This just seems a bit clunky to me, since all reports would have to have
> this parameter. It would make it slightly more difficult to allow users to
> design their own reports.
> Any suggestions?
> :-D Malik
>
>|||Thank you for your quick response. After doing a search through the entire
newsgroup on this issue, I can see that setting a data source dynamically is
a common problem. However, I still have one question:
Is it possible to dynamically set a connection string or data source at
run-time by developing a Data Processing Extension? For my solution, it must
be set for that user's run-time only, and must not change the data source
for other users trying to access the same report at the same time. This is a
specific requirement of the integration.
I want to avoid as much as possible resorting to multiple copies of the
report, and setting the data source dynamically in the stored procedure.
These are workarounds, where I am hoping that DPE could allow me to write a
real solution. If DPE is the way to go, could you suggest any resources for
this? I know C# and I have read some of the Books Online stuff.
Malik
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> see this:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> or search for "dynamic connection" in this newsgroup
> Antonio
> "Abdul Malik Said" wrote:
> > Hello,
> >
> > I am designing a SRS integration where the data source for a report must
be
> > changed depending on which database the user chooses. There are any
number
> > of databases, all having the same schema. The user can select which
database
> > they want to query in a drop-down list in the web application, and then
> > select the report to run.
> >
> > Any ideas on the best way to dynamically set the connection at run-time?
> >
> > I see that in RDL there is a tag for <ConnectionString> so I could set
that.
> > However, I think with lots of people accessing the report this may cause
> > problems. Perhaps I need to make a .NET assembly that modifies the
> > connection string tag at run-time?
> >
> > I am accessing the report via an internet shortcut to the URL, but it is
> > possible for me to modify the URL before it is called. Another
possibility
> > is to include the connection string as a parameter and pass it in the
URL.
> > This just seems a bit clunky to me, since all reports would have to have
> > this parameter. It would make it slightly more difficult to allow users
to
> > design their own reports.
> >
> > Any suggestions?
> >
> > :-D Malik
> >
> >
> >|||No experience with DPE. You could change programmatically the connection string using web services to generate the report but the problem is the user's concurrency. I think this is a big issue in reporting services.
sorry
Antonio
"Abdul Malik Said" wrote:
> Thank you for your quick response. After doing a search through the entire
> newsgroup on this issue, I can see that setting a data source dynamically is
> a common problem. However, I still have one question:
> Is it possible to dynamically set a connection string or data source at
> run-time by developing a Data Processing Extension? For my solution, it must
> be set for that user's run-time only, and must not change the data source
> for other users trying to access the same report at the same time. This is a
> specific requirement of the integration.
> I want to avoid as much as possible resorting to multiple copies of the
> report, and setting the data source dynamically in the stored procedure.
> These are workarounds, where I am hoping that DPE could allow me to write a
> real solution. If DPE is the way to go, could you suggest any resources for
> this? I know C# and I have read some of the Books Online stuff.
>
> Malik
> "Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
> news:F8D54E0A-8428-43E2-941D-C5DEA489349F@.microsoft.com...
> > see this:
> >
> >
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=uaVMmQoEEHA.2600%40TK2MSFTNGP09.phx.gbl
> >
> > or search for "dynamic connection" in this newsgroup
> >
> > Antonio
> >
> > "Abdul Malik Said" wrote:
> >
> > > Hello,
> > >
> > > I am designing a SRS integration where the data source for a report must
> be
> > > changed depending on which database the user chooses. There are any
> number
> > > of databases, all having the same schema. The user can select which
> database
> > > they want to query in a drop-down list in the web application, and then
> > > select the report to run.
> > >
> > > Any ideas on the best way to dynamically set the connection at run-time?
> > >
> > > I see that in RDL there is a tag for <ConnectionString> so I could set
> that.
> > > However, I think with lots of people accessing the report this may cause
> > > problems. Perhaps I need to make a .NET assembly that modifies the
> > > connection string tag at run-time?
> > >
> > > I am accessing the report via an internet shortcut to the URL, but it is
> > > possible for me to modify the URL before it is called. Another
> possibility
> > > is to include the connection string as a parameter and pass it in the
> URL.
> > > This just seems a bit clunky to me, since all reports would have to have
> > > this parameter. It would make it slightly more difficult to allow users
> to
> > > design their own reports.
> > >
> > > Any suggestions?
> > >
> > > :-D Malik
> > >
> > >
> > >
>
>

Dynamic Column header in CASE statement

Hello,
Can you dynamically change the header name in a CASE statement? In the
following, I would like to be able to change the column header depending on
what the value of @.Actual is.
Thanks in advance, Steven
DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
SET @.strParm03 = 'Direct'
SET @.Actual = 'Actual-' + @.strParm03
SET @.Forecast = 'Forecast-' + @.strParm03
SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
From MyTable
"sck10" <sck10@.online.nospam> wrote in message
news:emwZ%231IRGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can you dynamically change the header name in a CASE statement? In the
> following, I would like to be able to change the column header depending
> on
> what the value of @.Actual is.
> Thanks in advance, Steven
>
> DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
> DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
> SET @.strParm03 = 'Direct'
> SET @.Actual = 'Actual-' + @.strParm03
> SET @.Forecast = 'Forecast-' + @.strParm03
> SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
> From MyTable
>
No. Column names are determined at compile time. Why not just display a name
dynamically in your client application?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi sck,
Welcome to use MSDN Managed Newsgroup Support. And thanks David's great
reply.
From your description, my understanding of this issue is: You want to
dynamically change the header name. If I misunderstood your concern, please
feel free to point it out.
As David mentioned, you can not change the column name directly in a sql
statement. But there is a way to do it.
For example:
DECLARE @.colname as varchar(50)
DECLARE @.cmd as varchar(8000)
SET @.cmd = 'select columnName as '+ @.colname + ' from Mytable'
EXEC (@.cmd)
If you chagne the @.colname , the sql statement you execute will change. I
think this might meet your request.
Hope this will be helpful!
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Column header in CASE statement

Hello,
Can you dynamically change the header name in a CASE statement? In the
following, I would like to be able to change the column header depending on
what the value of @.Actual is.
Thanks in advance, Steven
DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
SET @.strParm03 = 'Direct'
SET @.Actual = 'Actual-' + @.strParm03
SET @.Forecast = 'Forecast-' + @.strParm03
SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
From MyTable"sck10" <sck10@.online.nospam> wrote in message
news:emwZ%231IRGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can you dynamically change the header name in a CASE statement? In the
> following, I would like to be able to change the column header depending
> on
> what the value of @.Actual is.
> Thanks in advance, Steven
>
> DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
> DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
> SET @.strParm03 = 'Direct'
> SET @.Actual = 'Actual-' + @.strParm03
> SET @.Forecast = 'Forecast-' + @.strParm03
> SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
> From MyTable
>
No. Column names are determined at compile time. Why not just display a name
dynamically in your client application?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi sck,
Welcome to use MSDN Managed Newsgroup Support. And thanks David's great
reply.
From your description, my understanding of this issue is: You want to
dynamically change the header name. If I misunderstood your concern, please
feel free to point it out.
As David mentioned, you can not change the column name directly in a sql
statement. But there is a way to do it.
For example:
DECLARE @.colname as varchar(50)
DECLARE @.cmd as varchar(8000)
SET @.cmd = 'select columnName as '+ @.colname + ' from Mytable'
EXEC (@.cmd)
If you chagne the @.colname , the sql statement you execute will change. I
think this might meet your request.
Hope this will be helpful!
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic change of number of series on the chart

Depending on the result of my stored procedure logic I need to display
different number of lines on the chart in my report. I can not drop the data
fileds into the chart design as suggested in the Help, because at design time
I do not know their names and how many of them I will have when report is
called. Is it possible? What is involved?
Thanks.Not sure I understand what you are trying to do. It sounds like you want a
dynamic series grouping expression which is basically just based on the
field that comes from the stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> Depending on the result of my stored procedure logic I need to display
> different number of lines on the chart in my report. I can not drop the
> data
> fileds into the chart design as suggested in the Help, because at design
> time
> I do not know their names and how many of them I will have when report is
> called. Is it possible? What is involved?
> Thanks.|||Thank you Robert,
You indirectly answered my question. I just could not make sense of grouping
capabilities of the chart. The Help on this topic is not very "helpful".
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand what you are trying to do. It sounds like you want a
> dynamic series grouping expression which is basically just based on the
> field that comes from the stored procedure.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > Depending on the result of my stored procedure logic I need to display
> > different number of lines on the chart in my report. I can not drop the
> > data
> > fileds into the chart design as suggested in the Help, because at design
> > time
> > I do not know their names and how many of them I will have when report is
> > called. Is it possible? What is involved?
> > Thanks.
>
>|||I am having to do the same thing like dynamically having series elements
depending on the query results... is this possible to do?
"Simon Gold" wrote:
> Thank you Robert,
> You indirectly answered my question. I just could not make sense of grouping
> capabilities of the chart. The Help on this topic is not very "helpful".
> "Robert Bruckner [MSFT]" wrote:
> > Not sure I understand what you are trying to do. It sounds like you want a
> > dynamic series grouping expression which is basically just based on the
> > field that comes from the stored procedure.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > Depending on the result of my stored procedure logic I need to display
> > > different number of lines on the chart in my report. I can not drop the
> > > data
> > > fileds into the chart design as suggested in the Help, because at design
> > > time
> > > I do not know their names and how many of them I will have when report is
> > > called. Is it possible? What is involved?
> > > Thanks.
> >
> >
> >|||Yes, It is possible to do that. The "Help" does not have a good wordage on
it, but here how you do it.
Imagine that you want to display stock prices for several different
companies for a say 10 days period. Create a table 'Prices' that would have
at least three columns: 'DayNumber', 'StockPrice' and 'CompanyName'. Populate
that table with your data. Create a dataset for your chart where run a
"SELECT DayNumber,StockPrice, CompanyName FROM Prices".
Om the chart drug and drop 'DayNumber field under the horizontal axis
(Category fields), drop StockPrice field above the chart into 'data fields'
and drop 'Companyname' filed to the right of the chart into 'Series fileds'.
This will create a chart that will have as many series as many company names
you have in your table.
Good luck...
Simon.
StockPrice
"Mathi" wrote:
> I am having to do the same thing like dynamically having series elements
> depending on the query results... is this possible to do?
> "Simon Gold" wrote:
> > Thank you Robert,
> > You indirectly answered my question. I just could not make sense of grouping
> > capabilities of the chart. The Help on this topic is not very "helpful".
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Not sure I understand what you are trying to do. It sounds like you want a
> > > dynamic series grouping expression which is basically just based on the
> > > field that comes from the stored procedure.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > > Depending on the result of my stored procedure logic I need to display
> > > > different number of lines on the chart in my report. I can not drop the
> > > > data
> > > > fileds into the chart design as suggested in the Help, because at design
> > > > time
> > > > I do not know their names and how many of them I will have when report is
> > > > called. Is it possible? What is involved?
> > > > Thanks.
> > >
> > >
> > >

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic (Parameterized) Page-Breaks

Hi,

I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.

Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?

Thanks in Advance.

Isnt there a way to achieve this ?

Its URGENT. Any help is highly appreciated.

|||

you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

|||

Thanks a lot. It worked.

Thanks again.

|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one group

Dynamic (Parameterized) Page-Breaks

Hi,

I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.

Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?

Thanks in Advance.

Isnt there a way to achieve this ?

Its URGENT. Any help is highly appreciated.

|||

you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

|||

Thanks a lot. It worked.

Thanks again.

|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one group