Is it possible to output a Field Value based on another field value?

I would like the data value of a text field in a table to based on the
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>

Is it possible to make Calculation-values persistent in a Cube !?

Hello

Is it possible to make Calculation-values persistent in a Cube !?

I made an very intensive calculation based on historic data. This resolves rather in a complex MDX-statement and becomes slow at retrieving time

I was wondering.... Is there a way to calculate the value at processing time (by MDX-expression) and store it in the cube ?

I know there is a field 'MeasureExpression' but this field is not full MDX suitable !
Actually... Except for the aggregation-functions (sum, max, min, ...) I want to use a MDX-calculation-expression ?!Well, this functionality is present in AS2005 (in the form of the CACHE statement) but it's unsupported and pretty much undocumented, so I wouldn't recommend using it.

It might be a better idea to focus on tuning your MDX and/or your cube. Can you give us some more details on what you want to do?

Chris|||

Okay if you ask for it

Calculating the today's stocking:
By summerizing all historic mutations till today !

Sum({[Posting Period].[All Posting Period].FirstChild.FirstChild.FirstChild.FirstChild : Head(Descendants([Posting Period].CurrentMember, [Posting Period].[Day])).Item(0)}, [Measures].[Mutation])

[Posting Period] levels => [Year].[Quarter].[Month].[Day]

Can you tune up this MDX-expression ?

Kind regards

|||It depends on what kind of measure [Measures].[Mutation] is. Is it a regular measure of type Sum? If so, then the following technique might be useful:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

If [Measures].[Mutation] is a calculated measure, what is the definition?|||[Measures].[Mutation] is not a calculated measure but a fact ! Yes, a regular measure of type Sum.

At my first sight ytd seems to be the function I need !?
ytd is a server-side calculation and not client-side !?

Thank you for your usefull reply |||

Well, no, YTD is a function which does something similar to what you've done in your calculated member, but it probably won't perform much better than what you've done.

The performance optimisation comes from the fact that in your calculated member, you are summing up a large set of Days. This is likely to perform badly for two reasons:
1) There might be a lot of Days to sum up
2) There are unlikely to be any aggregations built at the Day level

Therefore what the articles I linked to suggest doing is trying to replace any Days in the set to be summed up with their common ancestors. So, for example, say you wanted to sum up all the Days in 2005 up to 7th December. What you could do is find the set of Days from 1st January to 7th December, then go through it replacing all the Days in Q1 with the Q1 member, all the Days in Q2 with the Q2 member, all the Days in Q3 with the Q3 member, all the Days in October and November with those members, and then leave the rest. So your set would be something like:
{[Time].[2005].[Q1], [Time].[2005].[Q2], [Time].[2005].[Q3], [Time].[2005].[Q4].[OCTOBER], [Time].[2005].[Q4].[NOVEMBER], [Time].[2005].[Q4].[DECEMBER].[1], [Time].[2005].[Q4].[DECEMBER].[2], [Time].[2005].[Q4].[DECEMBER].[3], [Time].[2005].[Q4].[DECEMBER].[4], [Time].[2005].[Q4].[DECEMBER].[5], [Time].[2005].[Q4].[DECEMBER]., [Time].[2005].[Q4].[DECEMBER].[7]}

You can probably guess that summing up 12 values (which is what would happen if you have the correct aggregations built) is going to be quicker than >300 values!

|||

But my issue is more intensive than that....
For every day... I want to sum up from the very first mutation till today... Even if this very first mutation begins at the year eg. 1998 !

[Time].[2005].[Q1].[jan].&[1] = Sum(all days of 98, 99, ..., 05 day 1)
[Time].[2005].[Q1].[jan].&[2] = Sum(all days of 98, 99, ..., 05 day 1, 2)
[Time].[2005].[Q1].[jan].&[3] = Sum(all days of 98, 99, ..., 05 day 1, 2, 3)

For every day in a year: I have to go back to the very first mutation (and the very first year) and sum all (previous days of every previous year) up till this year and this day !
That's why I was wondering... can you make calculations persistent ? or even cache it in the background !?

