Dynamic Row group in matrix

Hi All !

I want to show row groups as hierarchy levels and need the

sub total values belongs to each group and sub group levels. But the

most important point is that my top next top group (from child to

parent ) is not static its dynamic.i.e for a diffrent senario my under

displayed example can have Universe>Earth as parent for Australia and USA.

eg:

1.Australia

|-sydney

|-Melbourne

2.USA

|North US

|North US(1)

|North US(2)

|South US

|South US(1)

|South US(2)

Can I get some help from anybody for making a dynamic row groups in the matrix.

Waiting for a kind help.

Regards,

This is the Dynamic Group I used. I am not the original author, I search on dynamic grouping for SSRS and modified the example showed.

Put this is group expression:

=iif(Parameters!Group_By.Value is Nothing,Fields!SomeField.Value , Fields(iif(Parameters!Group_By.Value is Nothing, "DefaultID",Parameters!Group_By.Value)).Value)

|||Look at this blog entry from Chris Hayes http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||thanx for ur help.
I have done this with some other logic.

Dynamic Row group in matrix

Hi All !
I want to show row groups as hierarchy levels and need the sub total values belongs to each group and sub group levels. But the most important point is that my top next top group (from child to parent ) is not static its dynamic.i.e for a diffrent senario my under displayed example can have Universe>Earth as parent for Australia and USA.
eg:
1.Australia
|-sydney
|-Melbourne
2.USA
|North US
|North US(1)
|North US(2)
|South US
|South US(1)
|South US(2)
Can I get some help from anybody for making a dynamic row groups in the matrix.
Waiting for a kind help.
Regards,

This is the Dynamic Group I used. I am not the original author, I search on dynamic grouping for SSRS and modified the example showed.

Put this is group expression:

=iif(Parameters!Group_By.Value is Nothing,Fields!SomeField.Value , Fields(iif(Parameters!Group_By.Value is Nothing, "DefaultID",Parameters!Group_By.Value)).Value)

|||Look at this blog entry from Chris Hayes http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||thanx for ur help.
I have done this with some other logic.sql

Dynamic Row Group in a matrix

Hi All !

I want to show row groups as hierarchy levels and need the

sub total values belongs to each group and sub group levels. But the

most important point is that my top next top group (from child to

parent ) is not static its dynamic.i.e for a diffrent senario my under

displayed example can have Universe>Earth as parent for Australia and USA.

eg:

1.Australia

|-sydney

|-Melbourne

2.USA

|North US

|North US(1)

|North US(2)

|South US

|South US(1)

|South US(2)

Can I get some help from anybody for making a dynamic row groups in the matrix.

Waiting for a kind help.

Regards,

Hi Sanjib,

Check this link.It will be helpful.

|||

Hi Dear !

Thanx for reply me , but i'm sorry to say that there is no link mentioned.

Plz mention the link.

waiting for u'r reply,

dynamic row formatting based on dataset field values

HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.

Dynamic Query

I need to write a stored proceed that has 15 parameters that returns a
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:

> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:

> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql

Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;

SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql

Dynamic Parameter

