Hi,
I'm totally new to SSRS2005 and I was wondering if I could extend the existing report items. For instance, I'd like to add a few extra properties to the textbox report item. I tried wrting a custom Textbox control that inherits from System.Windows.Forms.Textbox. When I imported the dll in VS, the new textbox showed up in the toolbox, but it was grayed out.
Any idea what I'm missing here?
Thanks,
Phil
I'm trying to create a report in SSRS using a Matrix.
The data in my dataset looks like this one:
Part Action SortID Count
---------------------------------------------------
Login LA1 1 12
Login LA2 2 25
Login LA3 3 548
Register RA1 1 12
Register RA2 2 56
I have one rowgroup by the column Part and one columngroup by SortID and my Data should look like this:
Login LA1 LA2 LA3
12 25 548
Register RA1 RA2
12 56
But it looks like
Login LA1 LA2 LA3
12 25 548
Register RA1 RA2 LA3
12 56
how I could solve my Problem, that in row number 2 the LA3 isn't shown?
Hi all,
In my cube there is a customer dimension and a product dimension.
If I filter on a single customer, and then click on the filter dropdown for product, all of the products for every customer are shown.
Is it possible to make it so it only show the members for the customer selected?
I suspect the answer is no, as that would mean you can't untick any previously ticked members, but our account management team have got the right hump about them showing so I need to at least attempt it.
Regards,
Phil
I have developed a custom report item that works fine in design and preview mode while in Visual Studio. I cannot get it to show up on my deployed reports. Here's what I have done so far:
1. Deployed the report using Visual Studio
2. updated the rsreportserver.config file with the following entry:
<ReportItems>
<ReportItem Name="PedigreeChart" Type="Uabr.Rap.PedigreeChart.PedigreeChartRenderer, Uabr.Rap.PedigreeChart" />
</ReportItems>
3. Updated the rssrvpolicy.config file with the following entry.
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Description="This code group grants Uabr.Rap.PedigreeChart.dll FUllTrust permission. ">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerinUabr.Rap.PedigreeChart.dll" />
</CodeGroup>
I've also tried using the StrongNameMembershipCondition with no better results.
4. The dll and its dependencies are copied to the bin directory of the report server.
5. When I load a report with this custom report item on it, the report loads fine with no errors or warnings in the log file (even with verbose tracing). The area where the custom item should be is just white. It's almost like Reporting Services isn't registering the item correctly.
This is particularly frustrating because the report works fine in Visual Studio - apparently I configured that correctly. Any suggestions would be greatly appreciated. I'm stumped.
Hi All,
I am having a main report having two subreports, say M1,S1 and S2 respectively.
The issue is S2 normally tend to go beyond one page, for all pages except first page of the of the subreport I am getting the page header and footer blank,
Actually this is not loading the ReportItems that are used in main report but it shows text boxes containing strings for eg . "My Name" and date functions eg Today()
Any Solution?
Thanks and Regards
Pragash
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
I have built a report using the Report Model but want to be able to collapse rows as is possible with the Toggle and Hidden attributes in normal reports. Can this be done?
Thanks
Hi All,
I am using SQL Server 2005 Reporting Services and have successfully created and deployed a Custom Report Item to use instead of the standard Chart Control.
This new Item works fine in both Report Designer (Visual Studio) and when generating Reports on the Report Server
However, I have a requirement that the same chart style must be available in Report Builder. Unfortunately, when using Report Builder to create a new report I can only select Tabular, Matrix or Chart style, and the chart option uses the standard style which is not suitable.
I cannot see any obvious way of including a Custom Report Item within Report Builder, so I have 2 questions :
Can I use a Custom Report Item in Report Builder? None of the samples or tutorials that I have found give any indication of whether this is possible.
Thanks in advance
Paul
Hi,
I'm currently on the task to develop a custom report item to display date values from a data source in a Gantt-chart-alike way.
I took a look at the documentation and the examples (especially [1]) but the documentation I found mainly concentrated on the case of assigning a single value to a custom property. As far as I can see I need to access the data source directly to retrieve the data I'd like to show. In the best case I could retrieve data sets only from a certain data grouping. But that's where I'm stuck. I realize I have to define the CustomData property in the designer component, but I have no clue what to set for DataRowGroupings, DataColumnGroupings, DataRows and so on.
I hope somebody can point me to some documentation about the contents of the CustomData class. The MSDN documentation I found is utterly incomprehensible to me.
Thanks in advance!
[1] http://technet.microsoft.com/en-us/library/ms345254.aspx
We are trying to do something basic with SRS but we can't seem to get iit to work. Here's the scenario: We want to send out a monthly report that consists of numerous subreports. Each subreport stands on its own withh its own data source.
On our main report we drag the subreports into position as we want them to display. But when we run the report sometimes we get 2 subreports on 1 page, other times we get 1.... Bottom line is that we want to get 7-8 subreports on one page. Any ideas on what we can do to get everything on one page?
Hi,
I'm new to visual studio and reporting services. We have report models, reports and data sources on a temporary server that need to get moved to a new report server. I have been able to upload certain reports to the new server but I'm still having problems with other items. What is the easiest way to move over these items to the new server? Is there a way from visual studio to connect to the old server? I want to save the items to my local drive and then upload them from there. Any suggestions will be highly appreciated. Thanks!
Does anybody knows how to reference a value inside a group in a Matrix.
I know it should be possible to use a calculated field, but I can't find a way to calculate a simple percentage!
example: (The Orders Group have "Received" and "Accepted" columns and these are created Dynamically, and I want to add a calculated field (ie. "%Accepted") to the group.
Simple Formula
%Accepted = "Accepted" / "Received"
i.e.
%Accepted = 5/10 :. (50%)
Matrix Object ( creates columns dinamically)
---------------------------------
Orders "Group"
---------------------------------
Received Accepted %Accepted
10 5 ?
Any Help or Ideas are very much appreciated.
Thanks
Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.
I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.
Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.
With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!
My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.
Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)
I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.
My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.
One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.
Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.
Thank you in advance.
SELECT
convert(nvarchar(20), AlertDateTime,120)
AlertDateTime,
AlertName,
AlertPriority,
AlertCount,
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
SrcAddressInt,
SourcePort,
SourcePortName,
convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) % 256)))
DestAddressInt,
DestPortName,
dbo.SensorData1.ObjectName,
SensorName,
SensorInterfaceName,
AlertTypeID,
convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) % 256)))
SensorAddressInt,
ProtocolID,
Cleared,
VulnStatus,
dbo.SensorDataAVP.SensorDataID,
dbo.SensorDataAVP.AttributeName,
dbo.SensorDataAVP.AttributeDataType,
dbo.SensorDataAVP.AttributeText,
dbo.SensorDataAVP.AttributeValue,
dbo.SensorDataAVP.AttributeBlob,
ResponseTypeName,
ResponseName
from
dbo.SensorData
LEFT JOIN
dbo.SensorDataAVP
ON dbo.SensorDataAVP.SensorDataID =
dbo.SensorData1.SensorDataID
LEFT JOIN
dbo.SensorDataResponse
ON dbo.SensorDataResponse.SensorDataID =
dbo.SensorData1.SensorDataID
LEFT JOIN
dbo.ObjectView
ON dbo.ObjectView.ObjectName=
dbo.SensorData1.ObjectName
WHERE
convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDate
AND
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
between @LowerIP and @UpperIP
AND
AlertName = @EventName
I'm not sure if this can be done. There are some threads that say yes and some that say no.
If my report's detail section is hidden unless toggled by group2 is there a way to retain the collapsed entries as collapsed when the report is exp[orted to excel?
Right now when I export to excel the report is all expanded. An export to pdf looks fine but I need to in excel.
I tried this and I must have done something wrong cause it did not work.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2376478&SiteID=1
My orginal report rows are:
Table header
Group1
group2
Details
In details I have the visibilty as hidden, toggle item as group2_item.
I did try creating group3 and set it as the the above thread said.
I'm operating SSRS2000 with SP4.
How do I reference report items (such as textboxes, datasets, tables, etc...) from within a custom assembly?
Thanks!
I just finish my first basic report using Reporting Services and now I have to design a very complicated report.
I would like to know the purpose of tow Report Items : Subreport and Matrix.
If somebody can explain me the purpose and if he/she can point me to an example, it would be great.
Thanks
Hi,
We have created a report chart, a simple bar graph, and need to put 2 vertical lines (target limits) on this chart, At the end we need to export this charts in excel and html, but it says that the overlapping of reports items is not supported in all renderers.
It seems that HTML and Excel donot support the overlapping of this 2 items.
Can anyone please help me in this issue? or is their any other alternative to get the chart and the static lines placed on this charts.
Regards,
Sumeet
I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)
View 5 Replies View RelatedHi,
View 3 Replies View RelatedHi,
I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion,
if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth.
If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using.
Select
laa.PlanId,
laa.LoanId, ( 1-5 values are stored)
los.Name
From
LoansAttriApplied laa
Inner Join LoanOptions los on laa.LoanId = los.LoanId
Where
PlanId = @PlanId
So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get
can I display the data in A,B or 1,2,3 instead of
1
2
3
Can someone please give me some insight into this.
Regards
Karen
I have the following record set; for each group where the code of OP=MTL summarize Cost; that is group 10-10= 300, group 20-20=300, group 30-30=600. Then I need to total groups 10-10 and 30-30.
I used ReportItems fields for each break as;
=SUM(IIF(Fields!OprSeq.Value=10 and Fields!RelatedOperation.Value = 10, Fields!Cost.Value,0), "Related Operation") etc,
But obviously when the result is false the ReportItem returns zero, and now I lost the value calculated previously.
Line OP Mtl COST
01 10 10 100
01 10 10 200
01 20 20 100
01 20 20 200
01 30 30 300
01 30 30 300
I have a project with SQL sever reports in it. During a test upon returning to the VS 2013 Professional interface, the tool box area still had the previous information displayed and never returned to what should be there for the report design tools.I restarted VS, and the toolbox no longer looked like it did before. Specifically, the datatable tool among others was missing. I was unable to "refresh" the datatable after making changes to it.
I opened other Reports in design mode, and the tools did not show up for those either.The Report Items in the toolbox does contain things like Pointer, Text Box, Line and so forth.
How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
Thanks in advance!
I need to sort my tablix report where I have several calculated columns like:
=ReportItems!Textbox47.value+ReportItems!Textbox48.value..
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.
Any help is much appreciated. Thanks.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies View RelatedI am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I have a problem with report built in SSRS and deployed with Dashboard Designer to Sharepoint. There are few filters connected to report, 2 of them are multivalue. Regardless of data returned, when I select too many items in filter, the report is getting super small. It doesn't matter what you select, size changes when you select exact number of items or more. I replaced report with single line (filters where still conected) - result was the same.
Small amount of items selected:
More items selected:
Size of the raport in Dashboard Designer is set to "Percentage of dashboard page", when I selected autosize, result was the same.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
Hello:
How do I go about bringing a report down from the report server to modify it? What kind of security access will I need? The report is in production and it was created by someone who recently left the company.
Thanks in advance for your assistenance.
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
Thanks in advance!
Hi,
I need to export a report created in SQL Server 2005 Reporting Services to one allready existing Excel workbook document. The report is deployed, so it is the deployed report I want to export. As I can see, the option are just to export to different formats, but not to a specific document
Is there an option in Reporting services that can allow me that ? Thanks.
All,
I want to retrive the column names from an existing report. The report is deployed on the report server and i want to write a code which will list all the column names of the reports along with the properties.
Is there any way i can do this. If so , please let me know. A code sample should help.
Regards..
Girija Shankar