|||Well yes, but with this technique each extra complete year only means one more member to be added up (all days in 1998 are replaced with the 1998 member on the year level), so it doesn't make much difference. Try it if you can work out the MDX: I've seen it make a massive difference to query performance, and to be honest even if you did work out how to cache calculations then I think this would be the better approach.|||The following article also covers the issue:

http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

I have found it very useful...|||Yes indeed.... it's a very very interested article
Many thx Michael.

Is it possible to make Calculation-values persistent in a Cube !?

Hello

Is it possible to make Calculation-values persistent in a Cube !?

I made an very intensive calculation based on historic data. This resolves rather in a complex MDX-statement and becomes slow at retrieving time

I was wondering.... Is there a way to calculate the value at processing time (by MDX-expression) and store it in the cube ?

I know there is a field 'MeasureExpression' but this field is not full MDX suitable !
Actually... Except for the aggregation-functions (sum, max, min, ...) I want to use a MDX-calculation-expression ?!Well, this functionality is present in AS2005 (in the form of the CACHE statement) but it's unsupported and pretty much undocumented, so I wouldn't recommend using it.

It might be a better idea to focus on tuning your MDX and/or your cube. Can you give us some more details on what you want to do?

Chris|||

Okay if you ask for it

Calculating the today's stocking:
By summerizing all historic mutations till today !

Sum({[Posting Period].[All Posting Period].FirstChild.FirstChild.FirstChild.FirstChild : Head(Descendants([Posting Period].CurrentMember, [Posting Period].[Day])).Item(0)}, [Measures].[Mutation])

[Posting Period] levels => [Year].[Quarter].[Month].[Day]

Can you tune up this MDX-expression ?

Kind regards

|||It depends on what kind of measure [Measures].[Mutation] is. Is it a regular measure of type Sum? If so, then the following technique might be useful:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

If [Measures].[Mutation] is a calculated measure, what is the definition?|||[Measures].[Mutation] is not a calculated measure but a fact ! Yes, a regular measure of type Sum.

At my first sight ytd seems to be the function I need !?
ytd is a server-side calculation and not client-side !?

Thank you for your usefull reply |||

Well, no, YTD is a function which does something similar to what you've done in your calculated member, but it probably won't perform much better than what you've done.

The performance optimisation comes from the fact that in your calculated member, you are summing up a large set of Days. This is likely to perform badly for two reasons:
1) There might be a lot of Days to sum up
2) There are unlikely to be any aggregations built at the Day level

Therefore what the articles I linked to suggest doing is trying to replace any Days in the set to be summed up with their common ancestors. So, for example, say you wanted to sum up all the Days in 2005 up to 7th December. What you could do is find the set of Days from 1st January to 7th December, then go through it replacing all the Days in Q1 with the Q1 member, all the Days in Q2 with the Q2 member, all the Days in Q3 with the Q3 member, all the Days in October and November with those members, and then leave the rest. So your set would be something like:
{[Time].[2005].[Q1], [Time].[2005].[Q2], [Time].[2005].[Q3], [Time].[2005].[Q4].[OCTOBER], [Time].[2005].[Q4].[NOVEMBER], [Time].[2005].[Q4].[DECEMBER].[1], [Time].[2005].[Q4].[DECEMBER].[2], [Time].[2005].[Q4].[DECEMBER].[3], [Time].[2005].[Q4].[DECEMBER].[4], [Time].[2005].[Q4].[DECEMBER].[5], [Time].[2005].[Q4].[DECEMBER]., [Time].[2005].[Q4].[DECEMBER].[7]}

You can probably guess that summing up 12 values (which is what would happen if you have the correct aggregations built) is going to be quicker than >300 values!

|||

But my issue is more intensive than that....
For every day... I want to sum up from the very first mutation till today... Even if this very first mutation begins at the year eg. 1998 !

