Multiple DataSets w/multiple Lists controls

I understand I can have multiple DataSets in a single report. (Which I've
tried in a test report).
Maybe I'm missing something...but can't I use nested list controls to build
a hierarchical report?
We have
Organization -> Region -> Branch -> Office
Note that I am able to cobble up a report using nested sub reports. Is that
the way it's supposed to be done?
Kyle!Yes. Subreports is how you should do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:eBoUqs3RGHA.5728@.tk2msftngp13.phx.gbl...
>I understand I can have multiple DataSets in a single report. (Which I've
>tried in a test report).
> Maybe I'm missing something...but can't I use nested list controls to
> build a hierarchical report?
> We have
> Organization -> Region -> Branch -> Office
> Note that I am able to cobble up a report using nested sub reports. Is
> that the way it's supposed to be done?
> Kyle!
>

Multiple datasets in one report?

I am trying to create a report that runs four queries.
If I use:
=(Fields!LongValueName.Value, "MTReg") I get:
c:\documents and settings\me\my documents\visual studio
projects\registrations\regVsAct.rdl The value expression
for the textbox 'LongValueName' contains an error:
[BC30198] ')' expected.
If I change it to =(Fields!MTReg.LongValueName.Value)
I get this:
c:\documents and settings\me\my documents\visual studio
projects\registrations\regVsAct.rdl The value expression
for the textbox 'LongValueName' refers to the
field 'MTReg'. Report item expressions can only refer to
fields within the current data set scope or, if inside an
aggregate, the specified data set scope.
Is this possible or am I just messing up the syntax?Looks like you are trying to refer to the field outside of any data regions.
You can change the expression to =First(Fields!LongValueName.Value,
"MTReg").
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <bmurtha@.corp.sms.ac> wrote in message
news:363c01c471d2$063f5060$a401280a@.phx.gbl...
> I am trying to create a report that runs four queries.
> If I use:
> =(Fields!LongValueName.Value, "MTReg") I get:
> c:\documents and settings\me\my documents\visual studio
> projects\registrations\regVsAct.rdl The value expression
> for the textbox 'LongValueName' contains an error:
> [BC30198] ')' expected.
> If I change it to =(Fields!MTReg.LongValueName.Value)
> I get this:
> c:\documents and settings\me\my documents\visual studio
> projects\registrations\regVsAct.rdl The value expression
> for the textbox 'LongValueName' refers to the
> field 'MTReg'. Report item expressions can only refer to
> fields within the current data set scope or, if inside an
> aggregate, the specified data set scope.
> Is this possible or am I just messing up the syntax?
>|||Thanks Fang,
Actually, as it turned out, I was trying to call a
dataset , say b, when I was using a table that was bound
to databset a. I didn't get that what I'm really dealing
with is a datagrid and that it has a bound connections.
This is what happens when you start depending on the GUI
rather then thinking for yourself. Also it didn't occur to
me that iterators for each of these datasets would be
completely different.
Regards,
Bryan
>--Original Message--
>Looks like you are trying to refer to the field outside
of any data regions.
>You can change the expression to =First(Fields!
LongValueName.Value,
>"MTReg").
>Fang Wang (MSFT)
>Microsoft SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"bmurtha" <bmurtha@.corp.sms.ac> wrote in message
>news:363c01c471d2$063f5060$a401280a@.phx.gbl...
>> I am trying to create a report that runs four queries.
>> If I use:
>> =(Fields!LongValueName.Value, "MTReg") I get:
>> c:\documents and settings\me\my documents\visual studio
>> projects\registrations\regVsAct.rdl The value expression
>> for the textbox 'LongValueName' contains an error:
>> [BC30198] ')' expected.
>> If I change it to =(Fields!MTReg.LongValueName.Value)
>> I get this:
>> c:\documents and settings\me\my documents\visual studio
>> projects\registrations\regVsAct.rdl The value expression
>> for the textbox 'LongValueName' refers to the
>> field 'MTReg'. Report item expressions can only refer
to
>> fields within the current data set scope or, if inside
an
>> aggregate, the specified data set scope.
>> Is this possible or am I just messing up the syntax?
>>
>
>.
>

Multiple datasets in a single chart?

