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 a user getting the attached error when trying to access a SQL server reporting web page.  

I login to the SQL server and launch the web page as an administrator.  I see the user already has System User access in the site permission.  

Our SQL admin is not available and I need to resolve this issue.  If you know where I should check, please advise the steps.    

Thanks in advance.
ssrs-error.png
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi Experts,

My customer needs a report like this(Please see the attached report). How to create something like this. I have all the Data. I am using ASP.Net, VB.Net and RDLC reports. Please help.

Thanks in advance.
BGMenu.png
0
I have three parameters. if ShowCost, ShopPrice & ShowETA are all set to N then hide the row. I've tried several examples and each throws an error.

Thank You
0
I am trying to render a SSRS report to csv file, but for some reason  it comes out as TAB separated instead of comma separated.

This is the code:

            byte[] result = null;                  
            string historyID = null;                
            string devInfo = null;                
            string encoding;                      
            string mimeType;                        
            string extension;                      
            Reservice.Warning[] warnings = null;    
            string[] streamIDs = null;            

            _rsExec.LoadReport(rptPath, historyID);
           _rsExec.SetExecutionParameters(parameters, "en-us");
           result = _rsExec.Render("CSV",
                    devInfo,
                    out extension,
                    out encoding,
                    out mimeType,
                    out warnings,
                    out streamIDs);

                FileStream stream = File.Create(_excellocation, result.Length);
                stream.Write(result, 0, result.Length);
                stream.Close();

When I render to Excel there is no problems.
Thank you
0
Hi,
I am having an issue with my dev server. In my dev server SQL 2014, SQL 2106 is installed and also both reporting service is installed too. I have created a couple of reports by using SQL server data tool 2015. but I want to publish that report in a native mode of SQL Server Reporting Service 2016. Once I try to configure reporting service in "SQL Server 2016 Reporting Service configuration manager" I am getting this message "Unable to connect to the Report Server VA1-HGF8374 ".

Need some good answer.

Thanks,
Rashed Hossen
0
I am trying to render report to csv file, but for some reason  when I open the file in Notepad looks like columns are separated by TAB , not comma. When I open the file in Excel all columns  come as one:
208438-1      7/11/2017      9:30 AM      Eric Sherman      Eric Sherman      "300 E 56th St

This is the code:
            byte[] result = null;                  
            string historyID = null;                
            string devInfo = null;                
            string encoding;                      
            string mimeType;                        
            string extension;                      
            Reservice.Warning[] warnings = null;    
            string[] streamIDs = null;            

            _rsExec.LoadReport(rptPath, historyID);
           _rsExec.SetExecutionParameters(parameters, "en-us");
           result = _rsExec.Render(_invoice.ExportFileFormat,
                    devInfo,
                    out extension,
                    out encoding,
                    out mimeType,
                    out warnings,
                    out streamIDs);

                FileStream stream = File.Create(_excellocation, result.Length);
                stream.Write(result, 0, result.Length);
                stream.Close();

Thank you
0
I have 2 columns (A&B) and I want to group these 2 columns data , however I do not have any thing common in these 2 columns to group.
So users request is to see 1 column with A&B column data grouped and they should be able to un group the data if they want to see data individually by clicking on toggle button.

How can we achieve this?
0
Hello,
i'have question about reporting service reportservices,I'm trying to connect to the reporting  web service sql server but an exception is raised, password or user are wrong error 401 unauthorized .
But i'm sure that they are correct this is how i use it :
I have imported a wsdl and i have got reportservice2005.pas.
Why i can't Authentify knowing that i can with browser how to use the methode LogOnUser() ? or THttpRio? to authtify.
this is the procedure i'm using :
procedure TfMenu.Button1Click(Sender: TObject);
 const
   defURL  = 'http://khali-pc:80/ReportServerSQL2016/ReportService2005.asmx';
var
   Report : CreateReport;
   ReportingService :  ReportingService2005Soap;
   stream           :   TFileStream;
   tailleBuffer,I     : Integer;
   UserLog          : logonUser;
   Rio1              : THttpRio;

begin
 
 Rio1 := THttpRIo.Create(self);
   Rio1.HTTPWebNode.UserName      := 'SomeDomain\SomeUser';
   Rio1.HTTPWebNode.Password      := 'xxxxxxx';

ReportingService := GetReportingService2005Soap(false, defURL, nil) ;

/////********** Second way to authenificate*********************// PS: and the first way to athentificate is to include password and user in RIO (THttpRio) above

/// second method to authetify to SSRS
//   UserLog                  :=       LogonUser.Create;
//   UserLog.userName  := 'SomeDomain\SomeUser';  
//   UserLog.password   := 'xxxxxx';
//   UserLog.authority     :='' ;
//   …
0
Hello All,

We have SQL 2014 with Ent edition and our DB size is 340 GB. Can you please review and suggest below SQL memory settings are correct or do I need to change min or max memory settings.

Physical memory : 256GB
Min memory: 64000 MB
Max memory: 216000

We have dedicated DB (VM machine) and on same DB we are running SSIS, SSRS.

In DB server-->task manager-->we always see sql server management studio.exe, the memory utilization is 98-99%. Is there an way to reduce this value by memory settings?

