Dynamic Query expression

I am receiving the "expression expected" error when trying to include the
following in an expression for a sql statement. I don't receive it without
the IIF statement & do if I include it:
"AND (table.field = @.Team OR @.Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = 3 ")
" ORDER BY Year, Weeknum, OpenDate"
Thank you for any help.
--CorySorry if this is too obvious, but have you remembered the 1st '='?
Your expression should read
="AND (table.field = @.Team OR @.Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
= 3 ")
" ORDER BY Year, Weeknum, OpenDate"|||Hi. I only sent part of the query... Here's more of the query - I just
removed field names etc. Everything works until I add in the iif statement.
="SELECT " &
"table.field, table.field etc" &
"FROM table " &
"GROUP BY table.field, table.field etc" &
"HAVING table.field IN ('text value') " &
"AND table.date >= @.StartDate " &
"AND table.date <= @.EndDate " &
"AND (table.field = @.Team OR @.Team = '(All Teams)') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = " &
Parameters!Category.Value & "")
" ORDER BY Year, Weeknum, OpenDate"
"TomP" wrote:
> Sorry if this is too obvious, but have you remembered the 1st '='?
> Your expression should read
> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> = 3 ")
> " ORDER BY Year, Weeknum, OpenDate"
>|||What is biting you here is typical of dynamic sql. I assume category is a
string. It needs to be enclosed in single quotes. When you have a query
paramter RS is handling all of this for you. When you assemble the string
yourself you need to enclude any necessary single quotes.
AND table.field = '" & Parameters!Category.Value & "'")
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> Hi. I only sent part of the query... Here's more of the query - I just
> removed field names etc. Everything works until I add in the iif
> statement.
> ="SELECT " &
> "table.field, table.field etc" &
> "FROM table " &
> "GROUP BY table.field, table.field etc" &
> "HAVING table.field IN ('text value') " &
> "AND table.date >= @.StartDate " &
> "AND table.date <= @.EndDate " &
> "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = "
> &
> Parameters!Category.Value & "")
> " ORDER BY Year, Weeknum, OpenDate"
> "TomP" wrote:
>> Sorry if this is too obvious, but have you remembered the 1st '='?
>> Your expression should read
>> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
>> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> = 3 ")
>> " ORDER BY Year, Weeknum, OpenDate"
>>|||Do you have to use the value of the parameter in the = statement?
What I have is a situtation where the user is allowed to choose from:
All Categories
Exclude Category 3
Only Category 3
The actual value of the field being used in for query is the character 3
(varchar).
So what I ended out trying was the following:
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '" & 3 & "'")
and I also tried:
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '3'")
but neither worked. I can't use the actual value of the parameter because
I'm going to need 2 if statements, one that is equal to 3 and one that is not
equal to 3. So 3 would need to be the value twice... which of course won't
work.
I get the feeling I'm making this more complicated than necessary. If you
have suggestions I'll take any.
Thanks again.
--Cory
"Bruce L-C [MVP]" wrote:
> What is biting you here is typical of dynamic sql. I assume category is a
> string. It needs to be enclosed in single quotes. When you have a query
> paramter RS is handling all of this for you. When you assemble the string
> yourself you need to enclude any necessary single quotes.
> AND table.field = '" & Parameters!Category.Value & "'")
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> > Hi. I only sent part of the query... Here's more of the query - I just
> > removed field names etc. Everything works until I add in the iif
> > statement.
> >
> > ="SELECT " &
> > "table.field, table.field etc" &
> > "FROM table " &
> > "GROUP BY table.field, table.field etc" &
> > "HAVING table.field IN ('text value') " &
> > "AND table.date >= @.StartDate " &
> > "AND table.date <= @.EndDate " &
> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = "
> > &
> > Parameters!Category.Value & "")
> > " ORDER BY Year, Weeknum, OpenDate"
> >
> > "TomP" wrote:
> >
> >> Sorry if this is too obvious, but have you remembered the 1st '='?
> >>
> >> Your expression should read
> >>
> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> = 3 ")
> >> " ORDER BY Year, Weeknum, OpenDate"
> >>
> >>
>
>|||Those are the labels, not the value correct? Remember that the values do not
have to equal label. You can accomplish this without dynamic sql.
I have three label, value pairs: All Categories, 0 Exclude Category
3, -1 Only Category 3
myfield = @.CategoryParam or @.CategoryParam = 0 or (@.CategoryParam = -1 and
myfield != 3)
For the above to work 0 and -1 have to not be valid categories.
So, take a look at the three choices. If you select All Categories then
@.CategoryParam will = 0 and all your categories will returned. If Only
Category 3 is selected then @.CategoryParam will = 3. Then finally the last
one handles returning everything except where the category equal 3.
Now you can totally get away from dynamic SQL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> Do you have to use the value of the parameter in the = statement?
> What I have is a situtation where the user is allowed to choose from:
> All Categories
> Exclude Category 3
> Only Category 3
> The actual value of the field being used in for query is the character 3
> (varchar).
> So what I ended out trying was the following:
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> table.category_id
> = '" & 3 & "'")
> and I also tried:
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> table.category_id
> = '3'")
> but neither worked. I can't use the actual value of the parameter because
> I'm going to need 2 if statements, one that is equal to 3 and one that is
> not
> equal to 3. So 3 would need to be the value twice... which of course
> won't
> work.
> I get the feeling I'm making this more complicated than necessary. If you
> have suggestions I'll take any.
> Thanks again.
> --Cory
> "Bruce L-C [MVP]" wrote:
>> What is biting you here is typical of dynamic sql. I assume category is a
>> string. It needs to be enclosed in single quotes. When you have a query
>> paramter RS is handling all of this for you. When you assemble the string
>> yourself you need to enclude any necessary single quotes.
>> AND table.field = '" & Parameters!Category.Value & "'")
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Cory" <Cory@.discussions.microsoft.com> wrote in message
>> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
>> > Hi. I only sent part of the query... Here's more of the query - I just
>> > removed field names etc. Everything works until I add in the iif
>> > statement.
>> >
>> > ="SELECT " &
>> > "table.field, table.field etc" &
>> > "FROM table " &
>> > "GROUP BY table.field, table.field etc" &
>> > "HAVING table.field IN ('text value') " &
>> > "AND table.date >= @.StartDate " &
>> > "AND table.date <= @.EndDate " &
>> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
>> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> > = "
>> > &
>> > Parameters!Category.Value & "")
>> > " ORDER BY Year, Weeknum, OpenDate"
>> >
>> > "TomP" wrote:
>> >
>> >> Sorry if this is too obvious, but have you remembered the 1st '='?
>> >>
>> >> Your expression should read
>> >>
>> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
>> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> >> = 3 ")
>> >> " ORDER BY Year, Weeknum, OpenDate"
>> >>
>> >>
>>|||Thank you Bruce. That was exactly what I was looking for. I had a suspicion
I was making it more difficult than necessary.
Thanks again.
--Cory
"Bruce L-C [MVP]" wrote:
> Those are the labels, not the value correct? Remember that the values do not
> have to equal label. You can accomplish this without dynamic sql.
> I have three label, value pairs: All Categories, 0 Exclude Category
> 3, -1 Only Category 3
> myfield = @.CategoryParam or @.CategoryParam = 0 or (@.CategoryParam = -1 and
> myfield != 3)
> For the above to work 0 and -1 have to not be valid categories.
> So, take a look at the three choices. If you select All Categories then
> @.CategoryParam will = 0 and all your categories will returned. If Only
> Category 3 is selected then @.CategoryParam will = 3. Then finally the last
> one handles returning everything except where the category equal 3.
> Now you can totally get away from dynamic SQL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> > Do you have to use the value of the parameter in the = statement?
> >
> > What I have is a situtation where the user is allowed to choose from:
> >
> > All Categories
> > Exclude Category 3
> > Only Category 3
> >
> > The actual value of the field being used in for query is the character 3
> > (varchar).
> >
> > So what I ended out trying was the following:
> >
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> > table.category_id
> > = '" & 3 & "'")
> >
> > and I also tried:
> >
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> > table.category_id
> > = '3'")
> >
> > but neither worked. I can't use the actual value of the parameter because
> > I'm going to need 2 if statements, one that is equal to 3 and one that is
> > not
> > equal to 3. So 3 would need to be the value twice... which of course
> > won't
> > work.
> >
> > I get the feeling I'm making this more complicated than necessary. If you
> > have suggestions I'll take any.
> >
> > Thanks again.
> >
> > --Cory
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What is biting you here is typical of dynamic sql. I assume category is a
> >> string. It needs to be enclosed in single quotes. When you have a query
> >> paramter RS is handling all of this for you. When you assemble the string
> >> yourself you need to enclude any necessary single quotes.
> >>
> >> AND table.field = '" & Parameters!Category.Value & "'")
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> >> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> >> > Hi. I only sent part of the query... Here's more of the query - I just
> >> > removed field names etc. Everything works until I add in the iif
> >> > statement.
> >> >
> >> > ="SELECT " &
> >> > "table.field, table.field etc" &
> >> > "FROM table " &
> >> > "GROUP BY table.field, table.field etc" &
> >> > "HAVING table.field IN ('text value') " &
> >> > "AND table.date >= @.StartDate " &
> >> > "AND table.date <= @.EndDate " &
> >> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> >> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> > = "
> >> > &
> >> > Parameters!Category.Value & "")
> >> > " ORDER BY Year, Weeknum, OpenDate"
> >> >
> >> > "TomP" wrote:
> >> >
> >> >> Sorry if this is too obvious, but have you remembered the 1st '='?
> >> >>
> >> >> Your expression should read
> >> >>
> >> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> >> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> >> = 3 ")
> >> >> " ORDER BY Year, Weeknum, OpenDate"
> >> >>
> >> >>
> >>
> >>
> >>
>
>