I have created the following two parameters:
DateRange Parameter with available values:
1. label = today, value = 1
2. label = yesterday, value = 2
StartDate with Default Value (Non Queried) = =
iif(Parameters!PMDateRange.Value = "1",
now(),
iif(Parameters!PMDateRange.Value = "2",
Today.AddDays(-1),
nothing))
The idea is that the user selects a daterange parameter and that the
startdate parameter is populated accordingly, the user must still have the
ability to manually override the start date if desired.
When I run the report I get the following error message.
he value expression for the report parameter â'PMStartDateâ' contains an
error: [BC30654] 'Return' statement in a Function or a Get must return a
value.The way I have implemented this is by creating another dataset like
(although against oracle)
select sysdate-1 as Yesterday, sysdate as Today from dual
and use this dataset in the default section of the parameter as 'Queried'..
the user can change the dates if needed..
Hope this helps..
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:D74138F5-D842-4AB8-82E9-5CCB4315AA90@.microsoft.com...
> I have created the following two parameters:
> DateRange Parameter with available values:
> 1. label = today, value = 1
> 2. label = yesterday, value = 2
> StartDate with Default Value (Non Queried) = => iif(Parameters!PMDateRange.Value = "1",
> now(),
> iif(Parameters!PMDateRange.Value = "2",
> Today.AddDays(-1),
> nothing))
> The idea is that the user selects a daterange parameter and that the
> startdate parameter is populated accordingly, the user must still have the
> ability to manually override the start date if desired.
> When I run the report I get the following error message.
> he value expression for the report parameter 'PMStartDate' contains an
> error: [BC30654] 'Return' statement in a Function or a Get must return a
> value.|||Thanks RK but its not quite what I am looking for. Does anyone have any other
ideas?
PLEASE!!!
"RK Balaji" wrote:
> The way I have implemented this is by creating another dataset like
> (although against oracle)
> select sysdate-1 as Yesterday, sysdate as Today from dual
> and use this dataset in the default section of the parameter as 'Queried'..
> the user can change the dates if needed..
> Hope this helps..
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:D74138F5-D842-4AB8-82E9-5CCB4315AA90@.microsoft.com...
> > I have created the following two parameters:
> >
> > DateRange Parameter with available values:
> > 1. label = today, value = 1
> > 2. label = yesterday, value = 2
> >
> > StartDate with Default Value (Non Queried) = => >
> > iif(Parameters!PMDateRange.Value = "1",
> > now(),
> > iif(Parameters!PMDateRange.Value = "2",
> > Today.AddDays(-1),
> > nothing))
> >
> > The idea is that the user selects a daterange parameter and that the
> > startdate parameter is populated accordingly, the user must still have the
> > ability to manually override the start date if desired.
> >
> > When I run the report I get the following error message.
> >
> > he value expression for the report parameter 'PMStartDate' contains an
> > error: [BC30654] 'Return' statement in a Function or a Get must return a
> > value.
>
>

Dynamic modification of data flow objects

My idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.

You cannot alter the metadata of the data-flow pipeline. In english, that means you cannot change the names and data-types of the columns, not can you add or remove them.

However, you CAN dynamically set the external sources and destinations. Would this be sufficient for you?

-Jamie

|||Hi Jamie - thanks for the quick reply. I don't think this will be sufficient. The 200 tables are all different - we are replicationg tables from an Oracle 8i ERP database to SQL for reporting and analysis purposes. The metadata on each source-destination combination will be different from the next so this will be a problem. As I see it the only way to accomplish this concept is to dynamically create a new package for each table i.e each iteration of the ForEach loop. Do you agree?|||

OK, you have to create 200 packages. But you only have to create them once.

You are correct that the only other option is to dynamically build the package. That's not much fun, believe me!

-Jamie

|||

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

|||

Peter G D wrote:

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

200 different requirements means 200 things to build. The complexity is in your requirement. I'm slightly confused how it could be made more elegant. I'd welcome your ideas though.

Peter G D wrote:

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Interesting one. I don't know is the honest answer but I'd love to know. It depends on alot of things, mainly on the amount of data you're moving. The larger dataset then the less the proportionate time to build the package.

Peter G D wrote:

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

No way. You won't be able to do this using VBScript. I don't even think you can do it in the Script Task. You are in custom task territory.

-Jamie

|||

i think you you need to use ado.net to iterate over a lookup table that has the table name, source info, and destination info. for each table, you read the data into a recordset, then insert that data into the destination table. you should also probably use a transaction to rollback everything in the event of an error. all of this can be accomplished in a script task.

hope this helps.

|||

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

|||

Peter G D wrote:

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

Correct. You cannot do that.

-Jamie

|||

Peter G D wrote:

I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

actually, i rather back away from the recordset solution. a better method would be to use raw files instead (for performance reasons). perhaps you could stage the data as raw xml when pulling it out of the source -- i'm not sure if this is the best way. then, you could load that staged data into the destination.

unfortunately, i don't know of any examples to point you towards. all i can tell you is that this solution requires knowledge of ado.net.

Dynamic labels in chart.

