tim laqua dot com Thoughts and Code from Tim Laqua

8Apr/140

jderef.com is dead – Create your own using SSRS and JDE metadata tables

ssrs-jderef-1

Anyone who has used jderef.com is certainly mourning its demise as JDE, like most ERP systems, is a bear to understand without a really big instruction book. Luckily you can chat with your JDE people and, given the correct libraries, modify the following query to get the same information:

SELECT
       RTRIM(TDOBNM)   AS TABLE_NAME,
       RTRIM(d.SIMD)   AS Table_Description,
       RTRIM(d.SIPFX)  AS Table_Prefix,
       RTRIM(TDOBND)   AS Data_Dictionary_Object_Name,
       RTRIM(TDSQLC)   AS SQL_Column_Name,
       RTRIM(b.FRDSCR) AS Column_Description,
       RTRIM(e.DRDL01) AS Field_Type,
       RTRIM(c.FRDTAS) AS Field_Length,
       RTRIM(c.FRDTAD) AS Field_Decimals,
       --RTRIM(c.FROWDI) as DD_Item,
	CASE 
		WHEN RTRIM(c.FROWER) = 'UDC' THEN 'UDC: RTRIM(DRSY)=''' || RTRIM(c.FROER1) || ''' AND RTRIM(DRRT)=''' || RTRIM(c.FROER2) || ''''
		ELSE 'Generic Edit'
	END AS Edit_Type,
       --RTRIM(c.FROWER) as Edit_Rule,
       --RTRIM(c.FROER1) as Edit_Rule_Parm1,
       --RTRIM(c.FROER2) as Edit_Rule_Parm2,
       RTRIM(TDPSEQ)   AS Program_Sequence_Number
FROM
        COPD910.F98711 a
        LEFT OUTER JOIN DD910.F9202 b
                ON a.TDOBND = b.FRDTAI
                   AND FRLNGP = '  '
                   AND FRSYR = '    '
        LEFT OUTER JOIN DD910.F9210 c
                ON a.TDOBND = c.FRDTAI
        LEFT OUTER JOIN OL910.F9860 d
                ON a.TDOBNM = d.SIOBNM
                   AND d.SIFUNO = 'TBLE'
        LEFT OUTER JOIN PRODCTL.F0005 e
                ON LTRIM(RTRIM(c.FROWTP)) = LTRIM(RTRIM(e.DRKY))
                   AND e.DRSY = 'H98'
                   AND e.DRRT = 'DT'
WHERE
        RTRIM(TDOBNM) LIKE ?
ORDER BY
        TDOBNM,
        TDPSEQ

ssrs-jderef-2

Sample RDL can be found here: http://timlaqua.com/jderef.rdl. You will need to change the data source to work in your environment as well as update the following libraries to your environment:

  • COPD910.F98711 - F98711 varies per environment, so make sure you get the right library
  • DD910.F9202 - all the F92 files are in the same library
  • PRODCTL.F0005 - whatever library your UDC table is in
  • OL910.F9860 - the Object Librarian library
Tagged as: , , No Comments
11Jan/121

Hiding SSRS Schedule Jobs In SSMS

Everybody hates these things. If you're in the Activity Monitor or browsing via the SSMS tree view, these GUID jobs that represent SSRS subscriptions are really just none of our concern. Sure, I can admit that I've seen people manually fire these to re-send a given subscription, but you can just do that using the AddEvent proc in a query window. Personally - I don't want to see these... usually...

Connect to the database instance you want to filter the agent jobs out on
Browse to Databases > System Databases > msdb > Programmability > Stored Procedures > System Stored Procedures
Right-click on dbo.sp_help_category and select Modify...

At the top, change the definition of @where_clause to NVARCHAR(MAX)

19
  DECLARE @where_clause   NVARCHAR(max)

At the bottom, add in a few lines to append the @where_clause variable with a predicate that filters out the Report Server category when it's you from your workstation (so you can still see that category from another machine if you need to).

94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
  SELECT @cmd = @cmd + N'FROM msdb.dbo.syscategories '
 
  SET @where_clause += N'
  AND
	CASE
		WHEN 
			name = ''Report Server'' 
			AND (
				SELECT RTRIM(nt_username) + ''|'' + RTRIM(hostname) 
				FROM sys.sysprocesses 
				where spid = @@spid) = ''tlaqua|TIMSLAPTOP''  THEN 0
		ELSE 1
	END = 1 '
 
  -- Execute the query
  EXECUTE (@cmd + @where_clause + N'ORDER BY category_type, name')
 
  RETURN(@@error) -- 0 means success
END

So, what on earth are we doing here? First, replace my nt_username with yours and replace my hostname with yours. From my less-than-exhaustive trial and error testing, it seems that when either the SSMS Jobs node is expanded or the Job Activity Monitor fetches jobs, two DB calls are made - one to fetch categories and another to fetch jobs. I tried filtering out the jobs portion originally and that yielded some errors. So I'm assuming it's trying to marry the categories and the jobs internally, and it expects there to be jobs for each category the first query returned. By not returning the Report Server category at all, the resulting merged list of jobs doesn't contain any jobs belonging to that category (logically an INNER JOIN).

Sure, this is a dirty hack, but I don't mind.

Update (2012-01-11)
Here is the predicate for hiding those jobs from ALL SSMS clients:

96
97
98
99
100
101
102
103
104
105
106
  SET @where_clause += N'
  AND
	CASE
		WHEN 
			name = ''Report Server'' 
			AND (
				SELECT program_name 
				FROM sys.sysprocesses 
				where spid = @@spid) = ''Microsoft SQL Server Management Studio''  THEN 0
		ELSE 1
	END = 1 '
Tagged as: , 1 Comment
19Oct/114

Pattern for Conditionally Sending SSRS Subscriptions

This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.

To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.

First, write a query to return rows meeting that criteria:

Tagged as: , Continue reading
19Oct/110

Script for Ad-Hoc SSRS TimedSubscription Scheduling

Quick script to locate and execute SSRS timed subscriptions (useful for testing).

1. Execute the top (uncommented) part and identify which subscription you want to schedule.
2. Paste the SubscriptionId from that row in to the @id variable in the lower section
3. Run the entire lower (commented) section

DECLARE @ReportName VARCHAR(255) = 'Report Name'
 
select
      a.Path
      ,b.SubscriptionID 
      ,b.Description
      ,CAST(b.Parameters AS XML) AS [Parameters]
      ,b.LastRunTime
      ,b.LastStatus
      ,CAST(b.ExtensionSettings AS XML) AS [ExtensionSettings]
from ReportServer.dbo.[Catalog] a
      INNER JOIN ReportServer.dbo.[Subscriptions] b
            ON b.Report_OID = a.ItemID
where a.Name = @ReportName
 
/*
DECLARE 
       @id varchar(260) = ''
      ,@Type VARCHAR(32)                  = 'TimedSubscription'
 
EXEC [ReportServer].dbo.AddEvent @EventType=@Type, @EventData=@id
 
*/
Tagged as: No Comments
22Apr/110

Troubleshooting Kerberos Configuration for SSRS

I recently worked with Dave DuVarney while he was configuring one of our Reporting Services instances for Kerberos authentication. While working through it, he suggested we take a look at network traffic to see what's actually happening. Turns out, that DuVarney fellow has some clever ideas now and then 😉

We used Network Monitor 3.4 for our tests and really weren't 100% on what we were looking for. Examining traffic over the KerberosV5 protocol, you'll always seem some chatter from the krbtgt as it does whatever it does, but other than that, we just kind of captured and sifted. Here are a few examples of what we saw:

20Apr/110

Reporting Services Source Control 1.0

Reporting Services Source Control is an application to create copies of all the reports stored in your Report Manager and commit them to source control (SVN is the only supported source control in the 1.0 release). The need for this arises for two reasons:

  • First, we empower our users to create reports using Report Builder and those reports usually only exist the ReportServer database.
  • Second, Report Builder has matured so well that it is no longer very beneficial to create a Report Server project using Business Intelligence Development Studio (BIDS).

So now we have intellectual property generated by both end users and developers sitting in Report Manager with no version control whatsoever. Reporting Services Source Control addresses this by scripting out reports, subscriptions, and other properties and committing them to source control.

Tagged as: , , Continue reading
19Apr/111

Monitoring Failed Report Server Subscriptions

While we love to empower our users as much as possible, we still need to pay attention to them and what they're up to. The first place I usually see users running amok as Microsoft BI adoption grows in organization is the Report Subscriptions in SSRS. They go nuts with them. I was at one company where 50% of the report subscriptions went to users outside of the company - that means our customers were depending on these subscriptions! Which brings up an interesting licensing debacle... but that's another story. Needless to say, when people set up a subscription, they expect it to work. If it doesn't, we REALLY need to let someone know.

Here, we have a procedure to monitor for Reporting Services subscription failures. Specifically, email subscription failures. When a failed subscription is detected, an email is sent to both the subscription owner and a digest of failures is sent to the specified admin group.

Tagged as: , , Continue reading
1Oct/090

Reporting Services (SSRS/MSRS) 2008 Error: Set used with the complement operator must have all members from the same level

When you use the Not In operator in a SSRS 2008 MDX query filter to exclude a named set, it uses a the complement operator in the constructed MDX. This is fine as long as "all members [are] from the same level." Since you got this error, they are not 😉 You can get around this by using the Except() MDX function instead of letting SSRS use the Complement operator

In the ReportServerService log, you'll see something like this:
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (..., ...) Set used with the complement operator must have all members from the same level.

Original filter
Dimension: Time
Hierarchy: Calendar Date
Operator: Not In
Filter Expression: [Today]

New filter
Dimension: Time
Hierarchy: Calendar Date
Operator: MDX
Filter Expression: Except([Time].[Calendar Date].[Calendar Date].MEMBERS, [Today])

11Sep/081

Searching for keywords in all Reporting Services (SSRS) reports

During impact analysis for any changes to existing database tables, cube dimensions, cube measures, etc, it's nice to know which reports are going to horribly break before your end-users let you know about it 😉 All of the rdl content for reports uploaded to SSRS is stored in the ReportingServices database in the Content column of the Catalog table (in binary, of course) so here's what I came up with to get the list of soon to be broken reports:

SELECT [Path], ContentText
FROM
(
SELECT [Path], CAST(CAST([content]AS varbinary(8000)) AS VARCHAR(8000)) AS [ContentText]
FROM [catalog] cat WITH(nolock)
WHERE [TYPE]=2
) a
WHERE ContentText LIKE '%ColumnName%' 
	OR ContentText LIKE '%columnname%' 
	OR ContentText LIKE '%MeasureName%' 
	OR ContentText LIKE '%AttributeName%' 
	OR ContentText LIKE '%etc...%'