Is it possible to incorporate data from 2 datasets into a single chart? For example I have 1 set of data from 1 db which allows me to display datapoint "A" by week. I have a second dataset from another db which allows me to display datapoint "B" by week. I'd like to plot each of these separate datapoints on a single chart with the x axis being week and the y axis being a stacked bar chart incorporating both A and B values as individual data series. Further, assuming this is possible, is it then possible to have 1 series displayed as an area while the 2nd series is a column or line?
Thanks in advance!

Did you ever find a way to do this? In the middle of a Reporting Services 2005 evaluation and we are trying to reproduce this functionality.|||Sorry this is currently not supported. You would need to join the two result sets already in the dataset query to be able to use it in the same chart.

-- Robert

Multiple datasets in a single chart?

Is it possible to incorporate data from 2 datasets into a single chart? For example I have 1 set of data from 1 db which allows me to display datapoint "A" by week. I have a second dataset from another db which allows me to display datapoint "B" by week. I'd like to plot each of these separate datapoints on a single chart with the x axis being week and the y axis being a stacked bar chart incorporating both A and B values as individual data series. Further, assuming this is possible, is it then possible to have 1 series displayed as an area while the 2nd series is a column or line?
Thanks in advance!

Did you ever find a way to do this? In the middle of a Reporting Services 2005 evaluation and we are trying to reproduce this functionality.|||Sorry this is currently not supported. You would need to join the two result sets already in the dataset query to be able to use it in the same chart.

-- Robert

Multiple Datasets for a report

I was wondering if there is a way we can join two different datasets and use that as a result for a report. These two datasets would be a part of the same report, but say they are pulling data from two different databases and I want to perform a join on them. Is that possible?
maybe write a stored procedure inside one the the databasees that does the join using a openquery if it is a linked server, and that way when you pull in your dataset, you will only have to set the conection string up for the one database.|||If I am reading this right, you want to use data from two different datasources on one report.

One of your options is to have two datasets for the report, and you can drag and drop fields from eqach dataset onto the same report. You may want to have them both use the same parameter.

I hope this helps.|||You read it right, that is what I want. I have already tried the approach you have mentioned, the only problem being, I don't know how I would join the two datasets once I have them on the report. There is a field called, order id which I need to join the datasets on.|||

what is wrong with the approach joining the data from the database and bringing it in as a single dataset?

|||Amnogeek,

When you have multiple datasets on one report, you haev the option of selecting fields from each dataset, and dropping them on the report designer. When you are using multiple datasets, wether you are using stored procedures or SQL statements, you can have each dataset pulling information using the same criteria.

Salameh is also correct on just joining the two databases if there is a relation between them.

Again, I hope this helps somehow...|||There is nothing wrong with it, in fact it was my first thought also. The thing is there are way too many records to pull and it takes the join much longer on the server. That was why I was wondering if I could perform a join on the client after I get the data. That might speed the things up a little bit. But anyways, thanks for all your responses.|||

If there are too many records to join then there might be something wrong with your selection criteria - maybe not - but worth looking at again. As an alternative, why not use a stored procedure to extract the records that you need to temporary tables and then join them. This will give you fewer records to join. When you use temporary tables, its worth noting that often an update of a column is quicker than joining tables - this means that if you need to join tables just to populate a description column (for example) that its quicker to create the temporary table, populating the other columns and then populate the empty description column by means of an update. You need to play around with this approach in Query Analyser to see whats most efficient for the number of records that you have to process.

Hope this helps...

Multiple datasets embedded within each other..

