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


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 :: 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...


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

Totals && Grouping In SSRS 2005

May 1, 2008

I think this is a simple question for creating a report with SQL Reporting Services, but I can't seem to find any straight forward answers for this.

I have some detail data I'm pulling in from 1 view into my report dataset:
Loan NumberLoan AmountPayment DatePayment
1000550000Jan 200815000
1000550000Feb 200815000
1000550000Mar 200815000
1002300000Jan 200850000
1003450000Jan 200820000
1003450000Feb 200820000
I'm creating a RDL with this data and in my detail row, I'm showing the Loan Pmt and Loan Date with Grouping on the Loan Number & Loan Amount.

My problem is that when I look at my grand total, it is summing up the Loan amount more than 1 time - once for each detail record.
Loan Number Loan Amount Payment Date Payment
1000 550000 Jan 2008 15000
Feb 2008 15000
Mar 2008 15000
Subtotal 45000
Loan Balance505000

1002 300000 Jan 2008 50000
Subtotal 50000
Loan Balance250000

1003 450000 Jan 2008 20000
Feb 2008 20000
Subtotal 40000
Loan Balance410000

Grand Totals 2850000 135000

So my total on the Loan Amount column is incorrect, it should really be 1,300,000, but instead it's calculating 2,850,000.

Can someone explain to me how I should correct my grouping or dataset in a table?

I was able to use a Subreport for my detail records, and keep the Loan details in my Parent report to get the right totals, but then I hit the issue with exporting to Excel - "Subreports within table/matrix cells are ignored"

Any suggestions??

View 2 Replies View Related

SSRS: Problem With Tables And Totals

Apr 24, 2008

Hello all,

I'm running into problems generating a report with totals in it, and was rather hoping one of you guys could help...

We have a database storing the results of people taking a quiz (or rather, a set of quizzes). I need to produce a report which, for a given person, displays their score for each learning outcome, and their overall score for the quiz, for each attempt of each quiz. It should be noted that some questions may feature in none, one, or multiple learning outcomes. In addition to this, it should display the average score for each quiz (i.e. the sum of the overall mark for each attempt divided by the number of attempts).

The data has been normalised into the following table structures:

Quiz (containing quiz name, unique ID, and course)
Learning Outcome [LO] (containing name, quiz it belongs to, and description)
Session (containing user, unique identifier, quiz being taken, and a timestamp)
Answers (containing session identifier, question number, answer, and score)
LO-Qn link (links LO to a specific question)

* Note: this is slightly simplified as a question can contain mulitple sub-questions, but you get the idea. *

After running a fairly long query on the tables (basically, it calculates the score for each question per attempt per quiz as a CTE, which is then used twice - to calculate the total score per attempt per quiz, and the score for each LO per attempt per quiz - the results of which are then glued together, along with some other text-identifier data, and DENSE_RANK() used to extract the attempt number), some data gets spat out in the following format:

QuizName | AttemptNumber | ScoreForAttempt | LOName | LODescription | ScoreForLO
Quiz1 | 1 | 5 | LO1 | Blah blah | 3
Quiz1 | 1 | 5 | LO2 | Blah blah | 2
Quiz1 | 2 | 10 | LO1 | Blah blah | 5
Quiz1 | 2 | 10 | LO2 | Blah blah | 10
Quiz2 | 1 | 7 | LO1 | Blah blah | 7
... etc ...

I have created a report in Visual Studio that consists of a single table, which looks like this:

Now, up to this point, it all works fine. However, what I want to do is include an average attempt score for each quiz in the top right cell of the table (in the blank mid/deep blue space directly below "Score"). However, if I set this to be "=AVG(Fields!ScoreForAttempt.Value)", this doesn't work as it includes too many values - number of attempts x number of LO's.

Any ideas on how to do this, or is it not possible?

Thanks, and sorry if it's a silly question (I'm new to Reporting Services).

P.S. Sorry for the huge post, but I wanted to try to make sure I gave all the relevant information.

View 4 Replies View Related

SSRS - Run Stored Procedure Before Other Datasets

Jun 4, 2008


I have a report which contains several queries - one of which is a stored procedure which creates a Temp table in the database. This temp table is then used by the other queries. The temp table provides the lowest granularity of data, and the subsequent queries aggregate it at different levels in order to produce the report charts and matrix's.

The issue is that when I run the report, the stored procedure does not create the temp table before the other queries start using it. In fact, I need to run the report, and then refresh it in order to get the report to pull in the correct data (1st run populates the temp table, refresh then allows the queries to use it.).