Hi
Is there a way in which I can set the labels for the X and Y axes for a chart programatically? The values for the labels are returned from the database...

I already have a stored proc which acts as the source for the chart. I might have the labels retrieved as a seperate query from the DB.

ThanksThe Y-axis labels are determined by the chart control and are numeric values. You can set the format code property to get special numeric formatting and locale settings.

The X-axis has two modes: numeric ("timescale or numeric values" checkbox) and non-numeric (default). In the non-numeric mode, the labels are determined by the category grouping value expression or the category grouping label expression (if explicitly specified).

-- Robert|||Thanks....

Can we also display the Titles for the X and Y-axes from the database? I have one query that generates the data for the report...

I was wondering if I could set the Titles for these axes from a seperate query from the Database.

Thanks
Kannan

Dynamic labels for X and Y-axis in a chart

Hi
Is there a way in which I can set the labels for the X and Y axes for a
chart programatically? The values for the labels are returned from the
database...
I already have a stored proc which acts as the source for the chart. I might
have the labels retrieved as a seperate query from the DB.
ThanksThe Y-axis labels are determined by the chart control and are numeric
values. You can set the format code property to get special numeric
formatting and locale settings.
The X-axis has two modes: numeric ("timescale or numeric values" checkbox)
and non-numeric (default). In the non-numeric mode, the labels are
determined by the category grouping value expression or the category
grouping label expression (if explicitly specified).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
> Hi
> Is there a way in which I can set the labels for the X and Y axes for a
> chart programatically? The values for the labels are returned from the
> database...
> I already have a stored proc which acts as the source for the chart. I
> might
> have the labels retrieved as a seperate query from the DB.
> Thanks|||Thanks...
Can we also display the Titles for the X and Y-axes from the database? I
have one query that generates the data for the report...
I was wondering if I could set the Titles for these axes from a seperate
query from the Database.
Thanks
Kannan
"Robert Bruckner [MSFT]" wrote:
> The Y-axis labels are determined by the chart control and are numeric
> values. You can set the format code property to get special numeric
> formatting and locale settings.
> The X-axis has two modes: numeric ("timescale or numeric values" checkbox)
> and non-numeric (default). In the non-numeric mode, the labels are
> determined by the category grouping value expression or the category
> grouping label expression (if explicitly specified).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "PV" <PV@.discussions.microsoft.com> wrote in message
> news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
> > Hi
> > Is there a way in which I can set the labels for the X and Y axes for a
> > chart programatically? The values for the labels are returned from the
> > database...
> >
> > I already have a stored proc which acts as the source for the chart. I
> > might
> > have the labels retrieved as a seperate query from the DB.
> >
> > Thanks
>
>|||The x-axis title and y-axis title can be expressions. By using aggregate
functions, you can reference fields from other datasets, e.g.
=First(Fields!XAxisDescription.Value, "OtherDataset")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:7C67DBA0-DD8B-4403-B49B-11B16A51D80B@.microsoft.com...
> Thanks...
> Can we also display the Titles for the X and Y-axes from the database? I
> have one query that generates the data for the report...
> I was wondering if I could set the Titles for these axes from a seperate
> query from the Database.
> Thanks
> Kannan
> "Robert Bruckner [MSFT]" wrote:
>> The Y-axis labels are determined by the chart control and are numeric
>> values. You can set the format code property to get special numeric
>> formatting and locale settings.
>> The X-axis has two modes: numeric ("timescale or numeric values"
>> checkbox)
>> and non-numeric (default). In the non-numeric mode, the labels are
>> determined by the category grouping value expression or the category
>> grouping label expression (if explicitly specified).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "PV" <PV@.discussions.microsoft.com> wrote in message
>> news:15141351-1BB5-45D4-8364-B132948612D8@.microsoft.com...
>> > Hi
>> > Is there a way in which I can set the labels for the X and Y axes for a
>> > chart programatically? The values for the labels are returned from the
>> > database...
>> >
>> > I already have a stored proc which acts as the source for the chart. I
>> > might
>> > have the labels retrieved as a seperate query from the DB.
>> >
>> > Thanks
>>

