Archive for July, 2009

In SSRS 2005, you go to Table properties and tick the "Repeat header rows on each page" option under Row Headers. This will do the trick; however, it does not necessarily work in SSRS 2008 especially if you have added grouping on to the tablix, which is very annoying.
In SSRS 2008, under Row Groups – Column Groups pane, click the pointed downward arrow at the corner right then select "Advanced Mode".  For row grouping, under Row Groups, select "Static". In the properties for "Static", set the following:
– KeepTogether = True
– KeepWithGroup = After
– RepeatOnNewPage = True

Read Full Post »

Answer: use self join as described in the following blog:
1 select  ds1.StoreID

    2 ,       ds1.SalesDate

    3 ,       ds1.SalesValue

    4 ,       ds2.SalesDate

    5 ,       ds2.SalesValue

    6 from    Daily_sales ds1

    7 inner   join Daily_sales ds2

    8 on      ds1.StoreID = ds2.StoreID

    9 where   ds2.SalesDate = (

   10                         select    max(ds.SalesDate)

   11                         from      Daily_sales ds

   12                         where     ds.SalesDate < ds1.SalesDate

   13                         and       ds.StoreID = ds1.StoreID

   14                         )

   15 order   by ds1.StoreID

   16 ,       ds1.SalesDate

   17 ;

Read Full Post »

I come across this error very often even though misleading. I would get this error when one of the dtsx packages failed because of the following:
– unclean data such as duplicate key values (you will see this message on the Progress tab)\
– bad logic within the package has occurred.
An example of the bad logic  is shown in the snapshot below. The source view is picking all data regardless if the site is active or inactive but the logic inside the Lookup transform for site has been restricted to only lookup for ACTIVE sites only.  So when the package gets to the rows that belongs to inactive sites, it fails to find the  corresponding lookup Id for the site; hence the package failure.
To resolve this, tick "Use a table or a view" then select the relevant table/view that stores site information, for example core.dm_site. Alternatively, you can also use the existing query but remove the where IsActive = 1 clause.

Read Full Post »

%d bloggers like this: