Advertise Here

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

SSRS

Solutions

Content

Contributors

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.

SQL Server 2014 - optimize query

Hi Experts,

I have this bit of code:

,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail]  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchFail
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchPercent
,(Select count(*) as [All] FROM CTE_Compliance as di1 where di1.[Branch

Open in new window

…

4 Comments

I need to make SSRS 2012 reports available to users on a mini ipad.
What would be the best way to implement?

1 Comment

I have a large report developed in Visual Studio 2015. I am using SSRS 2016. I can run the report in VS 2015

This is the exact msg when deploying to SharePoint

but when I try to deploy to Sharepoint 2013 I get the following error msg:

Native compiler return value: '[BC30494] Line is too long.'

1 Comment

I had to make some major changes to an SSRS report including the field I was sorting and grouping on. I keep getting the following error for 6 different fields. Those fields that it references are no longer in the data file or on the report. I've checked all of my sorts, all of my fields that I'm using, all of the shading that is being done on some of the fields and cannot find any place where these old fields are being used. The error refers to AskDate, but AskDate is not used any place... Any ideas?

Error 4 [rsFieldReference] The SortExpression expression for the grouping ‘table1_Details_Group’ refers to the field ‘AskDate’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. R:\RE\Reports\Solicitors\Pipeline Summary\Proposal Pipeline New.rdl 0 0

SSRS

4 Comments

Hi Experts,

I am generating some labels as shown in Label1.png. My design is in Labeldesign.png. My client needs the output in two columns as shown in Label_Desired.png. Please suggest is there any way to do it?

Thanks in advance.
Label1.png
LabelDesign.png
Label_Desired.png

4 Comments

I have a table which has FirstName,LastName columns and i need to display the result like Names must be entered Last name , then a comma, followed by First name (e.g., "Arnsley, Robert"). I want to handle this on SSRS reports and tried like that but not working =Fields!LastName.Value, Fields!FirstName.Value

SSRS

2 Comments

Hey guys-
I have a sql server that runs a few apps but is also our main reporting server. the apps aren't heavy loads on the sql database.

is there a way to tell which reports are causing all the cpu usage? do I have to find the query behind the report in activity monitor on ssms or is there another way.

basically, whats a good way to figure out what's killing our server here.

Update... this is an edit. the spike occurs when I go to the SRSS landing page as a user from internet explorer

0 Comments

On Demand Webinar: Networking for the Cloud Era

LVL 10

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

LVL 7

Question by

Jeff S

2017-09-12Solved

Passing two parameters in SSRS report to get pop up window - error.

I am trying to pass two parameters through my report to get a report window to pop up. I am getting the following error and cannot see my issue. Perhaps tired eyes and could use a hand.

Name 'PATIENTVISITID' is not declared.

="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"

&"/Billing Reports/Jeffs/Testing Folder/Sub_rdl_Detail_QuestionAnswerChecklist&rc:Parameters=False&rc:Toolbar=False
&PATIENTPROFILEID="& Fields!PatientProfileId.Value & PATIENTVISITID="& Fields!PatientVisitId.Value &"
','DetailsWindow','left=300,top=180,width=850,height=365,status=no,menubar=no,scrollbars=yes,location=no'" & ")"

Open in new window

8 Comments

I have 2 problems, but they are inter-related.

MS CRM 2013 on premise, all functions on a Windows Server 2008 R2 VM with the SQL DB on a separate Windows Server 2008 R2 server.

1) Report Server not working as expected
2) CRM workflows stopped working.

CRM 2013 has been installed and working fine for years. About a month ago they wanted to start using reports, but the report server was never used or tested before. Tried to do some subscriptions and got the error message about credentials not stored. So I researched and at that time the SQL server was SQL 2008 standard. The requirements stated it must be at least SQL 2008 R2. So on Sept 4 I upgraded the SQL server to 2008 R2. Everything still working as normal after that but still no reports or subscriptions. Further research suggested that both the CRM server and the SSRS needed to be at 2013 SPM U4. The Server was 2013 SP1 U3 but SSRS was only 2013 SP1. So Friday afternoon I installed 2013 SP1 U3 on SSRS but it prompted for a restart. Well can not do that so waited until Saturday morning. At that time I restarted the SSRS server then installed 2013 SP1 U4 on the CRM server and on the SSRS server and both were restarted.

So, Monday morning I get an email saying all workflows failed after Friday afternoon. I was told this AFTER I had been attempting to get reports to work by making some changes to the SSRS via the SQL Reporting Services Configuration Manager.