Dynamic filter based on parameter

I have searched and haven't found the bets way to handle this:
I have a parameter that has 1 of 2 values: sold, not sold
I want to filter out the records that appear in a table based on the user
selected value to the paramter.
It doesn't appear that I can do this in the group filter, since I can't say
something like...
if p1 = "sold' then.. etc
What is the best way to handle? Is it possible to create a nestled Iif on
the row detail to handle?
Tx
LesOk.. not sure if this is the best route, but I used a nestled Iif clause on
the row detail and it works nicely (perhaps crude, but works!):
=Iif(((Parameters!Report.Value)<>"Sold" and Fields!UNITPRCE.Value > 0),
True, (Iif(((Parameters!Report.Value)="Sold" and Fields!UNITPRCE.Value = 0),
True, False)))
Cheers!
Les
"LesWright" wrote:
> I have searched and haven't found the bets way to handle this:
> I have a parameter that has 1 of 2 values: sold, not sold
> I want to filter out the records that appear in a table based on the user
> selected value to the paramter.
> It doesn't appear that I can do this in the group filter, since I can't say
> something like...
> if p1 = "sold' then.. etc
> What is the best way to handle? Is it possible to create a nestled Iif on
> the row detail to handle?
> Tx
> Les|||On Nov 22, 11:32 am, LesWright <LesWri...@.discussions.microsoft.com>
wrote:
> It doesn't appear that I can do this in the group filter, since I can't say
> something like...
> if p1 = "sold' then.. etc
1. Pass the Report Parameter into your SQL. This will filter your
data on the server side.
SELECT * FROM ...
WHERE ( @.Report = 'Sold' AND UNITPRCE > 0 )
OR ( @.Report <> 'Sold' AND UNITPRCE = 0 )
2. Add a Calculated Field to your Dataset, then filter on the
Calculated field. Right click on the dataset, Add..., then fill in
the Expression in the Calculated field area. Then, in the Table, go
to Properties, then the Filter tab, then add an expression here. The
Dataset field is evaluated when the data is pulled, and I believed
cached with the data. This is faster than filtering row by row.
3. Do what you did and put an Expression on the Visible property. I
find this to be the slowest, since it has to go through every row in
the dataset at render time, but it gives you the most control.
-- Scott|||Scott,
Thanks for your post. That is definately a much better and more efficient
method! I was reusing the same sproc for a number of reports and once
completed, didn't even think to add another parameter.
Tx
Les
"Orne" wrote:
> On Nov 22, 11:32 am, LesWright <LesWri...@.discussions.microsoft.com>
> wrote:
> > It doesn't appear that I can do this in the group filter, since I can't say
> > something like...
> >
> > if p1 = "sold' then.. etc
> 1. Pass the Report Parameter into your SQL. This will filter your
> data on the server side.
> SELECT * FROM ...
> WHERE ( @.Report = 'Sold' AND UNITPRCE > 0 )
> OR ( @.Report <> 'Sold' AND UNITPRCE = 0 )
> 2. Add a Calculated Field to your Dataset, then filter on the
> Calculated field. Right click on the dataset, Add..., then fill in
> the Expression in the Calculated field area. Then, in the Table, go
> to Properties, then the Filter tab, then add an expression here. The
> Dataset field is evaluated when the data is pulled, and I believed
> cached with the data. This is faster than filtering row by row.
> 3. Do what you did and put an Expression on the Visible property. I
> find this to be the slowest, since it has to go through every row in
> the dataset at render time, but it gives you the most control.
> -- Scott
>

Dynamic field base on parameter

Hi,

I have a matrix based on a cube. I would like to load up a field based 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 =Fields!Actual.Value

if it's Budget, then =Fields!Budget.Value

if it's Target, then =Fields!Target.Value

Is this possible? And If so, how should I do it?

Thanks a lot.

You can try

=IIF(Parameters!<ParamName>.Value="Actual", Fields!Actual.Value, IIF(Parameters!<ParamName>.Value="Budget", Fields!Budget.Value, Fields!Target.Value))

|||

hi,

thanks a lot for your reply.