I have a dilemma. I am creating an invoice report with a fairly
complicated hierarchy.
I have it working so far. All the invoice elements are doing fine
except the taxes. Because of business requirements, it is not possible
for me to get everything in a single dataset. My invoice may have 40
lines. Some of those lines are taxable and some are not. Taxes might
include 1 type of tax of 5 types of tax.
So i need to get the tax in a separate dataset. What I need to know is
if it is possible to embed one dataset inside another based on a
filter (the ID field used to link the taxes to a line for example).
I know I can reference certain fields in other datasets, but I have an
unknown number of tax lines I have to embed into my existing dataset
in the report. I couldn't find any information on this so if osmeone
can point me in the right direction or flat out tell me its not
possible I would appreciate it.
Thanks.What you want is to embed a subreport in a cell of the report table (or onto
your list control). This subreport can have just a single field if you want.
Have the subreport parameters (remember a subreport is just a regular
report) be whatever values you need to calculate the tax. Test the report
standalone and then drag and drop the report into the cell of the report
control (if that is what you are using). Then do a right mouse click on the
subreport and map the parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ" <resonantblue@.gmail.com> wrote in message
news:1172171435.536157.39690@.v45g2000cwv.googlegroups.com...
>I have a dilemma. I am creating an invoice report with a fairly
> complicated hierarchy.
> I have it working so far. All the invoice elements are doing fine
> except the taxes. Because of business requirements, it is not possible
> for me to get everything in a single dataset. My invoice may have 40
> lines. Some of those lines are taxable and some are not. Taxes might
> include 1 type of tax of 5 types of tax.
> So i need to get the tax in a separate dataset. What I need to know is
> if it is possible to embed one dataset inside another based on a
> filter (the ID field used to link the taxes to a line for example).
> I know I can reference certain fields in other datasets, but I have an
> unknown number of tax lines I have to embed into my existing dataset
> in the report. I couldn't find any information on this so if osmeone
> can point me in the right direction or flat out tell me its not
> possible I would appreciate it.
> Thanks.
>

Multiple Datasets but needs to be tied together

I am needing to take data from one data set that consists of a list of parts and their stock information. Then I am needing to show any transactions that the individual parts may have had during the time that user defined through the filters.

This requires me to have two data sets since I can't do a join between the stocking information and the transactions due to the fact that it is very possible that every part won't have a transaction for each month of each year. Doing a left join still filters down the data too much so thus the need for two data sets.

I have these parameters:

Vendor, Year, Month, Type of request

Vendor and Type of Request is handled through StockInfoDataset. I then need to update the table showing 0 or transaction amount from TransactionDataset filtered by the ItemNumber and WarehouseCode that is in StockInfoDataset. Since it seems List and Table only allow one dataset in them, I am at a loss as to how I would go about doing this.

Explaining this is quite difficult in a forum post, but hopefully someone would be able to shine some light on possibly avenues to follow here....thank you in advance.

Josh

I'm not sure I understand 100% what you're facing, but here's how I would try to tackle this...

Each parameter can have a separate dataset, for lookup purposes.
Then the main dataset can be based on the selected report parameters.
Finally, I would use either a view or a stored procedure to return the needed dataset.
Using the view/sp provides a lot of flexibility that direct table queries do not, including the ability to join, summarize, and otherwise process values from multiple tables.

I hope that helps.

|||

Your solution may very well work, but I have never done it that way so I am a bit at a loss.....

Unfortunately, unless there is something I am missing, I can't use a view or stored procedure to derive this information....here is an example of what I am wanting to do

Vendor = ABC, Year = 2006, Month = 07, Type = Qty On Hand > 0

Table A

[Column AA] [Column AB] [Column AC]

Item1 Whs1 QtyOnHand1

Item2 Whs1 QtyOnHand2

Item3 Whs2 QtyOnHand3

Item4 Whs1 QtyOnHand0

Table B

[Columnb BA] [ColumnBB] [Column BC] [ColumnBD]

Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 07/01/06 QtySold2

Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 06/01/06 QtySold4

Item4 Whs1 07/01/06 QtySold5

So when I do

SELECT * FROM [TableA] LEFT OUTER JOIN [TableB] ON [TableA].[Column AA] = [TableB].[Column BA] AND [TableA].[Column AB] = [TableB].[Column BB] WHERE [TableA].[QtyOnHand] > 0

I am going to get a result set of

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 QtySold1

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 QtySold4

This is essentially what my View Does that I am selecting from....

Now in my report I have them select year and month......to filter what transactions are going to be summed.

My problem is that I still need to show the qtyOnhand and such even though there my be no transactions for the time period they select....so using the parameters above. This is what I get then after that I'll show what I want...

This is what I get:

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

This is what I want:

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 0

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 0

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 0

I hope this clarifies things a bit....

Thank you,