dynamic query "expression expected" error

I'm using the following SQL and receiving: "The expression for the
query 'NOL' contains an error: [BC30201] Expression expected."
="SELECT C.CardholderLastName, C.CardholderFirstName,
C.CardNumberLast4Digits, C.CUDiscoveryDate,
C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE"
I've looked over all the message related to Dynamic Queries and from
what I can tell I'm doing this correctly, but for some reason it's not
working. I can remove the IIF and after and it works fine, I'm sure
its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
string value.
ThanksDoes this work?
SELECT C.CARDHOLDERLASTNAME,
C.CARDHOLDERFIRSTNAME,
C.CARDNUMBERLAST4DIGITS,
C.CUDISCOVERYDATE,
C.ESTIMATEDLOSSAMOUNT_QUANTITY LOSSAMOUNT,
C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'FraudTypeGrouping1'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.FRAUDTYPE)) AS FRAUDTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'TransactionType'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) AS TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'BankCardProgram'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.CARDPROGRAM)) AS CARDPROGRAM,
'ALL' AS ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C,
APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE
" &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', ", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE
formatting thanks to
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
I don't think it will, I think the comma in the IIF before the AND is the
problem.
Steve MunLeeuw
<kelkoenig@.gmail.com> wrote in message
news:1139434326.076692.110810@.g43g2000cwa.googlegroups.com...
> I'm using the following SQL and receiving: "The expression for the
> query 'NOL' contains an error: [BC30201] Expression expected."
> ="SELECT C.CardholderLastName, C.CardholderFirstName,
> C.CardNumberLast4Digits, C.CUDiscoveryDate,
> C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
> FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
> WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
> IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
> C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
> ORDER BY C.CUSTOMERREFERENCE"
> I've looked over all the message related to Dynamic Queries and from
> what I can tell I'm doing this correctly, but for some reason it's not
> working. I can remove the IIF and after and it works fine, I'm sure
> its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
> string value.
> Thanks
>|||Nope, no luck, still same error w/in Reporting Services. I had to add
the = and "s at the beginning and end of the statement, unless you left
those out intentionally?
THanks|||Try enclosing the word ALL in double quotes instead of single in your
original. SRS doesn't like single quotes at all!
kelkoenig@.gmail.com wrote:
> Nope, no luck, still same error w/in Reporting Services. I had to add
> the = and "s at the beginning and end of the statement, unless you left
> those out intentionally?
> THanks|||Thanks a ton Toolman, that worked like a charm.
kelsql