Is there a way to force the execution of the stored procedure before the other queries run?...I don't want to create stored procedures for each query, because the intitial creation of the Temp table is quite slow (5seconds), and to do this for each data subset would be very resource intensive.



View 10 Replies View Related

Reporting Services :: Adding Group Totals In SSRS

Oct 27, 2015

I have a field on my report that uses the following expression to determine the commission amount for each order line.  It works correctly to get the commission amount for each line, however, I need to get a total of the commission amount for each Salesperson. 

My report is grouped in the following manner:

Salesperson, Type of Sale, Invoice Number, then the detail invoice line items (where the formula below reside).  How can I get the totals for the Salesperson and the Type of Sale?

=IIF(Fields!PartIsSerialized.Value=True, (Sum(Fields!OrderLineSubtotal.Value)/Fields!Quantity.Value),Sum(Fields!OrderLineSubtotal.Value))
Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),

[Code] ....

View 2 Replies View Related

Reporting Services :: How To Create 2 Conditions From 2 Datasets In Row Visibility SSRS

Apr 29, 2015

In ssrs, I want to create an expression for the row visibility. But, the expression will contain 2 conditions from different 2 datasets (DealStarts & RowofTrendingVisibility). I have applied a solution from online, but got an error message is 

"The Visibility.Hidden expression for the tablix ‘Tablix9’ contains an error: [BC30451] Name 'launchdate' is not declared. "

I think that there is a minor issue in my syntax.

=iif ((Last(MonthName("DealStarts"))=monthname(month(today())) or launchdate ("RowofTrendingVisibility")<Parameters!StartDate.Value),true, false)

View 3 Replies View Related

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 Title
% Remaining




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

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.


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

Reporting Services :: How To Export 2 Datasets To 2 Excel Sheets In Single Report SSRS

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

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?



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 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 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


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,


View 1 Replies View Related

Possible To Stop SSRS GUI From Destroying Hand Rolled Mdx In Datasets By Silently Reverting To Design Mode?

May 30, 2007

Since as soon as you extend your mdx datasets manually you can no longer switch back into design mode without losing your changes, right?

If that's the case, is there some way to disable design mode completely? i'm finding that the GUI has the tendency to SILENTLY revert the dataset editor back to design mode while I'm busy editing a layout, thereby losing my carefully crafted MDX.

View 4 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


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.


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

Employee E
INNER JOIN TransCurrentMaster TCM ON E.EmployeeCode = TCM.EmployeeCode
CROSS APPLY udfReportTaxableAllowances(E.EmployeeCode, TCM.PaySequence, ) TA
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..


View 4 Replies View Related

Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Group1              Value
Sub Total 1:         30

Group2                 Value
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 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
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")

View 3 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:


Now this command returns two data sets like:


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

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00


As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:


9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00


To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:


300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])





cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]


Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by, cag.WorkDay


select * from SalesCTE



sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!


P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 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

Multiple Drill Through In A SSRS Chart

Sep 10, 2007


we have a requirement that, when we click on the multiple series bars in a SSRS chart, it should drill through that chart and the detail report should filter based on these x axis and y axis of the bars selected.

As far as my knowledge, we can drill through the chart by selecting single series bar at a time in SSRS. Is there any way to acheive this multiple drill through of a chart.


View 1 Replies View Related

How To Pass Multiple Parameters In SSRS

Apr 25, 2008


I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.

Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.

Please help.


View 4 Replies View Related

SSRS Table Layout Of Multiple Graphs

Apr 30, 2014

I need to have a table type layout of graphs. These various graphs make use of 5 different datasets. Is there a way to use a tablix in the report to do this? I have tried but one requires a dataset and then require all the various fields to be in that table which is not possible.

I require all 5 datasets.

Is there a way of doing this apart from just putting 5 separate tables on the report and the graphs in them, which is not ideal or what I want to do.

View 1 Replies View Related

How To Pass Multiple Parameters Across Reports In SSRS

Apr 25, 2008


I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.

It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View 3 Replies View Related

Sub: How To Pass Multiple Parameters Across Reports In SSRS

Apr 25, 2008


I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.

It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View 1 Replies View Related

SSRS 2005 - How To Have Multiple Subtotals In Matrix

Jan 15, 2008

Is there any way to get multiple subtotals in a matrix? For example, one that does a count and the 2nd that does an averages as per the desired result below ...

Code Block

A 1 2 3
B 2 3 4
C 3 4 5
Total 6 9 12
Avg 2 3 4

View 8 Replies View Related

Copyrights 2005-15, All rights reserved