FAQ Database Discussion Community


Cannot add new shared dataset to report

visual-studio-2013,reporting-services,sql-server-2008-r2,ssrs-2008-r2
I have a summary report with about 20 datasets, linking to 20 detail sub-reports. I have added datasets based on stored procedures for each of the detail reports. Now when I try to add another one I get an error message Could not create a list of fields for the...

PercentOfSum(fld, condfld) SSRS Equivalent

function,reporting-services,crystal-reports,ssrs-2008,ssrs-2008-r2
Crystal Reports has a built-in function PercentOfSum(fld, condfld) (documentation here). How can I achieve the same functionality in SSRS?

Visibility of report field dependent on the value of another field

reporting-services,ssrs-2008-r2,ssrs-tablix
I have two columns one [CUS SKU], the other [UPC]. I have 2 specific id's 1234, 1233 and many others but only care if these two show up. My problem- If either of these two show up on both columns I only want to display one column and hide the...

Filter report by date to show users registered this week through email in SSRS

sql-server,reporting-services,ssrs-2008,reporting,ssrs-2008-r2
I have a report with startDate and StopDate as parameters. I also configured smtp settings etc to send a report through email on specified time which is working fine. I am getting the stopDate from today() function but I dont know how to keep the startDate to Monday of the...

How to insert excel formula to cell in Report Builder 3.0

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

SSRS Calculate duration between two dates

sql,reporting-services,ssrs-2008-r2,ssrs-2012
How do I write the follow SSRS Calculation below in SSRS expression Actual Calculation example below =((deldate+deltime) –(orddate+ordtime))*24 deldate delTime orddate ordTime *24 13/01/2015 14:25 14/01/2015 13:55 23.50 20/01/2015 12:00 20/01/2015 17:02 5.03 This worked in SQL Toad but not in SSRS (TO_DATE(TO_CHAR(A.DELDATE,'DD/MM/YYYY') ||' '|| TO_CHAR(A.DELTIME,'HH24:MI'),'DD/MM/YYYY HH24:MI') - TO_DATE(TO_CHAR(A.ORDDATE,'DD/MM/YYYY') ||'...

SSRS - Returning the average where date is 2015

reporting-services,ssrs-2008,ssrs-2008-r2
I have been trying to figure this out for a while now and just couldn't find the answer anywhere. I have a report in SSRS with a column group assigned to "Year", this column expands depending on what parameter the user enters into StartYear. If the user enters "2013" the...

Can't use in where clause

reporting-services,ssrs-2008-r2
I have as part of my where clause the following WHERE (@ProgramID IS NULL or ProgramID IN (@ProgramID)) AND (@ProgramName = '<ALL>' OR ProgramName IN (@ProgramName)) This always throws a "expression of non-boolean type specified in a context where a condition is expected, near ','" when I select 2 or...

SSRS Reports Viewing mode: A dire need for vertical scrolling using IE 11

reporting-services,ssrs-2008-r2,ssrs-tablix
I would really like some help with this problem. I plan on making a large set of reports, which have MANY rows. I want to keep all the rows on a single page, so I set the property "Keeptogether" for the entire tablix as true. THis works well, but I...

The dataset refers to a shared dataset which is not available

c#,asp.net-mvc-4,ssrs-2008-r2
I am accessing a SSRS Report in my ASP.NET MVC 4 application through Reporting Services web service (SOAP API). but getting below error when try to send comma seperated parameter. "The dataset refers to a shared dataset which is not available." ...

Sum Grouped Data in SSRS

reporting-services,ssrs-2008,ssrs-2008-r2
I want to SUM grouped data in a report but can't figure out a way to do it. My data looks something like this: I am trying to get value 4 in Grand Total which would show the number of orders on different schedules. Simple count would count all the...

Drill through reports in SSRS

sql,reporting-services,charts,report,ssrs-2008-r2
I am making a report that takes in date parameters to filter result and shows the result in a pie-chart. I want to create a drill-through (could be drill down or any other type) where if a user clicks on a part of a pie chart which is divided by...

Adding Multiple Trend Lines - SSRS 2008R2

reporting-services,ssrs-2008,ssrs-2008-r2
I need to add multiple trend lines to this chart. See below. http://imgur.com/cI4fnPr&bO4OkJo This what I have currently. In this sample, there should be a Blue trend line and a Gold trend line. I cannot find any way to do this without removing the x-axis grouping. If I do that,...

Backup SSRS Encryption Keys Using Powershell

powershell-v2.0,ssrs-2008-r2
I have made the following script from google for backing up the SSRS Encryption keys: cls $pwd = "[email protected]@123" $SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_BPSSRS\v10\admin" -class "MSReportServer_ConfigurationSetting" $key = $SSRSClass.BackupEncryptionKey($pwd) $stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length) $stream.Write($key.KeyFile, 0, $key.KeyFile.Length) $stream.Close() But I'm getting the following errors: Method invocation failed because [System.Object[]] doesn't contain...

Asking datasource credentials

reporting-services,datasource,ssrs-2008-r2,rdl
I have created one report and the .rdl file is uploaded to report server from another machine. But when running the report it is asking for data source credentials. Can we avoid asking data source credentials while running report?

SQL to split Comma Separated values and compare it with a multi list value in SSRS

sql-server,reporting-services,ssrs-2008-r2,ssrs-2012
I have a field in my table which has multiple reason codes concatenated in 1 column. e.g. 2 records Reason_Codes Record1: 001,002,004,009,010 Record2: 001,003,005,006 In my SSRS report the user will be searching for data using one of the above reason codes. e.g. 001 will retrieve both records. 005 will...

Need custom groups based on strings in ssrs

reporting-services,ssrs-2008-r2
Ok so I can do this in crystal reports but my users can not view live data which is a problem so I want to move the report to ssrs. What I have is pretty simple. It's a report that lists businesses and number of clients. I need two groups...

Getting an SSRS map to include 0 in data

ssrs-2008-r2
I've been working a lot with maps in SSRS recently and can't seem to find a way to set a visualization to include a 0 value in the Field to Visualize area. Has anyone found a way to include 0 so the legend reflects nothing is in a certain area...

SSRS - Table of Expressions

sql,sql-server,reporting-services,ssrs-2008-r2
I'm not that experienced in sql server and even less so in using SSRS. I'm working with 2008 R2 edition. Question: Is it possible to use a sql table that has expressions in a report, so that the report will evaluate the expressions before it is run? I just tried...

How to sort SSRS Matrix report by Grand total field

reporting-services,ssrs-2008-r2
I created a matrix report. The report looks like this: I want to sort the output so the region with highest sales should come on top. e.g. From the above report South should show on top then West, Central and East. How can this be done in SSRS (2008R2)?...

=WeekdayName(weekday(Today())) gives me tomorrow

reporting-services,ssrs-2008-r2
I have noticed with one of my reports (SSRS), when I add weekdayname, that tomorrow's value appears. I tested this by adding a textbox with =WeekdayName(weekday(Today())) in it. I have just run this (on a Monday) and it is saying Tuesday. So clearly it's one day out. Does anyone know...

Performance Issue in SSRS - Multiple Dataset

sql-server,ssrs-2008-r2,ssrs-tablix
I have one complex report which fetches records from multiple tables. I saw at many places that SSRS does not allow multiple data tables returned from single stored procedure, that is the reason I created one stored procedure and I created six dataset for report that was filtered from shared...

Want to display only last 3 digits number in textbox in ssrs report

reporting-services,ssrs-2008,ssrs-2008-r2,ssrs-tablix,ssrs-2012
I am getting output from sql is like 1011153, 1011154, 101155. I want to display only last 3 digits in like 153, 154, 155 respectively. I'am using SSRS reports....

NOT IN affecting output

reporting-services,ssrs-2008-r2
I have a fairly simple script, but one field (person_name) has some test users' names (e.g. 'John Smith') in that I want to exclude from a report I am creating. The field also has some null values, which I want to keep in the report. I have put in: AND...

Export to pdf SSRS

reporting-services,ssrs-2008-r2
I've some questions regarding SSRS: I want to create Account Statements of each client Is it possible in SSRS to do so that I will pass AccountID as a parameter for each client, and it will form and save pdf files in C drive for each AccountID. How can I...

Modifying SSRS directly in the Database does not reflect in ReportViewer

c#,reporting-services,ssrs-2008-r2,rdl,dynamic-rdlc-generation
I am working on Web Based SSRS Editor. I have succeeded in developing a Parser which access the SSRS schema from CATALOG Table from Database and converts it to HTML. From here User can modify the report and submit the change, again Parser receives the HTML and converts it to...

SSRS Total based on Expression Value

reporting-services,ssrs-2008-r2
I'm trying to create a total that is based on a calculation to provide Gross Weight. Now before someone suggests (modify the SQL to give results and then total that in SSRS) this is coming from a dynamics AX query which i don't have access to. This is my report...

Scope parameter issue in SSRS report Expression

sql-server,reporting-services,ssrs-2008-r2
Friends, I have a detailed chart which represents individual expenses, but instead of handwritten fields, I want to use them from expression to load automatically as those fields are not the same every time. Hence I have used a Row Group with a Scope Parameter belonging to Row Grouping. More...

Is there an API or other library available for SQL reporting services XML?

xml,reporting-services,ssrs-2008,ssrs-2008-r2,ssrs-tablix
While I do not have the full context of all that goes on in the generation of reports using SRS, I have recently been tasked with creating customized reports which are used to print labels ( a fairly daunting task, considering the ease that the designer is supposed to offer...

Totaling an expression in SSRS

reporting-services,ssrs-2008-r2
I have an expression that calculates a value in a textbox. I would like, at the end of the report, to have a total of all the fields in that textbox. However, the expression uses named fields from the query, which are totaled also. =SUM(Fields!Total_Defective__.Value) / SUM(Fields!sales_Dollars.Value) Total_Defective__ and sales_Dollars...

different dataset in switch in ssrs

reporting-services,ssrs-2008-r2,ssrs-tablix
I have 2 datasets in a single report, out of the 5 columns 2 are derived from dataset1 and 3 from dataset2 (dataset 2 being the primary dataset). Now when I try to use the below switch command,it does not give me any error but doesn't show any results. The...

SSRS Use 1st Parameter (Multi choice) or 2nd Parameter (Text) for same SQL Parameter

reporting-services,parameters,ssrs-2008,multi-select,ssrs-2008-r2
I'm not sure if this is possible, but I would like to use 2x Report Parameters for the same SQL parameter. Currently I have the following in my WHERE clause which relates to a multi-select drop-down: and traderid in(@traderid) But I would like to have something along the lines of:...

Group by week separately for year

reporting-services,ssrs-2008-r2,ssrs-grouping
I have to group my sql query by week. But the record contains many years data. I used the query SELECT datepart(week, myDate) as week. But under the week 1 it shows data from every year How to group the weeks again having the same year...

Hide parameters from SSRS Report when it Renders inside an iframe

reporting-services,ssrs-2008,ssrs-2008-r2
I have go through this question about the same topic in stack overflow but i did not get any accurate answers from the same that is why i have ask the question again enter link description here I havce created a ssrs report and i have render it in an...

How to make Boolean parameter disable depending on other parameter values?

reporting-services,ssrs-2008,ssrs-2008-r2
I have two text box parameter i made them Null by default. And i have one Boolean parameter. I want to make Boolean parameter disabled if the other two text box parameter are not Null.I tried this expression but don't know syntax is correct or not =iff(Parameters!CvrgEffDt.Value = "" Or...

5th BUSINESS DAY subscription SSRS

reporting-services,ssrs-2008-r2
I have a report subscription that needs to send out on the 5th business day of the month, right now i can run it on the 5th of the month but the user wants it to be on the 5th business day so that it does not get sent out...

Level function not working

reporting-services,ssrs-2008-r2
I have a table with a row group hierarchy and I would like to be able to control the indentation amount based on the group level. There are other things I need to be able to do programmatically using this information as well. The Level() function in SSRS seems straightforward....

SSRS 2008: Clear report contents when new parameter value is chosen

reporting-services,parameters,ssrs-2008-r2
I have an internal SSRS report that can be run against data for different clients. The report has just one parameter "Client" which is a drop-down menu populated by a DataSet which populates from essentially "select distinct Client from ClientStuff" which returns a short list of clients. The user chooses...

Visibility property in report not working

reporting-services,reportviewer,ssrs-2008-r2
I have a SSRS report with a few graphical elements. I'd like to hide these when the report is exported out to Excel, so I've changed the Visibility property to: =Globals!RenderFormat.Name = "EXCEL" Exporting the report from Report Builder to Excel works fine; the graphics are shown when viewing the...

SSRS and PowerShell: Get report as Excel

powershell,reporting-services,ssrs-2008-r2
I'm trying to make PowerShell send a web request to our SSRS server and capture the results. I've hit a wall using the rs:FORMAT=EXCEL parameter in the SSRS url string. I have the following: First, init the credentials: $User = "MYDOMAIN\MyUser" $PWord = ConvertTo-SecureString -String "WooHooStringP$W0rd" -AsPlainText -Force $c =...

SSRS Hide text not text box on toggle

reporting-services,ssrs-2008-r2,ssrs-tablix
I have created a report that has a text field that is pretty substantial, and when brought into the SSRS report give a large text box. So to combat this I have created a preview field like so : and I have set the description column to hide with a...

Showing day name and month name in ssrs

reporting-services,ssrs-2008-r2,ssrs-2012
I am having a date example 4/29/2015 . I need to change the format of date like " Wednesday,april 29,2015. How to achieve this using SSRS.?

SSRS Date column sorting

sql,sql-server,sorting,reporting-services,ssrs-2008-r2
I have a SSRS 2008 R2 tablix which displays like this : _____________________________ | | Date | |_____________________________| | Row1 | | | Row2 | | | Row3 | | |_______________|_____________| The Date displays data in YYYY/mmm format (2015/01 , 2015/02, 2015/03). This column has a grouping on it (column...

Formatting Datetime in SSRS Expression

reporting-services,ssrs-2008-r2,ssrs-2012
Part of my query is like so: SELECT * FROM TableA WHERE ColumnA >= DATEADD(DAY, - 30, GETDATE()) With the expression at the where clause above, you can pull a rolling 30 days data without having to supply values. Now users of the report want to see it represented like:...

how to using IN operator in ssrs expression

reporting-services,ssrs-2008-r2
First of all sorry about my english. I want to use in ssrs in tablix-fields expresion something like this: =iif(CStr(Fields!Id.Value) in Split(Parameters!IdListStr.Value, ","), Fields!Sum, "") where Parameters!IdListStr.Value = "1,2,3". is it possible?...

Placeholder expression to retrive total based on condition

sql-server-2008,reporting-services,ssrs-2008-r2,reportbuilder3.0
I have created a place holder within the report, which reports gets the total of transactions that are confirmed. But, I get an error. The exact message is: The value of expression for the textbox 'textbox3' refers directly to the field status without specificyng the dataset aggregate. When the report...

SSRS - Pass Multi Value Parameter with Available Values Option Enabled Through Querystring

asp.net,sql-server,sql-server-2008,reporting-services,ssrs-2008-r2
I have one Parameter on my report, that has multi value on. And it's values came from a Dataset. On my application, when I request a report, this parameter goes as an Querystring value. But when I use the multi value option, the parameter is not a single value anymore,...

overlapping issues SSRS

reporting-services,ssrs-2008,ssrs-2008-r2,ssrs-2012
Every expression is left align, I don't know why some labels are overlap and have more space. see pic 2, label cancer within 5 years is overlap with expression. see pic 3, output for unresponsive state has more space than output 1 How do I fix those problems? ...

SSRS IIF Multiple expression not working

reporting-services,ssrs-2008-r2
Please see pic For some reason my expression is not working and I cannot figure out why... What I'm trying to do is check the UseByDate if it's blank set time to blank AND if pickeddatetime is blank (time field) also set to blank if not blank use pickeddatetime. However...

Finding week start and week end date

sql,sql-server,sql-server-2008,reporting-services,ssrs-2008-r2
I have a week number and year and i need to display "total for mm/dd/yy to mm/dd/yy in a row of my ssrs report. My week starts with Monday. For example if my week number is '2' and year is '2010' then I have to display "total for 01/04/2010 to...

Sorting Row In SSRS

reporting-services,ssrs-2008,ssrs-2008-r2
I am trying to get the "Total" row of a report in SSRS to stay put at the buttom but all to no availed. My Total field is being calculated from a query like so: Select A , B , C FROM D UNION SELECT 'Totals' , SUM(B) , SUM(C)...

Background colour expression brings up a surprising result

reporting-services,ssrs-2008-r2
I have a report where I want a cell colour to change if a duplicate value appears. I have used this expression for Background Colour: IIF(Previous(Fields!CustomerSurname.Value) = Fields!CustomerSurname.Value, "Red", Fields!CustomerSurname.Value) This works fine but if the customer surname is 'Brown' or 'Grey' then the cell colour matches the colour of...

SSRS Report Viewer Toolbar Broken

reporting-services,ssrs-2008-r2
I have just noticed that the toolbar in SSRS (with the page numbers, save/print etc) is slightly askew (see image). After doing some testing, this only happens when accessing a report using the server's FQDN (http://reports.domain.com/Reports/). If I use the netbios/short url (http://reports/Reports/) it works fine - can anyone guess...

Using Cutsom Colors For Charts and Tablix in SSRS

reporting-services,ssrs-2008-r2
So I have been tasked with creating this. I have the charts in the tablix and I have created a custom pallete that is not implemented yet, this is just the sea green pallete, but I need to color the rectangles in the description so they match the charts like...

How to repeat Left Most rows for a group in SSRS report

reporting-services,ssrs-2008,ssrs-2008-r2,ssrs-tablix
I have an SSRS Report which is having groups. I need left most rows to be repeated in the tablix for the group. Example: Original Report is like below Country Region Category Total ------------------------------------------ Group Total 1000 Product 1 200 USA NORTH Product 2 300 Product 3 300 Product 4...

SSRS - Display latest value in column group

reporting-services,ssrs-2008,ssrs-2008-r2
tables - I can't post images yet, so I posted a link to the examples. I have two tables - one for sales and one for inventory. The dates in these tables are week ending dates - so the sales are totals for 1 week and the inventory is the...

Spacing Issues between textboxs and expressions

visual-studio,reporting-services,ssrs-2008-r2
In the design everything look fine(pic 1), but when I preview the report, somehow there are the inconsistent gap between labels and expressions (pic 2); ex DOB. Some have more space, and some are very close to labels. How can I fix the spacing issues? ...

SSRS expression output shift upward

reporting-services,ssrs-2008-r2,ssrs-2012
Somehow the expression output shifting up few rows. Any ideas what cause this problem? ...

Switch expression for conditional formatting in SSRS 2008 R2

sql-server,reporting-services,switch-statement,ssrs-2008-r2,conditional-formatting
I have this conditional formatting expression (for background colour) which works fine: =SWITCH( Fields!Fire_Safety.Value = "F", "LimeGreen", Fields!Fire_Safety.Value = "i", "Red", Fields!Fire_Safety.Value = "E", "Yellow", TRUE,Nothing ) I have now changed the data table (via SQL) so that where the “F” was – there are now dates. I changed the...