Dynamic ParallelPeriode

Hi,

with this MDX expression as a calculated member in my cube

100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])

I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.

Thanks

Hans

Hi Hans,

Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?

>>

select

{[Measures].[Sales Amount]} on 0,

Generate(Ascendants([Date].[Calendar].[Date].&[800]),

{[Date].[Calendar].CurrentMember,

ParallelPeriod([Date].[Calendar].[Calendar Year],

1, [Date].[Calendar].CurrentMember)}) on 1

from [Adventure Works]

-

Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20

>>

|||

Hi Deepak,

Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )

but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like

IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))

which work fine, but this doesn't work here. How can I do it here?

Thanks

Hans

|||

Hi Hans,

With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:

([Measure].[SalesGrowth], [Date].[Date].Members) =

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]

* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );

Dynamic order by case expression problem

Hello,
I want to do a dynamic order but with several criterias, my code look like:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price,stock
WHEN @.order = 'S' THEN stock,price
ELSE name,price
END
But it does not work, MSSQL doesn't like to have more than one value for
the order by, the code below works but that not what i want:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END
How can i do ?
ThanksOne method is with multiple CASE expressions in your ORDER BY clause:
SELECT name,price,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Hi
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
IF @.order="S"
SELECT name,price,stock FROM products ORDER BY stock,price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Untested:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
CASE WHEN @.order = 'P' THEN stock
WHEN @.order = 'S' THEN price
ELSE price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Uri's suggestion might be much better for performance|||Good one Uri and might perform better than the multiple Case.
You just forgot one:
IF @.order not in('S', 'P')
SELECT name,price,stock FROM products ORDER BY name,price
Or he can use Else.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ubd7mXHtFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> IF @.order="S"
> SELECT name,price,stock FROM products ORDER BY stock,price
>
> "Not4u" <Not4u@.chez.com> wrote in message
> news:43203431$0$11421$626a14ce@.news.free.fr...
>|||Thanks it's work great.
Not4u wrote:
> Hello,
> I want to do a dynamic order but with several criterias, my code look like
:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||AK wrote:
> Uri's suggestion might be much better for performance
>
Before discovering the dynamic order by (with case), i used the "IF then"
My select statment is much more complicated than the exemple in this
post and i have multiple order by conditions, the code managing is
easier with the "ORDER BY CASE".
What do you mean by much better performance ?
Thanks|||if at compile time there is an appropriate index, then SQL Server can
satisfy one ORDER BY clause without a sort. If you are specific:
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
the optimizer has a better chance to give you a better plan FOR THIS
PARTICULAR BRANCH of your IF statement.
If you are not specific:
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
the optimizer will utilize "one size fits all" approach, it will always
sort. SQL Server is very good at sorting, but still sorting is not
repeat not free...|||AK wrote:
> if at compile time there is an appropriate index, then SQL Server can
> satisfy one ORDER BY clause without a sort. If you are specific:
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> the optimizer has a better chance to give you a better plan FOR THIS
> PARTICULAR BRANCH of your IF statement.
> If you are not specific:
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END,
> the optimizer will utilize "one size fits all" approach, it will always
> sort. SQL Server is very good at sorting, but still sorting is not
> repeat not free...
>
My request is like this :
SELECT name,
(select min(price) from Price
INNER JOIN Reference ON
Price.id_reference = Reference.id_reference
WHERE Reference.id_product = Products.id_product
) as 'price'
,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
ENDsql