But what if I loaded the parameter list from the database, that means there maybe more option later in the course, what can I do in that case?

|||You can try using this syntax =Fields(Parameters!<paramName>.Value).Value.|||thanks a lot for your great help... that works!

Dynamic DataSource

How do I change a DataSource on the fly. Do I make the Data Source and the Catalog values parameters in the report then in code feed the parms in like you would a other report parms or in code just make a new definition then call rs2005.SetDataSourceContents(reportPath, definition);?
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.

For example, if you are using SQL Server 2005 as your data source:
Original: data source=localhost\instanceName; initial catalog=AdventureWorks
Expression Based: ="data source=" + Parameters!P1.value + "; initial catalog=" + Parameters!P2.value

You might need to add quotes if your catalog name has spaces. You can use either parameters or an expression. For, example you might have a function you define in your report that looks up the right database for a given user:
="data source=" + Parameters!P1.value + "; initial catalog=" + Code.LookUpDatabaseForUser(Globals!UserID)

The variations on this theme are endless. You might use a different database if you have a different language to get the right group names, etc.

The thing to note is that the databases all have to have the same schema so that your query works.

Of course, you could then make you query to be expression based... but that's adding a whole lot of complexity and should be considered only if you really need it for your report.

-Lukasz|||Thank You|||

do you have a sample for rs2000? Thanks.

|||Expression based connection strings are new in RS 2005.

Thanks
Tudor

Dynamic calculation

Hi,
I get different values and a calculation from a query and need to bring them
all together to make another calculation.
@.PRICE,@.TIME,@.CALC,@.TOTAL
@.PRICE = 3
@.TIME = 5
@.CALC = '* .5'
@.TOTAL = (@.PRICE @.CALC) * @.TIME
I tried to use the exec command, but couldn't get it to work
How can I do this using Transact SQL?
Thank you,
MosheProbably you can make use of CASE expressions to do this, assuming you
know something about the types of optional calculations. Example:
SET @.total =
CASE @.calc_type
WHEN 1 THEN @.price*@.time*0.5
WHEN 2 THEN @.foo*@.bar*0.25
WHEN ... etc
END
If you think you will be forced to use dynamic code then there should
be no reason why you can't do it with EXEC. That's not necessarily the
approach I would recommend but maybe if you post some actual code
rather than pseudo code we could help you fix it.
David Portas
SQL Server MVP
--|||Seems to me you're trying to use dynamic SQL - don't quite know why, since
what you need can be done much more efficiently without dynamic SQL, but
still...
Read more here:
http://www.sommarskog.se/dynamic_sql.html
For a more efficient solution, please provide more information.
ML|||SET @.TOTAL = (@.PRICE @.CALC) * @.TIME
HTH, jens Suessmeyer.|||Hi Moshe,
I've done something very similar with a financial research app i wrote for a
client, they specify a couple of hundred dynamic formula that i then need to
calculate on the fly.
Basically you can use sp_executesql and get the output...
declare @.nsql nvarchar(4000)
set and declare... @.PRICE,@.TIME,@.CALC,@.TOTAL
SET @.PRICE = 3
SET @.TIME = 5
SET @.CALC = '* .5'
SET @.nsql = '@.TOTAL = (@.PRICE ' + @.CALC + ') * @.TIME'
EXEC sp_executeSQL @.nsql,
N'@.PRICE int, @.TIME int, @.TOTAL
decimal( 10, 2 ) OUTPUT',
@.PRICE, @.TIME, @.TOTAL OUTPUT
PRINT @.TOTAL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Moshe Allen" <mosheallen@.hotmail.com> wrote in message
news:dmerbr$hba$1@.news2.netvision.net.il...
> Hi,
> I get different values and a calculation from a query and need to bring
> them all together to make another calculation.
> @.PRICE,@.TIME,@.CALC,@.TOTAL
> @.PRICE = 3
> @.TIME = 5
> @.CALC = '* .5'
> @.TOTAL = (@.PRICE @.CALC) * @.TIME
> I tried to use the exec command, but couldn't get it to work
> How can I do this using Transact SQL?
> Thank you,
> Moshe
>