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

HI there, I am using SSRS and have a problem where my grand total on the last page of the report will only calculate the the times of the pages that have been shown. In other words, if its a 3 page report and I run it and view the first page then skip to the last it will calculate the first and the last missing out page 2. If however, I run the report and go through each page using the next button it then calculates the total correctly.

My total is a SUM of a previous total and both are code elements within the report, after doing a bit of research I found out that this is why, the code elements only fire on the page that is in view.

My 2 code elements are:

Public shared dim totalJobTypeRateTotal as Decimal

Function AddTotal(ByRef JobTypeRateTotal AS Decimal) AS Decimal
 
                totalJobTypeRateTotal = totalJobTypeRateTotal + JobTypeRateTotal
                return JobTypeRateTotal

End Function

====This function calculates row items based on a lookup that finds the specific rate for each job type=====

 Public Function GetTotal()
                return totalJobTypeRateTotal
End Function

====This is the grand total at the end of the report group that will only calculate the total of the AddTotal function expression on pages that have been viewed====

How do I get the 2 code calculations to produce a grand total correctly without having to scroll through each page?
0
Learn to develop an Android App
LVL 12
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

I have a Oracle table (not mine) that puts all below comments in 1 field called RCOMMENTS in a table called INVOICETABLE. If a new comment is entered it will be added to the beginning of this field. The example below is exactly how the field looks. Would there be way to read this field and only extract all the dates and times (7/3/17 6:12 PM, 6/30/17 4:07 PM etc.) ignoring the comments associated to each date and time ?  Also, I would like the dates to be in descending order, if possible.
This is a Oracle table and the field type is CLOB. If this is possible, my idea is to extract these dates and insert them put them into a SQL table (using OpenQuery)  called InvoiceDates and accompany the date records with a customer number that is in the INVOICETABLE.

Now, I am not allowed to create tables in the Oracle DB. I can only read Oracle tables and do open queries to put the records I want into SQL tables. I then use the SQL tables to create SSRS reports. I occasionally have to go to Oracle tables for data.  


7/3/17 6:12 PM 164830
C.Pi(NF-AR): Received an email from Calvin advising that they just received their proposed terms on June 26, 2017 and are in the process of reviewing whether they can come to an agreement on the rental rate.
 
6/30/17 4:07 PM 164830
C.Pi(NF-AR): Sent an email to  advising that we issued an invoice to the customer, but the new lease agreement has not been singed
 
6/30/17 3:58 PM 164830
C.pinto(NF-AR): Correction to my previous entry, …
0
A coworker posted a report definition (.rdl) file out onto the server, asking that I help her with adding some fields from some tables to several of the data sets for the report.  I imported the report into Visual Studio (2010 SP1), but each of the data sets had a DataSource property which was not defined in my solution explorer.  If I tried to edit the data set in the query designer I received an error message:

An Error occurred while connecting to the data source.  Only the text-based query designer will be available.
Additional information:
The shared data source 'Kermit' does not exist or you do not have permissions.  For more information, contact your report server administrator.

So I created a new shared data source with that name, pointing it to the appropriate Server and database, but I'm still getting that error.

If I change the data source to another previously defined data source, which works for other reports in my VS solution, I can open the data sets and run the queries just fine in the query designer.  But when I try to preview this report I'm getting an error:

An error occurred during local report processing.
Error in the Application.
Could not find a part of the path '\\servername\gpshare\SRS_Projects\GPReports\GPReports\Maestro FP\Maestro FP\Maestro FP\bin\debug\data sources\MaestroFP.rds'

The \Data Sources\ folder does not exist on this path, but the rest of that path does exist.  I cannot figure out where this path for the …
0
I need to highlight (fill) a field based on when the numeric value is greater than 7.00.

I tried the following expression in the text box properties > "Fill"

=IIF(SUM(Fields!Dispatched_to_at_Scene.Value, "DataSet1") >7.00, "yellow", "no color")

However, it highlights the entire column regardless of the value...

Thanks in advance.
0
Hi Guys,

I am straight out of school and working on my first SSRS report via SQL server 2012.  It is pretty small with only 3 lines of data.
I have an issue with the date/time formatting on the X-axis of my line graph.

My SQL Query has the following format: YYYY-DD-MM HH:MM:SEC. (Ex, 2018-02-08 13:35:37:363), however when I preview the line graph in SSRS, the X axis is formatted as MM:dd:YYYY and I am not getting the time stamp.

