[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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'm needing to find when an order is on time.  The issue is some orders can have items that are considered on time while other items are not.  I need to find when the whole order is on time.  

select order_no, item_id, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
from sales_history
GROUP BY order_no, item_id, requested_date, ship_date

So below is what I'm needing which would be column D
 2.png
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I have an SSRS data set which returns columns: ClientName, Date, Description, and Miles based on a number of parameters.  I have assigned this dataset as the source for a tablix in my report but cannot figure out how to configure the tablix to get the output to look similar to:

grouping
0
I need to create a SSRS report from a list and document library in SharePoint 2016. They are joined by an ID called "BMSId"
What I need is a list of both data points and be able to filter (Parameter) the data. The filter needs to filter both a start date and an end date.
I am not looking for a report that has a sub report, but a straight line item with all the related data based on the ID number.
Being able to sort and group would be great, but just to get a list that has all the date would be great.
I do not have access to Visual Studio.
Thank you so much.
0
I had to change my login in active directory.  After doing that all of my SSRS (2008 R2) subscriptions wouldn't run because the previous AD entry didn't exist.  I had a tool to make those changes, but I still have the same issue when I create a new subscription.  It says my AD login isn't recognized.  How do I change that?  I've googled it and found some hits, but nothing quite explains how to fix the issue for new subscriptions.  Can I just go to the users table and change the old login to the new login?
0
Hi all,
I'm having a problem reading in an RDLC xml file.  My purpose is to collect information about the dataset(s), e.g. Dataset Name and Table name.
I've been dealing with xml projects for decades and for some reason every piece of code I try returns nothing.
Try
            Dim xmlDOC As New XmlDocument()
            xmlDOC.Load(XMLFILE)

            Dim nodeList As XmlNodeList = xmlDOC.SelectNodes("/Report/DataSets/DataSet")
            MsgBox(nodeList.Count)
            For Each node As XmlNode In nodeList
                MsgBox("DataSet Name: " & node("Name").InnerText)
            Next



        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

0
Using Report Builder in SQL Server 2016 and I cannot get group contents to split across multiple pages.  Just a simple group with headers and footers, no subreports or fancy images, just a footer full of text as a test because the fancy stuff was broken :(.  The report is grouped by quote number and then line, the details of the line are in the detail section and then a group footer on the line with a bunch of text that will print after each line.  The issue I am facing is the second line jumps to a new page becuase there is not enough room after the first line to fit the group header, detail, and footer for the next line.  All KeepTogether Properties are set to false.  The group is contained within a tablix.  I scanned the xml of the RDL to ensure every last KeepTogether option was set to false.  I have been through every static property in the groups changing the KeepWithGroup option from None, to Before, to After, all three produce the same result.  My goal is to have the header, detail, and begninning of the footer for line 2 begin on page 1 and roll to page 2 as needed and so on.  Anyone have any ideas why this is automatically rolling to the next new page, why is it trying to keep the group together for quote lines when all options are set to false?  I have attached a sample print out of the report in PDF format and the RDL file for report builder just in case you need to examine the properties for yourself, the file is there.
0
SSRS- Created a blank report and used a DB dataset to just drag and drop a text field onto it.  It only shows one row.  This was a limitation of SSRS and that field.  
Solution was to create a Tablix and go through the following to display a separate row per page:
https://www.mssqltips.com/sqlservertip/3493/display-a-fixed-number-of-rows-per-page-for-an-ssrs-report/
The problem is that when I place the Tablix field in my report it is in the position I want.  But due to the page breaks I lose my position and the field ends up on the top of my report with my report being broken up.  Is there a way or how do I get the position of the Tablix field to not move when traversing through my data?
0
Hi,

is there's a way to consume JSON API in SSRS?  I did some research and I landed to a 3rd party ODBC driver for ZappySys, is there's a free way, as my company won't be paying for a license.

thanks
0
Need the IIF statement for the following.  [SUBJECT_SIGNED_DATE] is the Matrix Text Box Value that displays.

In Simple Terms with the 2 Fields and Values Needed to determine Fill Color

If the [SUBJECT_SIGNED_DATE] is null and the [SYSTEM_SUBJECT_STATUS] is <> 'Screen Failed' or 'Early Terminated' or 'Completed'
Set the Fill Color to Red otherwise Set the Fill Color to White
0
I have report that lists sales documents. There are several different types of documents so I created a parameter field for each type. In this case, the user will most likely opt to see Invoices so I want that parameter to default to True and the other 4 to default to False. When I try to set the default value of each parameter to either True or False when I run the report those values are completely ignored. At first I tried typing =true in the formula field and then I even tried =Parameter.Field=true. How do I get these default values to be assigned properly?
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

SSRS 2012 - Visual Studio 2012 - SQL 2012
I have a field called "Trend" and a field called "Multiplier" that I was to multiple together.  Then I need to take the sum of those two fields and roundup to a multiple of a field called "Inv_Max"

For example:  Trend is 1572 and multiplier is 3.  (1572 * 3 = 4716)  The Inv_Max field is 2400 so I want to roundup the 4716 to 4800, which is a multiple of Inv_max.

I want to be able to add a calculated field and can't figure out the formula.
0
Good Day Experts!

As a previous other current question I have open, I am working on SSRS reporting of which I am relatively new to.  The query in question I have working in SQL Management Studio.
Knowing that I will need to be able to handle multiple values for a parameter to be processed by the query, I tested my query with a variable and the string 'Q1,Q2,Q3,Q4'. I could have used 'Q1,Q2' or 'Q4,Q3' to simulate the User selection from the dropdown list in the report.  At the top of my query, I first needed to build a function to process each value(Q1 Q2 Q3 Q4) in the list and retrieve the start and end dates for the value from a database table.  After doing that for each of the values in the list, I then built a string for my where clause that checks if a field, which happens to be a date, lies between the retrieved dates.  So, needless to say it was a bit tricky to generate the string but it is working now fine in Management Studio.  

The issue I am having now is that apparently the passing of multiple values to the query in the report is not in the format 'Q1,Q2,Q3,Q4' and my parsing apart and processing is failing.

I have found some information online but it is centered on the query using a "where in" clause in the query.  The other unique thing about this, is that I am specifying the values for the available values for the drop down list.  

So, I am not sure how a multiple value list of specified available values is passed to the query to know how to modify…
0
Good Day Experts!

I am hoping you can help me understand my options for resolving a dilemma.  I have developed a SQL query that runs fine and the desired output is achieved in ManagementStudio.
A parameter to the query(used to simulate User selection from an SSRS report) is a comma separated list.  Using that comma separated list,  I retrieve values from a table in my database and then build a string.  Here is the output of that string for the 4 comma separated values:

(incidentDate >= '2017-08-17' and incidentDate <= '2017-10-12') OR
(incidentDate >= '2017-10-16' and incidentDate <= '2017-12-19') OR
(incidentDate >= '2018-01-03' and incidentDate <= '2018-03-16') OR
(incidentDate >= '2018-03-19' and incidentDate <= '2018-05-31')

I set this to a variable to use in my query string which is then set to @cmdString. I then exec(@cmdString).  Works great no issues.

The issue I am having is when I try to use this in my SSRS report.  The editor does not recognize the fields to display on the report since they are in the string I am executing.  
Then when I try to create that query the "regular" way, I run into trouble because of trying to use a variable in the Where clause. I am not sure what to do.

Please ask questions as I probably did not explain well,
jimbo99999
0
I'm needing to find the DateDiff between two dates in the same column.  B is when an order goes on backorder.   So I'm basically look for how long an order was on backorder.  The difference between when (old_value is NULL and new_value = 'B') and (old_value = 'B' and new_value is NULL).

SELECT        column_changed, key1_cd, key1_value, old_value, new_value, date_created
FROM            p21_view_audit_trail_oe_hdr_1319
WHERE        (item_id = 'BWTREG-DF-1') AND (column_description = 'disposition') AND (key1_value = '2732473')
ORDER BY date_created DESC

Open in new window


results:
  1. column_changed        key1_cd        key1_value       old_value       new_value                  date_created
  2. disposition                     order_no      2732473              B                       NULL                    2018-01-18 18:58:31.633
  3. disposition                     order_no      2732473              D                       B                            2018-01-11 19:10:40.503
  4. disposition                     order_no      2732473              B                       D                            2018-01-11 19:07:57.397
  5. disposition                     order_no      2732473              NULL               B                            2018-01-11 19:07:42.213
0
I have the below dataset. I am trying to find an SSRS expression to check the value of it and return 0.00 if it equals 100.00. If it does not equal 100.00, the expression should return the value of the dataset. Any guidance is appreciated.

=Sum(Fields!Compliance_Rate.Value, "Failure_Rate_Month")
0
ssrs question with a user submitting a subscription.  Attached is a screenshot of the subscription filters.  The focus is on these two filters for now
Start Date
End Date

The default for the report is set to Start date = today   end date = today + 1 (tomorrow)

This user wants a subscription configured that sets the start date to "Today - "1  and end date set to "today"

is it possible to submit this kind of logic through the subscription interface provided?  if not, best way to tackle this....
ssrs-subscription-question.jpg
0
I have the following query that executes in SSMS in 1 minute 28 sec. However when I attempt to run it in SSRS it times out. I have increased the Time out to 600 and it still fails. I would use query analyzer to place some indexes however this is our ERP database and each time they work on the DB they restore the database as it was deployed meaning indexes, tables, views, etc. are dropped and I have to rescript them.

The query returns 37557 rows. It began to time out in SSRS when I added the following Join LEFT OUTER JOIN [dbo].[KLL Customer Projections] C ON C.customer = ORD.customer

This is a summary amount that needs added.  Specifically this column C.projection_amt , So at the summary level I have 40 customers. Each with a sales projection amount. So part of my issue is that I'm linking thousands of records to 1

Is there anyway to tweak this query so that it runs more efficiently?
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  ORD.customer ,
        zzxcustr.cust_name ,
        ORD.Style_Season ,
        zzxseasr.seas_name ,
        ORD.style ,
        ORD.color_code ,
        ORD.lbl_code ,
        ORD.dimension ,
        ORD.MAINLBL ,
        ORD.SCALEFIN ,
        ORD.CLASS ,
        ORD.TYPE ,
        ORD.slsperson1 ,
        SUM(CONVERT(MONEY, CASE WHEN ( ( YEAR(ORD.ship_date_cascaded) = @CurYear
                                         - 2 )
                                       AND ( ORD.line_status = 'I' )
                   

Open in new window

0
In an SSRS report I want to hide the tablix/table when there are no rows appearing. Problem is there are rows, but I hide the rows when column 1 is the same as column2. So my problem is that I don't want the column headers / table to appear when no other row appears. Is this possible I think there are scope issues doing this.

At moment I have two datasets. One retrieves current values, the other dataset retrieves historical data from a specific point in time. So I am using the lookup value '=Lookup(Fields!LabelLookup.Value, Fields!LabelLookup.Value, Fields!Value.Value,"Date2")' to show in one cell what the value was at a specific date and in the other cell what is is now on the same row.  When the two values are the same I hide the row because there was no change. This works fine, but sometimes I am left with just the table header showing no rows. However, there might be lots of rows but are actually duplicate, as the value has not changed between the two dates.

Is there a way to check if there are no rows appearing in the table after the hide rows has been applied? I'm not sure it is possible? If not is there another way I might be able to do this?

Using sql server and SSRS instance 2014
0
I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
0
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

I have been tasked with creating some reports out of VS and SSRS from an Oracle 12c database.  I am an Oracle developer but I don't know much about VS or SSRS.  I have both VS 2015 (14.0.25431.01 Update 3) and 2017 (15.7.5) installed and I have installed ODTwithODAC for 12c.  I have also installed SSDT for both VS 2015 and VS 2017.  

Using VS 2017, i have created a new project but I am unable to create a shared data source.  As soon as I select Oracle Database in the Type drop-down, the Edit button next to the connection string becomes grayed out.  

Using VS 2015, I have cloned an existing project that works fine for my colleague.  However, when I first tried to load the project I received a message that the version of the report server project is not supported and the project must be upgraded.  If I say no, then the project won't load.  If I say yes, the project loads and everything seems to be ok. However, when I try to Preview an existing report from the project, I receive the following error:
"An error has occurred during local report processing.  An error has occurred during report processing.  An attempt has been been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."  There is nothing in the Error List when this happens.  

The only difference between my colleague's setup and mine is that he has SQL Server Reporting Services 14.0.806.134 and I have …
1
Please help with writing a query to get aggregated OrderAmount for each SalesPerson for the last month (last 28-31 days). For example, if today is 09/13/2018, we need sum from 08/13/2018 to 09/12/2018.

TableOrders
----------------
SalesPerson
OrderAmount
OrderDate


Thank you in advance.
0
while exporting my report data to excel it is running 10 -15 minutes after that im getting server Runtime error in  ssrs report .I have 3 lakhs data and 49 columns in excel.
There is no exceeding limitations problem with my excel data and im trying to export it into .xlsx format only.Please suggest what i have to do
0
The following command string exports a PDF to be saved or opened.

start explorer "<http://ServerName/ReportServer$NamedSQLInstance?%2fPurchasing%2fPurchaseOrder&PoNumber=103290&rs:Format=PDF"

Need to NAME and SAVE the file Automatically, from a Delphi desktop application.
0
I'm trying to recreate a report for a client, and in that report they have three fields Night, Weekend, Holiday (0 or 1) which need to be displayed as check-boxes.
In Visual Studio, I've made the column for these values .2" wide, have set the font to Wingdings, and have the expression which looks like:

= IIF(Fields!Night.Value = 0, chr(0168), chr(0254))
& IIF(Fields!Wknd.Value  = 0, chr(0168), chr(0254))
& IIF(Fields!Holiday.Value = 0, chr(0168), chr(0254))

In VS, this displays like the following image:2018-09-05-SSRS-Checkbox1.jpgBut after I deploy the report and run it in SSRS it looks like the following, making it difficult for the client to review the data in the report.2018-09-05-SSRS-Checkbox2.jpgIf my client exports this from SSRS to a .pdf file, it renders properly, but the client needs to export the report to Word (so that it can be edited) and when they do, that column automatically resizes itself so that these stacked checkboxes display horizontally.  My client recognizes that they can simply resize the column, but would prefer not to have to do so.2018-09-05-SSRS-Checkbox3.jpgI've tried inserting a carriage return/line feed between each character in the report textbox expression, but that results in extra spacing between the checkboxes.

I've also tried using 'X' and 'o' with the wingdings font, which places an X inside the checkbox, but the formatting issue still persists.

Does anyone have any recommendations?
0
Visual Studio 2010

I have an SSRS report that looks like:2018-08-30-Contact-Overlap-Repeat-He.jpgThe report is correctly grouping on Program and County and each new occurance is going to a new page.
But I cannot seem to get any of the column headers (the yellow is hidden in the report view)  to repeat on new pages.

I've tried various combinations of Group Properties and "static" properties but cannot seem to get this right.
Any help would be greatly appreciated.
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.