Thanks in advance.
0
Guys,
There is one system stored procedure in Reportserver use to update the subscription where we can update the "TO" in the report delivery option.  but in our ssrs server I have almost 400 subscriptions that I need to delete old email address  and add new email address  at "To" field.
so instead of doing this one by on in SSRS - Report delivery option page, can I do this via bulk update T-sql in Reportserver db ? I provide a screenshot so you guys know where I means by "To" in report delivery option page in SSRS.

SSRSReportdeluiveryOption
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I have a Column that calculates Cost for each Record. I need to add a Column that has the Grand Total for all records to be used in a calculation.

SELECT  CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' ) THEN ( 1 )
             WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' ) THEN ( 2 )
             WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
                    OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
                  ) THEN ( 3 )
             WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
                    OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
                  ) THEN ( 4 )
             WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' ) THEN ( 5 )
             ELSE ( 6 )
        END AS SeasonNumber ,
            ISNULL(( SELECT SUM(total_qty) AS qty
                 FROM   zzxinvtr AS PrvIV
                 WHERE  ( division = SC.division )
                        AND ( style = SC.style )
                        AND ( color_code = SC.color_code )
                        AND ( lbl_code = SC.lbl_code )
                        AND ( dimension = SC.dimension )
                        AND ( DATEADD(dd, DATEDIFF(dd, 0, trx_time), 0) <= DATEADD(yy,
                                                              -1, '06/30/2017') )
               ), 0) * ( SC.std_cost ) AS PrvCost ,
            ISNULL(( SELECT SUM(total_qty) AS qty
                 FROM   zzxinvtr AS PrvIV
                 WHERE  ( division = SC.division )
                  …
0
Hi,

Can anyone help me regarding scripts for monitoring SSIS and SSRS (SQL Server 2012/2014/2016).

-- Blocking issues
-- SSIS and SSRS Services status
-- SSIS Packages Performance
-- SSRS reports Performance
-  CPU/Memory/Disk IO utilization.

Thanks,
Chandra
0
Hi,
Please could someone help me write this expression (below) for an SSRS report? When I run this expression in the report it gives me an error saying that the field WIP_ADD_12M_CP is not contained within the current dataset or aggregate...but I've checked the fields and the spelling and it is correct - when running a simple divide it doesn't require the dataset name in there. Not sure why it is not picking up the field..? Does the syntax of this expression look correct? I am quite stumped at this point...

The code below is trying to:
1) Find where UBWIP_VAL_CP.Value is below zero and then make that field 499 and then divide the rest of the calculation x 365
2) Find where WIPADD_12M_VAL_CP.Value is below zero and then make that field 499 and then divide the rest of the calculation x 365
3) Code.divide gets rid of the divide by zero error. Find the overall sum where it is greater than 499 and cap the total to 499
4) Code.divide gets rid of the divide by zero error. Find the overall sum where it is less than 499 and cap the total to 499

