DataTable query...

Hi...

In my appllication, i am having a DataTable... For that DataTable i have to write a query for a expression... This epression is more of mathematical expression type. Here is the sample expression-

(column1 < 1 && (column2 = 2 || column3 > 5) || column4 != 3)

How i can write a query for such a equation...? Is it possible to use logical & mathematical operator in the equation & to write a query for this type of equation... Is there is any good article on this...

Thanks in adavnce...

IamHuM

What exactly you need here. You can use the logical/math opertor as per your requirement.

Pls provide more clarity on your requirement.

|||

A good place to start is referring to Books Online, Topic 'Operators [Transact SQL]'.

Unfortunately, T-SQL does NOT have a full and rich set of mathematical operators. Depending upon your needs, you may be better served by exploring the capabilities of creating custom CLR functions (using VB.NET/C#.NET) and then having your SQL code use those CLR functions. A place to start with that is, again, Books Online, Topic: 'CLR User Defined Functions'.

As Mani indicated, if you post a more in-depth description of your requirements, we may be better able to guide you to the best resources.

|||

Code Snippet

select *

from
where Column1 < 1

and

(

(Column2 = 2 OR Column3 > 5)

OR

(Column4 <> 3)

)

Which is functionally equivalent to:

Code Snippet

select *

from <TableName>
where Column1 < 1

and

(

Column2 = 2

OR Column3 > 5

OR Column4 <> 3

)

DatasourceView named query creation problem

Hi to all,
I have a problem within the editor of Named Query in Visual Studio.
Whenever I try to add a named query to a Datasourceview I receive the common "Object Reference not seto to an instance of an object". In the PC Visual studio have the SP1 installed, and the Client Components of Sql Server are installed and patched to the Sp2.
I tried to debug the IDE of visual studio using another instance of VS attached to the first but... nothing! What can I do to add a named query to my datasource view ?
Thanks in advance!
Marco

When these kind of strange problems appear I usually do a total reinstall of the software.

Is the data source SQL Server or another database?

You can also tell us about the O/S and version and other software installed on your computer/workstation.

HTH

Thomas Ivarsson

|||Oh... I would be happy if I can NOT DO a total reinstall of the software!
The Datasource is Sql Server (the same datasouce of other table in the Datasourceview), OS is WinXp sp2 continuosly update by windows update. I have a lot of software installed on my computer... (VS 6.0, VS2003, VS2005, VSS, MSOff2007, Firefox, MSSQL Express, Client Tools of SQL Server, MsnMessenger, Dameware remote control, Nero...)
I think it's a problem with the dll that create the dialogue to make the named query, although I don't know wich dll it is!
|||This problem has also appeared on my workstation (it used to work OK).
I am Windows XP, SP2, SQL 2005 SP2, VS2005 SP1.
I've also compared the contents of:
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge
with
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge
and they match.
I'm not sure what caused the problem to start happening - I suspect an automatic update may be the culprit.
I've also uninstalled/reinstalled & reapplied SP2 for SQL Server 2005 to no effect.
The error dialog does not have a "Details>>" button, so I cant get any further details on the issue.
Note that the error occurs before the "Add New Neamed Query" dialog box appears - when I click OK on the error the dialog box appears for a microsecond and then disappears.

Any help appreciated!
PK

Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

Datasource Error

I'm getting this message when I try to enter an SQL statement for a new
report:
"There is an error in the query. MinimumCapacity must be non-negative."
My SQL statement is:
"CALL GET_INSTALLMENTS_HEADER(?,?)"
I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400 provider
and get the same error.
Any ideas?
Thanks in advance,
MikeI am going against Sybase using OLEDB so although it is not the same
database as you are going against it is using an OLEDB provider. I do not
use the syntax you are using. I use the following syntax:
pr_test ?, ?
If you already have report parameters created then go to the parameters tab
of the dataset (click on the ...) and map the query parameter to the report
parameter. If in the generic query designer I have found I have to leave the
command type as text rather than setting it to stored procedure.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <MichaelLopez@.inds.com> wrote in message
news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
> I'm getting this message when I try to enter an SQL statement for a new
> report:
> "There is an error in the query. MinimumCapacity must be
non-negative."
> My SQL statement is:
> "CALL GET_INSTALLMENTS_HEADER(?,?)"
> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
provider
> and get the same error.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Bruce. Thanks for responding.
That's how I did it, though I discovered it through empirical means.
We've used Crystal Reports for some time, and it's not a bad product at all.
But we always had to jump through hoops to gain access to an IBM AS/400
(iSeries) machine, and forget about accessing stored procedures on the 400.
We never could natively. Yesterday was my first attempt at accessing the
same system using RS and got it to work in about an hour.
That's a good sign. My next step is to figure out how to emulate CR's "Group
Tree" functionality.
Good chance this group will be hearing from me soon.
Thanks again,
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OGgUbenxEHA.824@.TK2MSFTNGP11.phx.gbl...
>I am going against Sybase using OLEDB so although it is not the same
> database as you are going against it is using an OLEDB provider. I do not
> use the syntax you are using. I use the following syntax:
> pr_test ?, ?
> If you already have report parameters created then go to the parameters
> tab
> of the dataset (click on the ...) and map the query parameter to the
> report
> parameter. If in the generic query designer I have found I have to leave
> the
> command type as text rather than setting it to stored procedure.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <MichaelLopez@.inds.com> wrote in message
> news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
>> I'm getting this message when I try to enter an SQL statement for a new
>> report:
>> "There is an error in the query. MinimumCapacity must be
> non-negative."
>> My SQL statement is:
>> "CALL GET_INSTALLMENTS_HEADER(?,?)"
>> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
> provider
>> and get the same error.
>> Any ideas?
>> Thanks in advance,
>> Mike
>>
>sql

dataset with parameterized top query

I have a report based on a dataset that has a "top()" statement in it.

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

I wanted wanted to return a report with 10 rows as the default.

But allow the user to change the default and regenerate the report with more rows.

I tried the following;

SELECT TOP (@.N) name, val FROM tab ORDER BY val DESC

Normally in VS2005, the paraemeters work fine for things like the WHERE clause. But when i do the TOP(@.N) the query/report parameter synchronization messes up. If i manual go to the parameter tab of the dataset and configure the matching between query and report params it works. But doing anything to the dataset resets this. Its getting very tiring.

Any advice for a newbie would be appreciated.

John

Hi,
you cannot do that without building dynamic SQL which is not best practice leaving you exposed to risks.
What you could do if your TOP parameter list is a constrained list like top = 10 or 20 or 50 or 100 or ALL would be to build your query with a series of If or Case statements where you evaluate the value of the TOP parameter and then build the select top query accordingly.
Another related question I have posted in this forum was how can I get the "Other" lumped into an extra result row. No easy answer so far, I wait for the silver bullet.
Philippe|||

Lets say i opt for the constrained list of top values as you suggest and write a use if/case as you suggest.

Would this not be also be dynamic SQL?

I saw another thread where someone was looking for a column that with a rank of the sorted rows.

rank name val

1 sam 9.4

2 ted 5.8

3 bob 2.4

if i could do this in the dataset sql, i could return all rows, then i could filter the rows in the report table with rank <= @.N

|||

I tried your if suggestion. Works in the dataset designer wizard in VS2005 just after entery sql code. But then testing the report it doesnt.

entering the dataset wizard again get me this error, "The Compound statement SQL construct or statement is not supported."

code:

if (@.N = 10)

begin

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

end

if (@.N = 20)

begin

SELECT TOP (20) name, val FROM tab ORDER BY val DESC

end

if (@.N = 999)

begin

SELECT name, val FROM tab ORDER BY val DESC

end

|||I guess there are many ways to do it, here is a working example:

USE AdventureWorks
DECLARE @.TOP INT
SET @.TOP = 10

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

Philippe|||

That is much simpler. Thx

I have one question however;

this works in a report i configured

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

why doesnt this form work

SELECT TOP (@.TOP)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

it works in the dataset wizard. when i test the query the wizard prompts for @.top and the results work

however i run the run the report and the i get error. You mentioned dynamic queries previously. is you form not dynamic? The @.top param has to be evaluated at runtime either way.

john

|||

If you're using SQL 2005, I suggest looking at ranking functions. It's quite handy, with ROW_NUMBER()

http://www.aspfaq.com/sql2005/show.asp?id=11

http://www.sql-server-performance.com/ak_ranking_functions.asp

http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

I don't think the previous code is dynamic query, as TOP was a declared variable

not something like

EXEC 'SELECT TOP ' + @.TOP + ' * FROM table'

where you may get unwanted input (injection attack?)

dataset question

hi,
i am trying to run the following query in the dataset which works fine when
i have a number in the branch_code
SELECT tbl_calendar.date, tbl_day_type.day_type
FROM tbl_calendar INNER JOIN
tbl_day_type ON tbl_calendar.day_type = tbl_day_type.type_id
WHERE (tbl_calendar.date BETWEEN
(SELECT opened_date
FROM
HOBISINT.account_statistics.dbo.t_branches
WHERE branch_code = 56) AND GETDATE())
Problem occurs when i add a branch parameter (int) & make the following change
WHERE branch_code = @.branch)
i get the following error message
Application uses a value of the wrong type for the current operation.
thanks in advanceIs branch_code actually defined as an int in your table?
Can you run (SELECT opened_date FROM
HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
itself without an error?
"Tango" wrote:
> hi,
> i am trying to run the following query in the dataset which works fine when
> i have a number in the branch_code
> SELECT tbl_calendar.date, tbl_day_type.day_type
> FROM tbl_calendar INNER JOIN
> tbl_day_type ON tbl_calendar.day_type => tbl_day_type.type_id
> WHERE (tbl_calendar.date BETWEEN
> (SELECT opened_date
> FROM
> HOBISINT.account_statistics.dbo.t_branches
> WHERE branch_code = 56) AND GETDATE())
> Problem occurs when i add a branch parameter (int) & make the following change
> WHERE branch_code = @.branch)
> i get the following error message
> Application uses a value of the wrong type for the current operation.
> thanks in advance|||Yes it is int
yes i can run below without any errors
thanks for your interest
"daw" wrote:
> Is branch_code actually defined as an int in your table?
> Can you run (SELECT opened_date FROM
> HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
> itself without an error?
> "Tango" wrote:
> > hi,
> > i am trying to run the following query in the dataset which works fine when
> > i have a number in the branch_code
> >
> > SELECT tbl_calendar.date, tbl_day_type.day_type
> > FROM tbl_calendar INNER JOIN
> > tbl_day_type ON tbl_calendar.day_type => > tbl_day_type.type_id
> > WHERE (tbl_calendar.date BETWEEN
> > (SELECT opened_date
> > FROM
> > HOBISINT.account_statistics.dbo.t_branches
> > WHERE branch_code = 56) AND GETDATE())
> >
> > Problem occurs when i add a branch parameter (int) & make the following change
> > WHERE branch_code = @.branch)
> > i get the following error message
> > Application uses a value of the wrong type for the current operation.
> >
> > thanks in advance

Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