So, I need urgent help. First to get the workflows …

14 Comments

This "Go To URL" in my SSRS report textbox works fine except it does not pass the text box value.
&SuffixSearch=Fields!Custno.Value (this does not pass the textbox value)

What am I missing ? How do you pass the actual textbox value ?

Entire URL:

https://sqldevrs01.cn.ca/ReportServer/Pages/ReportViewer.aspx?/AGILERPTS/RMReports/ARInvoiceDetail&ProblemGroupSearch=&CCIDSearch=&SuffixSearch=Fields!Custno.Value&CustNameSearch=

SSRS

9 Comments

Question by

mburk1968

2017-09-06Solved

Query syntax help with a Join

I have the following query which pulls back details per style for how many units that are available to sell.

The query pulls 5 units where Size_Ind IS NOT NULL

DECLARE @OTSOVR CHAR(3);
SET @OTSOVR = 'OTS';

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT DISTINCT LTRIM(RTRIM(cs.group_code4)) + '-' + LTRIM(RTRIM(cs.STYLE)) AS Scale_Style ,
        ISNULL(zzxexfvr.stor_file, ' ') AS Img_File_Name ,
        cs.Scale_Name ,
        cs.group_code4 ,
        cs.SeaSyrScale ,
        cs.STYLE ,
        cs.EndLocation ,
        SUM(CONVERT(INT, CASE @OTSOVR
                           WHEN ( 'OTS' ) THEN ( cs.OTSPlan )
                           ELSE ( cs.OTSOpen )
                         END)) AS OTS ,
        cs.COLOR_CODE ,
        cs.lbl_code ,
        CONVERT(DECIMAL(10, 2), cs.std_cost) AS std_cost ,
        cs.Size_Ind ,
        CONVERT(DECIMAL(10, 2), cs.a_price) AS std_price ,
        cs.Main_Label ,
        SUM(cs.AvailQOH) AS AvailQOH ,
        SUM(cs.QOH) AS QOH ,
        CASE WHEN ( EXISTS ( SELECT *
                             FROM   zzxrangd AS Rg
                             WHERE  ( Rg.style = cs.STYLE )
                                    AND ( Rg.color_code = cs.COLOR_CODE )
                                    AND ( Rg.lbl_code = cs.lbl_code ) ) )
             THEN ( 'Y' )
             ELSE ( 'N' )
        END AS RangeComponent ,
        cs.season ,
        cs.Classification ,

Open in new window

…

3 Comments

I am completely new to SSRS and I have been asked to learn it on my own and transfer all weekly reports to SSRS (eventually). As I built my first report with help of online tutorials I was hit with a warning: "The report or subreport has a recursive subreport 'SubReport1' that exceeded the maximum recursion limit allowed." I am not sure what it means so please dumb it down for me.
Here is what I did - I created a brand new report, defined datasource, datasets based on a SQL query that runs without any problems in SQL. It has 4 columns and rows mostly contain text and numbers and there are no additional operations after the report is run. When I run the preview, it runs and shows me all the rows but gives me a warming as stated above. The query has 150 some rows so it is not huge but I also know at times this query will return thousands of rows.
How do I fix this? I can't seem to find much on google.
Thank you in advance.

SSRS

0 Comments

I am running SharePoint 2007 with Reporting Services. I am trying to dynamically set the reports data sources.

I am able to view the ReportService2010 at https://servername/sitename/_vti_bin/ReportServer/ReportService2010.asmx

In a C# Console application, I have set a reference to ReportService2010.

The credentials I am using are correct and have all the required permissions.

The path to my .rdl file is "https://servername/sitename/Coupons/MyReport.rdl" - if I put this into my browser the report comes up with no issues.

The path to the data source is "https://servername/sitename/Coupon%20Connections/CouponDataSource.rsds" - if I put this into my browser the report comes up with no issues.

The code in my console application is below.

So not matter what value I give to the variable "string path" - I get the same error - "Server was unable to process request. ---> Client found response content type of '', but expected 'text/xml'.\nThe request failed with an empty response."

It seems that the path I am specifying for the .rdl is not being found. Should the "itemPath" parameter for SetItemDataSources or GetItemDataSources be something other than "https://servername/sitename/Coupons/MyReport.rdl"?

   ReportingService2010 client = new ReportingService2010();
           
            client.Url = Settings.Default.ServerUrl + siteName + "/" + reportServiceUrl;
            client.UseDefaultCredentials = false;
            client.Credentials = new

