Multiple Datasets

May 31, 2007

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?

View 4 Replies


ADVERTISEMENT

Grouping Multiple Datasets

Mar 28, 2008

I have multiple different data regions on one report body. I need to be able to arrange the a few of the data regions so that they are grouped together and print together on same page. I've tried using a list around the data regions that I want to group together, but the list data region needs to have a data set specified and it only allows one to be specified.
Has anyone tackled this before?

View 5 Replies View Related

Multiple Datasets But Needs To Be Tied Together

Aug 29, 2006

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

View 3 Replies View Related

Sum Across Multiple Datasets/tables

Apr 4, 2008

I have three datasets that are currently displaying data in three side-by-side-by-side tables. This gives the appearance of one large table were all rows match up nicely. Now, I need to somehow calculate the sum of those rows values across and display that value as a calculated sum at the end of each row. How can I access the values of each column from those three different tables in order to be able to add them together for each detail row. Or is this just a dream? Thanks in advance....Using SQL Server 2005. The backend queries that create those datasets are MDX and trying to manipulate those to get one dataset back will be a pain. That's why, I am hoping that there is a front end solution to this.

View 1 Replies View Related

SSRS / Add Totals From Multiple Datasets?

Nov 17, 2014

I have 3 tables pointing to 3 different datasets on my report.

How Can I take the totals from Each Table(Dataset) and add them together for a Grand Total?

View 3 Replies View Related

Locale Issues With Multiple Datasets

Nov 26, 2007



My company is implementing a multilingual solution for our application, so naturally the reports must follow. Every report I create has 2 datasets. It seems to me that the first dataset translates appropriately based on the browser's language setting, but the second dataset disregards the locale and just displays things in en-US. I've even created a sample report that accesses the exact same columns from the exact same view and dataset1 returns the correct language, but dataset2 returns en-US.



The report's language is set to =iif( User!Language = "fr-CA", "fr-CA", "en-US"), but I've also tried just setting it to = User!Language or leaving it at Default just to see if that would work.

Has anyone come across this before and been able to find a work-around?

Thanks!

mmat

View 2 Replies View Related

Multiple Datasets Are Not Processing Correctly...

Apr 15, 2008

Hello, I'm at a loss with this problem....

I have written many reports with multiple datasets but today I have something bizarre happening. I have three stored procedures (datasets) in this report. This is a rolling twelve month report that looks at three numbers (restraints, census, and percent of census) by month and year...pretty simple. The first data set is for the graph for all twelve months. The second and third data sets are broken out by MonthNum in matrix tables to show the first and then second six months in separate tables for displaying purposes...so that that they don't have to scroll far over to the right since I'm printing all the three values per month.

This should be no problem. What's happening is that when I refresh the report not all three datasets are displaying properly. Some times the data in the graph shows and some times it doesn't. Another odd thing is that in my matrix tables the restraint number and then percent values sometimes double or sometimes they are 0. What makes it even more strange is the census number is always there and is always accurate.

All the aggregates are calculated in the T-SQL stored procedure. So, all I'm doing in RS is displaying the values. It almost appears to be cycling through the datasets when I refresh the report. Other than the fact that the census value is always there.

I rebuilt the report with only the graph...no problem. Once I add another data set, every other time I refresh the report the graph has no data. I can't imagine what I'm missing as I've done this numerous times.

Has anyone come across anything like this?

Thanks!! Jamie

View 3 Replies View Related

Multiple Datasets In A Single Chart?

Aug 20, 2005

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?

View 9 Replies View Related

Grouping On Multiple Datasets - ANY Suggestions

Mar 22, 2007

I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:

DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92


DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92

Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:

Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190

or

Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190


Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%


I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:

= count((Fields!sale_number.Value,"DS2"))

This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.

If I try something fancier such as:

=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))

The report won't even run.

I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).

I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.

Thank you,

cmk8895

View 1 Replies View Related

Working With Multiple Datasets And Connection Strings

May 8, 2008

Hey guys,

I am pretty new to reporting software and I was just wondering in reporting services 2005 when working with multiple datasets and data connection string in order to populate text boxes if the data is a number it is prefixed with SUM at the start of the expression and if it is text it is prefixed with FIRST even though it is just for one value.

I was wondering if this is normal or have I messed up somewhere?

Many thanks.

View 1 Replies View Related

Exporting Datasets Into Multiple Sheets In Excel

Jan 23, 2008

Hi,

I had a procedure which returns 5 result sets. Now i want to export 3 results sets in 1 excel sheet and the remaining 2 resultsets in another excel sheet. Can we do this in reporting services.

ThanksDinesh

View 6 Replies View Related

Matrix Reports With References To Multiple Datasets

Oct 4, 2007

Hello and thank you for the help in advance.