Dataset Query using Parameters

OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >

Dataset Query Size limit (300 lines)

Hi,
I have a formatted Dataset query with size exceeding 300 lines (vertically). The dataset editor is not allowing me write it. Is this a limitation that the query size should be less than 300 lines or do we need have any way to increase this.
Formatting helps understand the query it in a legible format.
ThanksBy "dataset editor" do you mean the Dataset dialog, generic query designer
or graphical query designer? How long is the query in characters? What do
you mean by "not allowing me to write it"? Do you get an error message?
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> Hi,
> I have a formatted Dataset query with size exceeding 300 lines
(vertically). The dataset editor is not allowing me write it. Is this a
limitation that the query size should be less than 300 lines or do we need
have any way to increase this.
> Formatting helps understand the query it in a legible format.
> Thanks|||Apologise for this confusion.....I talked about the generic Query Designer [Report Design Window --> Data Tab -- > Place where the query is written]
I have a query as, say,
"select " & Parameters!col_name>Value & " as myValue from dual UNION ALL ...(almost 18k characters)"...
I wrote the above query as follows (for legibility)
" select " & Parameters!col_name>Value & " as myValue " & (press enter)
" from dual " & (press enter)
" UNION ALL " & (press enter)
... (exceeds 300 lines vertically now)
Here, i notice that only 300 lines come in the window and after that it does not paste or allow me manually write the query. Nothing happens when i write.
Kinldy let me know if it cannot work beyond this or a solution.
"Albert Yen [MSFT]" wrote:
> By "dataset editor" do you mean the Dataset dialog, generic query designer
> or graphical query designer? How long is the query in characters? What do
> you mean by "not allowing me to write it"? Do you get an error message?
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > Hi,
> >
> > I have a formatted Dataset query with size exceeding 300 lines
> (vertically). The dataset editor is not allowing me write it. Is this a
> limitation that the query size should be less than 300 lines or do we need
> have any way to increase this.
> >
> > Formatting helps understand the query it in a legible format.
> >
> > Thanks
>
>|||I am unable to reproduce your problem with a query with up to 600 lines and
30K characters. Could you post the text of your query? Although, it would
seem strange if the specific text made any difference.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> Apologise for this confusion.....I talked about the generic Query
Designer [Report Design Window --> Data Tab -- > Place where the query is
written]
> I have a query as, say,
> "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
...(almost 18k characters)"...
> I wrote the above query as follows (for legibility)
> " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> " from dual " & (press enter)
> " UNION ALL " & (press enter)
> ... (exceeds 300 lines vertically now)
> Here, i notice that only 300 lines come in the window and after that it
does not paste or allow me manually write the query. Nothing happens when i
write.
> Kinldy let me know if it cannot work beyond this or a solution.
> "Albert Yen [MSFT]" wrote:
> > By "dataset editor" do you mean the Dataset dialog, generic query
designer
> > or graphical query designer? How long is the query in characters? What
do
> > you mean by "not allowing me to write it"? Do you get an error message?
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > Hi,
> > >
> > > I have a formatted Dataset query with size exceeding 300 lines
> > (vertically). The dataset editor is not allowing me write it. Is this a
> > limitation that the query size should be less than 300 lines or do we
need
> > have any way to increase this.
> > >
> > > Formatting helps understand the query it in a legible format.
> > >
> > > Thanks
> >
> >
> >|||Well, this is really strange.
Please follow the process as,
format the query in an editor (textpad, in my case) with an enter character after every line. Copy this query which is beyond, say 400 lines, and paste it directly in datatset querystrign editor. Are you sure you get the complete query in the window. Try copying it back to the textpad editor and check for the number of lines.
My focus is primarily on having the copy and paste operation performed, as i did. Otherwise, if you can suggest any probable reason for this behaviour..
Thanks.
"Albert Yen [MSFT]" wrote:
> I am unable to reproduce your problem with a query with up to 600 lines and
> 30K characters. Could you post the text of your query? Although, it would
> seem strange if the specific text made any difference.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > Apologise for this confusion.....I talked about the generic Query
> Designer [Report Design Window --> Data Tab -- > Place where the query is
> written]
> > I have a query as, say,
> > "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
> ...(almost 18k characters)"...
> > I wrote the above query as follows (for legibility)
> > " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> > " from dual " & (press enter)
> > " UNION ALL " & (press enter)
> > ... (exceeds 300 lines vertically now)
> >
> > Here, i notice that only 300 lines come in the window and after that it
> does not paste or allow me manually write the query. Nothing happens when i
> write.
> > Kinldy let me know if it cannot work beyond this or a solution.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > By "dataset editor" do you mean the Dataset dialog, generic query
> designer
> > > or graphical query designer? How long is the query in characters? What
> do
> > > you mean by "not allowing me to write it"? Do you get an error message?
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a formatted Dataset query with size exceeding 300 lines
> > > (vertically). The dataset editor is not allowing me write it. Is this a
> > > limitation that the query size should be less than 300 lines or do we
> need
> > > have any way to increase this.
> > > >
> > > > Formatting helps understand the query it in a legible format.
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||I do see a limit of 32K characters in the textbox, after which I can't enter
any more text. I think the 32K character limit is imposed by the textbox
control, not us. That sounds like what you are seeing, except it's a limit
on the number of characters, not the number of lines. I'm not sure why you
have a problem after only 18K characters. At any rate, I don't have any
workarounds for this problem.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> Well, this is really strange.
> Please follow the process as,
> format the query in an editor (textpad, in my case) with an enter
character after every line. Copy this query which is beyond, say 400 lines,
and paste it directly in datatset querystrign editor. Are you sure you get
the complete query in the window. Try copying it back to the textpad editor
and check for the number of lines.
> My focus is primarily on having the copy and paste operation performed, as
i did. Otherwise, if you can suggest any probable reason for this
behaviour..
> Thanks.
> "Albert Yen [MSFT]" wrote:
> > I am unable to reproduce your problem with a query with up to 600 lines
and
> > 30K characters. Could you post the text of your query? Although, it
would
> > seem strange if the specific text made any difference.
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > Apologise for this confusion.....I talked about the generic Query
> > Designer [Report Design Window --> Data Tab -- > Place where the query
is
> > written]
> > > I have a query as, say,
> > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
ALL
> > ...(almost 18k characters)"...
> > > I wrote the above query as follows (for legibility)
> > > " select " & Parameters!col_name>Value & " as myValue " & (press
enter)
> > > " from dual " & (press enter)
> > > " UNION ALL " & (press enter)
> > > ... (exceeds 300 lines vertically now)
> > >
> > > Here, i notice that only 300 lines come in the window and after that
it
> > does not paste or allow me manually write the query. Nothing happens
when i
> > write.
> > > Kinldy let me know if it cannot work beyond this or a solution.
> > >
> > > "Albert Yen [MSFT]" wrote:
> > >
> > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > designer
> > > > or graphical query designer? How long is the query in characters?
What
> > do
> > > > you mean by "not allowing me to write it"? Do you get an error
message?
> > > >
> > > > --
> > > > Albert Yen
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > (vertically). The dataset editor is not allowing me write it. Is
this a
> > > > limitation that the query size should be less than 300 lines or do
we
> > need
> > > > have any way to increase this.
> > > > >
> > > > > Formatting helps understand the query it in a legible format.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||Is it practical for you to write the query as a stored procedure and call it
from the report?
"Albert Yen [MSFT]" wrote:
> I do see a limit of 32K characters in the textbox, after which I can't enter
> any more text. I think the 32K character limit is imposed by the textbox
> control, not us. That sounds like what you are seeing, except it's a limit
> on the number of characters, not the number of lines. I'm not sure why you
> have a problem after only 18K characters. At any rate, I don't have any
> workarounds for this problem.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> > Well, this is really strange.
> > Please follow the process as,
> > format the query in an editor (textpad, in my case) with an enter
> character after every line. Copy this query which is beyond, say 400 lines,
> and paste it directly in datatset querystrign editor. Are you sure you get
> the complete query in the window. Try copying it back to the textpad editor
> and check for the number of lines.
> >
> > My focus is primarily on having the copy and paste operation performed, as
> i did. Otherwise, if you can suggest any probable reason for this
> behaviour..
> >
> > Thanks.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > I am unable to reproduce your problem with a query with up to 600 lines
> and
> > > 30K characters. Could you post the text of your query? Although, it
> would
> > > seem strange if the specific text made any difference.
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > > Apologise for this confusion.....I talked about the generic Query
> > > Designer [Report Design Window --> Data Tab -- > Place where the query
> is
> > > written]
> > > > I have a query as, say,
> > > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
> ALL
> > > ...(almost 18k characters)"...
> > > > I wrote the above query as follows (for legibility)
> > > > " select " & Parameters!col_name>Value & " as myValue " & (press
> enter)
> > > > " from dual " & (press enter)
> > > > " UNION ALL " & (press enter)
> > > > ... (exceeds 300 lines vertically now)
> > > >
> > > > Here, i notice that only 300 lines come in the window and after that
> it
> > > does not paste or allow me manually write the query. Nothing happens
> when i
> > > write.
> > > > Kinldy let me know if it cannot work beyond this or a solution.
> > > >
> > > > "Albert Yen [MSFT]" wrote:
> > > >
> > > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > > designer
> > > > > or graphical query designer? How long is the query in characters?
> What
> > > do
> > > > > you mean by "not allowing me to write it"? Do you get an error
> message?
> > > > >
> > > > > --
> > > > > Albert Yen
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > >
> > > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > > (vertically). The dataset editor is not allowing me write it. Is
> this a
> > > > > limitation that the query size should be less than 300 lines or do
> we
> > > need
> > > > > have any way to increase this.
> > > > > >
> > > > > > Formatting helps understand the query it in a legible format.
> > > > > >
> > > > > > Thanks
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