Dynamic Grouping.. Is it possible?

Hello All,
I have a table with 2 groups in my report. When I edit a group I can
specify the expression to "group on" for the groups.
I would like to be able to make the expression, for the top group, a value
from a report parameter so that the user can specify this when the report
is generated.
The Edit Group Window certainly allows me to pick a report parameter but I
am not sure if it would actually work.. nor what the value of the parameter
should be to make it work. Is this possible?
Example:
Top Group: User Selectable (Country, State, City)
Second Group: Product
Detail Row: Sales data for that product/place combo
So the user could say he would like to see sales grouped by
country/product, state/product, or city/product.
Is this doable without creating 3 different reports?
--
Message posted via http://www.sqlmonster.comTry the following dynamic group expression:
=Fields(Parameters!TopGroup.Value).Value
This requires that the values (or labels) of the TopGroup parameters have
matching field names in your dataset (i.e. Country, State, City).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian W via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7c78eafd5f1d46329db04bd95646fb57@.SQLMonster.com...
> Hello All,
> I have a table with 2 groups in my report. When I edit a group I can
> specify the expression to "group on" for the groups.
> I would like to be able to make the expression, for the top group, a value
> from a report parameter so that the user can specify this when the report
> is generated.
> The Edit Group Window certainly allows me to pick a report parameter but I
> am not sure if it would actually work.. nor what the value of the
> parameter
> should be to make it work. Is this possible?
> Example:
> Top Group: User Selectable (Country, State, City)
> Second Group: Product
> Detail Row: Sales data for that product/place combo
> So the user could say he would like to see sales grouped by
> country/product, state/product, or city/product.
> Is this doable without creating 3 different reports?
> --
> Message posted via http://www.sqlmonster.com|||Worked like a charm. Thanks.
--
Message posted via http://www.sqlmonster.com

Dynamic Grouping please?

Hi, I want to have an optional group for a table.
I place this in the group expression:
=iif(Code.m_RR.GetControlParameter(Parameters!ENVIRONMENT.Value,"PA_Split_Internal_External")="true",Fields!MAKE_BUY.Value,"")
But it doesn't group at all.
If I just put =Fields!MAKE_BUY.Value it does. I thought the grouping
allowed dynamic groups? Does anyone know how to do this?
Thanks heaps,
CraigSilly me, you can do this and it works great. It helps if you group on the
correct field though!!!
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:O2i3zs4MGHA.3908@.TK2MSFTNGP10.phx.gbl...
> Hi, I want to have an optional group for a table.
> I place this in the group expression:
> =iif(Code.m_RR.GetControlParameter(Parameters!ENVIRONMENT.Value,"PA_Split_Internal_External")="true",Fields!MAKE_BUY.Value,"")
> But it doesn't group at all.
> If I just put =Fields!MAKE_BUY.Value it does. I thought the grouping
> allowed dynamic groups? Does anyone know how to do this?
> Thanks heaps,
>
> Craig
>

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 Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Expressions