I know this has to be possible maybe I am just missing somthing.

I am creating a matrix report which will compare year by year quotes to orders The issue is quotes and orders each have their own dataset. I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables? If not possible why does it allow you to name the dataset in the expression?

Thanks, Leo

View 1 Replies View Related

Payslip Report With Multiple Tables/datasets

Jan 14, 2008

A report I am currently working on is a payslip report.
This report has multiple tables on the page, each linked to their own specific dataset.
Eg I have Taxable Allowances table linked to a dataset that returns information relating to any taxable allowances for an employee, then Non-Taxable allowances, Deductions and so on...

Taxable allowance dataset example

SELECT
TA.TaxableTrDesc,
TA.TaxableTrText,
TA.TaxableSubQty,
TA.TaxableTrRate,
TA.TaxableTrFact,
TA.TaxableSubTtl
FROM
Employee E
INNER JOIN TransCurrentMaster TCM ON E.EmployeeCode = TCM.EmployeeCode
CROSS APPLY udfReportTaxableAllowances(E.EmployeeCode, TCM.PaySequence, ) TA
WHERE
E.EmployeeCode IN (@EmployeeCodeParameter)
AND TCM.PaySequence IN (@PaySequenceParameter)

The report works fine when you select one employee.
Currently when you try to select more than one employee, all the employee results are displayed in the tables, for example, Taxable Allowance table contains all taxable allowances records for all employees on the one page...
I'm not sure how I can amend this report so that it is able to be run for multiple employee records...
Any ideas, thoughts, feedback would be much appreciated..

Cheers

View 4 Replies View Related

Reporting Services :: Using Multiple Datasets In A Table - Outer Join In SSRS

Jun 17, 2015

I want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:

I want to display a ssrs table like:

Both the datasets are coming from different sources. So I cannot integrate them at sql query level.

View 4 Replies View Related

Reporting Services :: Count Of Lookup Expression Column In Report With Multiple Datasets?

Sep 27, 2015

My report has two data sets that hold inventory from two different departments.    

ds_DeptA and ds_DeptB

I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine.  Typically means DeptB does not have the record yet.   I need to count these empty (null) feilds and populate it in a Text box outside of the table.
 
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset. 

table: ds_DeptA
fields:
ID
Name 
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")

View 3 Replies View Related

Reporting Services :: SSRS 2008 R2 / Grouping Multiple Tables (tablix) With Different Datasets

Mar 27, 2012

We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.

If we run the report passing only a single value for the grouped parameter then the report works perfectly.  What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between.  Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:

Table 1:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
....

Table 3:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...

But we want it to render like this:

Table 1:
Detail rows for Group Param Value 1...
Table 2:
Detail rows for Group Param Value 1...
Table 3:
Detail rows for Group Param Value 1...

[code]....

The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.The report must export cleanly to excel and currently does for the single value passed.

View 5 Replies View Related

SQL Server 2012 :: Create XML File From AS400 Stored Procedure Returning Multiple Datasets

Oct 3, 2014

I have a store procedure in MC400 which I can call from SSMS using the below command:

EXEC ('CALL GETENROLLMENT() ')At serverName

Now this command returns two data sets like:

HA HB HC HD HE
1112
112571ABC14
113574ABC16
114577ABC87
DADBDCDD
1115566VG02
1115566VG02
1115566VG02

I want to generate two different XML files from these two datasets.Is there any way this can be achieved in SSIS or t-sql ?

View 3 Replies View Related

Multiple Datasets In One Table (not The SQL Table, But The Control)

Dec 7, 2007

Hey Everyone,



I am trying to create a table that will list data for me. The problem that I have is that my data has been separated across multiple stored procedures. The way I want to display the items are as follows:



title1 title2 title3 title4 title5 title6 title7

item1 item2 item3a item4 item5 item6a item7

item3b item6b

item3c item6c



As you can see, items3 & 6 contain significantly more rows than the others. In order to prevent duplicating items1, 2, 4, 5, and 7, I had to split the statement up into 3 stored procedures; all three procedures take parameters, item 3 and 6 taking item1 as a parameter. How will I display data like that in a report? In the design view, I am only able to use 1 dataset, let alone link them up together.



If this cannot be done, is there a query that I can perform that will omit repeated items? Basically, it will look exactly like about. I thought it was the GROUP BY, but I was way off.

View 9 Replies View Related

Where Did The Datasets Go In .NET 2.0???

Jul 5, 2006

I typically use  DataAdapters and Datasets from the toolbox when connecting to a SQL database.  Since switching to VS 2005, I am not able to find (or add) these items to the tool box for web site development.
   Any ideas on how I can get them back?  Or is there a better alternative to retrieving the data so I can process it?  Any help or suggestions would be appreciated.
 