Josh

|||

It seems like it's possibly to query what you need and get it in 1 dataset, then maybe doing some filtering or special exclusions, but first,

can you please give your relevant tables and their column names? I know you were trying to be discrete, but COLUMNAA, COLUMNAB is hard to put into motion. After that, I'm sure someone can get you a query that will do the job (full outer join might be necessary).

Multiple Datasets but needs to be tied together

I am needing to take data from one data set that consists of a list of parts and their stock information. Then I am needing to show any transactions that the individual parts may have had during the time that user defined through the filters.

This requires me to have two data sets since I can't do a join between the stocking information and the transactions due to the fact that it is very possible that every part won't have a transaction for each month of each year. Doing a left join still filters down the data too much so thus the need for two data sets.

I have these parameters:

Vendor, Year, Month, Type of request

Vendor and Type of Request is handled through StockInfoDataset. I then need to update the table showing 0 or transaction amount from TransactionDataset filtered by the ItemNumber and WarehouseCode that is in StockInfoDataset. Since it seems List and Table only allow one dataset in them, I am at a loss as to how I would go about doing this.

Explaining this is quite difficult in a forum post, but hopefully someone would be able to shine some light on possibly avenues to follow here....thank you in advance.

Josh

I'm not sure I understand 100% what you're facing, but here's how I would try to tackle this...

Each parameter can have a separate dataset, for lookup purposes.
Then the main dataset can be based on the selected report parameters.
Finally, I would use either a view or a stored procedure to return the needed dataset.
Using the view/sp provides a lot of flexibility that direct table queries do not, including the ability to join, summarize, and otherwise process values from multiple tables.

I hope that helps.

|||

Your solution may very well work, but I have never done it that way so I am a bit at a loss.....

Unfortunately, unless there is something I am missing, I can't use a view or stored procedure to derive this information....here is an example of what I am wanting to do

Vendor = ABC, Year = 2006, Month = 07, Type = Qty On Hand > 0

Table A

[Column AA] [Column AB] [Column AC]

Item1 Whs1 QtyOnHand1

Item2 Whs1 QtyOnHand2

Item3 Whs2 QtyOnHand3

Item4 Whs1 QtyOnHand0

Table B

[Columnb BA] [ColumnBB] [Column BC] [ColumnBD]

Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 07/01/06 QtySold2

Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 06/01/06 QtySold4

Item4 Whs1 07/01/06 QtySold5

So when I do

SELECT * FROM [TableA] LEFT OUTER JOIN [TableB] ON [TableA].[Column AA] = [TableB].[Column BA] AND [TableA].[Column AB] = [TableB].[Column BB] WHERE [TableA].[QtyOnHand] > 0

I am going to get a result set of

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 QtySold1

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 QtySold4

This is essentially what my View Does that I am selecting from....

Now in my report I have them select year and month......to filter what transactions are going to be summed.

My problem is that I still need to show the qtyOnhand and such even though there my be no transactions for the time period they select....so using the parameters above. This is what I get then after that I'll show what I want...

This is what I get:

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

This is what I want:

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 0

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 0

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 0

I hope this clarifies things a bit....

Thank you,

Josh

|||

It seems like it's possibly to query what you need and get it in 1 dataset, then maybe doing some filtering or special exclusions, but first,

can you please give your relevant tables and their column names? I know you were trying to be discrete, but COLUMNAA, COLUMNAB is hard to put into motion. After that, I'm sure someone can get you a query that will do the job (full outer join might be necessary).

Multiple Datasets and Report Design problems

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
How do I accomplish this?
Thanks in advance.
PamHi Pam,
I'm not sure how your databases are set up (are they on the same
server?), but I would probably choose to combine the data from the
database instead of combining it at the report level - that way you
only need 1 report table and no sub reports and grouping/toggling the
data will be a piece of cake:
SELECT * from User INNER JOIN DatabaseB.dbo.CallLogs CallLogs ON
User.username = CallLogs.username ORDER BY Department, UserName
I hope this helps.
Take Care!
Michelle

