List of Items in Business Objects

Today one of the Users asked if it was possible to update the list of items that appear in a query list….

For example, the list above shows all months in 2014 .. they only want to see dates in 2013 and 2014.

You can do this using Information Designer.

1) Open the Universe

2) Select the “Parameters List of Values option”

3) Edit the existing list or create a new one.

4) Edit the Query and change the name

5) Add the additional values you want in the list and click OK.

6)

7) Next ensure that the filter/field in the Universe is associated with the correct list

8) Click on “Business Layer” and the field; then click “Advanced”

9) Publish the Universe.

CHM file is blank when opened

Today I was downloading some API documentation which was in CHM format.  The problem is the files where blank when I opened them!

The solution was simple…. Just right click on the file in windows Explorer and click the “Unblock” button on the General tab.

Now when you open the file the document information is shown.

Running an SSRS report against SharePoint

One of the things that comes up again and again is having the ability to run reports against SharePoint Lists. You create the report like you would any other using Visual Studio and deploy it to your sever.

You set the report to run against a list and as a specific user.

Click on the down arrow and selecting the “Manage option”

In the DataSource Tab ensure you are using a custom data source selected with the details set such as URL and Username and Password.

Click “Test Connection” and it will confirm that everything is working correctly

Nintex workflow Inline functions

Found a handy post that lists all the Nintex workflow inline functions. http://connect.nintex.com/forums/permalink/4119/4892/ShowThread.aspx

Compressing PDF files with Nitro PDF

Today I had a problem getting a file sent to to a client. The issue was that the PDF was running to 32Mb in size and the email limits where stoping transport. The solution was to "Optimise" the document.

Open the doc in Nitro PDF; select File / Optimize

Select "Web" and click "Optimize" button .. simple. After a few seconds you end up with a file just under 6Mb!

Problems with date/time formats with Telerik Grid

Today I had a problem with Telerik Grid showing a databound column on the test server, in that crazy US format (no offence to Dave!) 'mm-dd-yyyy' rather than they far more logical format 'dd-mm-yyyy'. This was not the case on the Production server or when running locally on a development PC. Logging on to the server and manually setting the region to "Australia" did not work.

The solution is to check the user account under which the application is running under IIS; then logon to the server using this account and changing the region. After that you'll need to do an IIS reset and all should be well.

Next key violation in Primavera

If you get this error message after copying and pasting a record in Primavera, the next key value may be out of sequence. This may be due to an import issue.

Run this script to find the key errors.
PRINT '====================================================================='
PRINT 'create a script for key errors and show the max key values and next key values'
PRINT '====================================================================='
GO
DECLARE @S NVARCHAR(MAX) = ''
;WITH
key_table_list
AS
(
select key_name, key_seq_num, table_name = LEFT(key_name, CHARINDEX('_',key_name, 0)-1), column_name = RIGHT(key_name, LEN(key_name) - CHARINDEX('_',key_name, 0)) FROM NEXTKEY
)
,
single_script
AS
(
SELECT
    [RowNumber]= ROW_NUMBER() OVER(ORDER BY tl.table_name)
, script = 'SELECT table_name =''' + tl.table_name + ''', max_key_seq_num = MAX(' + tl.column_name + '), key_seq_num = '
+ CAST(tl.key_seq_num AS VARCHAR) + ', KeyError = CASE WHEN MAX(' + tl.column_name + ') > ' + CAST(tl.key_seq_num AS VARCHAR)
+ ' THEN ''EXEC dbo.getnextkeys N''''' + tl.column_name + ''''', '' + CAST(MAX(' + tl.column_name + ') - ' + CAST(tl.key_seq_num AS VARCHAR) + ' + 1 AS VARCHAR) + '', @NewKeyStart OUTPUT'' ELSE NULL END FROM ' + tl.table_name
FROM
    key_table_list tl
INNER JOIN sys.objects ob ON ob.name = tl.table_name
WHERE ob.type = 'U'
)
SELECT @S = @S + CASE WHEN [RowNumber] != 1 THEN ' UNION ' ELSE '' END + script
FROM
single_script
EXEC ('SELECT * FROM (' + @S+ ') AS se WHERE KeyError IS NOT NULL')
GO
table_name
max_key_seq_num
key_seq_num
KeyError
spidmap
539
100
EXEC dbo.getnextkeys N'spid', 440, @NewKeyStart OUTPUT
wbrscat
3
1
EXEC dbo.getnextkeys N'wbrs_cat_id', 3, @NewKeyStart OUTPUT
The 'key_seq_num' should be + 1 greater than the 'max_key_seq_num'. Use the script from 'KeyError' to update the tables as below.
PRINT '====================================================================='
PRINT 'update next key to number of missing increments '
PRINT '====================================================================='
GO
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @NewKeyStart AS INT
EXEC dbo.getnextkeys N'spid', 440, @NewKeyStart OUTPUT
EXEC dbo.getnextkeys N'wbrs_cat_id', 3, @NewKeyStart OUTPUT
ROLLBACK TRANSACTION
--COMMIT TRANSACTION