reporting-services,ssrs-2008,ssrs-grouping , Replace Null value with string in ssrs expression

Replace Null value with string in ssrs expression


Tag: reporting-services,ssrs-2008,ssrs-grouping

I have a row group of STATUS fields. There is a group that returns null that I would like to change to chat. I have tried:

=Iif(IsNothing(Fields!STATUS.Value), "Chat", Fields!STATUS.Value)

but the group still returns blank. Would anyone have any ideas on how to help me resolve this?


Your code is right. Are you sure it's returning nothing, and not something like a space or carriage return?


SSRS Report Viewer Toolbar Broken

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 ( If I use the netbios/short url (http://reports/Reports/) it works fine - can anyone guess...

Multiple Datasets in one Table OR show distinct Value in Table (SSRS)

I am fairly new to SSRS and currently I'm working on building my first reports. I encounter some problems with a report that in my opinion should be fairly easy, but so far I couln't find the right answer. I have a table similar to this: Material - - Date...

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

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

SSRS Get first Item LookUpSet

So I have a dataset which contains a list of dates and foreign keys. Now I need to get the first date for a certain foreign key. I have tried the expression below: =First(LookUpSet(Fields!Title.Value, Fields!Linked_Study.Value, Fields!Subject_Enrollment_Date.Value, "FPI")) But it just returns #Error. Does anyone know why this expression doesn't work...

SSRS: How to restore a deleted title in a chart

I made some charts and deleted the titles. I decided that was the wrong thing to do and want to put them back. Is there any way to do this that doesn't involve redoing the chart?

Why rsAccessDenied error on SSRS when using Chrome but not in IE

I am trying to identify reason why SSRS service URL throws an exception when using Chrome while not with IE. I tried running both of them with Administrative account. http://xyz-shantanug/Reports/Pages/Folder.aspx User 'Domain\userName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC)...

Where does the syntax for expressions in SSRS come from?

The syntax for the expressions in SSRS seem to be very different to the other products that Microsoft produces. For example, I am not aware of any other tool that uses IIF() for an if statement and using 'is nothing' to assess if value is NULL. Is there any history...

How to insert excel formula to cell in Report Builder 3.0

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 re-sorts data meticulously sorted from the SQL query! Why?

I'm writing up a report using SQL Server Reporting Service via Visual Studio 2008. I have a stored procedure on a SQL Server database that returns a list in which I need the entry with value "F" in colulmn [Column] to be always on top, as such: F A B...

SSRS Color Expression Lesser than X

I have a table that handles stock, I have the data outputting in to a table in SSRS so I can see stock levels. like so: PartNumber, PartDescription, Qty I can use =IIF (Fields!Qty.Value < 40, "red", "teal") on the quantity field to set a threshold within the table but...

QTD MDX calculated value displaying differently on SSRS than SSAS

I have created an SSAS cube in SQL 2005 and included calculated members for MTD, QTD as well as YTD. With QTD measure I actually want to get the total over all 3 months of the quarter, not just the up-to-date value. Now when I browse the cube itself the...

Restart or no Restart after config file change

I have to make a small change to a SQL server config file ( Do I need to restart the server in order for the changes to be made or will it be fine without? Determines if I have to wait till an appropriate time to take the server down...

Drill through reports in SSRS

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

SSRS ListRenderingExtension Unauthorised Exception

I am currently using Report Viewer 11 to connect to a SQL Server 2008 r2 SSRS endpoint in order to run reports within a web page. Previously this was all working when SSRS and the DB were running on the same virtual server. We just moved the DB and SSRS...

SQL inner join list split for an SSRS report

I have a field that lists all language descriptions that a product has and the field can contain data like: EN;FR;DE It will always be a two letter language code followed by a semi colon. I then have a stored procedure that looks for all products with a particular language...

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

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

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

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

Tablix in Tablix - Header not repeating

I have one tablix embedded in another tablix. I cannot get the parent tablix's header to repeat when the detail table spans pages. I have tried numerous methods using the Advanced Mode's KeepWithGroup and RepearOnNewPage properties with no luck. I thought I had done this before but I don't remember...

PercentOfSum(fld, condfld) SSRS Equivalent

Crystal Reports has a built-in function PercentOfSum(fld, condfld) (documentation here). How can I achieve the same functionality in SSRS?

Hide parameters from SSRS Report when it Renders inside an iframe

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

SSRS mdx report: use dimension on columns

This simple mdx query do work in SSMS: SELECT CrossJoin({[Measures].[Qnt]}, {[Sales_step].CHILDREN}) ON COLUMNS, [City] ON ROWS FROM [SALES_PIPE] But it fails to run within Visual Studio for rdl report. An error accures: The query cannot be prepared: The query must have at least one axis. The first axis of the...

SSRS Average Time in Minutes and then Format to HH MM

I've seen a lot of questions about average time in SSRS but I'm pretty new to this and none of these questions address my specific need so any help would be appreciated. I have a duration field (Fields!Duration1.Value) which is pulling in a duration in minutes (example: 170). In my...

Trying to use Switch statement in SSRS

=Switch(Fields!CompnyID.Value=5, "company 1" "dsSalesManagement", Fields!CompnyID.Value=6, "company 2" "dsSalesManagement" ) Trying to use switch statement to display two different results in my report. Was maybe looking for syntax correction or is there a way to use an else if statement? Problem is I want this expression to be dependent on value...

SSRS Single row for Grouping

I'm working with SharePoint Lists, I have 2 Lists for this issue i.e. Movies & Theatres. Only one list is used tough, because Theatres reference a movie. (I know this is weird but I'm not actually working with movies and theatres, it's just using an analogy so anyone could understand...

MVC5 & SSRS ReportViewer - How to Implement?

I have spent hours trying to solve this and so far I can find MVC1, MVC2, and MVC3 based solutions but nothing about MVC5 and using SSRS and ReportViewer. Frankly, I don't know WebForms, since I joined the programming world after MVC was a big thing in the shop I...

Toggle groups based on a parameter

I have a reporting requirement in which I have 3 groupings Group_1 parents -> Group_4 lowest child I have a groupingID param which is an int that I'm using to show the same report either as region/div or emp/supervisor. Group_1 =totals and Group_4 = Employee and NULL for region/division. Updated...

How to format label in chart control

I have created a column chart in I am showing the date on x-axis. At the moment the date is shown as dd/mm/yyyy. All I need is to show the date as dd-mm-yy and ideally Weekday, dd-mm-yy e.g. Monday, 12-05-15. <asp:Chart ID="Chart1" runat="server" Height="400px" Width="900px" BorderWidth = "1"> <Series>...

Multi select parameter default set by previous multi select parameter

I have a report with several parameters, all in varying degrees depending on the value of its predecessors. I’m trying to convert from single selects to multi-selects. I’ve set the multiselect values for Report Type – see image – and would like to set the default value for the multiselect...

Why PDF exported from Report Viewer opens in different zoom size in Adobe Reader

I have 16 Reports in my current application. when I export the reports through Export drop down menu to PDF file, it saves to my local disk. But when I open the PDF through Adobe Acrobat Reader it opens it with a different zoom size for each report. Why I...

Setting a offset on my SQL Report with Paramters

I'm using SQL reports for label printing and I want to be able to pass a top and left offset to the report to make float adjustments to where the data will appear on the label. I have all the data in a rectangle and I'm trying to using TopOffset...

Use web service As Datasource in CRM reporting

I working with Reporting Service (SSRS) on CRM 2011 then I want to use Web Service as DataSource. I make simple web service that query data from CRM Database (not Filtered) then I call it's from report preview on VS2012 It's work fine but When I deploy my Report in...

Sum Grouped Data in SSRS

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

Error: Query with id 'DAXSecurityUsersAndRoles' does not exist. How to resolve this?

I am trying to run a report in Dynamics AX "Menu/Sys Admin/Reports/Security/Roles and Users Attached List" which is throwing me below error Query with id 'DAXSecurityUsersAndRoles' does not exist. Anybody faced this before? What to do to fix this?...

Don't produce report if there is no data - PDF

I have a report for which there is sometimes no data to output. This report is run along with several other reports and all are then output to PDF. However, this report still produces the header and footer with a blank page even tho there is no data. I can...

“add data driven subscription” missing in SSRS sharepoint mode

I'm using sharepoint 2013 with SQL server 2012 Enterprise Edition SP2. I am using SSRS in sharepoint integrated mode. When I hover over report options I see the "Manage Subscriptions" option and when I click it I can see the "add subscription" option. However, the "add data driven subscription" is...

SQL IF and ELSE block with temp tables inside

I got a quite big query with CTEs, temp tables and I'd like to put it into IF and ELSE blocks, for having different conditions for both of them, depend on parameter. In simplified it looks as below: DECLARE @parameter varchar(15) If(OBJECT_ID('tempdb..#table1') Is Not Null) Begin Drop Table #table1 End...

How to customize the header of the CSV file that exported by a SSRS report

I have created an SSRS report and exported that to a CSV file. The header of that CSV file displays an odd name "Donations_Chart1_CategoryGroup_label". I want to customize the name. If any one have an idea about that please let me know the solution....

Convert integer of MYYYY into Date in ssrs

I have a field that is a bunch of integers formatted like so: 92014 102014 I would like to convert the field into a datetime of the first of each month. So the newly formated field would be: 9/01/2014 00:00:00 10/01/2014 00:00:00 (or however datetimes would actually get formatted). Can...

SSRS Multi-Value Parameter Not Filtering

I have a few Parameters that I am trying to set based off of results that the user selects, but when I select from my Multi-Value Parameter my report errors with Failed to evaluate the FilterValues of the DataSet 'DST_MyThirdDataSet' I have my first DataSet called DST_MyFirstDataSet which is used...

Format Numbers in Multiple columns of SSRS Report

Currently I am working on a ssrs report. The table in report is having about 30 columns. For each column I have to modify the number format (to either 2 decimal or no decimal numbers). I can do it by click on every column and modify the number property in...

Find Mondays between 2 dates

I need to display dates of all Mondays in the given date range. For example, if my start date is 01/05/2015 and end date is 31/05/2015, I need to show 04/05/2015 11/05/2015 18/05/2015 25/05/2015 How is it possible?...

Cannot add new shared dataset to report

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