[Time].[2005].[Q1].[jan].&[1] = Sum(all days of 98, 99, ..., 05 day 1)
[Time].[2005].[Q1].[jan].&[2] = Sum(all days of 98, 99, ..., 05 day 1, 2)
[Time].[2005].[Q1].[jan].&[3] = Sum(all days of 98, 99, ..., 05 day 1, 2, 3)

For every day in a year: I have to go back to the very first mutation (and the very first year) and sum all (previous days of every previous year) up till this year and this day !
That's why I was wondering... can you make calculations persistent ? or even cache it in the background !?

|||Well yes, but with this technique each extra complete year only means one more member to be added up (all days in 1998 are replaced with the 1998 member on the year level), so it doesn't make much difference. Try it if you can work out the MDX: I've seen it make a massive difference to query performance, and to be honest even if you did work out how to cache calculations then I think this would be the better approach.|||The following article also covers the issue:

http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

I have found it very useful...|||Yes indeed.... it's a very very interested article
Many thx Michael.

Is it possible to hide a field or fields based on User Groups?

RS 2000

I have a report that has a few sensitive fields and would only like to show them to a certain AD group.

Is this possible?
Hi,

sure this is possible through custom .NET code. Implement your own Active Driectory checking class and use that within reporting Services. Based on this class you can make the textbox Visible or Hidden.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Is it possible to have SQL send an email out based on a value in a table?

Hi all,

I'm working on a trouble ticket application and would like to add one more bit of functionality to the system:

When a new trouble ticket is created via the application front end (ASP.NET web application) the system sends an email to an appropiate party indicating that a new ticekt has been issued and needs to be responded to. The user must then log into the system to see the new ticket. The ticket is of course stored in the database (SQL Sever 2000). My question is, whether it is possible to tell SQL server to automatically send emails on a set time interval as long as their are tickets in the database that have a status of new?

ThanksThere is a SQL Server extended stored procedure, xp_Sendmail, that will allow your SQL code to send mail (presuming the SQL Mail agent is configured and running).|||You can also use CDONTS from a stored procedure if you don't have SQL Mail installed - http://support.microsoft.com/view/tn.asp?kb=312839|||Thanks all. I have a procedure that uses CDONTS and it works fine. now I gotta figure out hot to schedule it to run based on a field in a database. But hopefully that should be easy.

Wendell|||Hi

This KB is not anymore available online. Can you provide the details for this?

Cesar|||Here'sanother article on the same topic.

Regards,

Xander

Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.

|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to do this?

In an OLAP data source view I added ten additional fields to it based on certain conditions and it is used for the aggregation for a report. It is only stored as a number '0' or '1' so that I can sum it up for the report as a measure. It is working fine in the report. However I want to reperesent this ten additional fields on the X axis on a graph and I am not sure how to achieve this. It sounds like the reverse of the "pivot table", creating multiple rows (10 in my case) from a single rows of record. I have created a dimension with this ten fields. My question is how to merge this with another two new field, one to capture the column heading and the other to capture the value of it in an OLAP dataset. Thanks.Actually the MDX function I am looking for is "UNPIVOT" like in T-Sql.

Is it possible to display the selected label (not value) of a query based Parameter in rep

Hi,
If I have a query based parameter which has labels and values, for example,
parameter: Department, label: DepartmentName, Value: DepartmentID, I am
using the parameter value (e.g. DepartmentID) in the dataset filter, but I
want to display the parameter label in the report page footer. Is there an
easy way to do it? Right now I can use only Parameters!Department.Value
which displays the chosen DepartmentID.
Thanks.You can access the report parameter label by using the Label property:
=Parameters!Department.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wendy H" <WendyH@.tequilasoftware.com> wrote in message
news:%23sh1GK$eEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If I have a query based parameter which has labels and values, for
example,
> parameter: Department, label: DepartmentName, Value: DepartmentID, I am
> using the parameter value (e.g. DepartmentID) in the dataset filter, but I
> want to display the parameter label in the report page footer. Is there an
> easy way to do it? Right now I can use only Parameters!Department.Value
> which displays the chosen DepartmentID.
> Thanks.
>
>