Report with Multiple Data sources: Can I do this?

Okay, so I have this ADO.NET dataset defined in my project. I created
an RDLC report and from the menu Report->Data Sources... I chose two
different tables that exist in my ADO dataset. So far so good. At this
point I can drag specific columns into various text boxes I have in my
report (I'm not using tables) and I end up with entries like
"=First(Fields!ContactName.Value)". That's all well and good, but what
I was hoping to do, in addition to that, is something where I can
specify a particular record from one of the tables. Basically what I
have is a table that has two different links to another table. Now
each of those links may be to the same record, or to two different
records. I have a "Job" table and a "Company" table. The Job table has
a link to a "Dealer" and an "Installer" (both of which are records--or
the same record--in the Company table). I was hoping to be able to
display the installer name in one text box and the dealer name in
another. I have two issues: I don't know how to refer to a specific
table when referring to a particular field (both the Job and Company
tables have a "ContactName" column), nor does there appear to be a way
to pick a particular record. I can't seem to be able to do something
like "=Fields!Job.ContactName.Value" when I want to refer to the
ContactName in the Job table as opposed to the Company table. Further,
I can't figure out how to do something like (kinda pseudo code here)
"=Fields!Company.ContactName WHERE Company.ID == First(Fields!
Job.InstallerID.Value)"
So it comes down to this: Can I do anything like what I have described
above? If I can't, my next thought it to create a stored procedure
that gets every value I need and stores it in a custom named field so
I have JobContactName, InstallerContactName, etc to refer to, but then
my next question is can I define a stored procedure that will let me
grab the data I'm looking for from my in-memory ADO.NET Dataset?So I discovered that using subreports would solve my immediate
problem, but I'm still curious if there is a way to do some of what I
describe above, which is: access each different datasource via Fields,
and access specific rows in that source. Right now we can access First/
Last and what ever is chosen as the default when you don't specify
First or Last, but why can't we access a specific record based on the
value of a field in that record? I just want to know if any/all of the
above is possible or not, and if not, what are the patterns people
follow to get around these limitations?
Thanks!

Report with default of all

Have a report with three datasets. The first dataset pulls the
department table and uses this as one of the parameters.
select DEPT from dept
The second dataset pulls the division table and uses that as one of the
parameters.
select DIV from div
The third dataset calls a stored procedure that passes the startdate,
enddate, division and department for the report. With the startdate and
enddate also being parameters.
exec wo_sstm @.startdate, @.enddate, @.division, @.department
How can I set the parameters for @.division and @.department to utilize a
default of all for each.for your first dataset for dept:
select 0 as DeptID, '<All>' as DEPT FROM dept
UNION ALL
select DeptID, DEPT from dept
Then your value will be DeptID and label will be DEPT in Parameters
In your second dataset, same thing:
select 0 AS DivisionID, '<All>' as DIV from div
UNION ALL
select DivisionID, DIV from div
Then in your stored procedure wo_sstm you will process 0 as the indicator
for all.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> Have a report with three datasets. The first dataset pulls the
> department table and uses this as one of the parameters.
> select DEPT from dept
> The second dataset pulls the division table and uses that as one of the
> parameters.
> select DIV from div
> The third dataset calls a stored procedure that passes the startdate,
> enddate, division and department for the report. With the startdate and
> enddate also being parameters.
> exec wo_sstm @.startdate, @.enddate, @.division, @.department
> How can I set the parameters for @.division and @.department to utilize a
> default of all for each.
>|||Chris,
Getting invalid column name 'DeptID'. How does this work with no DeptID
column?
Chris Conner wrote:
> for your first dataset for dept:
> select 0 as DeptID, '<All>' as DEPT FROM dept
> UNION ALL
> select DeptID, DEPT from dept
> Then your value will be DeptID and label will be DEPT in Parameters
> In your second dataset, same thing:
> select 0 AS DivisionID, '<All>' as DIV from div
> UNION ALL
> select DivisionID, DIV from div
>
> Then in your stored procedure wo_sstm you will process 0 as the indicator
> for all.
> =-Chris
> "chad" <chad.heilig@.gmail.com> wrote in message
> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> > Have a report with three datasets. The first dataset pulls the
> > department table and uses this as one of the parameters.
> > select DEPT from dept
> >
> > The second dataset pulls the division table and uses that as one of the
> > parameters.
> > select DIV from div
> >
> > The third dataset calls a stored procedure that passes the startdate,
> > enddate, division and department for the report. With the startdate and
> > enddate also being parameters.
> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
> >
> > How can I set the parameters for @.division and @.department to utilize a
> > default of all for each.
> >|||No - DeptID is something I made up - Do you have a column that uniquely
identifies your departments? I assumed you would understand that DeptID in
this case was an ID that uniquely identifies that department.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163601753.813127.152770@.h54g2000cwb.googlegroups.com...
> Chris,
> Getting invalid column name 'DeptID'. How does this work with no DeptID
> column?
>
> Chris Conner wrote:
>> for your first dataset for dept:
>> select 0 as DeptID, '<All>' as DEPT FROM dept
>> UNION ALL
>> select DeptID, DEPT from dept
>> Then your value will be DeptID and label will be DEPT in Parameters
>> In your second dataset, same thing:
>> select 0 AS DivisionID, '<All>' as DIV from div
>> UNION ALL
>> select DivisionID, DIV from div
>>
>> Then in your stored procedure wo_sstm you will process 0 as the indicator
>> for all.
>> =-Chris
>> "chad" <chad.heilig@.gmail.com> wrote in message
>> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
>> > Have a report with three datasets. The first dataset pulls the
>> > department table and uses this as one of the parameters.
>> > select DEPT from dept
>> >
>> > The second dataset pulls the division table and uses that as one of the
>> > parameters.
>> > select DIV from div
>> >
>> > The third dataset calls a stored procedure that passes the startdate,
>> > enddate, division and department for the report. With the startdate and
>> > enddate also being parameters.
>> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
>> >
>> > How can I set the parameters for @.division and @.department to utilize a
>> > default of all for each.
>> >
>

