Problem with SqlDataSource using sub-query and date as parameters

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.

In my SqlDataSource, I setup the SQL like this.

select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)

Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appended

and exists (select 1 from detailTable where pid = masterTable.id and productNo = @.productNo)

The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.

Any suggestion on tackling this problem? Thanks!

hi,

U can avoid this by diffarent options...By above information I can explain like this

1 use UNION

2 Use Primary Key in your every Subquery Query followed by the Search.

3 Use Joins with valid Key Elements.etc

or send the required result columnes and the Table Design

bye

murthy

|||

Hi Murthy,

1. use UNION

I don't know how should I use UNION in master-detail structure. Please give more detail.

2. The primary key is already used in the where clause of the subquery. The column pid means the primary key ID in master table.

3. Use Join

The use of join is not desirable. I have to group the records back together afterward. I just want to search the master table but use detail record as criteria. If I join them without group, the master record will repeat themselves in the result.

Actually, I'm strange about the performance difference by using ADO.NET and SQL Server management studio.

|||

Hi,

OK

Do this Use "#' table with one primary key ID as the Column column in all the condictions

and finally join all the Table with the key Elements..

Ex:

select <Key>,<Search column 1 > into #TableA from <Master table> where <Condition>

select <Key>,<Search column 2 > into #TableB from <Master table> where <Condition>

.....

....

and Finally

select <Search Column1>,<Search Column2>.<Search Column3>,... from #TableA, #TableB, #TableC...where #TableA.Key=#TableB.Key,#TableA.Key=#TableC.key etc

drop all temp table

your result is ready now...

|||

This approach seems making the simple request into a complex one.

problem with report parameters in nested IIf

I have the following to diplay the frequecny a report is run for -
based on parameters selected at run time...
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", Parameters!EndDate.Value,
Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
but if I leave the start and end date null I get an error #Error and
the following...
The value expression for the textbox 'textbox1' contains an error:
Object variable or With block variable not set.
However, if I replace the above with the following (instead of printing
out the date parameter value in the inner iif, print out a string
instead)....
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", "Parameters!EndDate.Value",
"Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
it works fine and will display the frequency if both dates are null.
Any idea why this is?
Also as an aside - is there a better way to check is a date field is
null instead of > "01/01/1900"'
Thanks in advance,
Gear=F3idThis occurs for 2 reasons:
1. an Iif statement always evaluates all parts of an equation.
Therefore, if 1/2 of the statement is invalid, the whole thing throws an
error.
2. You're trying to AddHours(1) to a null value, which throws an error.
I don't know why putting it in "" causes it to work, though.
As for a better option than > "01/01/1900", I recommend >
DateTime.MinValue, as that is the default value for an unassigned DateTime.
Ciao,
Noah
Gearoid wrote:
> I have the following to diplay the frequecny a report is run for -
> based on parameters selected at run time...
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", Parameters!EndDate.Value,
> Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
> but if I leave the start and end date null I get an error #Error and
> the following...
> The value expression for the textbox 'textbox1' contains an error:
> Object variable or With block variable not set.
> However, if I replace the above with the following (instead of printing
> out the date parameter value in the inner iif, print out a string
> instead)....
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", "Parameters!EndDate.Value",
> "Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
> it works fine and will display the frequency if both dates are null.
> Any idea why this is?
> Also as an aside - is there a better way to check is a date field is
> null instead of > "01/01/1900"'
> Thanks in advance,
> Gearóid
>|||Hey Noah,
Thanks for getting back to me on this. I put it aside for a while but
have to get it sorted now.
I reckon you're right about it trying to add 1 hour to a null value and
bombing out. But it seems kinda crazy that it would try to evaluate
all parts of an IIf statement. How would I conditionally add an hour
to a datetime value so if it's not null? I thought that's what an if
statements for?!...

Problem with passing report parameters using POST request.

Hi

We are accessing reports through our web application. Our front end is implemented using tapestry
Our report is using four parameters. The report url is like
http://<servername>/ReportServer/Pages/ReportViewer.aspx?/<Report_Proj_Name>/<ReportName>

We are using POST requests with our report parameters defined as hidden variables.
If we use GET request, with parameters as query string then we are able to view our reports.

When I tried to access the report through web application, it gives out an error message as:

Reporting Services Error
---

An attempt was made to set a report parameter 'formids' that is not
defined in this report. (rsUnknownReportParameter) Get Online Help

---

For POST request, we get the above mentioned error. When I saw the view source I could see that tapestry
internally sets its own hidden variable for persisting its state in session.
formids is one of the hidden variable set by it.

How should I specify in the report that formids is not one of the report parameter.
There may be other hidden variables other than formids too that may get generated at runtime.
What setting should I do at the report end or at the application end?

Please help me find a solution for this problem.

Thanks!

I would suggest using a separate <form> tag in the application and populating this with just the parameters expected by the report. So I would make the change at the application level.|||

Thanks for your response! but I tried doing that still its not working.

Tapestry at runtime generates those hidden variables.

problem with parameters in report url

I'm am trying to produce a report which has a parameter value in its url.
as I understand it
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
should produce the job_details report for the job named "PAPERLESS". but all
I get is the exact same page as I get if I use
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
I am using reporting servces 2005, can anyone see what I am doing wrong?
Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1update, I found out I should have user the following url
http://reports.server.local/Reportserver/Pages/ReportViewer.aspx?%2fdrift%2fjob_details&rs%3aCommand=Render&job_navn=PAPERLESS
tvb wrote:
>I'm am trying to produce a report which has a parameter value in its url.
>as I understand it
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
>should produce the job_details report for the job named "PAPERLESS". but all
>I get is the exact same page as I get if I use
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
>I am using reporting servces 2005, can anyone see what I am doing wrong?
>Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1

Problem with Parameters

I am writing a Data Processing Extension for Reporting Services and am
having a problem getting the values of parameters I pass in the query string
in ReportDesigner.
I have implemented the IDbCommandAnalysis GetParameters() method to parse
the query string in ReportDesigner. When I enter
"&CompanyId=1000&EmployeeId=2000" in the "Query string" field,
GetParameters() returns a local collection with my two parameters and their
values.
Upon command execution, the IDataParameterCollection IDbCommand.Parameters
property getter is called twice before calling ExecuteReader() and return my
member parameter collection. When ExecuteReader() is called, my member
parameter collection contains the two parameters of the correct name but
with values of null.
Because the parameters with correct names are added to the collection, it
seems that ReportDesigner saved the parameter collection I returned from
GetParameters() but what happened to the parameter values?
Since the values are null, ReportDesigner pops a window for the values to be
input. I don't want this to happen after the report is deployed but cannot
find a way to simulate the parameter name-value pairs being submitted on the
URL string.
What am I missing?
Thanks,
TomHi Tom,
Welcome to use MSDN Managd Newsgroup!
I am looking into this issue and will update you as soon as possible. I
have also noticed you have another two duplicated posts. To ensure the
integrity of newsgroup, I will answer your questions and follow up issue in
this thread.
Thank you for your patience and cooperation. 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
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for your reply. I wanted to pass along some additional information
about the parameter problem I'm having.
I published my report and fount the parameters are passed to the report when
added to the URL. That's good, but it points out a big disparity between
parameter handling in ReportDesigner and in published reports.
I look forward to learning what you discover about the correct way to handle
parameters.
Thanks,
Tom
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:WJlr33TeFHA.4856@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Welcome to use MSDN Managd Newsgroup!
> I am looking into this issue and will update you as soon as possible. I
> have also noticed you have another two duplicated posts. To ensure the
> integrity of newsgroup, I will answer your questions and follow up issue
> in
> this thread.
> Thank you for your patience and cooperation. 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
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tom,
Sorry for the delay, would you please provide me a sample project? I
understand the information may be sensitive to you, my direct email address
is v-mingqc@.online.microsoft.com (please REMOVE "online" before you click
SEND as it's ONLY for SPAM), you may send the file to me directly and I
will keep secure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I am having the same problem. Did you ever figure this out for Tom?
I am trying to run a report by passing in the parameter through the url.
The report comes up but prompts for the parameter instead of taking it
through url. If I enter it at the prompt, the report runs fine. My url looks
like:
http://server-1/Reports/Pages/Report.aspx?ItemPath=%2fMarkListingbyStudent%2fMarkListingbyStudent&rs:Command=Render&calendarID=155
It's internal though.
Thank you for your help!
Sharlyn
"Michael Cheng [MSFT]" wrote:
> Hi Tom,
> Sorry for the delay, would you please provide me a sample project? I
> understand the information may be sensitive to you, my direct email address
> is v-mingqc@.online.microsoft.com (please REMOVE "online" before you click
> SEND as it's ONLY for SPAM), you may send the file to me directly and I
> will keep secure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Problem with Parameter

Hi ,

I am having a peculiar problem when i am using report parameters in a report. I am using report paramter to let the user select the month for which the report has to run, but the drop down has ordered the months in alphabetical order,for selection Is there any way I can set it to list it in the correct order ?

Thanks in advance
PMJ

If you are using an analysis services datasource, there is an easy way to solve this.

Open up your Date Dimension. Select the Month attribute. Right-click and select properties. Set the "Order By" property to key. Build and deploy your solution. Refresh the project in Reporting Services. And you will be good to go.

|||Hi Joel,

thanks a lot it worked !!!

Thanks and regards
PMNJ