Multiple Datasets and Report Design confusion

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
Any ideas on how to accomplish this scenario?
Thanks in advance.If both these databases are in the same SQL Server then it is a piece of
cake to do this in a Stored Procedure, just join the two tables. If they are
in different servers it is a little more difficult, you would need to use
linked servers. Same strategy though, you need to look at creating a stored
procedure. Based on your description it does seem to me that a subreport
will not work for you.
Oh, another idea. You don't even need a stored procedure if they are on the
same server, use the generic query designer and create the sql:
select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
b.somedatefield > @.startdate and b.somedatefield < @.enddate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23v$M9Z7AFHA.2112@.TK2MSFTNGP09.phx.gbl...
> I have a table in Database A with users and a table in Database B with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally started
> with a subreport to print the call detail for a user. Unfortunately, I
> need to be able to total and avg call detail for each user, dept which I
> believe must happen in the main report. By using a sub report I don?t
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||I believe that your best bet would be to either have some process that
creates an intermediary resultant table combining the abstract data
relationship between the two tables, or design your stored procedure so
that you are joining the two together.
As a design key I always try to leave the real work to the best of
breed. In this case you are going to be better off doing your real data
work in the database layer(SQL Server), not in the application layer
(Reporting Services).
-Brian
pb wrote:
> I have a table in Database A with users and a table in Database B
with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally
started
> with a subreport to print the call detail for a user. Unfortunately,
I
> need to be able to total and avg call detail for each user, dept
which I
> believe must happen in the main report. By using a sub report I
don't
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||THanks Brian, Bruce and Michelle. I have chosen to link my servers (data
on two different servers). I created some views and stored procedures.
All is woking great. Thanks for the direction!
Bruce L-C [MVP] wrote:
> If both these databases are in the same SQL Server then it is a piece of
> cake to do this in a Stored Procedure, just join the two tables. If they are
> in different servers it is a little more difficult, you would need to use
> linked servers. Same strategy though, you need to look at creating a stored
> procedure. Based on your description it does seem to me that a subreport
> will not work for you.
> Oh, another idea. You don't even need a stored procedure if they are on the
> same server, use the generic query designer and create the sql:
> select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
> innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
> b.somedatefield > @.startdate and b.somedatefield < @.enddate
>

multiple datasets

Is it possible to have more than one dataset in a report?

I'm trying to create a parameter that draws its drop down box values from a query that is seperate from the result set.

Thanks

Yes, you can have multiple DataSets in reports. You can set up a DatsSet to be the source for valid parameter values like you describe.

-Chris

multiple datasets

I have a report that I have created with multiple subreports and datasets. There should be a better solution so I am asking the question here.

The report should display like this:

Object Object Title Budget Curr-Spent Ytd-Spent Ytd-Encum Post-Ytd Balance % Remaining 4100 EMPLOYEES $380.00 $250.93 $343.67 $0.00 $0.00 $61.33 15.94% 4102 EMPLOYEES - TEMPORARY $149.00 $110.75 $139.70 $0.00 $0.00 $47.30 30.16% 4201 EMPLOYEE BENEFITS $138.00 $73.16 $12.60 $0.00 $0.00 $32.40 25.28%

Each column represents another dataset.

Each column right now is a subreport so I can use the object as a parameter to the subreports dataset. Each column needs to have the object as a parameter.

Is there a way I can pass a field from the first dataset as a parameter for the next dataset without using subreports?

Why can't you create a SQL query which puts every thing together in one dataset and use that dataset in a table. Looks like you are using related information anyways so why not do everything in SQL and then use table for it.

Thanks,

-Rohit

|||

I will need to run each query with the object as a parameter. I guess I can do this with a table variable or a cursor in the sql but there has got to be a better way.

Here is the main dataset

SELECT DISTINCT sum_object AS object, object_desc

FROMfin_detail WITH (NOLOCK)

JOIN bud_objectWITH (NOLOCK)

ON FIN_DETAIL.SUM_OBJECT = bud_object.OBJECT_CODE

WHERE pca = @.pca

AND budget_year = @.budgetyear

AND sum_object <> '4100'

UNION

SELECT DISTINCT sub_obj AS object, object_desc

FROM fin_detailWITH (NOLOCK)

JOIN bud_objectWITH (NOLOCK)

