Advertise Here

SSRS

Solutions

Articles & Videos

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.

Related Topics

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

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)

SSRS

2 Comments

I'm trying to have all dollar signs line up left aligned (in my $amt text box) while the values are right aligned without using 2 fields.

Here is an example:

$ 6,123.00
$ 1.00
$ 12.22
$ .35

Is there a way to do this ?

SSRS

4 Comments

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.

SSRS

0 Comments

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.

SSRS

2 Comments

I am trying to create a new data source in SSRS to connect to a csv file. Below is my connection string and i am getting a "Could not find installable ISAM" error message. I am using OLE DB as the Data source type.

Connection String
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\SERVER\FOLDER\SUBFOLDER;Extended Properties=”text;HDR=No;FMT=Delimited”

3 Comments

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

1 Comment

WHY???? Once again Microsoft and their disgusting lack of personal service to customers rears its head. Ridiculous

2 Comments

I have an SSRS report that when I view in a Report Viewer on the site 2 blank spaces are reflected. When I export to PDF the blank spaces are not reflected. I have looked at the source code and don't see what could be causing the spaces. Any suggestions?

Source Code

<%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="CTG.aspx.vb" Inherits="Operations_CTG" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
    Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
     <ajaxToolkit:ToolkitScriptManager ID="toolkitScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>
  <h1>Crafted To Go (CTG)</h1>
    <asp:ValidationSummary ID="ValidationSummary1" runat="server" 
         ValidationGroup="Run" />
  <table style="width: 100%;">
        </table>
        <table style="text-align:center;">
        <tr>
        <td><asp:TextBox ID="txbgn" runat="server">Begin Date</asp:TextBox> <ajaxtoolkit:CalendarExtender ID="txbgn_CalendarExtender" runat="server" 
        TargetControlID="txbgn"></ajaxtoolkit:CalendarExtender></td>
        <td><asp:TextBox ID="txend" runat="server" TabIndex="1">End Date</asp:TextBox>
    <ajaxtoolkit:CalendarExtender

Open in new window

…

SSRS

5 Comments

Hi Experts,

How can I display the group header, on the page header of each page. I have attached the screen shot. In the page2.png, My group header is "Chalk - Clybourn" so my page header is "Chalk - Clybourn". In the second page page3.png my group header is "test - test " how can I make the page header the same. Actually I want the school name and its address on every page. I already set the "Keepwithgroup" to "After" and "repeatOnNewPage=true". It did not help.

Thanks in advance.

Page1.png
Page2.png
Page3.png

0 Comments

I have deployed two SSRS reports to a web page. I have a date parameter that must be entered before the report displays. If I enter a date and press View Report, the report displays as it should. If I change the date and press View Report, the data does not change. If I press View Report again, the data changes. If I close the report and open it again and type in a different date, it gives me the last report data before I closed it. It acts like it has a copy of the old report render in memory or the parameter. In development in visual studio, it doesn't do this. I have tried editing the settings on the web page going into Manage but nothing I have done works. I have set the parameter to have no default value and it can't be NULL.

Thanks

11 Comments

Enroll in June's Course of the Month

LVL 8

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

I have 2 tablix's that are identical. When they both are connected to the same dataset, the export is clean. When I change the dataset on one of the tablix, the export is ugly.

2 Comments

I'm currently experiencing severe event log spam in a new SfB environment, consisting of two sites containing 1x FE/Edge/RP each.

the Reverse Proxy is an A10 Thunder, the Edge and FE are just generic SfB 2015 / Server 2012.

The events are exactly as described In this Technet question

An exception was encountered when sending Conference Error Report.

Conference Error Report + Exception Details.
Error = Machine:{FRONT-END-NAME}BrowserId:a10hm/1.0 Join attempted at (UTC):15/06/2017 10:47:49
Error: request context was lostException = at Microsoft.Rtc.InternalJoinLauncherTelemetryProxy.SendCERReport(JoinLauncherAsyncContext asyncContext)

Cause: Failed to report Conference Error Report. Please look through the exception details for more information.

Resolution: Please check if the Data collection agent is running fine & Storage Service is accepting connections.

I can see that this is caused by the A10 (likely heartbeats?), but I can't seem to find any issue that would be causing the error reports to fail.

I've tried:

Disabling Storage / Conferencing / Etc. Services.
Entirely Removing Monitoring from Topology.
Moving users between front-end servers.

however the errors still come in once every 5-10 seconds aprox. without fail.

there's no load balancing in place for the front-end server, and the Monitoring SQL is SQL2014.

this is a standard edition deployment, Any help would be much appreciated.

0 Comments

Guys: I have a simple sql table

Acctid | PrdSrvc1|
================
1001 | $200
1002 | $300

