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
Announcing the Most Valuable Experts of 2016
LVL 6
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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
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
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
[Webinar] Learn How Hackers Steal Your Credentials
LVL 9
[Webinar] Learn 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

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 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 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
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
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
Hi Experts,

In my RDLC report, when I group by Column, it displays only one record. I am not using "First" function anywhere. Please help what could be the reason.

Thanks in advance.
0
I'm looking for some SCCM/SSRS expertise here. What I am looking for is a query that will return the computer name and the count of approved/deployed updates that are not installed. This would provide a quick reference to see what workstations/servers are having issues installing updates.

I found this report from Eswar Koneti (https://gallery.technet.microsoft.com/SCCM-Configmgr-SSRS-Report-87d7dd17) which does what I'm looking for. However, When I point it at my "All Systems" collection it only returns ~3,500 rows and the collection has just over 5,000 members. I have also confirmed that some of the missing devices from the report are missing approved updates.

Any help would be appreciated!

SQL Results
All Systems Collection
select vrs.resourceid, vrs.name0 [PC Name],vrs.User_Name0 [User Name],os.Caption0 [OS],convert(nvarchar(26),WS.LastHWScan,100)[LastHWScan],
convert(nvarchar(26),uss.lastscantime,100)[LastSUScan],
case when uss.LastErrorCode='0' then 'Yes' else 'No' end as 'LastSUScan Success?',
case when DP.isRequired!=0 and REQ.Deployed!=0 then 'No'
     when DP.isRequired!=0 and REQ.Deployed=0 then 'Yes'
     when DP.isRequired=0 and REQ.Deployed=0 then 'Yes' end as 'Compliant ?',
REQ.Deployed [Approved],
DP.isRequired
from v_r_system vrs
  join v_ClientCollectionMembers ccm on ccm.ResourceID=vrs.ResourceID
  join v_GS_OPERATING_SYSTEM OS on os.ResourceID=vrs.ResourceID
  join v_GS_WORKSTATION_STATUS WS on WS.ResourceID=vrs.ResourceID
  

Open in new window

0
Hi Experts,

I have a simple report. Just grouping  By Column "[ComponentName]" and by Row "School". When I run, it is displaying weird.  The second component quantities should display next to the first. Instead it starts from second line. Same with third and fourth.

Please help.
Production1.png
Production_Design.png
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

data in SSRS shows like this as below-
Can someone please help me how to get the totals as for BB-1,BB-2 ,Ac-1 ,Ac-2 below -

Country          Type                    Names        Aug 2015    Sep 2015            ..................................... Jan 2017
US                    A                           BB-1             1000            10                                                                 100
                                                       BB-2             10                100                                                               50

US                    B                           AC-1              10               20                                                                 0
                                                      AC-2               5                  0                                                                  100

IN                    A                            BB-1             100             100                                                               20
                                                      BB-2             100              5                                                                   10

IN                    B                            AC-1              100            10                                                                50
                                                      AC-2               50              20                                      …
0
I am trying to create an expression that will give me the number of workdays in each month.  My table has a row for each month.  So, i am looking for a way to get the first day and last day of any given month.  Once i have that i have a function that will tell me how many workdays are between two dates.  Below is what i am using for the current month and it is working.  I just need to get this for past months.

=iif(month(today())=Fields!Month.Value, (DateDiff(DateInterval.day,DateAdd("d",-(Day(today)-1), Today),today())+1)
- (DateDiff(DateInterval.WeekOfYear,DateAdd("d",-(Day(today)-1), Today),today())*2)
- IIF(Weekday(DateAdd("d",-(Day(today)-1), Today),1) = 1,1,0)
- IIF(Weekday(DateAdd("d",-(Day(today)-1), Today),1) = 7,1,0)
- IIF(Weekday(today(),1) = 1,1,0)
- IIF(Weekday(today(),1) = 7,1,0),999)
0
Hi Guys,
i have a report where i have 4 different row groups , which are child groups of each other in a Matrix report. i want to display the grouping based on parameter inputs . but when i set the visibility based on a expression in row group properties all the rows disappear in the report.
and If i try to write a expression in visibility setting of Rowgroup column properties it hides the column but then leaves a white space. I really need help to fix this issue any suggestion will be really helpful.
0
Recently I split our all staff security group into 2 different groups.  I use those security groups to grant access to SSRS reports on the web.  I added all of the same rights to the new security group and everyone in that new group can run every report but one.  It happens to be a linked report - the person will run one report first to get summary information and then they click a fund to go to a detailed report of the income and expense for that fund.  When anyone in the new security group clicks that hyperlink they get "The permissions granted to user XXX are insufficient for performing this operation".  I have checked all the security settings within the website folders and have gone back to SQL and made sure both security groups have exactly the same rights.  I don't have a clue what is wrong.   Can anyone give me some guidance?  

BTW, I moved one person back into the original security group and she could once again run the hyperlinked report.  Something must be different, but I can't find it.
0
I have deployed my code and its working fine on all Environment accept Test Environment and only for two pages i am getting this error (error has occurred during report processing. (rsProcessingAborted)) can you please tell me why this is happening only for 2 pages. If there is issue in the code then it should give errors for all the pages and on all Environment. But its just throwing an error for 2 pages for other its working fine. I have attached Error Log. Can you please help me.
ErrorLog.txt
0
WHY???? Once again Microsoft and their disgusting lack of personal service to customers rears its head.  Ridiculous
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.