ON FIN_DETAIL.SUB_OBJ = bud_object.OBJECT_CODE

WHERE pca = @.pca

AND budget_year = @.budgetyear

AND exp_sum_object= ‘4100’

Here is the dataset I need to call for each main dataset

IF substring(@.object,1,2)='41'

BEGIN

--starts with 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = '4100'

And sub_obj = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

ELSE

BEGIN

--not 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

Basically, I need to get all the sum_object in the first query if the sum_object <> 4100 and the sub_object if the sum_object = 4100.

Then I need to call the second dataset to sum the amounts with the @.object from the first dataset as the parameter.

The only way I figured out how to do this was so use subreports instead of a loop in sql.

BTW, this second dataset is one of 5 that I need to call for each object in the first query.

|||

I figured out how to do what I needed from reading this article http://www.code-magazine.com/articleprint.aspx?quickid=0701061&page=2

I created this function

Function GetBudget(ByVal myobject As String, ByVal pca As String, ByVal budgetyear As String) As Integer
Dim oConn As New System.Data.SqlClient.SqlConnection
oConn.ConnectionString =

oConn.Open()
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn

oCmd.CommandText =

IF substring(@.object,1,2)='41'

BEGIN

--starts with 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = '4100'

And sub_obj = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

ELSE

BEGIN

--not 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

oCmd.Parameters.AddWithValue("@.sumobject", myobject)
oCmd.Parameters.AddWithValue("@.pca", pca)
oCmd.Parameters.AddWithValue("@.budgetyear", budgetyear)

Dim Budget As Integer = oCmd.ExecuteScalar()
oConn.Close()
Return Budget
End Function

I then called called the code in my textbox like this

=Code.GetBudget(Fields!object.Value,Parameters!pca.Value,Parameters!BudgetYear.Value).

For every row this function is called and returns my budget value because the Fields collection contains the object record from my main report dataset.

I did have problems placing the query on multiple lines even with the normal VB string concatination techniques so I put it all on one line in the XML.

This is the first time that I saw an example of querying a database from custom code in SSRS.

Multiple Datasets

I am trying to write an invoice report for. It will include details of both
companies involved and then list worked hours and expenses on a per project
basis. I need the worked hours to display in a table under each project and
the expenses in a seperate table under each project.
I have tried what seemed to be a simple way... Created on dataset for the
address info of both companies and then a second dataset which contains the
the expeneses and worked hours per project and then created a list for
containing the project name and 2 tables for holding the hours worked and
expenses repectively. I then used filters on each table to display only the
row types it was concerned, expense or hours. Doing this made it so the
parent list failed to render the project name for projects beyond the first.
A second method I though would be to have each table have it's own dataset
and then somehow filter it according to the parent data regions current
project, but can't figure out how to create the join between the 2 datasets.
Any help would be appreciate including maybe a totally different approach to
this problem.
-stanRead up on subreports. A subreport is just a normal report with parameters.
First get the report working with parameters. Then embed it, right click on
the report and set the parameter to the field you want it mapped to in the
main report. Very easy once you get the hang of it.
Bruce L-C
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:OmVGkGwgEHA.216@.tk2msftngp13.phx.gbl...
> I am trying to write an invoice report for. It will include details of
both
> companies involved and then list worked hours and expenses on a per
project
> basis. I need the worked hours to display in a table under each project
and
> the expenses in a seperate table under each project.
> I have tried what seemed to be a simple way... Created on dataset for the
> address info of both companies and then a second dataset which contains
the
> the expeneses and worked hours per project and then created a list for
> containing the project name and 2 tables for holding the hours worked and
> expenses repectively. I then used filters on each table to display only
the
> row types it was concerned, expense or hours. Doing this made it so the
> parent list failed to render the project name for projects beyond the
first.
> A second method I though would be to have each table have it's own dataset
> and then somehow filter it according to the parent data regions current
> project, but can't figure out how to create the join between the 2
datasets.
> Any help would be appreciate including maybe a totally different approach
to
> this problem.
> -stan
>|||One other note. Filters bring over all the data and then filters it. If you
have a lot of data then this will be very very slow. It will particularly
kill you if you are developing off of a subset of the data and then you go
live with the real stuff which is exponentially bigger.
Bruce L-C
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:OmVGkGwgEHA.216@.tk2msftngp13.phx.gbl...
> I am trying to write an invoice report for. It will include details of
both
> companies involved and then list worked hours and expenses on a per
project
> basis. I need the worked hours to display in a table under each project
and
> the expenses in a seperate table under each project.
> I have tried what seemed to be a simple way... Created on dataset for the
> address info of both companies and then a second dataset which contains
the
> the expeneses and worked hours per project and then created a list for
> containing the project name and 2 tables for holding the hours worked and
> expenses repectively. I then used filters on each table to display only
the
> row types it was concerned, expense or hours. Doing this made it so the
> parent list failed to render the project name for projects beyond the
first.
> A second method I though would be to have each table have it's own dataset
> and then somehow filter it according to the parent data regions current
> project, but can't figure out how to create the join between the 2
datasets.
> Any help would be appreciate including maybe a totally different approach
to
> this problem.
> -stan
>