Attached are quick illustrations of what I am looking at.  As my SQL skills and formatting are entry level, any help would be appreciated.

Thanks.
Graph.JPG
SQL.JPG
0
I am trying to refresh my CRM 2011 TEST org using a bak from Production SQL. I can restore the SQL Production bak to SQLTEST but when I try to import the org using CRM 2011 deployment manager I get the following error:

Setup failed to validate specified Reporting Services Report Server http://sqltest/ReportServer.
Error: Error occurred while finding an item on the report server.
The request failed with HTTP status 401: Unauthorized.

This stops the org import with the above error.  I have tried deleting the encryption using Reporting Services Configuration Manager and redefining the data sources.

I have been stuck at this point for a day.

Help would be greatly appreciated
0
Hi Team,

I am new to SSRS, I want to fine tune the long running reports?
could you please tell me where to start?? how to fine tune it.

Thanks
Jai
0
Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;


Select NAME from CITY inner join CITY.COUNTRYCODE = COUNTRY.CODE;


in above queries what is difference between where and inner join

Are these queries are case sensitive like below


Select name from city where CITY.CountryCode = COUNTRY.Code;



Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;



below query how is different

SELECT City.Name
FROM City, Country
WHERE City.CountryCode = Country.Code AND Continent = 'Africa' ;

in the FROM clause do i need to mention both tables like city , country or one is ok then where


can i write above using inner clause without where?
any performance hits or advantages, disadvantage in either approaches?
0
how below query
SELECT SUM(City.population)
FROM Country
INNER JOIN City
    ON Country.Code = City.CountryCode
WHERE Country.Continent='Asia';

different from below query

SELECT SUM(City.population)
FROM City//from City instead of Country here makes any difference?
INNER JOIN Country//from Country instead of City here makes any difference?
    ON City.CountryCode=Country.Code //here which is before and after matters in case of inner, left, right inner joins??
WHERE Country.Continent='Asia';

where i can learn in detail about all about inner and outer queries like any free video tutorials?
please advise
0
I have a blocking report (Blocking Detected On Production Server), Can anybody please help me understand that?

Here are the details:-

Session_id || Status || blocking_session_id || wait_type || wait_resource || WaitSec || cpu_time || Logical_reads || reads || writes

156 || Suspended || 137 || LCK_M_IS || OBJECT: 18:925246351:4 || 172.660000 || 7 || 204 || 66 || 0 ||

Anything more about this will be very helpful.

Thanks in Advance.
0
Hire Technology Freelancers with Gigs
LVL 12
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Below query not working


SELECT * FROM City WHERE ID=1661;

i see error as
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Query all columns for a city in CITY with the ID 1661.


SELECT * FROM City WHER' at line 1

please advise
0
I lost my SSRS report design but I have already deployed this project in the server.
How will I copy the SSRS reports from server back to my local so I could work on it in design mode?
0
Hey all I have the following query that returns the number of the longest word it finds within that column:
SELECT 
   MAX(LEN(id)) AS id, 
   MAX(LEN(linkToTbl2)) AS linkToTbl2, 
   MAX(LEN(description)) AS description, 
   MAX(LEN(number)) AS number, 
   MAX(LEN(line)) AS line, 
   MAX(LEN(network)) AS network, 
   MAX(LEN(type)) AS type, 
   MAX(LEN(IPhase)) AS IPhase, 
   MAX(LEN(environment)) AS environment, 
   MAX(LEN(sType)) AS sType, 
   MAX(LEN(bDescription)) AS bDescription
FROM 
   bLine

Open in new window

However, if the said column is smaller than the heading for that column then it doesn't take that into account when calculating the largest value.

Example (what I am looking to do):
|id |linkToTbl2 |description              |
|---|-----------|-------------------------|
|14 |hi         |This is just a demo.     |
|16 |baa        |Another description here.|

Open in new window

Which would look like this in an example query:
|id |linkToTbl2 |description |
|---|-----------|------------|
|2  |10         |25          |

Open in new window

Now this is what it currently looks like in my SSRS report:
|id |lin|description              |
|   |kTo|                         |
|   |tbl|                         |
|   |2  |                         |
|---|---|-------------------------|
|14 |hi |This is just a demo.     |
|16 |baa|Another description here.|

Open in new window

and this would look like this in the query:
|id |linkToTbl2 |description |
|---|-----------|------------|
|2  |3          |25          |

Open in new window

