Is there any way to find the dependency between two datasets in ssrs report. I want to delete one of dataset but need to find if I delete that it will work or any relation with the other dataset.
View 1 RepliesI have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies View RelatedI have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I'm getting this warning each time I auto-generate my model. I'm using named queries with logical primary keys.
The relation property of the role "xxx" refers to the target end of relation "xxx" which is not bound to a set of uniquely contrained columns.
I've searched and can't figured out what I am being warned about and whether I need to fix something. Can you help?
Thanks,
Toni
Currently we are using Custom Delivery Extension for SSRS 2008R2 We are planning to move it SSRS2012
My Question is: Whether we can use the same Code used for SSRS2008R2 to SSRS2012?
if not what code changes we should do?
Hi.
I am currently working With SSRS . I have created a Store Procedure To show Data from different table in my Database,I have Used a temp table with #xxx Name,when i am creating DataSet for this Store procedure . but its not Showing the Fields of Store Procedure, but when i create other Store procedure without temp table with #xxx then its showing the fields in Data Set.
What Could be the possible Reason,
Thanks
So I have this great MDX query that runs fine in Management Studio and I need to use it in a report. The statement contains a LookupCube call. I've tried everything but I am unable to get access to the fields in the dataset when I go to design the report.
I've tried adding the =" in the beginning and the " at the end. I've also tried escaping the " with "". Everything I've tried however never gives me the ability to access the 'PREV BATCH ELIGIBLE BALANCE' field.
Before you ask, I'm using LookupCube because the below code is just a piece of a much large MDX query. I just figured I'd start small and see how it goes.
WITH MEMBER [Measures].[PREV BATCH ELIGIBLE BALANCE] AS LookupCube("CLAIMS"," ([Measures].[TOTAL BALANCE] ,[CLIENT].[ATC Healthcare, Inc.] , [ELIGIBILITY].[TRUE], [ROLL_FWD].[TRUE], " + [Batch].PrevMember.UniqueName + ")") SELECT { [Measures].[PREV BATCH ELIGIBLE BALANCE] } ON COLUMNS, { [BATCH].[MyComp Healthcare, Inc.].Children } ON ROWS FROM [CLAIMS]
hello
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
-- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
@TotalMarket varchar(255),
@datasetname varchar(255),
@VisibleProducts varchar(500),
@DoctorSubTerritory varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal ,
SUM
(
CASE
WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN xpomeasures.rxvalues
ELSE 0
END
) AS totalrxvalues,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc <> 'Non -')
AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS rxrcount,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [high],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Medium')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [medium],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very high],
(SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography
ON xpomeasures.geographyid = xpogeography.geographyid
WHERE (xpomarket.productdesc = @TotalMarket)
AND (xpomeasures.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (xpomarket.datasetname = @DatasetName)
AND (xpogeography.datasetname = @DatasetName)
AND (xpogeography.doctorgroup = a.doctorgroup)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
) AS rxvaluesdoctorgroup
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN (
SELECT xpogeography.doctorgroup,
SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
WHERE xpomeasures.datasetname = @DatasetName
and (xpomarket.datasetname = @DatasetName)
and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
GROUP BY xpogeography.doctorgroup
) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname = @DatasetName)
AND (xpomarketsize.datasetname = @DatasetName)
AND (xpoproductsize.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (a.datasetname = @DatasetName)
AND (XpoMarket.datasetname = @DatasetName)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
AND (a.doctorsubterritory = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
fn_mvparamsorted.parame ,
fn_mvparamsorted.sortid ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal
ORDER BY s.totalmarketrank,
a.doctorgroup ,
fn_mvparamsorted.sortid
END
I can add more datasources and how can i frame my query based on the tables present in the various datasources?
View 5 Replies View RelatedNeed to change a Excel report to SSRS.
Excel report has around 15 tables all with different columns.
Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.
Note -- All 15 tables have differnt columns list.
I have created multiple data sources and datasets in my SSrS project. I created the datasets so I could use them as the basis for reports. However, when I open the wizard to create a new report, I am always asked for a data source, and then have to renter the SQL code that I have stored in one of my datasets. How can I have a report use an existing dataset as it source of data? Otherwise, the datasets I created seem useless . . .
View 5 Replies View Related
Hi,
I'm developing an ASP.NET application that creates on-demand reports with Reporting Services 2005 in report server.
I use a custom data set that contains the data that I want to bind to the report, and I use a WebService to pass that DataSet to the reporting services to accomplish my purpose.
The query passed to the reporting services is:
<Query>
<Method Namespace=http://myNameSpace.com Name="GetDataSetForReport"/> <SoapAction>http://myNameSpace.com/WebService/GetDataSetForReport</SoapAction>
<ElementPath IgnoreNamespaces="True">GetDataSetForReportResponse{}/GetDataSetForReportResult{}/diffgram{}/ReportDataSet{}/MyTable{Fields1,Fields2,Fields3,..,FieldsN,Image}</ElementPath>
</Query>
This works well and all the fields are displayed in the report fine except the Image Fields, that are not showing (the red X appears instead of the image).
The issue is that all the data that is bind to the dataset is contained in a SQL Server 2005 database, and the Image column in the table of that database is a VARBINARY(MAX) field (I insert the image into the database table).
Also, when I created the dataset in .NET, the column type of the dataset I declared for the Image field was Byte[], but that didn't work for me. I've also tried to declare as the field type Byte,SqlByte,Image,SqlBinary, etc without succesfull results.
I've also tried creating a XML Schema, declaring the Image field as base64Binary, then do DataSet.ReadXMLSchema(myschema) and then retrievied the data from sql server and filled the dataset, Again, the Images doesn`t display.
For finnishing my unsatisfactory tests, I've also tried to handle the image data with a MemoryStream object:
MemoryStream mem = new MemoryStream();
MemoryStream oStream = new MemoryStream();
mem.Write((Byte[])dr["Image"],0 , ((Byte[])dr["Image"]).Length); //dr is the datareader used to read from the DB
Image image = Image.FromStream(mem);
try
{
image.Save(oStream, ImageFormat.Png);
}
catch{}
newRow["Image"] = oStream.GetBuffer(); //newRow is a row of the custom DataSet.Table passed to the SSRS.
But again, the Image is not showing.
Any suggestions?
Thanks for you time.
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
View 1 Replies View RelatedI am using SSRS 2014 SP1 x64 and VS2013 Update 5.
I have the following expressions to attempt to calculate ageing on a statement report.
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=0, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=1 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"),Now())<=30, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=31 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=60, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=61 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=90, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=91, (Fields!LineAmount.Value, "Invoice"), 0))
Now when I try to add First to the field selector I get an error stating ... uses a First, Last or Previous aggregate in an outer aggregate.
How exactly do I reference this field from the Invoice dataset (there are at least two datasets)?
the Dataset in my report doesn't update the fields' values from the database. I refresh the dataset manualy in the SQL Server Business Intelligence Development Studio to see the new values in the report.
View 14 Replies View RelatedCould we use SSRS Shared Dataset as a source to the SSIS package?
View 3 Replies View RelatedWe are planning to develop weekly report in SSRS.For this we wants each day as column & some expenses[Numeric figure in row]we have dataset like
day
exp1
exp2
exp3
17/05/2015
120
150
650
[code]....
There are some other filters are there that i have applied in my report tablix property.
"Short" Story Version:
I have a VS 2005 Web Appplication solution that uses SQL Server Reporting Services 2005. One of the projects is a Report Server Project. In this project I have an RDL file that has:
1.)a non-shared XML DataSource (no credentials),
2.)a DataSet with a Command Type of Text and a Query String that is as follows:
<Query>
<XmlData>
<Root>
<LOAD LOAD_ID_PREFIX="" LOAD_ID="" TRUCK="" DIAGRAM=""/>
</Root>
</XmlData>
</Query>
3.)a field that comes from the Database that is of VarBinary(Max) that is a JPEG Image
What happens is, is that by doing #2 above, at design time I can create tables and hook into my datasets and get the report all designed out the way I want. Then at runtime, I have a Stored Procedure that uses the "FOR XML AUTO" option so that I can represent my results from the database in an XML fashion which will allow me to replace the XML Schema in between the Root tags above (in #2) with my actual data (which still has the same schema). I then save the modified RDL to a database table field and move on to happily ever after......in this case, not. In this case, since an VarBinary is involved, unless I want to get a pointer back for the Diagram Column, I have to use the "FOR XML AUTO, BINARY BASE64" option. This returns an Encoded string to represent the Binary value that was in the Database....and this is the partial source of my angst....read on.
My approach that I described above worked flawlessly in all cases where an image wasn't involved. This report is the only report in the whole system I am working on that will have an image and it is the last one being done, so that's why I didn't run into this earlier.
I have tried all kinds of things at different levels, but the thing that I have tried that seemed to me to have the most potential was to use Embedded Code to take the Encoded ASCII Value and convert into something binary that the SSRS Reporting Engine likes (so my image will show), but I have been unsuccessful thus far. The error message I keep getting now from my Embedded Function is: "Invalid length for a Base-64 char array", which I have not been able to resolve thus far....and I do know that the Binary value in the database is good becuase I can see it in pages in my application otherwise.
This aside, is there anyone out there that knows how to take the value of a VarBinary(Max) field and insert it into the Text (XML) DataSet in the markup of an RDL file....and have it appear correctly when the report is rendered in something like the standard Report Viewer Control (or even when being previewed at design time inside of Visual Studio for that matter)?
Just to be clear, the root of my problem, as I see and understand it, is that I have a VarBinary value in a database that is being converted to a string (ASCII...I am presuming) due to how I am writing my stored procedure and then needs to go back to something else binary that the SSRS Engine will accept so that I can see my Image in the report when I render it.
I am wishful that someone out there understands my problem and has run into this and has a solution......but I am wishful of winning the lottery, too, and that hasn't happened yet...............
Thanks.
When I click on the DataSet Query Desginer there is an Error Source:.NetSqlClient Data Provider. why this error comes and I have executed the SQL query in SSMS succesfully and the result comes. However this is causing the report from the report manager to fail.
View 8 Replies View RelatedIn one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.
I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).
Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.
Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).
Thanks.
Hi All,
I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.
Thanks,
Aneesh
Consider following script
create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set
I tried this below
Declare @child_ids int
@col_val varchar
@count int
select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1
But I want my output like below .....
I have a dataset that I need to search for a maximum value of a specified index.
Example of dataset:
Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126
I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.
Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.
Could I use an array?
In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:
Document Number
Version
Occurrence number
so if doc 1 is archived, then the records would be
Doc# | Version | Occurrence #
1 | 1 | 1
When a second copy is archived a new record is added:
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
and then when maybe a 3rd version is archived a 3rd entry added
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2
This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!
If I
select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]
then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.
How do I select just these?
I thought (wrongly) that I could simply say:
Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
We have SCCM 2012 primary site and Remote SQL 2012 server. Due to hardening and password reset we are facing reporting issue.
while we Open the SRS report in SQL server and try to edit the Report (Report Builder) we are getting following error due to which we are unable to configure Reporting Service point in the SCCM 2012 server. We created the New Reporting server database still we are getting the below error.
In an SSRS database install along with a log shipping configuration towards a failover server, do we need to log ship the ReportServerTempDB DB also necessarily ?
View 2 Replies View RelatedI have an encrypted database in server A. The reporting service is running on server B. I deployed all my reports to Server B. When I run the reports, I got the following message:
The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database.
I googled the problem, some said to backup the key, but when I opened the reporting service configuration tool to backup the encrypted, it said I needed to restore the key first. Some said I need to delete the key, but that defeated the purpose of an encrypted database.
I have found endless articles online and tried a few different formats of the web.config file but i can not get SSRS authenticating through the proxy.
</runtime>
<system.net>
<defaultProxy enabled="true" useDefaultCredentials="true">
<proxy bypassonlocal="true" proxyaddress="http://PROXY:PORT"/>
</defaultProxy>
</system.net>
</configuration>
Is there a way to pass credentials in so we can specify which account to authenticate with?
I have created an SSRS report, but the data does not refresh. I am using the SSRS Report Buidler to create reports and when I run the query in the dataset, the results return as expected. However, when I run the report itself, the dataset appears old.
View 6 Replies View RelatedSay I have a report with 3 pages, each page has a different dataset and takes some processing time.
is it possible to give the user the option to just run the first page say if thats all they need?
Reduce the processing time of the report to only that that is necessary?
While trying to configure data source and data set on Report Builder 3.0 I created a table using table wizard. After I run I get the following error message:
"The report server has encountered a configuration error. logon failed for the unattended execution account. (rsServerConfigurationError)"
I checked the SQL Server Configuration Manager and saw the following error when i click on "SQL Server Services" on the left panel."error message: "The Server threw an exception. [0x80010105]"