Go Premium for a chance to win a PS4. Enter to Win

x

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have an SSRS combo box that will pass 1 of 3 values to my stored proc.

'ALL'
'NATIVE'
'LINKED'


I need the report to do the following based on one of these 3 options being picked.

If the parameter Native is picked...then it should be be equal to the columns value in the report of Native.
the same if Linked is chosen..

If All is chosen it should return anything in this column...which could be "Native" , "Linked", '' , NULL

Please help me build the where clause to handle this.

Here is what I have ..but its not working.


(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or ((@NativeLinked = 'All') and t2.[Native/Linked] is null or  @NativeLinked = 'Native' or @NativeLinked= 'Linked'))
0
Vote for the Most Valuable Expert
LVL 7
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Hi,

Within a parent Rectangle I have a Textbox alongside a Table but the Table only appears below Textbox rather than alongside of it. I've tried various combinations of KeepTogether True/False with both the TextBox and the Tablix but to no avail. Screenshots attached to visualise the issue.

Report Layout
What i'd like is the List to appear alongside the "Sequence of Operations"

Report Issue
0
I have a dataset in SSRS that will return a  list of comma delimited strings.

based on the values returned...I need this list of strings turned into a temp table...so I can evaluate the temp table in the stored proc in an In clause.


Please help
0
Is there a design pack or CSS template I can purchase that will help me transform my SSRS reports from the plain black and white to something much more attractive?

If there is not a for purchase theme / style template, can anybody suggest a good youTube video or URL that show how to make these reports a little more attractive?

Thanks experts!
0
CREATE TABLE SSRSReport
(
    YearMonth		    INT,
    SSRSReportDateKey   INT,
    Membership		    INT,
    Admits              INT,
    AdmissionType	    VARCHAR(200),
    ManagingEntityName  VARCHAR(200)

)


INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALS')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PHYSICIAN Primary (CDPG)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','CULLMAN Secondary CARE IPA (CULL)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ETOWAH IPA (ETOW)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','FLN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GAN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GOTT')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IL')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IN (INAIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','INDEPENDENT PHYSICIANS (IPM)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MA MAIND')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSGC - LIGHTHOUSE IPA (MSGC)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSJAX')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','N/A')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','NC (NCIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PA PAIND')
INSERT INTO SSRSReport 

Open in new window

0
CREATE TABLE Report
(
    YearMonth        INT,
    ReportDateKey    INT,
    Admints          INT,
    AdmisionType     VARCHAR(200)
)
GO

INSERT INTO Report VALUES
(201701,20170131,10,'Acute Care')
 ,(201703,20170330,10,'Acute Care')
  ,(201704,20170430,70,'Acute Care')
,(201706,20170630,10,'Acute Care')
,(201702,20170228,80,'Acute Care')
,(201701,20170228,20,'LTE')
,(201702,20170228,30,'LTE')
,(201701,20170131,50,'RHEAB')
,(201702,20170228,100,'RHEAB')

Open in new window


EXEPECTED OUTPUT:-

AdmisionType	ADK	PreviousMonthADK	YTDADK"
Acute Care	80	10	90
LTE	30	20	50
RHEAB	100	50	150

Open in new window

0
I have a report from SSRS.   I need to include blanks...so that if a user selects all any value is returned for that column.

I am listing the dataset which populates the dropdown in SSRS.
I am listing the function used to parse the string from report server and create a comma delimited list of strings.

I am listing the where clause from the report where I call the function.
This where clause should be able to select all or just items on the list if a user selects individual values.

It was my thought that moving the 'blank' to the bottom of the sorted list in the dataset would fix this.  It is not working.


dataset - I also replaced the commas in the list ..just in case that was messing up...no luck
select row_number() OVER (order by t1.cTitle) as titlerow,replace(t1.cTitle,',','') as cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by CASE when  t1.cTitle = '' then 1 else 0 end

Open in new window



Here is my string parser:

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 8:58:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window



Finally my where clause to get the list of values including a blank....as I need all values evaluated.

( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )

Open in new window

0
I have a dataset like the following:

 select row_number() OVER (order by t1.cTitle) as titlerow,t1.cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by t1.cTitle
			 

Open in new window



This creates as dropdown on report server...

Iit is a "MULTISELCT"   with first value blank, and then all the distinct values.



Now my stored proc where clause for this is the following:


t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') )

Open in new window




When I do this...I can still get the value..but not when its ''


here is my split function which I think might ignore the leading ''

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 6:14:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window

0
I have a select statement...here is very simplified version.


Select
value1
value2
value3
value4
value5

from table

---these values are bits


I need to be able to have a dropdown in SSRS that uses the friendly value of each of these values in one dropdown.  so the dropdown might be

label1 of value1
label 2 of value 2
lable 3 of value3

etc.


Then my parameter passed would be one or more of these values in the select.

Since they are not pivoted and in one column Im not sure how to do this.

im guessing I need a cte of these values..  There are about 20 to be exact.
So that means 20 items in the dropdown..and 20 columns in the select

Please help.
0
I have a bunch of dropdowns on an SSRS report.

Each drop down is basically a distinct list of values.

Each drop down is a multi select drop down.


Here is the issue:

If a null or blank value is in the recordset...or the distinct list pulls nothing back in the dropdown...

I need the parameter passed to allow all rows to be sent....or perhaps I need an option to allow in the multiselect for the user to pick a blank or null value.

This way when the user selects the next set of dropdowns a value will be returned.

I dont have a way that I can athink of to make each dropdown based on the other.
0
[Webinar] Cloud and Mobile-First Strategy
LVL 11
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

what is the best way to do this.

I have a report in SSRS that will have a drop down  that is Active/Inactive/all.


The column in the stored proc to evaluate would be

lactive = 0    (Inactive)
lactive  = 1   (Active)

But the user woudl like to let the user select all so that both would be in the query as well.


Thanks,
0
How can I restart SSRS Service from other server using command prompt?
 When I try to restart from the server I am getting below message

" Windows could not stop SQL Server Reporting Service"
Service cannot accept control messages at this time
0
I just migrated an SSRS 2005 report to SSRS 2016 instance. The report that was printing on a single 8.5 x 11 page now prints on 2 pages. There is box at the bottom just above the footer that displays raw text comments that are stored in the database. The textbox was 1 inch tall and shrunk it to .5 to get it to fit on the page, but then the SSRS would print a blank page when printing to a printer.

In SAP BI, there is an option to scale all content in the report body to 95% or less and that is a trick we have used to get all the content to print on a single page without having to completely redo the layout.

Does SSRS 2016 report builder have this option? I have not found it.
0
=RIGHT(Fields!araccountname.Value, 2)="LF"?"1":"0"
0
Hi Experts,

I have some labels need to be printed in Avery format. I can create it in 2 columns and 5 labels in each column. But I can not print them exactly at the space provided for the label.  Any suggestions??

Thanks in advance.
0
When we convert Excel to text file that time it capture same things which is in EXCEL but not all values.
Scree Shot of Excel.TEXT file1414-001-2017-10-23.txt
1414-001-2017-10-23.xls
0
Hi,

I having little bit experience on SSIS,SSRS & SSAS. I want to get advanced knowledge on this.

Please provide some realtime scenarios with solutions?
0
I have a financial report that includes 3 fields that I'm trying to sum.  I only display a grouped total - not the detail.  One field sums a planned gift and both the grouped total and the final total works fine using sum(amount).  One of the fields is the asset balance and one is Net Present Value that is calculated on the report side.  All of the grouping totals work, but when I do the final totals I have a variety of problems.  I've listed some of the things I've tried and what the result is... sometimes it is an amount that is incorrect and sometimes it is an error.  The code I'm using (which I know very little about) is at the end of this.  

sum(asssetbalance)  - is the wrong amount - it is way too large (it should only sum the first asset balance)
sum(first(assetbalance)) - error that aggregate functions cannot be specified as nested aggregates
=Code.GetABTotal() - is the wrong amount - it appears that it totals each page, not a grand total
=sum(ReportItems!textbox72.Value) - gives me an error uses an aggregate function on a report item

I read that that you have to move the =sum(ReportItems!textbox72.Value)  to a footer so I tried that as well and it once again appears that it is totaling the page and not providing a grand total.  

Here is the code I tried and is for both fields that I'm trying to total:

Public Dim FVTotal as Double = 0
Public Dim ABTotal as Double = 0

Public Function AddFVTotal (Amt as Double) as Double
      FVTotal = FVTotal + Amt
      …
0
I have a trend defined in SSRS 2008 R2.

Under the Chart Data dialogue:
The value is defined as Sum(Result)
The category group is Result_On
The series group is Metric

My problem is I cannot seem to find the right expression for the Min/Max on the Vertical Axis Properties.
They are currently set to Min(Result) and Max(Result) respectively, but this does not work, as it does not sum the multiple Results for a given Result_On and Metric:

Sample Trend Issue
I would effectively like Minimum to be Min(Sum(Result)) over the Result_On category, and then take the min over all Metric series.
Similarly, the Maximum should be Max(Sum(Result)) over the Result_On category, and then take the max over all Metric series.

Is this do-able either through the Min/Max expressions, do I need to use ReportCode, or is this better done somehow in the database in SQL?

Thanks,
Jeff
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Please find the attachment .
I want the output like image Capture345.
Capture123.PNG
Capture345.PNG
LEDES-1998B.rdl
0
Hi,

I want to see the chart for the specific SQL report. But it is not displaying. Here i have attached the screen shot.

Thanks

Zahid
RPT_Design.PNG
Report.PNG
0
I have a report that needs to be run for 1 of 2 databases. The (2) databases are identical in structure, but not in data.

We have a company database that is identical in structure for 2 clients. I will call the clients "Coke" and "Pepsi" for this example.

I want the user to have a drop down in the report so the user can select to run the report for Coke or Pepsi. The stored procs that create the datasets exist in both Coke and Pepsi databases.

Is this possible or am I stuck managing 2 separate reports?
0
cumulative update to sql server 2016 express causes ssrs to fail with error about scale-out deployments not being supported.  any help appreciated in getting around this error.
0
I hope to spend some time digging into SSIS and wonder what are the most useful aspects of SSIS?

Thanks
0
Hi Experts,

How Can I display the group name in the table header. I have attached a screen shot. I need to move the circled text to the yellow shaded area. Any suggestions?

Thanks in advance.
Delivery.png
0

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.