Hello all,
With the new chart palette features in SP1 I'm trying to create a dynamic switch expression of colors that is passed from a database, but I can't get the chart to render correctly based on the expression I pass through.
Can this be done?
What I've done so far is create a db table of people with a color assigned to them,
Tom Black
John Yellow
David Orange
I'm then dynamically creating a switch statement that outputs the recordset like so,
=switch (Fields!User.Value = "Tom","Black",Fields!User.Value="John","Yellow",Fields!User.Value="David","Orange",true, "Transparent")
This is then passed through to the Chart's series Style expression from a dataset.
This all works fine, and the chart doesnt error like it can't evaluate the expression, but its not rendering the chart colours either.
If it won't evalutate the dataset field as an expression is there some other way I can dynamically create the expresssion from a db table?
--
Thankyou,
Tim WraggWhat chart type are you using? On which style property do you use the
switch-function? E.g. for a column chart you can modify the fill color and
the border line color. For a line chart you would want to change the border
line color.
Note: are you actually grouping on Fields!User.Value? You might need to
modify your expression to use the First() aggregate function:
=switch (First(Fields!User.Value) = "Tom","Black",
First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
="David","Orange", true, Nothing)
I also added an example for a pie chart below. You might want to investigate
the example and compare it with your report.
* copy & paste the RDL on the bottom into an empty report (in code view)
* switch back to the report designer layout view
* double-click on the chart
* double-click on the "Units In Stock" button which represents the chart
values - you should now see the "Edit chart values" dialog
* select "Appearance" tab, and click on "Series styles"
* on the "Styles properties" dialog click on "Fill"
* investigate the expression used for determining colors:
=Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
MSDN docs for Choose function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
Explanation: if SupplierID = 1, then the pie slice will be shown in Red, if
SupplierID = 2, the pie slice will be Yellow, otherwise (because of the use
of Nothing) the default color defined by the chart color palette will apply.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
==================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundColor>=Choose(First(Fields!SupplierID.Value),
"Red", "Yellow", Nothing)</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Top>0.125in</Top>
<Title />
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 7 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Hi Robert,
Thanks for your prompt reply.
I'm using the Pie Chart and the Series Style:Fill Property.
I know this expression works as I've hardcoded it into this property and the chart renders with the colors fine.
But what I'm trying to do is keep away from the hardcoding of names and colors in the expression and pass the whole expression (including the =switch) from my dataset.
So I've got a dataset called 'Formatting' that returns one field called 'ColorExpression' which is my expression string,
(=switch (First(Fields!User.Value) = "Tom","Black",First(Fields!User.Value)="John","Yellow",First(Fields!User.Value)="David","Orange", true, Nothing)
and im outputting that in the Fill property like so.
=First(Fields!ColorExpression.Value, "Formatting")
This is where the problem is, I've gathered that the Fill property wont see my outputted field as an expression and hence I can't think how I can keep both names and colors dynamic.
Thankyou,
Tim Wragg
"Robert Bruckner [MSFT]" wrote:
> What chart type are you using? On which style property do you use the
> switch-function? E.g. for a column chart you can modify the fill color and
> the border line color. For a line chart you would want to change the border
> line color.
> Note: are you actually grouping on Fields!User.Value? You might need to
> modify your expression to use the First() aggregate function:
> =switch (First(Fields!User.Value) = "Tom","Black",
> First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
> ="David","Orange", true, Nothing)
>
> I also added an example for a pie chart below. You might want to investigate
> the example and compare it with your report.
> * copy & paste the RDL on the bottom into an empty report (in code view)
> * switch back to the report designer layout view
> * double-click on the chart
> * double-click on the "Units In Stock" button which represents the chart
> values - you should now see the "Edit chart values" dialog
> * select "Appearance" tab, and click on "Series styles"
> * on the "Styles properties" dialog click on "Fill"
> * investigate the expression used for determining colors:
> =Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
> MSDN docs for Choose function:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
> Explanation: if SupplierID = 1, then the pie slice will be shown in Red, if
> SupplierID = 2, the pie slice will be Yellow, otherwise (because of the use
> of Nothing) the default color defined by the chart color palette will apply.
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> ==================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>BottomCenter</Position>
> <Layout>Table</Layout>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Style>
> <BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
> <BackgroundColor>=Choose(First(Fields!SupplierID.Value),
> "Red", "Yellow", Nothing)</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Marker>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Pie</Type>
> <Top>0.125in</Top>
> <Title />
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="chart1_CategoryGroup1">
> <GroupExpressions>
> <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ProductName.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <Left>0.25in</Left>
> <ValueAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SupplierID">
> <DataField>SupplierID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryID">
> <DataField>CategoryID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="QuantityPerUnit">
> <DataField>QuantityPerUnit</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="UnitsInStock">
> <DataField>UnitsInStock</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="UnitsOnOrder">
> <DataField>UnitsOnOrder</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="ReorderLevel">
> <DataField>ReorderLevel</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="Discontinued">
> <DataField>Discontinued</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="NullUnits">
> <DataField>NullUnits</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT TOP 7 *, NULL AS NullUnits
> FROM [Alphabetical list of products]
> WHERE (UnitsOnOrder > 0)</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>|||You should modify your dataset. Since it looks like your chart dataset
contains a User field and the Formatting table also contains a User field
you should join the two tables in the chart dataset query. Through the join,
the colors would be joined to the dataset and you could use an expression
like =Fields!ColorCode.Value in the series/datapoint styles.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Wragg" <TimWragg@.discussions.microsoft.com> wrote in message
news:3540FA53-F0BE-495E-959C-BEFA91A86CAE@.microsoft.com...
> Hi Robert,
> Thanks for your prompt reply.
> I'm using the Pie Chart and the Series Style:Fill Property.
> I know this expression works as I've hardcoded it into this property and
the chart renders with the colors fine.
> But what I'm trying to do is keep away from the hardcoding of names and
colors in the expression and pass the whole expression (including the
=switch) from my dataset.
> So I've got a dataset called 'Formatting' that returns one field called
'ColorExpression' which is my expression string,
> (=switch (First(Fields!User.Value) ="Tom","Black",First(Fields!User.Value)="John","Yellow",First(Fields!User.Val
ue)="David","Orange", true, Nothing)
> and im outputting that in the Fill property like so.
> =First(Fields!ColorExpression.Value, "Formatting")
> This is where the problem is, I've gathered that the Fill property wont
see my outputted field as an expression and hence I can't think how I can
keep both names and colors dynamic.
>
> --
> Thankyou,
> Tim Wragg
>
> "Robert Bruckner [MSFT]" wrote:
> > What chart type are you using? On which style property do you use the
> > switch-function? E.g. for a column chart you can modify the fill color
and
> > the border line color. For a line chart you would want to change the
border
> > line color.
> >
> > Note: are you actually grouping on Fields!User.Value? You might need to
> > modify your expression to use the First() aggregate function:
> > =switch (First(Fields!User.Value) = "Tom","Black",
> > First(Fields!User.Value)="John","Yellow", First(Fields!User.Value)
> > ="David","Orange", true, Nothing)
> >
> >
> > I also added an example for a pie chart below. You might want to
investigate
> > the example and compare it with your report.
> > * copy & paste the RDL on the bottom into an empty report (in code view)
> > * switch back to the report designer layout view
> > * double-click on the chart
> > * double-click on the "Units In Stock" button which represents the chart
> > values - you should now see the "Edit chart values" dialog
> > * select "Appearance" tab, and click on "Series styles"
> > * on the "Styles properties" dialog click on "Fill"
> > * investigate the expression used for determining colors:
> > =Choose(First(Fields!SupplierID.Value), "Red", "Yellow", Nothing)
> >
> > MSDN docs for Choose function:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
> >
> > Explanation: if SupplierID = 1, then the pie slice will be shown in Red,
if
> > SupplierID = 2, the pie slice will be Yellow, otherwise (because of the
use
> > of Nothing) the default color defined by the chart color palette will
apply.
> >
> > --
> > Robert M. Bruckner
> > Microsoft SQL Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > ==================================================> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> >
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> >
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Chart Name="chart1">
> > <ThreeDProperties>
> > <Rotation>30</Rotation>
> > <Inclination>30</Inclination>
> > <Shading>Simple</Shading>
> > <WallThickness>50</WallThickness>
> > </ThreeDProperties>
> > <Style>
> > <BackgroundColor>White</BackgroundColor>
> > </Style>
> > <Legend>
> > <Visible>true</Visible>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > <Position>BottomCenter</Position>
> > <Layout>Table</Layout>
> > </Legend>
> > <Palette>Default</Palette>
> > <ChartData>
> > <ChartSeries>
> > <DataPoints>
> > <DataPoint>
> > <DataValues>
> > <DataValue>
> > <Value>=Sum(Fields!UnitsInStock.Value)</Value>
> > </DataValue>
> > </DataValues>
> > <DataLabel>
> > <Style />
> > <Visible>true</Visible>
> > </DataLabel>
> > <Style>
> >
> > <BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
> >
<BackgroundColor>=Choose(First(Fields!SupplierID.Value),
> > "Red", "Yellow", Nothing)</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > <Marker>
> > <Size>6pt</Size>
> > </Marker>
> > </DataPoint>
> > </DataPoints>
> > </ChartSeries>
> > </ChartData>
> > <CategoryAxis>
> > <Axis>
> > <Title />
> > <MajorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MajorGridLines>
> > <MinorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MinorGridLines>
> > <Margin>true</Margin>
> > <Visible>true</Visible>
> > </Axis>
> > </CategoryAxis>
> > <DataSetName>Northwind</DataSetName>
> > <PointWidth>0</PointWidth>
> > <Type>Pie</Type>
> > <Top>0.125in</Top>
> > <Title />
> > <CategoryGroupings>
> > <CategoryGrouping>
> > <DynamicCategories>
> > <Grouping Name="chart1_CategoryGroup1">
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Label>=Fields!ProductName.Value</Label>
> > </DynamicCategories>
> > </CategoryGrouping>
> > </CategoryGroupings>
> > <Subtype>Plain</Subtype>
> > <PlotArea>
> > <Style>
> > <BackgroundColor>LightGrey</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </PlotArea>
> > <Left>0.25in</Left>
> > <ValueAxis>
> > <Axis>
> > <Title />
> > <MajorGridLines>
> > <ShowGridLines>true</ShowGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MajorGridLines>
> > <MinorGridLines>
> > <Style>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > </Style>
> > </MinorGridLines>
> > <Visible>true</Visible>
> > <Scalar>true</Scalar>
> > </Axis>
> > </ValueAxis>
> > </Chart>
> > </ReportItems>
> > <Style />
> > <Height>5in</Height>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=(local);initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>6.5in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="ProductID">
> > <DataField>ProductID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="ProductName">
> > <DataField>ProductName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="SupplierID">
> > <DataField>SupplierID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="CategoryID">
> > <DataField>CategoryID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > <Field Name="QuantityPerUnit">
> > <DataField>QuantityPerUnit</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="UnitPrice">
> > <DataField>UnitPrice</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > <Field Name="UnitsInStock">
> > <DataField>UnitsInStock</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="UnitsOnOrder">
> > <DataField>UnitsOnOrder</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="ReorderLevel">
> > <DataField>ReorderLevel</DataField>
> > <rd:TypeName>System.Int16</rd:TypeName>
> > </Field>
> > <Field Name="Discontinued">
> > <DataField>Discontinued</DataField>
> > <rd:TypeName>System.Boolean</rd:TypeName>
> > </Field>
> > <Field Name="CategoryName">
> > <DataField>CategoryName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="NullUnits">
> > <DataField>NullUnits</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT TOP 7 *, NULL AS NullUnits
> > FROM [Alphabetical list of products]
> > WHERE (UnitsOnOrder > 0)</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
> > <BottomMargin>1in</BottomMargin>
> > </Report>
> >
> >
> >
> >

Dynamic Expression

I would like to display calculated field on a group header.
The nature of the calculation must be passed as a parameter to the report.
For example lets assume that my report lists X,Y pairs.
On the break I would like to display the value of
Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
It is not practical to prepare an expression for every possible option and
use a selector as X,Y are also dynamically selected values from the query.
So I need a dynamic way to define the expression based on input parameters.
Is Custom code is the way to go ? How do I access the Parameters collection,
and the Report's data from Custom code ?
Any Ideas ?
Thanks.I think that custom code will be needed. You can pass the values you need
(Parameters, Report Data) in as parameters to a function.
"NL" wrote:
> I would like to display calculated field on a group header.
> The nature of the calculation must be passed as a parameter to the report.
> For example lets assume that my report lists X,Y pairs.
> On the break I would like to display the value of
> Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> It is not practical to prepare an expression for every possible option and
> use a selector as X,Y are also dynamically selected values from the query.
> So I need a dynamic way to define the expression based on input parameters.
> Is Custom code is the way to go ? How do I access the Parameters collection,
> and the Report's data from Custom code ?
> Any Ideas ?
> Thanks.
>
>
>
>
>
>|||How does one create a custom function that manipulates the headers of a
report based on parameter values?
"John W" wrote:
> I think that custom code will be needed. You can pass the values you need
> (Parameters, Report Data) in as parameters to a function.
> "NL" wrote:
> > I would like to display calculated field on a group header.
> > The nature of the calculation must be passed as a parameter to the report.
> >
> > For example lets assume that my report lists X,Y pairs.
> > On the break I would like to display the value of
> > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> >
> > It is not practical to prepare an expression for every possible option and
> > use a selector as X,Y are also dynamically selected values from the query.
> > So I need a dynamic way to define the expression based on input parameters.
> >
> > Is Custom code is the way to go ? How do I access the Parameters collection,
> > and the Report's data from Custom code ?
> >
> > Any Ideas ?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||The previous question was asking about display a calculated field on a group
header. You can do this by adding a field to the group header that references
a function defined in VB.NET code in the Report Properties Code tab.
Code sample:
Public Function GetDoubledValue(ByVal NumToDouble As Int) As String
Return CStr(NumToDouble * 2)
End Function
Field Reference:
=Code.GetDoubledValue(4)
"Leneise44" wrote:
> How does one create a custom function that manipulates the headers of a
> report based on parameter values?
> "John W" wrote:
> > I think that custom code will be needed. You can pass the values you need
> > (Parameters, Report Data) in as parameters to a function.
> >
> > "NL" wrote:
> >
> > > I would like to display calculated field on a group header.
> > > The nature of the calculation must be passed as a parameter to the report.
> > >
> > > For example lets assume that my report lists X,Y pairs.
> > > On the break I would like to display the value of
> > > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> > >
> > > It is not practical to prepare an expression for every possible option and
> > > use a selector as X,Y are also dynamically selected values from the query.
> > > So I need a dynamic way to define the expression based on input parameters.
> > >
> > > Is Custom code is the way to go ? How do I access the Parameters collection,
> > > and the Report's data from Custom code ?
> > >
> > > Any Ideas ?
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >

dynamic evaluation of expression operator (was "Substitution")

Hi I am trying to do something like the following:

DECLARE @.Operator varchar(1)
DECLARE @.Rate float
DECLARE @.Quantity float
DECLARE @.Converted float

SET @.Quantity = 6
SET @.Operator = '/'
SET @.Rate = 2
SET @.Converted = 0

@.Converted = (@.Quantity substituteTheValueOfThis(@.Operator) @.Rate)

PRINT @.Converted

so that the output would be 3

The reason I need to do it like this is that @.Operator will change at runtime...

Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.First of all, the following

DECLARE @.Operator varchar(1)
DECLARE @.Rate float
DECLARE @.Quantity float
DECLARE @.Converted float

SET @.Quantity = 6
SET @.Operator = '/'
SET @.Rate = 2
SET @.Converted = 0

Could be shortened to

DECLARE
@.Operator varchar(1)
, @.Rate float
, @.Quantity float
, @.Converted float
, @.cmd varchar(1000)

select @.Quantity = 6, @.Operator = '/', @.Rate = 2, @.Converted = 0
set @.cmd = 'select convert(decimal(38, 2), ' + convert(varchar, @.Quantity) + convert(Varchar,@.Operator) + convert(varchar, @.Rate) + ')'
exec (@.Cmd)

Sorry about the poor formatting, but this is cut n' pasted directly from Query Analyzer.

Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
3/2=1
3.0/2=1.500000|||Thanks, this seems to work great when:

select @.Quantity = 6, @.Operator = '/', @.Rate = 2, @.Converted = 0

but returns 0 when

select @.Quantity = 6, @.Operator = '/', @.Rate = 100, @.Converted = 0

rather than

.06

Ah!! Is this what you mean at the end of your post by...

"Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
3/2=1
3.0/2=1.500000"

I am trying to do conversions between grams and kilograms to 3dp.. so values maybe something like...

34.876 grams and i need to convert that to kilograms so would

0.034876 which I would probably round to 0.035 kilograms

This works but also value maybe

6 grams to kilograms which at the moment returns 0

Thanks for tips on the code and for making the title to thread more appropriate, I still have a lot to learn about transact sql and using this forum... :)|||Sorry I forgot, can I also assign the result of the EXEC statement to a variable like this...

@.Result = EXEC (@.cmd)

I know this doesn't work but it illustrates what I am hoping to do!

Thanks again!!!|||Yes it IS possible to write dynamic SQL in a way that makes the execution of the string return a value (do a search on the words "dynamic SQL OUTPUT" on these boards and you'll find quite a few posts about it.
However, dynamic SQL is not optimized by SQL Server (and couldn't be since it's not decided what it looks like until runtime.) and therefore it can be quite slow. If I understand your post correctly, basically what you are going for is a "mini-calculator"-procedure. You wanna send a first parameter, a operator and another operator to a procedure, and then do the evaluation and return the results in a proc.

If that is the case, you could probably get away with
-- Start proc
create procedure test as
@.Param1 float,
@.Param2 float,
@.Operator char(1)

as

select
case @.Operator
when '-' then @.Param1 - @.Param2
when '+' then @.Param1 + @.Param2
when '*' then @.Param1 * @.Param2
when '/' then @.Param1 / @.Param2
else
-1
end
return
-- End proc
However I cannot see how this is needed, since the calculation should probably be done in the client. (Given that this procedure is in a serverside solution, which I assumed that it is ...)
No need to do the dynamic SQL there. Perhaps I've simplified your problem and not considered all other factors, but as far as I can see my solution could work nicely, from a SQL point of view, but probably not from a architectual point of view. However, I choose not to discuss those matters here.

Good luck. :cool:|||Thank you very very much! You've set me thinking about the placing of this routine and maybe as you say, it should really be in the middle tier.

Thanks again for your time and very helpful comments

Cheers :)

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using &amp; instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using &amp; instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.

Dynamic DSN in Report

I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value

When I try to preview the report, I get the following error

An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.

I have also tried it without the parameter,

="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"

with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?

R

Hi Ron,

I hate to reply when I don't have an actual solution but oh well. I too am having this exact same problem. I am using OLE DB with provider=MSOLAP.3. I had no problem doing this when I was using SQL Server Analysis Services 9 connection.

Did you ever solve this problem?

Brian Welcker blogged about this at http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx#470856 and also another similar thing at http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx. Neither was very helpful to our problem though.

I found it interesting that he showed the actual XML from the RDL file in the first post. He was using &amp; instead of & which may be a hint - perhaps our connection strings contain a character which is confusing the native compiler? But your connection string above, and the one I am using do not contain any abnormal characters... where do we find the list of invalid XML characters?.

|||

Yes, I did get it to work. I messed around with alot of different ways, but ended up using String.Format. It worked quite well.

R

|||

Hi Ron,

I'm having the same error, may you explain a bit how did you manage to make it work using String.Format?

|||

I used

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

The parameter DBNAME had the database name I wanted to use. The downside is that you can no longer run data queries directly in VBSTUDIO. You have to actually preview the report.

If you have troubles, take it one step at a time. First try,

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

then go one more step,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog=DBNAME;",String.Empty)

then,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};","DBNAME")

lastly,

=String.Format("Data Source=MYSQLSERVER;Initial Catalog={0};", Parameters!DBNAME.Value)

Whenever I have trouble with these strings (DSN and dynamic SQL) I always back up and take baby steps forward. I find when I get impatient and just jump to the last step, I have missed something.This usually catches it.

R

|||

Thanks a lot Ron,

I was tryng your baby steps method but I got stuck on the very first one.

If I put

="Data Source=MYSQLSERVER;Initial Catalog=DBNAME;"

in the connection string field of the dataset, it ends up with the same error ([BC32017] Comma..)

Are you entering your connection string somewhere else than the specific field of the dataset? For example in the custom code field, as I noticed that I can't use String.Format in there.

|||

Update:

It looks like that is a problem connecting to Analysis Services, in fact if I try to use the string to connect to SQL Server, it works fine.

Any idea to connect to AS using a dynamic connection string?

|||

Found out that the problem was a space in the Datasource Name.

Now it works fine with AS datasource as well.