In SSRS how can i use the subtract function to get the value of -100 - Thx

P.S. I am using SQL Sever 2016

1 Comment

I am upgrading a series of applications from SQL 2000 and VB.net 2003 to SQL2014 and Visual Studio VB 2013.

The code all upgrade automatically, but I have to upgrade the reports. I was going along fine until I got to subreports.

When I try to pull data for the subreport I get the error: Data retrieval failed for the subreport Subreport3 located at "path to report" Please check the log files for more information.

I have done some research and found that I need to add subreport processing. I have found and deployed some code like the following.

Public Sub SubreportProcessingEventHandler(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

e.DataSources.Add(New ReportDataSource("AdventureWorksDataSet_Contact", Me.AdventureWorksDataSet.Contact))

End Sub

Private Sub <FormName>_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'
' Other code is here.
'
Dim instance As LocalReport = Me.ReportViewer1.LocalReport

Me.ContactTableAdapter.Fill(Me.AdventureWorksDataSet.Contact)

AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf Me.SubreportProcessingEventHandler
Me.ReportViewer1.RefreshReport()
'
' Other code is here.
'
End Sub

But when I change the e.DataSources.Add statement to reflect the values from my programs, which results in e.DataSources.Add(New ReportDataSource("DSPrtCommSheets", Me.DSPrtCommSheets14.usp_PrtWrittenComms4SalesRep))

I get a compile error of "Error 77 …

2 Comments

I have an ssrs report that is going out by email distribution / subscription to selected internal individuals - works great for the most part. Recently I moved the report and subscription from a SQL / SSRS 2008 platform to a 2014 platform. When the users were getting it within 2008 the report/email would print neatly and almost look like something was doing a 'fit to page'. Now with 2014 the right three columns are viewable and print fine from within SSRS, but when the users print the email distributed copy the columns are truncated. its important for the user to print the report from the email as it is a timed copy (inventory at midnight) instead of printing from within SSRS. so what am I missing? how come if I do the same rdl on 2008 and 2014 do the emails behave so much differently? is there some 'fit to page' option that was set in 2008 by default and not in 2014?

3 Comments

Is there a way to display SSRS pages immediately while long report is still executing ?

I do not want the users waiting until the entire report is completed before they can start seeing the report.

SSRS

2 Comments

I'm working on a stored procedure for an SSRS report and need some help converting the data type for the parameter to a list. My problem is in line 5 and 30. It's not converting the data correctly. Some sample data in r.Nombre would be the following:

Right Wall
Left Wall
Back Wall
Right Side Cabinet

@BeginDate DateTime
,@EndDate DateTime
,@StoreID  varchar (1000)
,@ItemStatus varchar (3)
,@Category nvarchar (512)

AS


SELECT			rdd.StoreID
				, rdd.TransactionDate
				, rdd.itmName
				, rdd.itmID
				, rdd.enable
				, rdd.orderquantity
				, rdd.unidadcasepack
				, st.StoreName
				, r.Nombre
		
						
FROM            RedemptionDataDetailByDay AS rdd 
						INNER JOIN Rubro AS r ON rdd.rubro = r.idrubro AND rdd.StoreID = r.StoreID 
						INNER JOIN Providers AS P ON rdd.ProviderID = P.ID AND rdd.StoreID = P.StoreID 
						INNER JOIN FiscalDatesByDay AS fd ON rdd.TransactionDate = fd.TransactionDate 
						INNER JOIN Stores AS st ON rdd.StoreID = st.StoreID
						
WHERE					rdd.StoreID IN (SELECT CAST(value as int) from dbo.ParmsToList(@StoreID,','))
						AND (rdd.TransactionDate BETWEEN @BeginDate AND @EndDate) 
						AND rdd.enable IN (SELECT CAST(value as int) from dbo.ParmsToList(@ItemStatus,','))
						AND r.Nombre IN (SELECT CAST(value as nvarchar (512)) from dbo.ParmsToList(@Category,','))
						
ORDER BY		rdd.StoreID, rdd.itmCode, rdd.TransactionDate

Open in new window

12 Comments

I have a main page with all the data in a tabular format.

I report dynamically generates data in number of tabs based on parameters with the main page as first tab.

Now when i export to the report and click on any one of the item (row data) in the main page then it should go that particular tab.

I tried DocumentMap - but it creates a separate tab with the links which i cannot use as the client wants to display the data in tabular format.

Also I tried bookmark - but it work only with the first row of the table in main page. On click of other tabs it displays an error message says "Reference is not valid".

Can you please help regarding this.

Thanks in advance.

2 Comments

There is probably a simple solution to this, but I've searched around the various properties within the Matrix and Chart controls and cannot work it out!
Anyway, using BIDS, I have a shared dataset which returns a simple value of the Number of Starters within a company Grouped by Gender. When I add the shared dataset and test using Query Designer, I get the expected results: "F 27, M 36" (columns are Gender, Starters and the field datatype on the SQL side is INT).
However, when I add a Matrix or a chart to the SSRS report, I always get M 1, F 1. It seems that the report is simply returning a count of the genders rather than displaying the starters.

The dataset itself is from a stored procedure in SQL; there are several parameters passed to the SP, but in this case I am passing NULL to most of the parameters apart from the Dates (First Date and Last Date in order to get the number of starters within a date range).

I think I am missing something simple, but have no idea what it could be!

Query designer output

Chart and Matrix output

Chart definition

11 Comments

Free Tool: Port Scanner

LVL 8

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Hello,

I need to create an ssrs report using 2 different tables from 2 databases on the same server. I"m looking for examples on how to to do this. I tried by creating a dataset from a view that I setup that works in SSMS but I keep getting errors when trying to use it in SSRS. Any examples would be helpful.

Thanks.

2 Comments

LVL 4

Question by

Thunder724

2017-06-05

SSRS Matrix Object

Hi all,
I have matrix in an SSRS report (2012) with the following fields....

I am trying to figure out how to sort on the Count column. It is sorting desc on the Person_Name column. the SQL string is ordering by COUNT DESC.

Thanks for anyone's help,
John.

2 Comments

I have the following query that I am using to build a report in SSRS. I have two issues. The first is my PriorGrandTotal for the prior year is not calculating properly. I get all 0.00. In addition I now have a need to calculate the prior year totals by month. As I am doing with the current year. I was initially helped with this query hence why I'm having a difficult time wrapping my head around it.

 WITH   ORD
          AS ( SELECT   ORD.* ,
                        IIF(ORD.ship_date > '19000101', ORD.ship_date, ORD.start_date) AS EffectiveDate ,
                        MONTH(IIF(ORD.ship_date > '19000101', ORD.ship_date, ORD.start_date)) AS EffectiveMonth
               FROM     [KLL All Order Detail With Style Data - Broken Down PPK Dtl]
                        AS ORD
               WHERE    IIF(ORD.ship_date > '19000101', ORD.ship_date, ORD.start_date) BETWEEN @BeginCancelDate
                                                              AND
                                                              @EndCancelDate
                        AND ORD.line_status IN ( 'I', 'O', 'P' )
                        AND ORD.division = 'KLL'
             )
    SELECT  ORD.customer ,
            CST.cust_name ,
            C.group_code4 AS SCALE ,
			C.group_code6 AS BRAND ,
            ORD.lbl_code ,
            Sea.seas_name ,
            SUM(IIF(EffectiveMonth = 1, ORD.Size_Qty * ORD.price - 1.0
                * IIF(ISNULL(DSC.disc_perc, 0) <> 0, ( ORD.Size_Qty

Open in new window

…

2 Comments

Question by

Sriv

2017-05-31Solved

SSRS export to excel

Hi have 2 charts on my ssrs report and below it is a tabular report. When I look at the report it looks fine but when I export the report to SSRS, 1 chart is exported normally and other chart is shrieked. How to show both charts to same size when they are exported to excel. This is only with excel export other exports like PDF are fine.

SSRS

10 Comments

I have this script below which will call this report in a new tab. But since the values in the parameter has ampersand symbol, those values doesn't get picked up and the system throws an error given below. Please request your help.

="javascript:void(window.open('http://grddwmrtwhq01/ReportServer/Pages/ReportViewer.aspx?%2fDev%2fIndia%2fIndiaEngineering%2fClinical+Centers+Critical+CRs+Teamwise&rs:Command=Render&ReportParameter1=" & Replace(Join(Parameters!Dept.value,"^"),"%26amp;","&")+"&Qtr="+JOIN(Parameters!Qtr.Value,"&Qtr=") + "','_blank'))"

error message
============
The path of the item '/Dev Teamwise, Outreach Eng' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath) Get Online Help

--------------------------------------------------------------------------------

2 Comments

SSRS from Microsoft SQL Server 2012 - 11.0.5058.0 (SP1) when I export a report to CSV with no result, I'm getting header and commas below the headings. I am expecting a header but I'm not expecting the commas.

SSRS report Output
Reference,Portfolio
, ,

I'm looking for output without commas
SSRS report Output
Reference,Portfolio

------------------------------------------------------------------------------------
My rsreportserver.config setting is as below
<Render>
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
</Render>

With same SSRS rsreportserver.config setting from Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 I'm getting correct result.

4 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

SSRS

Solutions

Articles & Videos

Contributors

Course Of The Month

SSRS

Related Topics

SSRS

Related Topics