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 Guys ,
 I need help with one query, i need to get multiple count and amount from the same table for a report e.g

 Loans table.

Status, Amount  , date
 approved , 1000 , 2018 03 16
approved , 2000 , 2018 01 16
 rejected , 10000 , 2018 03 16
rejected , 30000  , 2018 01 16
pending, 10000  , 2018 03 16
pending, 10000 , 2018 01 16

now the result I am looking for is to give me the count of how many cases I have for reach status for today, this month and this year with total amount e.g...
Approved                                             Rejected                                               Pending
Today ,     This MOnth , This Year      Today      , This MOnth   , This Year          Today        , This MOnth , This Year
1 , 1000  , 2 , 3000         ,2 , 3000        1 , 10000  , 2 , 40000      , 2 , 40000           1 , 10000   , 2 , 20000      ,2 , 20000    

Open in new window


Train for your Pen Testing Engineer Certification
LVL 12
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

I am using Subscriptions to save an SSRS report as pdf into a network folder mjd\send at 6:00 am on weekdays. The name of the file is rptPot.pdf. The file is overwritten each day. I would like to create a package in integration services that would rename this file to rptPot_CurrentDate_CurrentTime so that I have a copy of each day's file.  How would I accomplish this ? Thanks.
I have a Monthly expenses report deployed to a 2008 Reporting Service’s Server.
Individuals can run this report by choosing their name from a drop down list.
However they can also chose other names and run the report, and this is causing a privacy issue.
Is there some way I can use the users logon credentials (it is an AD network) as a parameter and not use the drop down list of names?
I am using Visual Studio 2008 for modifications.
Any advice appreciated, thanks
I am using the available begin date and enddate parms to populate the report title that shows  "ABC DATA FROM MM/DD/YY  TO   MM/YY/DD" on the header. Here is the code I am using:
" Data from "&Parameters!BeginDate.Value &" to "& Parameters!EndDate.Value " . It works fine when "optional" From and To Dates are filled in. When using null  then my report title looks like " ABC DATA  FROM   TO  " . Is there a way for me to change the code so when NULL dates are passed it should not print " FROM  TO" ?
I found in one of the text bon in our ssrs reort builder has expression set  <<EXPR>> when I right click -expression it has a paramater like

=Replace(Parameters!Kajang_Application.Value,"KTB ","")

I'm trying to figure out where did this expression get call ad what is KTB does within the expression
I have created an SSRS report and deployed it to the SQL report Server location.  I want to set this report up so it executes at a certain time and creates a pdf in a network folder. However when I click subscribe I get the error "Subscriptions cannot be created because the credentials used to run the report are not stored, or if linked report, the link is no longer valid." In the  credentials section of the data source of the report I am using Windows Authentication. The only way the report will execute is with Windows Authentication . I have one other report that is set up this way successfully. That report is using a different SQL database. One set up by our ERP providers. The credential setting on that report is do not use credentials. Admittedly my understanding of the security portion of SQL is weak. Any help would be appreciated. Thanks.
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, …
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.
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.

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
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

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.

how below query
SELECT SUM(City.population)
FROM Country
    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
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.
Below query not working


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
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?
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
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?

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
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' …
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
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.

How to migrate SSRS from Onprem to Azure VM.

OnPrem--> SSRS 2008
Azure--> VM SQL Server 2016
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.

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
How do I attach the date to the end of a file in a SSRS subscription?
Dear Experts:
I would like to set a date parameter default date value to today at 6AM.
e.g. 7/01/2018 6:00:00 AM
In other words I need the expression that can replace this expression that is not working:
=CDate(Format(Now(), "dd/MM/yyyy") + " 6 AM")

Note: I use SSRS 2016 and. I tried to run this report through the web service and fails with the invalid date error.
If I use expression =Today() then it works without any issue.
Relatively new to SSRS, but have been doing a bunch of work modifying reports and have had no problem deploying them on one "Project".  Client asked me to create a new report in another project, but when I attempt to run the existing reports in that project I get an error message:

An Error occurred during local report processing.
An Error has occurred during report processing.
The execution failed for the shared dataset 'AdoptionReferrals'.
Cannot create a connection to the datasource 'Data source for shared dataset'.
A network-related or instance-Specific error occurred while establishing a connection to SQL Server....
error: 40 - Could not open a connection to SQL Server.

When I created a new report the wizard created a new Shared Data Source allowing me to select the appropriate SQL Server and database, and I was able to get my report working in design/preview mode.  But when I attempted to deploy the report, I received the error:

Cannot deploy the report because the shared data source '/path/subpath/Shared Date Source Name' that the report references does not exist on the report server.

and it provides a filename 'Adoption Incentives.rdl'

So how do I deploy the Shared datasource name to the report server?


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.