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?
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
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
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
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.
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?
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?
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
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
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
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?
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
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
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.
I have a number of MS Access apps that link to SQL Sever tables. Ifthe SQL Sever database is moved to another server, or the server isreplaced, the unc address to the database changes as the server namehas changed. All my apps fall over as the linked tables are addressedusing the server name.Is there a way to set up an alias name for an SQL database so that youcan link to the tables through an ODBC connection without using theserver name.Thanks in advance
View 1 Replies View RelatedI 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.
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
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")
I want to use ONE stored procedure to update/insert about 300 tables in a database. I hoped this code would work but it gives me an error:
ALTER PROCEDURE [dbo].[InsertRowsTick]
@tableName varchar,
@bid float,
@ask float,
@last float,
@volume int,
@dateTimed DateTime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tableName (
bid,
ask,
last,
volume,
dateTimed)
Values (
@bid,
@ask,
@last,
@volume,
@dateTimed)
END
When I (1) commented out @tableName parameter and (2) instead of INSERT INTO tableName wrote: INSERT INTO <realtableName> the code (C#) executed and the table was filled.
How can I use a generic table name as a parameter?
Thanks.
I have SSRS 2005 installed. User wants to change email address on subscription to send to external customer or others in our organization.
If I modify right "Manage individual subscriptions" to enabled for his security profile, the email address box is greyed-out.
I tried changing value <SendEmailToUserAlias>xxxx</ SendEmailToUserAlias> to FALSE in rsreportserver.config, with SSRS restart, but address box is greyed-out.
If I modify right "Manage all subscriptions" enabled for his security profile, the address box is not greyed-out, but can now see-edit everyone's subscriptions for that report.
How can I turn on address box is not greyed-out, but not open view of ALL subscriptions to my users?
Default code page in ETL package is 1252 which will not work if the collation is different e.g in Japanese_CI_AS, it is 932.
My question is how to write a generic ETL package so that it can cater any collation or any code page.
Is there any way to display this information in the report?
Thanks
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
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
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?
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 RelatedHi,
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
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
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
How do you join two datasets in reporting services... one from sql and another from oracle?
Thanks
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
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 RelatedI 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?