Report Viewer

Hi,

I am new to using Report Viewer.

I created a basic report (Local Mode) using Report vIewr control in VS 2005 and typed dataset and it works fine.

But I would like to view a report with a parameter.

Can anyone explain me how to do that and bind it with the report viewer?

Thanks & Regards,

Lavanya.

Do you want to set parameters from your code?

|||

In the ASPX right after the

see the object source code and replace with your stuff

<asp:ObjectDataSourceID="ObjectDataSource2"runat="server"

SelectMethod="GetData"

TypeName="DataSetOTDTableAdapters.DataTableOTDFamilyTableAdapter">

<selectparameters>

<asp:querystringparametername="FromDate"querystringfield="FromDate"defaultvalue="01/01/2007"/>

<asp:querystringparametername="ToDate"querystringfield="ToDate"defaultvalue="07/01/2007"/>

</selectparameters>

</asp:ObjectDataSource>

that coresponds to a getdate method in the dataset that has 2 parameters @.FromDate and @.ToDate

You can also change the values of parameters in the PageInit event.

|||

I want to set the parameters in the report.rdlc.

I got one parameter working.

I have two textboxes. I have to see the report for a specific date range.

How can I do that?

For a single date, I set the operator in the filter as =.

What if I want to use two parameters (date range).

How do I set it in report.rdlc?

|||

I am assuming you are not using Crystal Reports. How do you fill the dataset? Can you supply the range parameters to the dataset instead of reportviewer? Also see if filtering works for you:

http://msdn2.microsoft.com/en-us/library/ms252125(VS.80).aspx

As I suggested, filtering data before report is rendered is a better option.

|||

Hi, I have a matrix report with rows as a name and the column as a date field.

When i run the report it works the way i want.

08/03/2007 07/31/200708/01/2007Adams , William484Anderson , David480Asseng , Steve440Bangerd , Michael048Barnaskas , Joe000Barry , Timothy084Berry , Jerry404Bowen , Nels000Breakall , Jeff4012Brockel , Robert048Campbell , Bryan000Castro , Carlos000Childs , Michael8124Conklin , Terry888

But when I give the parameter as the date(08/03/2007), I am not gettin the rows with 0. What am i doin wrong?

|||

Are you doing the filtering as suggested in that article?

|||

Yeah I create report parameters and then I include filters in the matrix.

|||

Am just curious, 0 implies there is no record or value for that date, right? So that means filter works as it should. Or, what do those values mean?

|||

Yeah... 0 means the count of the entries for that date is 0. If it is 4 it means the count of entries for that date and for that tech is 4.

Now when i dont include any parameters, I get the 0 entries also.

I need the same thing when i include my date parameter.. is it possible?

|||

I am sorry but maybe I am not getting it right. What is the purpose of a filter if you want to display all the values. Let me look into it and I will get back to you if I find something.

|||

It is just similar to the one without parameter. I add an parameter So that I can see the data only for that date. Now it is shoeing all dates...

|||

Lets backtrack. Is filtering working for you or not?

