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:
Detail rows for Group Param Value 1 ... Detail rows for Group Param Value 2 ... Detail rows for Group Param Value 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.
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. ------------------------------------------------------ RDL 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"
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:
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.
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.
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)) * IIF( Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),
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)
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?
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.
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.
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?
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.
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?
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:
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.
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.
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?
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?
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..
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.
Example: ... .... Group1 Value 10 20 Sub Total 1: 30
Group2 Value 15 25 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?
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 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:
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]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
select * from SalesCTE
) 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  as [Day 1], - as [Day 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...
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
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.
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.
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.
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.