Notice how the linkToTbl2 field is compressed since the longest value in that column is 3 (baa). linkToTbl2would would be 10 (linkToTbl2) so it should be 10 and not 3.

How can I add the columns name into the query to count that as well?
0
query issue

select * from city where population >= '100000' and NAME='USA';

why above query is not resulting any results while trying on ms sql server

please advise
0
Hey all I am pulling out my hair trying to figure out why this is telling me that there are duplicates of the key for ReportViewerWebControlHandler.

ssrserror.png
This is what my web.config file looks like (omitting things that do not apply here)
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="false" />
    <add key="UnobtrusiveJavaScriptEnabled" value="false" />
    <add key="owin:AutomaticAppStartup" value="false" />
    <add key="vs:EnableBrowserLink" value="true" />
  </appSettings>
  <system.web>
    <sessionState mode="InProc" timeout="30" />
    <machineKey validationKey="****" decryptionKey="****" validation="SHA1" decryption="AES" />
    <compilation defaultLanguage="c#" targetFramework="4.5.1" debug="true">
      <buildProviders>
        <add extension=".rdlc" type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.WebForms, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91" />
      </buildProviders>
      <assemblies>
        <add assembly="Microsoft.ReportViewer.Common, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
        <add assembly="Microsoft.ReportViewer.DataVisualization, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
        <add assembly="Microsoft.ReportViewer.Design, 

Open in new window

0
I am upgrading CRM 2011 to CRM 2013 for two organizations (CRMTEST and CRMPROD).  The plan is to upgrade each org to newer CRM versions to get to Dynamics 365 (so CRM 2011 to CRM 2013 to CRM 2015 to CRM 2016 to Dynamics 365)

My question is: we have built one CRM server to Dynamics 365 specs and one SQL Server built to Dynamics 365 requirements.  Is it recommended to have separate servers for  the CRMTEST org and CRMPROD org?  Given our current VMs, we would need to upgrade to a single set of servers, with CRMTEST and CRMPROD on the same Dynamics 365 servers.

The current CRM 2011 environments have separate servers for CRMTEST and CRMPROD.  What could be the problems with both TEST and PROD on the same servers?  For example, could this cause issues with refreshing CRMTEST with CRMPROD?

Thanks
0
Hey all I currently have my datasource/dataset defined inside my rdl file like so:
<DataSources>
  <DataSource Name="DataSource1">
    <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=Z92hf54d;Initial Catalog=B2018</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
    </ConnectionProperties>
    <rd:SecurityType>Integrated</rd:SecurityType>
    <rd:DataSourceID>00000000-0000-0000-0000-000000000000</rd:DataSourceID>
  </DataSource>
</DataSources>
<DataSets>
  <DataSet Name="DataSet2">
    <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
            <QueryParameter Name="guid">
                <Value>guid</Value>
                <rd:UserDefined>true</rd:UserDefined>
            </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT BL.id,BL.poc,BL.description,BL.number,BL.line,BL.network,BL.type,BL.IPhase,BL.environment,BL.servType,BL.bDescription FROM BL</CommandText>
        <rd:DesignerState>
            <QueryDefinition xmlns="http://schemas.microsoft.com/ReportingServices/QueryDefinition/Relational">
                <SelectedColumns>
                    <ColumnExpression ColumnOwner="BL" ColumnName="id" />
                    <ColumnExpression ColumnOwner="BL" ColumnName="poc" />
                    <ColumnExpression ColumnOwner="BL" ColumnName="description" />
                    <ColumnExpression ColumnOwner="BL" ColumnName="number" />
    

Open in new window

0
Hi again, Experts - another SSRS challenge on the final leg of designing a report as I'm receiving a #Error summing totals.

I am trying to sum totals for previous and current year using the following expression:

=(Sum(Fields!CurYr.Value)-Sum(Fields!PreYr.Value)) / Sum(Fields!PreYr.Value)

Of course, this works for 'grand totals' since there is never going to be any '0' values for previous or current year aggregate. However, for the individual manufacturer rows, there will be. Can you guys help? If you need any additional info, please let me know.

Thank you!

SSRS Report Design Screenshot
SSRS Report Preview Screenshot
SSRS Report Totals Screenshot
0
I am needing some help with a calculated field in SQL Report Builder.  I am a novice and converting from Crystal Reports to SSRS.  Yes I am building my first report but got to start somewhere.  Here is what I have attempted but obviously this throws up a big error.