Multiple DataSets

Can a single report (same layout) have multiple MATRIXES with all referring
to either SAME or SEPARATE tables with different conditions mentioned for
each matrix?
ThanksYes, you can do that. You can drop the matrixes with different datasets
directly on the report body. Matrixes inside a list need to either have the
same dataset as the list; or (if they need to have a different dataset) put
them inside a subreport on the list.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:70420480-8D78-426F-824B-BCA6F533F01B@.microsoft.com...
> Can a single report (same layout) have multiple MATRIXES with all
> referring
> to either SAME or SEPARATE tables with different conditions mentioned for
> each matrix?
> Thanks
>

Multiple Datasets

I have created several reports that use multiple datasets. I have
found two errors and I need help.
First, is there a way to define the order in which the datasets are
executed? In some of my reports, the first dataset populates a table
that the remaining datasets query from. However, when I run the
report, it is obvious that it executes some of the datasets that query
from the table BEFORE it runs the dataset that actually populates the
table.
Second, it seems like when my users run a report with multiple datasets
from the front-end, it only runs the one of the datasets and uses the
cashed results for the remaining datasets. For example, I have a
report that counts calls and mail received per a certain account. The
account is a parameter that the user selects. If they run the report
for Account A, then dataset 1 returns a value of 500, dataset B returns
a value of 250 and dataset C returns a value of 100. Then, when the
user runs the report for Account B, dataset 1 returns a value of 999
(which is the correct result), but dataset 2 returns a value of 500
(the result for Account A, not Account B) and dataset 3 returns a value
of 100 (the result for Account A, not Account B). The only way to get
the report to execute all three datasets, is by running the report
once, then hitting the refresh button. Anyone else have this problem?
I would appreciate any responses.
Thanks!You can not count on order of execution. RS is not setup to have
dependencies between datasets like you are trying to do. If that is what you
want then you should create subreports and use them instead. Everything
should work exactly as you want if you do that.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronni" <rlnjones@.yahoo.com> wrote in message
news:1106243121.671668.240810@.c13g2000cwb.googlegroups.com...
> I have created several reports that use multiple datasets. I have
> found two errors and I need help.
> First, is there a way to define the order in which the datasets are
> executed? In some of my reports, the first dataset populates a table
> that the remaining datasets query from. However, when I run the
> report, it is obvious that it executes some of the datasets that query
> from the table BEFORE it runs the dataset that actually populates the
> table.
> Second, it seems like when my users run a report with multiple datasets
> from the front-end, it only runs the one of the datasets and uses the
> cashed results for the remaining datasets. For example, I have a
> report that counts calls and mail received per a certain account. The
> account is a parameter that the user selects. If they run the report
> for Account A, then dataset 1 returns a value of 500, dataset B returns
> a value of 250 and dataset C returns a value of 100. Then, when the
> user runs the report for Account B, dataset 1 returns a value of 999
> (which is the correct result), but dataset 2 returns a value of 500
> (the result for Account A, not Account B) and dataset 3 returns a value
> of 100 (the result for Account A, not Account B). The only way to get
> the report to execute all three datasets, is by running the report
> once, then hitting the refresh button. Anyone else have this problem?
> I would appreciate any responses.
> Thanks!
>