Open in new window

…

0 Comments

Question by

red_75116

2017-09-05

Reporting Service Datasource issue

I am having trouble with an installation of reporting server where I have chosen to have the credentials stored securely in the report. The user I have entered is a domain admin account, but when I test the connection it takes about 30 secs to return the Green connection created successfully. This is causing all reports to run incredible slow. When I change to windows integrated or run the report via SSDT then all reports run in about 1 sec. Sql has about 75 gb of ram on a server with 100 gb of total memory. What could be causing this issue? Is this account messed up? Should I try another domain account? Any way to test?

1 Comment

I'm using SSRS 2012. I'm trying to create a report that has 3 separate TABLIX's on a single page and each Tablix has a separate data source.

I have made 100's of SSRS reports but this one has me stumped. I have 3 tables each with CUSTOMER_ID and STATE_ID. Besides CUSTOMER_ID and STATE_ID, the tables are unrelated. In the end I want to select and STATE and then print all the CUSTOMER data for the state - 1 page per CUSTOMER - 3 tables.

I could join the 3 tables together by the STATE and CUSTOMER ID's but it would be many-to-many relationships. I don't know how I would then cleanly single out data for each of the three tables. (I've tried this approach.)

A second way, which I would prefer, and seems it would be the cleanest way would be to have three TABLIX's and three unjoined data sources. I would like that but how would I print/keep together the same CUSTOMER ID for each page when I select a STATE. For example, I select TEXAS and want to print all the CUSTOMER data for each of the three TABLIX. In other words, I would need to print CUSTOMER_ID = '1' for each data set then move to the next CUSTOMER_ID for Texas.

I hope that makes sense. It doesn't seem like it should be this hard. Any suggestions on how to approach this would be appreciated.

Thanks.

SSRS

3 Comments

Whenever the details per header go beyond 1 page...I need to repeat the header with "Cont'd'..I can't figure it out
ESL_Consolidated_Job-Trial-Balance.rdl

6 Comments

Free Tool: Path Explorer

LVL 10

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.

Question by

HDM

2017-09-05Solved

Reporting services 2008 R2 update report

I have reporting services 2008 r2.

I have an active report that I need to update. I do not believe I have a copy of the original project.

Can I down load the report, make the update, then redeploy it??

SSRS

4 Comments

Question by

Mayank Tripathi

2017-09-05Solved

SSRS rendering issue in IE 11

I'm using SSRS 2016 and facing rendering issue in IE 11 (one e.g. Drilldown button is missing when we browse reports via IE 11 ). It works fine with Chrome, but our end users preferred browser for SSRS reports is IE 11. Is there anything can be done at server level to fix the rendering issue.

Your help is highly appreciated.

3 Comments

I have multiple datasets in Report Builder 3.0.

I'm trying to calculate the following:

=Code.Divider(Fields!Amazon2016.Value + Sum(Fields!Amazon2016.Value, "DataSetOpen") - Fields!Amazon2017.Value + Sum(Fields!Amazon2017.Value, "DataSetOpen")   / Fields!Amazon2016.Value + Sum(Fields!Amazon2016.Value, "DataSetOpen") )

Open in new window

Textbox.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Divisor' of 'Public Function Divider(Dividend As Double, Divisor As Double) As Object'.

SSRS

3 Comments

Hi all,
I'm joining a new web server to an exist farm, everything works well, enabled report sync and SQL Reporting service on the new Server.

The new site on new server has every items exactly like the current one, but when i click on any report, item, it shows "item "http://servername/report1.rdl" not found".

Even when i tried to create a new datasource on any library on the new site, it shows "http://servername/libraryname" not found. But if i create new other document like image on that same library, its ok.

I really appreciate your help! Thank you!!!

0 Comments

I am using some columns in Row Group of Table, When I am trying to hide the columns based on some conditions. The Column Visibility Property is in disabled mode. Is this Possible to apply hide/Show Property on Row Grouping Columns in SSRS?

SSRS

2 Comments

No matter what I have tried from internet searches, my SSRS report date fields will show as General or Custom (in excel Number Format) cells when exported to Excel.

User wants cell's number format, in excel, to show as a Date not General or Custom.

Any idea's ?

15 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

SSRS

Solutions

Content

Contributors

COURSE of the MONTH

SSRS

Related Topics

SSRS

Related Topics