=IIf(Fields!ShipViaCode.Value='CT1' then 'COMPANY TRK - GNG' else IIf(Fields!ShipViaCode.Value='CT2' then 'COMPANY TRK - NWC' else IIf(Fields!ShipViaCode.Value='CT3' then 'COMPANY TRK - NRC' else IIf(Fields!ShipViaCode.Value='CT4' then 'COMPANY TRK - NWN' else IIf(Fields!ShipViaCode.Value='FEDX' then 'FED-EX GROUND' else IIf(Fields!ShipViaCode.Value='LTL' then 'FREIGHT TRUCK' else IIf(Fields!ShipViaCode.Value='PU1' then 'PICK UP - GNG' else IIf(Fields!ShipViaCode.Value='PU3' then 'PICK UP - NRC' else IIf(Fields!ShipViaCode.Value='PU2' then 'PICK UP NWC' else IIf(Fields!ShipViaCode.Value='PU4' then 'PICK UP NWN' else IIf(Fields!ShipViaCode.Value='SLSP' then 'SALESMAN DELIVERY' else IIf(Fields!ShipViaCode.Value='SSI' then 'SSI' else IIf(Fields!ShipViaCode.Value='TAR' then 'TARTER TRUCK' else IIf(Fields!ShipViaCode.Value='WST3' then 'TARTER WEST - NRC' else IIf(Fields!ShipViaCode.Value='TIDR' then 'TI DIRECT DELIVERY' else IIf(Fields!ShipViaCode.Value='UPSP' then 'UPS -PLP' else IIf(Fields!ShipViaCode.Value='UPSW' then 'UPS - WEST' else IIf(Fields!ShipViaCode.Value='UPS4' then 'UPS - NWN - GNG' else IIf(Fields!ShipViaCode.Value='UPSS' then 'UPS - SPI' else IIf(Fields!ShipViaCode.Value='UPS2' then 'UPS 2ND DAY AIR' …
0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

How do I redirect the SSRS 2014 URL?  I'd like to make it so it's not http://server/reports but just http://reports for internal users.
0
Hello experts -

We are upgrading our SQL servers from 2008 to 2012 which brings with it the upgrade to the xlsx extension when sending Excel attachments. Is there any way to change the default from xlsx back to xls on a report by report basis? I am hoping this will be a non-issue, but I would not be surprised if we have some customers with software that cannot read xlsx format.

Thank you.

-- Lisa
0
Hey all I am trying to find a way to populate a SSRS report with what a user selects on a page. The user will be able to select any table and any database to put some data together and I need to be able to take that data and place it onto a report that they can download via PDF and/or Excel.

Currently I have not found any good examples of dynamic creating SSRS reports. The only close example I found doesn't work in VS 2017 (here).

Is it possible for a SSRS guru to post some sample code (compatible with VS 2017) that has a good example of the above issue I am looking to solve?

The current code setup in my ASP.net MVC project is this:

ReportViewer.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReportViewer.aspx.cs" Inherits="BOM2017.Reports.ReportViewer" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager" runat="server"></asp:ScriptManager>
        <div>
            <rsweb:ReportViewer ID="QIUReportViewer" runat="server" AsyncRendering="false" Height="700" Width="1150"
                

Open in new window

0
How to migrate SSRS from Onprem to Azure VM.

Details:
OnPrem--> SSRS 2008
Azure--> VM SQL Server 2016
0
Experts - Need your help. I'm stuck. I created an SSRS report  (partner business review) that uses 7 datasets to populate the report (I created an additional dataset for the customer_id parameter).

The original design called for manually entering exactly 1 customer_id and then generating the report. Now, we need to change this to allow for the selection of multiple customer_id's (from a drop-down) and have a separate, unique, report generated, for each customer_id selected from the drop down. I'm sure for an SSRS expert this is easy however, in all my years creating SSRS reports, I haven't had this as a requirement and am desperately lost.

Can anyone help a new member?

Thank you to anyone/everyone for your feedback and help.

Jeremy
0
Hello experts -

We are in the process of migrating servers and need to re-point all our SSRS data sources to the new environment. I need to find all the reports that have embedded data sources and what connection strings are being used to ensure they do not fail when the migration takes place. Is there a way to search the report server database to give me all the reports that have embedded connections?

I have searched for SQL Code that I can run against the ReportServer database with no luck. We are on SQL server 2008.

When I search the XML code of the embedded reports, below is what I find -

SSRS XML Screen Shot
The highlighted portion is what needs to change.

Any advice is appreciated.

Thank you.

-- Lisa
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.