The expression I have is:
=SWITCH
(
SUM(Fields!UBWIP_VAL_CP.Value) < 0, code.Divide(499,SUM(Fields!WIPADD_12M_VAL_CP.Value)) * 365,
SUM(Fields!WIPADD_12M_VAL_CP.Value) < 0, code.Divide(SUM(Fields!UBWIP_VAL_CP.Value), 499) * 365,
code.Divide(SUM(Fields!UBWIP_VAL_CP.Value),SUM(Fields!WIPAD‌‌​‌​​D_12M_VAL_CP.Valu‌​e)‌​)‌ * 365 > 499, 499,
code.Divide(SUM(Fields!UBWIP_VAL_CP.Value),SUM(Fields!WIPAD‌‌​‌​​D_12M_VAL_CP.Valu‌​e)‌​)‌​ …
0
So I have a multiple value parameter than contains 3 options. >250K, <250K, >2M. I also have a table that consists of multiple columns. Because the parameter is a multivalue, i am having difficulties filtering the dataset.

I need to filter the dataset by checking, (if > 250K is selected, filter the dataset accordingly), (if < 250K is selected, filter the dataset accordingly) and (if > 2M is selected, filter the dataset accordingly).

I was told to use a join and split on the parameter within the (>250K condition, then do a contains to see if it contains any of the parameter values) but I am not as advanced in my knowledge of coding to be able to do that.

Any Suggestion? Thanks in Advance

I previously tried the method below but then i came to realise that it wont work because the parameter is a multi value.
0
I have a report and when I run the report , data looks good. However when I export it to PDF , Excel , Word or any other format  some of the columns are that have value and are  visible when viewing the report are missing data. I checked Hide property and it is set to False.

What could the issue be?
0
Hi there,
Today I tried to setup a usual subscription for one of my reports, to email out. A fairly regular process that never seemed to cause issue previously.
However, I keep getting a "Login Failed" message.
Unknown user.

I don't know how this could be.
I have setup the subscription directly on the server
the only place I can enter in credentials are under the data source settings.
I have added the username and password that is currently being used successfully for other datasources.
I tried using windows account and it failed, I tried setting up with my own account and it failed, I'm just not sure what I'm doing wrong.

Any advice would be greatly appreciated.
Thanks,
0
I was wondering if you  could help me with this issue.  I am working on security in SCCM 1610 to give certain users access to report information for  their specific department needs.    
I have created a test user, created Security Scope, made a copy of the Report Viewer Only role and added user to this security role.  I am happy with most of the access they are limited to.  
The only reports that I cannot restrict their access to are the MBAM reports.  The user is able to see all the collection ID's listed rather then just the collection ID for their department.   Is this because the MBAM reports are not native SCCM reports but added when installing MBAM on SCCM so the security policies don't applied to these reports?
Can you tell me how I can restrict these reports to only show information for the collection/collection id's they need to see?  
Thank you.

MLStev
0
I need to give access to reports to users that are NOT in Active Directory. Is this possible?

SSRS 2016 Standard Edition on Windows 2012
0
I am actually looking for integrating SSRS to AD so all the access to the reports must be allowed windows integrated credentials (system login credentials). I mean reports URL should not ask windows credentials again, once user login to the domain.
0
Want Experts Exchange at your fingertips?
LVL 9
Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

I have an SSRS Report with a Matrix and a subreport.  I have my row group header property set to
KeepWithGroup = After
RepeatOnNextPage = True
however when my subreport continues on to the next page, I do not see my main report headers.
I have experimented with all different values of KeepTogether, KeepWithGroup, and RepeatonNextPage, but I can't get it to display.
0
How do I decrypt the user name in table subscriptions; column ExtensionSettings?

I would like to query all data driven reports for a specific user name however the user names are encrypted!

SELECT
CONVERT(VARCHAR, Convert(XML,[sub].[ExtensionSettings]).value('(//ParameterValue/Value[../Name="USERNAME"])[1]','nvarchar(50)')) AS UserName
FROM dbo.Subscriptions


Thanks!
Mitch Small
0
Hi Experts,

I want to display the "number" with "Unit"  for ex. 12 oz. I am using the expression "=Fields!AdjustedPan.Value & Fields!Unit.Value" on that textbox. It displays 12oz. I need and space in between. So I tried ""=Fields!AdjustedPan.Value & "  " & Fields!Unit.Value". But It does not work. What can I do to give a space between the number and the unit. Also need a "." at the end( Like 12 oz.) .

Thanks in advance.
0
We have a very large SSRS ReportServer db in excess of 1.4TB and the majority of this is the Segment table circa 940GB

The users make use of snapshots and ad-hoc reporting

When I try to link the segment table back through the SegementChunkMapping using SegmentId I have approximately half of the Segment table rows not found.

Does this mean they are orphaned and can be deleted or is it expected.

Also when I try to use Catalog and History and link all of the chunkData items back to see the sizes of the reports in the db, I can only find about 350GB which is considerable less thn the overall sie of the DB.

It feels a if system maintenance hasnt been functioning properly as I always believed SSRS looked pretty much after itself

any help welcome
bob
0
experts,

I have many sharepoint sites (3000+) that I'm  thinking about making use of the tasks lists. Each administrator has several hundred sites. Each admin could have a number of tasks spread across each of their sites. I'd like to give them a way to see all tasks that they have assigned to them across all of their sites instead of them having to visit every site to see what's assigned to them. Has anyone done anything like this before? What's the best approach to take? I'm toying with utilizing a SSRS report,  maybe a webpart, or maybe a custom application that queries the sites. I'm not sure though.

Any guidance would be much appreciated.

Thanks in advance,
0
I have a report that is ran once a month that gets exported into Excel, then two additional formulas are performed.  I get the Max and Median on (3) different columns on each row. I have no idea how to perform these in SQL and would like some help.

Using SQL 2012 - SSRS 2012

The 3 columns I want to show the Max and Median are labeled [3month avg], [6mth avg] and [12mth avg]
I want these to calculate for each row, NOT a grand total.  So when I run the report, I want 2 additional columns to appear with the Max and Median to show for each row.

I have copied the current SQL query into a word document and attached it.
SELECT     p21_view_inv_loc.location_id, p21_view_inv_loc.item_id, p21_view_inv_mast.extended_desc, p21_view_inv_mast.net_weight, 
                      p21_view_inv_loc.moving_average_cost, p21_view_inv_loc.stockable, p21_view_inv_loc.inv_min, p21_view_inv_loc.inv_max, p21_view_inv_loc.product_group_id, 
                      p21_view_inv_loc.purchase_class, COALESCE (derivedtbl_1.[3 month sales], 0) AS [3mnth sales], COALESCE (derivedtbl_1.[6month sales], 0) AS [6mnth sales], 
                      COALESCE (derivedtbl_1.[12 month sales], 0) AS [12mnth sales], ABS(COALESCE (derivedtbl_2.[3 month prod ord], 0)) AS [3mnth prod ord], 
                      ABS(COALESCE (derivedtbl_2.[6 month prod ord], 0)) AS [6mnth prod ord], ABS(COALESCE (derivedtbl_2.[12 month prod ord], 0)) AS [12mnth prod ord], 
                      COALESCE (derivedtbl_3.[3 

Open in new window

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.