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
ADVERTISEMENT
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
Jan 31, 2007
Hi,
i have developed a report with sql reporting services 2005 which consists of two datasets in the same report.
Any performance issues it will cause?
Thanks,
Shanthi
View 14 Replies
View Related
Apr 18, 2008
Im trying to use 2 datasets in a report. The first one works ok, but when I add another one (a different table from the same database), it runs ok and brings up results in the data tab, but when I try to view it on the preview tab I get the following error:
an error occured during local report processing.
The definition of the report '/PI' is invalid.
The data set name is missing in the data region 'Dataset2'.
any ideas?
The queries i am running are simple SELECT * queries.
View 1 Replies
View Related
Apr 16, 2008
I have a report with two datasets(and two tables). They both have a field in common(a customer code).
I want the results to be one customer per page.
This isn't a problem when using one dataset, because I can just group by customer_code and page break at the end.
But in this case I need the customer info from the second dataset to be on the same page as the customer info from the first set.
Does anyone have any formatting tips on this?
View 4 Replies
View Related
Mar 24, 2008
Take a look at my report in layout view:
http://img366.imageshack.us/my.php?image=unbenanntex1.png
My problem is that the left table works perfectly, the right one always sums all values and uses the first funtion on char-fields... I never encountered the problem before, is there an easy solution for this?
View 5 Replies
View Related
Dec 18, 2007
Hi,
I have a scenario, where I have to execute a procedure and then run a query to get rows from a table. Whenever the user clicks on view report, these two should happen.
So I came with a strategy like, I have kept two datasets, one for executing SP and one for executing select query.
Can I run these two datasets one after the other, when view report was clicked?
Any other ideas?
View 4 Replies
View Related
Oct 10, 2007
I am having two datasets in the same report, the column names are the same, in both the datasets, Now How can I use the individual fields in same report.
If I use (Fields!xyz.Value, "dsDataSet1") and (Fields!xyz.Value, "dsDataSet2")
It is giving me syntax error.
How can I use both these fieds, I do not want any aggregates
View 7 Replies
View Related
Aug 22, 2006
Dear ppl,
I have got 2 datasets D1 & D2 in a Report. How can i differentiate between the fields of these two.
e.g. I got Name field in both the datasets. So when i do =Fields!Name.Value in a textbox the report gives me error
How can i tell the report from which dataset to pick up the field ??
Regards
Nabeel
View 9 Replies
View Related
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
Apr 26, 2007
I have a local Reporting Services report that I am modifying to use a stored procedure.
Although I am executing a stored procedure in the dataset query window, I also have to run a SELECT statement to retrieve the fields from a table that will populate the report.
The code that I have in the dataset query window looks like the following:
------------------------------------------------------------------------------------------------------------
EXECUTE @retCode = RunClaimVerification @parmID, @parmDate, @parmRecordID OUTPUT
SELECT *
FROM ClaimsDetail
WHERE ClaimRecordID = @parmRecordID
------------------------------------------------------------------------------------------------------------
When I execute this code, the only results that are returned SEEM TO BE the return code associated with running the stored procedure.
I thought about putting the SELECT code in the stored procedure and returning a table or a cursor from the stored procedure BUT it looks like tables are not supported as Report Parameter data types.
The stored procedure code generates Claim data that is stored in a SQL Table. The fields in this SQL table need to be retrieved by a unique record id to populate the fields in the report.
Does anybody have any suggestions as to how to go about doing this OR any suggestions that would help me resolve this problem?
View 1 Replies
View Related
May 7, 2007
Hi all,
I need to use a multi-table dataset as a datasource for my report items in a SRS2005 report.
But from designer, I can only see the first table.
Can any one tell me if it is possible to use multiple tables in a dataset for SRS report?
Thanks,
Samson
View 1 Replies
View Related
Mar 5, 2008
Hi,
I would like to build an SSRS report from two different cubes (SSAS 2005). These cubes are contained in two different SSAS databases.
To do this, i created a dataset for each cube throught MDX expressions.
These datasets use common parameters defined in my report.
Thus, i try to integrate in my report chart datas from the two datasets, but my chart only target one dataset.
Is it possible to create a kind of dataset view to join my two datasets ?
Or is it possible to join the cubes from two different SSAS databases in an MDX expression to put all my datas in a single dataset ?
thx for your help.
Zohir
View 3 Replies
View Related
Jun 23, 2015
I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .
I would like to create a tabular report datasets on the report like shown below with grouping on col1
like
col1 some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.
View 2 Replies
View Related
Jun 21, 2015
I have two different dataset in one Report, Each Dataset result is binded to a different Table component.
When I export as Excel ,I am getting all this in One Excel sheet.
I need this in separate excel, as dataset1 in excel1 and dataset2 in excel2.
View 3 Replies
View Related
Feb 22, 2008
Hi,
I'm using SQL 2008 Februar CTP and trying to use SSIS for Data Source as described in http://msdn2.microsoft.com/en-us/library/ms159215(SQL.100).aspx.
I've created SSIS package and preformed steps described in http://msdn2.microsoft.com/en-us/library/ms345250(SQL.100).aspx (after fixing version to 10.0.0.0).
Now I got next error when trying to add SSIS DataSource (in Report Designer - Visual Studio):
Error messageThe data extension SSIS could not be loaded.
Please help.
Matej
View 3 Replies
View Related
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
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
Jul 31, 2007
is this possible? i need to make a report that accesses two different tables, one for user activity and the other for the number of reports that user has done. the report information is in a different database however so i have to use different datasets. is there a way to use both databases in the same table? all i need is the one field from the reporting one and they can be linked by the user id...
View 1 Replies
View Related