Multiple datasets

I have an area of my report that needs multiple data sets to be used to
populate the area. I keep getting the error "Report item expressions can only
refer to fields wtihin the current data set scope". Does anyone know how to
make the area allow me to use one data set in one row and one in data set in
the other? I have tried a table within a table, a list within a table....I
cannot find the fix to this problem. Please help!Have a look at subreports.
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:C6D864D3-BED0-48D2-BB39-C4C642A2B9C0@.microsoft.com...
> I have an area of my report that needs multiple data sets to be used to
> populate the area. I keep getting the error "Report item expressions can
only
> refer to fields wtihin the current data set scope". Does anyone know how
to
> make the area allow me to use one data set in one row and one in data set
in
> the other? I have tried a table within a table, a list within a
table....I
> cannot find the fix to this problem. Please help!|||Thank you. I will try.
"AshVsAOD" wrote:
> Have a look at subreports.
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:C6D864D3-BED0-48D2-BB39-C4C642A2B9C0@.microsoft.com...
> > I have an area of my report that needs multiple data sets to be used to
> > populate the area. I keep getting the error "Report item expressions can
> only
> > refer to fields wtihin the current data set scope". Does anyone know how
> to
> > make the area allow me to use one data set in one row and one in data set
> in
> > the other? I have tried a table within a table, a list within a
> table....I
> > cannot find the fix to this problem. Please help!
>
>

Multiple Datasets

I have two datasets.
I have a list with multiple tables in it to break out information.
There are two tables that I need to populate. They are using dataset 2.
THe rest of the tables are using dataset1.
How do I link the data that is pulled in dataset 1, to dataset 2?
Both datasets have the account number field in it. And when the primary
table 1 pulls the account number, I want dataset 2 to display the references
for the account number that is displayed in dataset 1?
ThankOn Jun 4, 3:36 pm, Susan R <Sus...@.discussions.microsoft.com> wrote:
> I have two datasets.
> I have a list with multiple tables in it to break out information.
> There are two tables that I need to populate. They are using dataset 2.
> THe rest of the tables are using dataset1.
> How do I link the data that is pulled in dataset 1, to dataset 2?
> Both datasets have the account number field in it. And when the primary
> table 1 pulls the account number, I want dataset 2 to display the references
> for the account number that is displayed in dataset 1?
> Thank
You should be able to achieve this via drill-through, where you pass
the account number from the main report to the drilled-through one.
Another way of accomplishing this is to use the dataset that includes
the account number as a report parameter and then reference the
parameter selected via the query/stored procedure that sources the
other table control. Assuming that the report parameter is called
AccountNum (that is linked to one dataset), you could use something
like this:
select ... from References where AccountNumber = Parameters!
AccountNum.Value
Also, you could try using Jump to URL/Report (as part of the
Navigation property of a table control, etc).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

multiple dataset fields in one table

How to use multiple dataset fields in one table.

Example:

I have one table --Table1.

Two DataSets --DataSet1,DataSet2

Table1 refers DataSet1.I want to use DataSet2 field in Table1.It is taking SUM if did this but I don't need sum.

A table can only be associated with 1 dataset.|||

thanx Adam.

Can you give me the solution

|||

It depends on the data and what else you are doing in the report. What I've done in the past is either

combine the data into 1 dataset in the query or|||

if you only want to use a SUM-Value from your second dataset,

this shouldnt be a problem

go to the field in your table where you want the SUM-Value

-> Expression -> Datasets -> <Dataset2> -> Sum(<yourValueToSum>)

this inserts a Sum-Function for your value over the scope of dataset 2

greets

|||

you could do something like this

=Sum(Fields!fieldnam.Value, "Dataset Name")

and it would get the value from the specified dataset

Multiple dataset calculation

I have 3 tables pulling data from 3 different datasets. In my 3rd table i need to sum the table1-column2 to table2-column3.

e.g.

=Fields!Column2.Value,"Dataset1"+(Fields!Column3.Value, "Dataset2")

This does not work.

Please help.

You cannot do cross-dataset operations except at an aggregate level, i.e.

=Fields!Column2.Value+Sum(Fields!Column3.Value, "Dataset2")