|||

Yeah it works. But the problem is it doesn not show me the columns with 0 .

|||

Let me put it crisp.

What I am trying to do is a Compliance report. If the tech comes in for that day he is compliant, if not he is not compliant.

So what I did was I did a matrix report with rows as Technician Name , Columns as Date and data as Count(Date)

It worked perfect. The result is shown below.

07/31/200708/01/200708/02/2007Adams , William848Anderson , David804Asseng , Steve404Bangerd , Michael488Barnaskas , Joe000Barry , Timothy844Berry , Jerry044Bowen , Nels000Breakall , Jeff0124Brockel , Robert4812Campbell , Bryan000Castro , Carlos000Childs , Michael1244Conklin , Terry888Contractor , Contractor000Cooke , Jackie000DeLeon , Carlos8120Dorn , Craig400Ehrlich , Chase000Elswick , James000English , Mike400Ewing , David000Favalora , Anthony41212Fierke , Bruce000Figueroa , Richard000

Now I need to include parameter to show the report for a specific date. In our example let us take 7/31/2007

I included report parameters for the report and filters for the matrix where Fields!Date.Value=Parameter!Date.Value

I have a textbox and a button in my aspx page.

On button click

Dim pubAs Microsoft.Reporting.WebForms.ReportParameter

Dim pubdateAsString = TextBox1.Text

pub =New Microsoft.Reporting.WebForms.ReportParameter("Arrival_Time", pubdate)Me.ReportViewer1.LocalReport.SetParameters(New Microsoft.Reporting.WebForms.ReportParameter() {pub})

ReportViewer1.LocalReport.Refresh()

And then I run the report with 07/31/2007 in the textbox and hit the button. I dont get

Barnaskas , Joe

Berry , Jerry

Bowen , Nels

Breakall , Jeff

Campbell , Bryan

Castro , Carlos

Contractor , Contractor

Cooke , Jackie

Ehrlich , Chase

Elswick , James

etc..,

because the count is 0.

Hope I explained it at my best. Please help.

Report Viewer

Hi, I am new to Report Viewer. I created a report using typed dataset and the report works fine,... But I am trying to build a report with parameters.

Can anyone tell me the step by step procedure of how to do this on entering the search criteria and clicking the button to generate a report?

seehttp://forums.asp.net/p/1143091/1844030.aspx#1844030 for some help

Report taking forever to render

Hi All,

This question pertains to sql server reporting services 2005.

I have a report in which i am using a stored procedure to populate the dataset. However if i run that report in the IDE or the browser it takes forever to run and nothing happens even after 30 min.

I thought something was wrong with the stored procedure so i ran it separately in the SQL server management and it runs in about 25 secs.

I am clueless why it is behaving like this.

Any suggestion will be appreciated.

HI,abhi81 :

Here are a few things to check:
- Make sure that you're report project Datasource is setup correctly
(i.e., has the correct connection string, etc).
- Make sure that if you are passing parameters to your stored
procedure/query, that you tie them correctly in the dataset (via the
Data tab -> Edit Dataset button [...] -> Parameters tab).
- If you are using report parameters, make sure that your datasets are
not erroring out for those parameters.
- Lastly, make sure that you have the latest service pack for SQL
Server.

And you can try to use a simply store procedure and make sure it is not the problem related to your comlicated one.
Hope this helps.

Report table does not display all rows from dataset

I have a dataset that when run returns 270 rows. The table using the dataset in the report only prints the first row. I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working. For now I just need the report to print all the returned rows. Help!!

Thanks!

Terry

Try to remove the grouping and see if you get the 270 rows in your report.

Jarret

Report shows no data

Why would my report not to show data while dataset has it?Hello, Mark
I have the same problem,
have you or anybody else got an answer?
"Mark Goldin" wrote:
> Why would my report not to show data while dataset has it?
>
>

Report Services

How do I set a Typed Dataset that is not connected to a database to a
report in Report Services? Is it possible or not?CJS wrote:
> How do I set a Typed Dataset that is not connected to a database to a
> report in Report Services? Is it possible or not?

You'll probably get a better response in
microsoft.public.sqlserver.reportingsvcs.

Simon

report server parameters

Hi!

I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:

An error has occurred during report processing.

Query execution failed for data set 'DataSet3_generar'.

Must declare the scalar variable "@.Param1".

I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.

But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.

I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)

So, is there a way of solve this using SQL server express? Or I need SQL server 2005?

If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?

Thanks! Any suggestion will help me a lot!

I also did have that sometimes. There is a refresh button at the dataset icons which will populate the used paramters from the dataset to the reports automatically. I always do the following, executing the query, then refreshing the reports to have the whole thing in sync and thats it. If this is a sql exeception brought to you by the SQL Server, it could be that you are using a variable in a dynamic sql statement which is not defined in the dyna′mic sql scope. Make sure that this does not apply.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi thanks for answering....

I have a query in my dataser similar to this...

select field1, field2 from table where field3 = @.param1

is this a dynamic sql statement? and where do I have to defined it for it to be in a dynamic sql scope?

I already try what you said about refreshing the datasources and then refreshing the report, but it doesn't populate the parameters to the report....

I am using visual basic. net web development I don't know if its diferent....

|||

I already find the problem ... what I had to to was to write in the *.rdl file that the parameter was used in a query and that it was a query parameter.

the code of my report is the following... hope it help someone...

<?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="Def_prescoConnectionString">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>fb5b58f0-f99b-4f88-9388-56107932e009</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Fecha1">

<UsedInQuery>Auto</UsedInQuery>

<DataType>DateTime</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>fecha1</Prompt>

</ReportParameter>

**** above -- the part of report parameter used in query

**** below -- the part of query parameters

<DataSets>
<DataSet Name="DataSet1_generar">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>DataSet1</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>generarTableAdapter</rd:TableAdapterName>
<rd:TableName>generar</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<QueryParameters>
<QueryParameter Name="@.fecha1">
<Value>=Parameters!Fecha1.Value</Value>
</QueryParameter>
</QueryParameters>

<CommandText>select id_form, sum(cant_latas) from generar where fecha = @.fecha1 group by id_form</CommandText>
<DataSourceName>Def_prescoConnectionString</DataSourceName>
</Query>

|||

It did help me! Thanks!

I had that same problem. I noticed in the Dataset designer you can add fields and parameters. Sometimes it fills them in automatically sometimes it doesn't.

Report running really really slowly!!

Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.

In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services? Any ideas of how to speed this process up?

probobaly becuase in access the data is cached locally.

|||How do you have your parameters structured? Are you building the sql string dynamically? If you give a few more details/examples, I might be able to point you in a direction with less overhead...|||

I wont list all my report parameters, but to show you what I am doing I will list the first few. I have parameters:

Publication, Division, Product Manager.

Parameter Publication is just bringing back a list of publications, divison is bringing back a list of divisions where publication is in paramter publication, i.e

="SELECT
division_code
FROM base_table
WHERE publication_code in ('
" & Parameters!par_pub.Value & "
') " &
"
GROUP BY 1
ORDER BY 1"

Product Manager is bringing back a list of product managers, where publication is in parameter publication and where division is in parameter division, ie

="SELECT
product_range_manager_code,
trim(product_range_manager_desc)as product_range_manager_desc
FROM base_table WHERE division_code IN ('
" & join(Parameters!par_div.Value," ','
") & "
') " &
"

AND publication_code in ('

" & Parameters!par_pub.Value & "

') " &

"
GROUP BY 1,2
ORDER BY 1,2"

Like I said this goes on, so the 4th parameter will contain 3 WHERE conditions until the 18th parameter contains 17 where conditions. My main dataset which populates my report table looks at each of the parameters and filters based on these values. Oh also as you probably have guessed all the processing is done on the server. Hope you can help.

|||First of all, I think that you will see some speedup if you make stored procedures. Depending on the database structure, and where the bottle-neck is that speedup could be considerable or not. It seemed that you were pulling all of your data from one base table? If that is the case, you could write one stored procedure that would populate each dropdown, with a where clause of the form:

Code Snippet

WHERE foo IN (@.param1) AND (@.param2 IS NULL OR bar IN (@.param2)) AND ...

You should also verify whether there are any dependencies in your parameters that you can exploit to reduce calls to the database. In other words check if selecting a particular parameter is the same as selecting another one downstream (1 to 1), in which case that second filter (and clause) does not need to be applied at all. Similarly, verify that you need the previous filters applied once you know the downstream selections. For example, if all publications are produced by only one division, then in the second query you supplied you do not need to filter for the divison code at all.

Since you are not aggregating any columns, the group by clause is unnecessary. The SQL compiler should have caught that, so eliminating it probably won't save you anything, but it never hurts to remove unnecessary bits.

Finally, if these steps still result in a report that takes too long to refresh, you have a couple of options. You can create a drill-through report, wherein you actually navigate to a child report with fewer parameters, and you can look at improving your database structure to optimize this sort of activity, e.g. via normalization and adding indexes.

I hope this helps.