Thank you

View 1 Replies View Related

About Datasets

Apr 19, 2007

Can anyone advise me on how to view the contents of a dataset in VS dotnet 2003..

I had filled a dataset from a query...But i don know what happens after filling the qry results.It might be helpful if someone tells me to view the contents of the dataset

View 1 Replies View Related

Ado Datasets

Jun 6, 2007

hi

i am using older ado datasets in a borland 6 program.

i need to retrieve the data per record count....



example is that i need all 1000 records but i only want to retrieve them 100 at a time....

how will i accomplish this?

View 1 Replies View Related

Datasets And SQL Injection

Oct 25, 2007

I have become a big fan of the datasets in Visual Studio 2005.  I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention?  I have read that using stored procedures is a good method for prevention.  Should I be using SP rather than SQL within my methods in the data table?

View 5 Replies View Related

Working With Datasets

May 3, 2007

Hi,

Is there any possibility of combining data from two datasets to a single report item. If there is any thing that we can do, can any one please tell me what to do, so that my problem will be solved.

Thanks

Rajeev

View 1 Replies View Related

Large Datasets

Mar 27, 2008

Hi,

I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.

What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?

Thanks

Gemma

View 5 Replies View Related

Datasets And Iff Statements

Sep 6, 2007



Hello experts,

I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...





Code Snippet
=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",
iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",
iif(Left(Fields!TestName.Value,6)="Search","Search Menu",
iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",
iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",
iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))






Not sure where I put the dataset name in this.

Thanks,
C

View 1 Replies View Related

Joining Two DataSets

Jan 2, 2007

How do you join two datasets in reporting services... one from sql and another from oracle?

Thanks

View 2 Replies View Related

Cached Datasets?

Aug 8, 2007

Hello all,
I have a report with a table and a chart. It uses dataset1 as the data source.
All works fine.
I create a new dataset called dataset2.
The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1)
I change the datasetName property of both the table and the chart to use dataset2.
When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?

regards,
Bill

View 9 Replies View Related

Relationship Between Two Datasets

Apr 29, 2008

If I have a dataset that has ID and CODE, and another dataset that has CODE and DESCRIPTION, is it possible to relate these two datasets to one another so I put the ID and Code in columns 1 and 2 and then put the related DESCRIPTION in column 3 on the report table layout? In other words, can you "JOIN" two datasets, or must all that be done in the query and create a unified dataset for the report contents?

View 6 Replies View Related

Joining Two Datasets?

Mar 8, 2007

Hi All,

I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).

A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.

I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?



Kind regards.

View 6 Replies View Related

Connect To SAS Datasets

May 4, 2006

Is it possible to connect to SAS dataset using MS SQL Server 2005? If yes, then please explain how do I access SAS data source.

View 1 Replies View Related

Using Two Datasets In A Report

Feb 25, 2008

hello everyone!

i have a report in which i use two datasets
the problem is that when i was using only one, there wasn't any scope argument you know, to tell in which dataset i am , because there was only one and all fields and cells in my table returned me the correct values

but as soon as i add the second data set and i drag and drop the datasets fields in my table

instead of having for instance



Code Snippet

=Fields!Consultant.Value






or




Code Snippet=(Fields!Consultant.Value, "dataset1")





it automtically applies the function "first"




Code Snippet=First(Fields!Consultant.Value, "dataset1")





and i don't understand why

please can you give me precious help on this

because it returns me the name of the first consultant only and if i deleter the function first in the expression, the report doens't work anymore

View 3 Replies View Related

2 Datasets In One Report?

Nov 29, 2007

Dear Experts,

I have a problem regarding my report.

Can we use 2 datasets in one report (tabular or chart, either one)?

my report get its data-fed from OLAP database (Cubes) and Oracle tables.

Let say the cube contains the actual sale (all aggregated and query using MDX) and the Oracle table contains the budget value for specific period.

And I am trying to construct a tabular like this:

date item type Revenue Budget Actual (Sale - Budget)
Jan calls regular 2000 1800 200
Jan calls super 3000 2500 500
Feb calls new 1000 900 100
Mar calls super 4500 4000 500
..... and so on

The data for date, item, type and Revenue are available in the OLAP (cubes) and the Budget value is available in Oracle table and we can't afford to transfer the Budget value to the cube.

Is this possible in SSRS? Please let me know if more information needed.

Thanks very much

View 6 Replies View Related

2 Datasets For 1 Table

Sep 28, 2007

Hi !

I'm creating a report with SSRS (2005). In that report I have a table, and in that table I need to put fields from two different datasets. But I don't know how to link those both datasets (because if I don't I get a cartezien product)...

Could someone help me please ??


PS : Excuse my poor english please...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved