שלום לכולם
הפגישה הבאה של ה BIUG תערך ביום רביעי האחרון של חודש אוקטובר, 27.10.
אם מישהו מעוניין להרצות, נא ליצור קשר עם רונן חן או איתי בהקדם.
כמו כן אנחנו רוצים לתת במה לחברות אשר רוצות להציג את המוצרים שלהם. אין לנו שום כוונה להפוך את המפגשים לכנס מכירות. מצד שני, אם חברה מסויימת רוצה להציג מוצר בפורום, נשמח לסייע.
הצגת המוצר תהיה עד 10 דקות. חשוב להתרכז ביכולות של המוצר ואילו בעיות הקשורות לתחום BI המוצר יכול לפתור.
בברכה,
איתי בראון
itay@twingo.co.il
http://www.twingo.com/
For some reason I've encountered some problems uploading the pictures. The attached Doc file contains the text + images.
Last week I was asked about adding comments to the reports.
Let's look on a typical scenario (projected on AdventureWorks)
- There is a report which shows monthly sales
- On July 23rd 2007 the company released new model called "KillerBike"
- Few days later, on August 5th 2007, one of their biggest competitors released new model called "EnemyBike"
- Users want to see these comments when relevant (only when reviewing year 2007)
- Users can disable (logical delete) a comment
First, I've created a simple chart for Monthly sales.
![]()
Create a new table for the comments
The first step is creating a new table for the comments.
I used a simple one. You can also add a column for the color (good things in green, bad events in red). Another advanced option is an expiry date for a comment, so it will disappear automatically after some time.
CREATE TABLE [dbo].[Comments](
[CommentID] [int] NULL,
[InsertDate] [date] NULL,
[CommentDescription] [nvarchar](70) NULL,
[FactTableName] [nvarchar](128) NULL,
[IsEnabled] [int] NULL
) ON [PRIMARY]
GO
Adding new Comment to the Comments Table
One of the problems with SSRS is the limited interactivity. Users can only select parameters and sort columns. So, is it possible to add new comments (or any other data) to a table using SSRS?
The short answer is: No.
The longer answer is: hmm…well…it is possible…technically….but not recommended. What you can do is creating a new report. Add parameters such as CommentName and IsEnabled. Use a stored procedure to write the parameters value to a table. In general speaking, it is absolutely OK to use stored procedures by SSRS reports to change data in the DB. However, in this scenario I think you should develop a simple ASP.NET interface to add new comments.
Adding Labels
Write a query to join the sales data with the relevant Comments
SELECT *, CAST(CalendarYear as CHAR(4)) + '-' + LEFT(EnglishMonthName, 3) as YYYY_Mon
FROM (
select D.CalendarYear, D.MonthNumberOfYear, D.EnglishMonthName, sum(F.ExtendedAmount ) as ExtendedAmount
from dbo.FactInternetSales as F
JOIN dbo.DimDate as D
ON F.OrderDateKey = D.DateKey
Group BY D.CalendarYear, D.MonthNumberOfYear, D.EnglishMonthName
) as F1
LEFT JOIN dbo.Comments as C
ON F1.CalendarYear = YEAR(c.InsertDate)
AND F1.MonthNumberOfYear = Month(C.InsertDate)
ORDER BY F1.CalendarYear, F1.MonthNumberOfYear
Now, add labels to the series of the monthly sales. Right click on the series and select Properties.
Under Label I changed: Visible (to True), BorderColor and BorderStyle (to Solid). The most important property is Label (the source column of the comments)
Now the chart looks almost as it should
What's Missing?
It is still quite hard to understand which comment related to each data point. All I can see is that around July 2007 there was an event of releasing a new model called KillerBike. However, while looking on the chart, one might thing the event took place in June, May or August.
I need a way to link the comment / label to the data point. Can anyone point us to an easy (or complicated) way to do it? (the Position property is not good enough).
Handling two comments or more for each month is also a problem. The second comment disappeared. Apparently, having multiple comments is a big problem too.
One of my customer is using Tersus to build applications "the visual way".
Apparently, Tersus (http://www.tersus.com/#Id=178) is 100% visual, no coding, no scripting. I'm under the impression it is free too (I need to double check that. I couldn't find any pricing tab or evaluation version. Just Download).
However, the quality of the SQL code generated by the application is not so great.
So. My question is: Does anyone actually use Tersus to build real-world application?
Can you please share your experience? Performances, Ease of use? Annoying bugs?
My customer used Tersus mostly for building quickly I-Phone applications (http://www.tersus.com/#Id=3212).