Dataset Query Parameter

I have a dataset query that contains the expression:
Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
I have had no luck replacing the { fn NOW() } with a parameter and was
wondering if it was at all possible.To use parameters in a dataset query expression, use @.ParameterName.
"Todd Simmons" wrote:
> I have a dataset query that contains the expression:
> Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> I have had no luck replacing the { fn NOW() } with a parameter and was
> wondering if it was at all possible.
>|||I tried that and get a "Syntax error or access violation"
"Harolds" wrote:
> To use parameters in a dataset query expression, use @.ParameterName.
> "Todd Simmons" wrote:
> > I have a dataset query that contains the expression:
> >
> > Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> > DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> >
> > I have had no luck replacing the { fn NOW() } with a parameter and was
> > wondering if it was at all possible.
> >

Dataset query in rdl

Hi,
I have a report that is going to have to show the following.
Columns 1-3 will be counts of a certain activity.
Columns 4-12 will have calculations based on the data contained in the 3rd
count.
I can get the counts, and I can perform the calculations just fine. Where I
run into a problem is on the display. I have a table with some grouping that
will do the calculations and show columns 3-12, with the data available if
the user wishes to drill down. If I have second data set with my counts how
can I get the data from it where my keys are equal? I know that I could put
all the counts in my data set but I donâ't want to have them in each row
because I am expecting to only have around 100 groups, but over 8000 rows of
data will be need to calculate columns 4-12.
I guess what I am really after is a way to query a dataset, or to join them
inside of the rdl.This may help explain what I am trying to accomplish.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AKAIMS Production">
<rd:DataSourceID>af566fca-304d-4f11-bc74-505d4c0175b4</rd:DataSourceID>
<DataSourceReference>AKAIMS Production</DataSourceReference>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>0e5aad5b-e956-4edb-8be1-9dbe4893dc7d</rd:ReportID>
<DataSets>
<DataSet Name="Counts">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountA">
<DataField>CountA</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountB">
<DataField>CountB</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 13 AS CountA, 21 As CountB
UNION
SELECT 2 As agency_id, 14 AS CountA, 24 As CountB</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="Detail">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Data">
<DataField>Data</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 1 AS Data
UNION
SELECT 1 As agency_id, 2 AS Data
UNION
SELECT 2 As agency_id, 3 AS Data
UNION
SELECT 2 As agency_id, 4 AS Data</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>5.5in</Width>
<Body>
<ReportItems>
<Table Name="table1">
<DataSetName>Detail</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1_agency">
<GroupExpressions>
<GroupExpression>=Fields!agency_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!agency_id.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>23</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountA.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>22</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"13","14")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>21</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountB.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<rd:DefaultName>textbox25</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>20</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"21","24")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="agency_id">
<rd:DefaultName>agency_id</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>19</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!agency_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>18</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<rd:DefaultName>textbox22</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox27">
<rd:DefaultName>textbox27</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>7</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>5</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<rd:DefaultName>textbox21</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<rd:DefaultName>textbox26</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Data">
<rd:DefaultName>Data</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Data.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>29</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count A</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>28</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>27</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count B</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>26</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>25</ZIndex>
<CanGrow>true</CanGrow>
<Value>agency id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>24</ZIndex>
<CanGrow>true</CanGrow>
<Value>Data</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.75in</Width>
</TableColumn>
</TableColumns>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>17</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<rd:DefaultName>textbox18</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<rd:DefaultName>textbox23</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>15</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<rd:DefaultName>textbox28</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Double</Top>
</BorderStyle>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>12</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Height>1.25in</Height>
</Body>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
"AKCodeRat" wrote:
> Hi,
> I have a report that is going to have to show the following.
> Columns 1-3 will be counts of a certain activity.
> Columns 4-12 will have calculations based on the data contained in the 3rd
> count.
>
> I can get the counts, and I can perform the calculations just fine. Where I
> run into a problem is on the display. I have a table with some grouping that
> will do the calculations and show columns 3-12, with the data available if
> the user wishes to drill down. If I have second data set with my counts how
> can I get the data from it where my keys are equal? I know that I could put
> all the counts in my data set but I donâ't want to have them in each row
> because I am expecting to only have around 100 groups, but over 8000 rows of
> data will be need to calculate columns 4-12.
> I guess what I am really after is a way to query a dataset, or to join them
> inside of the rdl.
>

Dataset Query IN Clause issue

Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
want to use a variable in my IN clause that that holds the different
values (strings) but I can't seem to get it to work. I've posted the
SQL statement as it currently is and what I tried to do to get it to
work.
Thanks for any help in advance.
Mike
Current Statemenet:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotos
What I tried:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotosMike,
The string variable is treated as a single data element, not a list or array
of elements. So, if:
SET @.PhotoSet = '1,23,56,78'
Your query would only return a row with the value '1,23,56,78' in
vewStatsInnerSummary.Batch.
You can slice up the string into a temporary table and then use that table
in your query, or use dynamic SQL, etc.
Vyas has a write up on your issue.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
RLF
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>|||The type of (Reporting Services) parameter you are using is for a
multivalued parameter. It would show up as a drop down with several values,
each value having a checkbox next to it. Then, whatever values are checked
will be in your "IN" clause. The top checkbox is used for "Select All".
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>

dataset query based on a global parameter

I need to create a report that displays data based on the userid. all the
records have a userid field filled with many userid values. how can I use the
global paramerer userid in the criteria for my dataset. so when a user run
the report olny records with his or her userid are showncreate a parameter with default value the global parameter. then use that
parameter in the query. ofcourse you can't use caching for those reports
"DJJIII" wrote:
> I need to create a report that displays data based on the userid. all the
> records have a userid field filled with many userid values. how can I use the
> global paramerer userid in the criteria for my dataset. so when a user run
> the report olny records with his or her userid are shown|||You do not have to have a 1:1 mapping between query parameters and report
parameters. Doing it the way suggested here means you have to muck around
with hiding it (since you don't want people to change it).
Have your query parameter, let's call it @.UserID. RS will automatically
recreate a report parameter called UserID but we won't use it. Click on the
..., go to parameters. Map the query parameter @.UserID to the global . The
parameter dialog box has a name and value columns. On the value side switch
it to expression. This brings you to the expression builder where you can
pick the User!UserID global variable. One thing to note, this variable has
the domain as well as the user id, so if you don't want this you will have
to strip it off.
Now, go you your layout, Report->Parameters and delete the now unneeded
UserID report parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> create a parameter with default value the global parameter. then use that
> parameter in the query. ofcourse you can't use caching for those reports
> "DJJIII" wrote:
>> I need to create a report that displays data based on the userid. all
>> the
>> records have a userid field filled with many userid values. how can I use
>> the
>> global paramerer userid in the criteria for my dataset. so when a user
>> run
>> the report olny records with his or her userid are shown|||great tip, that is indeed a lot more practical
"Bruce L-C [MVP]" wrote:
> You do not have to have a 1:1 mapping between query parameters and report
> parameters. Doing it the way suggested here means you have to muck around
> with hiding it (since you don't want people to change it).
> Have your query parameter, let's call it @.UserID. RS will automatically
> recreate a report parameter called UserID but we won't use it. Click on the
> ..., go to parameters. Map the query parameter @.UserID to the global . The
> parameter dialog box has a name and value columns. On the value side switch
> it to expression. This brings you to the expression builder where you can
> pick the User!UserID global variable. One thing to note, this variable has
> the domain as well as the user id, so if you don't want this you will have
> to strip it off.
> Now, go you your layout, Report->Parameters and delete the now unneeded
> UserID report parameter.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> > create a parameter with default value the global parameter. then use that
> > parameter in the query. ofcourse you can't use caching for those reports
> >
> > "DJJIII" wrote:
> >
> >> I need to create a report that displays data based on the userid. all
> >> the
> >> records have a userid field filled with many userid values. how can I use
> >> the
> >> global paramerer userid in the criteria for my dataset. so when a user
> >> run
> >> the report olny records with his or her userid are shown
>
>

Dataset error when deployed to Report Manager

Hi
I've got a custom assembly which performs validation on parameters
prior to submitting the query to the database. My dataset which
retrieves the report data is in Text format and calls an appropriate
validation method on free-text parameters, such as dates and numbers.
When validation passes, the code returns a string formatted for use in
the query, and when it fails it returns a string value that the stored
procedure recognises as a dummy value and returns without SELECTing any
data. My custom assembly sets a user-friendly error message as a
property, which is displayed on the report in place of any data.
I've designed a number of reports using this method and it works well
in the Designer environment. However, I've deployed a few of them to
Report Manager and am getting the following error when pressing "View
Report":
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Report'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset
'Report'. (rsQueryCommandTextProcessingError)
An example of one of the simple Datasets is:
="EXEC spAgentSettlementsDetailedRpt " &
Code.Validator.ResetValidator &
"@.p_StartDate_IN = '" +
Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
" &
"@.p_EndDate_IN= '" +
Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
Parameters!p_EndDate_IN.Value) + "', " &
"@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
&
"@.p_WS_Code_IN = " +
Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
Parameters!p_AgentID_IN.Value, false)
I'm happy to provide the code within the assembly if it would be of any
help in debugging this problem.
In deploying to the server, I have copied the assembly (dll) to the
\bin directory on the server and uploaded the rdl file as per normal.
Any help would be greatly appreciated. Thank you.
AshleyI've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
Ashley.Manos@.gmail.com wrote:
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley|||I've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley

Dataset Dynamic Query

I am trying to implement the setting of a parameter to the default value of
'-All-' using the IIF function in the WHERE clause.
I received a processing error then backed off and checked a basic 'SELECT
select list FROM tablename WHERE columnname = value' query. It ran in
Preview just fine.
Then I wrapped the query in ' = " query w/o changes" ' When this runs in
Preview I get the following message in a pop-up box .. "Processing Error ..
An error has occurred during report processing. Query execution failed for
data set 'TestDS'. Line 1: Incorrect syntax error'.' "
The only change is the ' = " ..." '
The Datatset Command type is 'Text'
Any idea what is happening here'
--
Thanks in advance
GHHi Vakar,
Based on my knowledge, the Generic Query Designer Execute button will grey
out when the query begins with an '='
This is expected behavior since dynamic query cannot be executed in Generic
Query Designer, which also means you are not able to Perview the data now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Correct the i cannot execute the query from the Query Designer in the Data tab.
I get the error when I go to the the Preview tab.
Thanks
"Michael Cheng [MSFT]" wrote:
> Hi Vakar,
> Based on my knowledge, the Generic Query Designer Execute button will grey
> out when the query begins with an '='
> This is expected behavior since dynamic query cannot be executed in Generic
> Query Designer, which also means you are not able to Perview the data now.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||If you get an error when you go to Preview it is because something is wrong
with your string. If you have to (and I avoid it) use an expression then do
these steps.
1. Create a report with report parameters and a text box (nothing else)
2. Set the textbox to the expression that will end up being the source for
the dataset. Preview and look at what is in the textbox and make sure that
it is valid SQL (copy and paste into query analyzer and validate you have it
correctly).
3. create a query that will have the same fields returned to get you field
list populated
4. change to the expression that you now know (from #2) is correct.
These are the steps I always take.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> Correct the i cannot execute the query from the Query Designer in the Data
tab.
> I get the error when I go to the the Preview tab.
> Thanks
> "Michael Cheng [MSFT]" wrote:
> > Hi Vakar,
> >
> > Based on my knowledge, the Generic Query Designer Execute button will
grey
> > out when the query begins with an '='
> >
> > This is expected behavior since dynamic query cannot be executed in
Generic
> > Query Designer, which also means you are not able to Perview the data
now.
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Great suggustion ... I was going to ask for debugging suggestions ... I
The following testing was just completed.
I created a dataset (query 2) that is a subset of my problem query (query 1)
which is populating another table. The IIF function is the SAME for both
queries (I cut and pasted from Query 2 to Query 1). Query 2 works but Query
1 gets the error. Query 1 runs when commenting out the IIF function and
removing the =" ".
Here are the queries:
Query 1
="SELECT DISTINCT
a.EID
,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
,a.PassageDistrictID
,Region = CASE
WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
WHEN e.PassageRegionFullName = '' THEN 'Unknown'
ELSE e.PassageRegionFullName
END
,a.EventName
,RegionalMgrName = CASE
WHEN b.LastName IS NULL THEN 'N/A'
WHEN b.LastName = '' THEN 'N/A'
ELSE b.LastName
END
,OperationsMgrName = CASE
WHEN d.LastName IS NULL THEN 'N/A'
WHEN d.LastName = '' THEN 'N/A'
ELSE d.LastName
END
,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
FROM dbo.Event a
LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=e.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
Query 2
="SELECT a.EID, a.EventName
FROM dbo.Event a
JOIN GeographicDim_PE b
ON a.PassageDistrictID = b.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
"Bruce L-C [MVP]" wrote:
> If you get an error when you go to Preview it is because something is wrong
> with your string. If you have to (and I avoid it) use an expression then do
> these steps.
> 1. Create a report with report parameters and a text box (nothing else)
> 2. Set the textbox to the expression that will end up being the source for
> the dataset. Preview and look at what is in the textbox and make sure that
> it is valid SQL (copy and paste into query analyzer and validate you have it
> correctly).
> 3. create a query that will have the same fields returned to get you field
> list populated
> 4. change to the expression that you now know (from #2) is correct.
> These are the steps I always take.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > Correct the i cannot execute the query from the Query Designer in the Data
> tab.
> >
> > I get the error when I go to the the Preview tab.
> >
> > Thanks
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> > > Hi Vakar,
> > >
> > > Based on my knowledge, the Generic Query Designer Execute button will
> grey
> > > out when the query begins with an '='
> > >
> > > This is expected behavior since dynamic query cannot be executed in
> Generic
> > > Query Designer, which also means you are not able to Perview the data
> now.
> > >
> > > Thank you for your patience and corporation. If you have any questions
> or
> > > concerns, don't hesitate to let me know. We are always here to be of
> > > assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Michael Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Get Secure! - http://www.microsoft.com/security
> > >
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
>|||I have heard that sometimes there is a problem with put in a hard carriage
return in the expression box. Make sure the iif part is on the same line.
I.e. let any line wrapping in the expression box occur on its own, don't do
a carriage return to put the iif on another line. Just a guess.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> Great suggustion ... I was going to ask for debugging suggestions ... I
> The following testing was just completed.
> I created a dataset (query 2) that is a subset of my problem query (query
1)
> which is populating another table. The IIF function is the SAME for both
> queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
Query
> 1 gets the error. Query 1 runs when commenting out the IIF function and
> removing the =" ".
> Here are the queries:
> Query 1
> ="SELECT DISTINCT
> a.EID
> ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> ,a.PassageDistrictID
> ,Region => CASE
> WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> ELSE e.PassageRegionFullName
> END
> ,a.EventName
> ,RegionalMgrName => CASE
> WHEN b.LastName IS NULL THEN 'N/A'
> WHEN b.LastName = '' THEN 'N/A'
> ELSE b.LastName
> END
> ,OperationsMgrName => CASE
> WHEN d.LastName IS NULL THEN 'N/A'
> WHEN d.LastName = '' THEN 'N/A'
> ELSE d.LastName
> END
> ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> FROM dbo.Event a
> LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> e.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> Query 2
> ="SELECT a.EID, a.EventName
> FROM dbo.Event a
> JOIN GeographicDim_PE b
> ON a.PassageDistrictID = b.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> "Bruce L-C [MVP]" wrote:
> > If you get an error when you go to Preview it is because something is
wrong
> > with your string. If you have to (and I avoid it) use an expression then
do
> > these steps.
> > 1. Create a report with report parameters and a text box (nothing else)
> > 2. Set the textbox to the expression that will end up being the source
for
> > the dataset. Preview and look at what is in the textbox and make sure
that
> > it is valid SQL (copy and paste into query analyzer and validate you
have it
> > correctly).
> > 3. create a query that will have the same fields returned to get you
field
> > list populated
> > 4. change to the expression that you now know (from #2) is correct.
> >
> > These are the steps I always take.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > Correct the i cannot execute the query from the Query Designer in the
Data
> > tab.
> > >
> > > I get the error when I go to the the Preview tab.
> > >
> > > Thanks
> > >
> > > "Michael Cheng [MSFT]" wrote:
> > >
> > > > Hi Vakar,
> > > >
> > > > Based on my knowledge, the Generic Query Designer Execute button
will
> > grey
> > > > out when the query begins with an '='
> > > >
> > > > This is expected behavior since dynamic query cannot be executed in
> > Generic
> > > > Query Designer, which also means you are not able to Perview the
data
> > now.
> > > >
> > > > Thank you for your patience and corporation. If you have any
questions
> > or
> > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > assistance!
> > > >
> > > >
> > > > Sincerely yours,
> > > >
> > > > Michael Cheng
> > > >
> > > > Online Partner Support Specialist
> > > > Partner Support Group
> > > > Microsoft Global Technical Support Center
> > > > ---
> > > > Get Secure! - http://www.microsoft.com/security
> > > >
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > Please reply to newsgroups only, many thanks!
> > > >
> > > >
> >
> >
> >|||I just tried that ...
I removed all cr/lf making on long line of code ... nastly ... but it
worked ...
My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
Thanks for you help!!
"Bruce L-C [MVP]" wrote:
> I have heard that sometimes there is a problem with put in a hard carriage
> return in the expression box. Make sure the iif part is on the same line.
> I.e. let any line wrapping in the expression box occur on its own, don't do
> a carriage return to put the iif on another line. Just a guess.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > Great suggustion ... I was going to ask for debugging suggestions ... I
> >
> > The following testing was just completed.
> >
> > I created a dataset (query 2) that is a subset of my problem query (query
> 1)
> > which is populating another table. The IIF function is the SAME for both
> > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> Query
> > 1 gets the error. Query 1 runs when commenting out the IIF function and
> > removing the =" ".
> >
> > Here are the queries:
> > Query 1
> > ="SELECT DISTINCT
> > a.EID
> > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > ,a.PassageDistrictID
> > ,Region => > CASE
> > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > ELSE e.PassageRegionFullName
> > END
> > ,a.EventName
> > ,RegionalMgrName => > CASE
> > WHEN b.LastName IS NULL THEN 'N/A'
> > WHEN b.LastName = '' THEN 'N/A'
> > ELSE b.LastName
> > END
> > ,OperationsMgrName => > CASE
> > WHEN d.LastName IS NULL THEN 'N/A'
> > WHEN d.LastName = '' THEN 'N/A'
> > ELSE d.LastName
> > END
> > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> > FROM dbo.Event a
> > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > e.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > Query 2
> > ="SELECT a.EID, a.EventName
> > FROM dbo.Event a
> > JOIN GeographicDim_PE b
> > ON a.PassageDistrictID = b.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you get an error when you go to Preview it is because something is
> wrong
> > > with your string. If you have to (and I avoid it) use an expression then
> do
> > > these steps.
> > > 1. Create a report with report parameters and a text box (nothing else)
> > > 2. Set the textbox to the expression that will end up being the source
> for
> > > the dataset. Preview and look at what is in the textbox and make sure
> that
> > > it is valid SQL (copy and paste into query analyzer and validate you
> have it
> > > correctly).
> > > 3. create a query that will have the same fields returned to get you
> field
> > > list populated
> > > 4. change to the expression that you now know (from #2) is correct.
> > >
> > > These are the steps I always take.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "GH" <vakar@.community.nospam> wrote in message
> > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > Correct the i cannot execute the query from the Query Designer in the
> Data
> > > tab.
> > > >
> > > > I get the error when I go to the the Preview tab.
> > > >
> > > > Thanks
> > > >
> > > > "Michael Cheng [MSFT]" wrote:
> > > >
> > > > > Hi Vakar,
> > > > >
> > > > > Based on my knowledge, the Generic Query Designer Execute button
> will
> > > grey
> > > > > out when the query begins with an '='
> > > > >
> > > > > This is expected behavior since dynamic query cannot be executed in
> > > Generic
> > > > > Query Designer, which also means you are not able to Perview the
> data
> > > now.
> > > > >
> > > > > Thank you for your patience and corporation. If you have any
> questions
> > > or
> > > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > > assistance!
> > > > >
> > > > >
> > > > > Sincerely yours,
> > > > >
> > > > > Michael Cheng
> > > > >
> > > > > Online Partner Support Specialist
> > > > > Partner Support Group
> > > > > Microsoft Global Technical Support Center
> > > > > ---
> > > > > Get Secure! - http://www.microsoft.com/security
> > > > >
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > > Please reply to newsgroups only, many thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great. Wasn't sure if it would solve the problem but I couldn't see anything
wrong (having the second query with the iif work but not the first was the
clue for me).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:FF5CFDA6-8144-482A-BA91-DB35B53ECE25@.microsoft.com...
> I just tried that ...
> I removed all cr/lf making on long line of code ... nastly ... but it
> worked ...
> My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
> Thanks for you help!!
> "Bruce L-C [MVP]" wrote:
> > I have heard that sometimes there is a problem with put in a hard
carriage
> > return in the expression box. Make sure the iif part is on the same
line.
> > I.e. let any line wrapping in the expression box occur on its own, don't
do
> > a carriage return to put the iif on another line. Just a guess.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > > Great suggustion ... I was going to ask for debugging suggestions ...
I
> > >
> > > The following testing was just completed.
> > >
> > > I created a dataset (query 2) that is a subset of my problem query
(query
> > 1)
> > > which is populating another table. The IIF function is the SAME for
both
> > > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> > Query
> > > 1 gets the error. Query 1 runs when commenting out the IIF function
and
> > > removing the =" ".
> > >
> > > Here are the queries:
> > > Query 1
> > > ="SELECT DISTINCT
> > > a.EID
> > > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > > ,a.PassageDistrictID
> > > ,Region => > > CASE
> > > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > > ELSE e.PassageRegionFullName
> > > END
> > > ,a.EventName
> > > ,RegionalMgrName => > > CASE
> > > WHEN b.LastName IS NULL THEN 'N/A'
> > > WHEN b.LastName = '' THEN 'N/A'
> > > ELSE b.LastName
> > > END
> > > ,OperationsMgrName => > > CASE
> > > WHEN d.LastName IS NULL THEN 'N/A'
> > > WHEN d.LastName = '' THEN 'N/A'
> > > ELSE d.LastName
> > > END
> > > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS
AssignmentLtrSentDt
> > > FROM dbo.Event a
> > > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > > e.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > Query 2
> > > ="SELECT a.EID, a.EventName
> > > FROM dbo.Event a
> > > JOIN GeographicDim_PE b
> > > ON a.PassageDistrictID = b.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > If you get an error when you go to Preview it is because something
is
> > wrong
> > > > with your string. If you have to (and I avoid it) use an expression
then
> > do
> > > > these steps.
> > > > 1. Create a report with report parameters and a text box (nothing
else)
> > > > 2. Set the textbox to the expression that will end up being the
source
> > for
> > > > the dataset. Preview and look at what is in the textbox and make
sure
> > that
> > > > it is valid SQL (copy and paste into query analyzer and validate you
> > have it
> > > > correctly).
> > > > 3. create a query that will have the same fields returned to get you
> > field
> > > > list populated
> > > > 4. change to the expression that you now know (from #2) is correct.
> > > >
> > > > These are the steps I always take.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "GH" <vakar@.community.nospam> wrote in message
> > > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > > Correct the i cannot execute the query from the Query Designer in
the
> > Data
> > > > tab.
> > > > >
> > > > > I get the error when I go to the the Preview tab.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Michael Cheng [MSFT]" wrote:
> > > > >
> > > > > > Hi Vakar,
> > > > > >
> > > > > > Based on my knowledge, the Generic Query Designer Execute button
> > will
> > > > grey
> > > > > > out when the query begins with an '='
> > > > > >
> > > > > > This is expected behavior since dynamic query cannot be executed
in
> > > > Generic
> > > > > > Query Designer, which also means you are not able to Perview the
> > data
> > > > now.
> > > > > >
> > > > > > Thank you for your patience and corporation. If you have any
> > questions
> > > > or
> > > > > > concerns, don't hesitate to let me know. We are always here to
be of
> > > > > > assistance!
> > > > > >
> > > > > >
> > > > > > Sincerely yours,
> > > > > >
> > > > > > Michael Cheng
> > > > > >
> > > > > > Online Partner Support Specialist
> > > > > > Partner Support Group
> > > > > > Microsoft Global Technical Support Center
> > > > > > ---
> > > > > > Get Secure! - http://www.microsoft.com/security
> > > > > >
> > > > > > This posting is provided "as is" with no warranties and confers
no
> > > > rights.
> > > > > > Please reply to newsgroups only, many thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >