Technique Demo -> Prepopulate Report Data (Demo is images)

[klincecum (Kevin Lincecum)]

I was looking at these two threads:

Dynamic url logos in reports won't render all of the sudden

So this is fun. We use a url stored in a UD field to dynamically render the correct logo for the report, for example, sales order, invoice, etc. This has been working fine for over a year, until yesterday, when all the sudden nothing will render. All environments and reports are effected, so its not something that was done to the rdl, and no changes have been made to the website or logos themselves. I have a ticket into the cloud team to ask if any changes were made to our report server conf…

Printing site logo on forms

At this time the best method I have successfully used in several Multi-Company Environments is. Create Company_UD.LogoPath_c and since its on the Company table it will easily exist in RDDs. You can also then use it in BarTender, BAQ Reports etc… Add it to Company Config and give the Epicor Service Accounts or All Users Readonly access to the Logo UNC Path. [image] In SSRS / BarTender its then just about referencing the external image. [image] However the Key is that you put a fixed dime…

That got me to thinking about prepopulating data into a BAQ report.
I had a general idea of how it worked, but I dug in a little further so I could document for everyone.

When a report is run, report parameters are stored in the SysTaskParam table for that report run, and you can have the system prepopulate these fields in a BPM.

For a BAQ Report, you can intercept the call in SubmitToAgent, TransformAndSubmit, and RunDirect. Chose one or all that is most relevant to you, the code should be the same.

• SubmitToAgent is the async version mostly (only?) used in classic.
• TransformAndSubmit is the async version of preference for the Kinetic UI.
• RunDirect is the synchronous version, commonly used in custom code to retrieve to run a report directly and grab the data. (Examples everywhere)

Anyway, for the demo, I decided to imagine a scenario where I would want to bring down a common set of images for use in every report. Would you want to do this? I don’t know, but it sounded plausible so that’s what I did.

I wanted to be able to retrieve these images by a key, so the logical thing was to pass down json, as a Dictionary<string, string> object. They key is the “filename”, and the value is Base64 image data.

I put some sample images in Image Maintenance, and made them in a class called “sample”. They were all named “sample1”, “sample2”, etc…

I just gather that data in a pre-processing BPM, and populate the Character01 field in the BAQReportParameter DataSet.

This will get stored and passed to the report for use.

Now this left a little gap in capabilities, because SSRS does not support JSON out of the box. I have a little demo in another thread of how to get SQL to parse it for you, but that was a little overkill for what I wanted to do.

I decided to use VB.Net in the report to grab the data from the dictionary json.
Well with a little “prompt engineering” in ChatGPT, I ended up with a nice little function you could add in the code section of an SSRS report. You can then call this little function and pass it the BAQReportParamter.Character01 value, and the “key” and it will give you back the image data.

=Code.GetDictionaryValue(First(Fields!Character01.Value, "BAQReportParameter"), "sample1")

And

Cool, it works.

I’ll show the code in the next post, but let’s keep in mind that this is a technique demo, and not necessarily a solution to a problem. (although it could be?)

You could use this technique to pass down both generic data like I did, or specific data, depending on the sophistication of your BPM and SSRS abilities and needs.

Let’s discuss!

Edit → Go here for No VB Code (SQL Only)

Technique Demo -> Prepopulate Report Data (Demo is images)

Messing around with this today, found a way to stay in SQL Land with no VB. Add a dataset → Images Use a dataset embedded in my report, chose appropriate. [image] Query → =" DECLARE @json NVARCHAR(MAX) SELECT @json = [Character01] FROM dbo.[BAQReportParameter_" + Parameters!TableGuid.Value + "] SELECT [key] AS FileName, [value] AS Base64Data FROM OPENJSON(@json) " [image] Fields → FileName → FileName Base64Data → Base64Data [image] Add Image to report, chose mime …

[klincecum (Kevin Lincecum)]

Pre-Processing BPM on one or all (depends):

• Ice.Rpt.BAQReport.SubmitToAgent
• Ice.Rpt.BAQReport.TransformAndSubmit
• Ice.Rpt.BAQReport.RunDirect

/*
* ==============================================================================================
* AUTHOR:    Kevin Lincecum
* COPYRIGHT: Kevin Lincecum 2024
* LICENSE:   MIT
* ==============================================================================================
* BPM Location:   Ice.Rpt.BAQReport.SubmitToAgent,
*                 Ice.Rpt.BAQReport.TransformAndSubmit,
*                 Ice.Rpt.BAQReport.RunDirect
*                  
* BPM Type:       Method Directive
* Directive Type: Pre-Processing
* BPM Name:       PrepopulateImageDataDemo
* Description:    Prepopulate image data for BAQ Reports
* Group:          EpiUsersDemos
* ==============================================================================================
* 
* This is intended as a demonstration, and not necessarily a solution to a particular problem.
*
* This demo pulls a list of images and passes the data down to EVERY BAQ Report.
* You could of course modify this to suit your needs.
*
* Initial inspiration:
* https://www.epiusers.help/t/dynamic-url-logos-in-reports-wont-render-all-of-the-sudden/120680
* https://www.epiusers.help/t/printing-site-logo-on-forms/70652/8
*
* CHANGELOG:
* 2024/11/09 | klincecum | Kevin Lincecum | Initial Implementation
*
* ==============================================================================================
*/




  //Get a List<string> of sample images  
  var imageList = Db.Image.Where(x => x.ImageCategoryID == "sample").Select(x => x.ImageFileName).ToList();
  
  //Get the image data for those and put the image data in a Dictionary<string, string>
  var images = Db.FileStore.Where(x => imageList.Contains(x.FileName)).ToDictionary(key => key.FileName, val => Convert.ToBase64String(val.Content));

  //Serialize the data and put it in the 'SysTaskParam' table for the report, in the `Character01` field.
  //This will store the data for the report and be available for use by some VB.Net code to parse it out.
  ds.BAQReportParam.FirstOrDefault().Character01 = JsonConvert.SerializeObject(images);



  //VB.Net Code for report: (with assistance from ChatGPT lol )
  /*

    Public Function GetDictionaryValue(dictionaryString As String, key As String) As String
        ' Construct a regex pattern to match "key" : "value" format, allowing for spaces around the colon
        Dim pattern As String = """" & key & """\s*:\s*""([^""]*)"""
        Dim regex As New System.Text.RegularExpressions.Regex(pattern)
        Dim match As System.Text.RegularExpressions.Match = regex.Match(dictionaryString)
        
        ' Return the matched value or an empty string if the key is not found
        If match.Success Then
            Return match.Groups(1).Value
        Else
            Return ""
        End If
    End Function

  */
  
  
  //Expression for report
  /*
  
    =Code.GetDictionaryValue(First(Fields!Character01.Value, "BAQReportParameter"), "sample1")
  
  */
  
  //Notes
  /*
  
    Make sure you set your MIME type in the report, and chose "Database" as the location instead of "Embedded".
  
  */

Add this to Code section in SSRS report:

Public Function GetDictionaryValue(dictionaryString As String, key As String) As String
    ' Construct a regex pattern to match "key" : "value" format, allowing for spaces around the colon
    Dim pattern As String = """" & key & """\s*:\s*""([^""]*)"""
    Dim regex As New System.Text.RegularExpressions.Regex(pattern)
    Dim match As System.Text.RegularExpressions.Match = regex.Match(dictionaryString)
    
    ' Return the matched value or an empty string if the key is not found
    If match.Success Then
        Return match.Groups(1).Value
    Else
        Return ""
    End If
End Function

Expression for SSRS Report to get the value by key

(Make sure you set your MIME type in the report, and chose “Database” as the location instead of “Embedded”.)

=Code.GetDictionaryValue(First(Fields!Character01.Value, "BAQReportParameter"), "sample1")

Edit → Go here for No VB Code (SQL Only)

Technique Demo -> Prepopulate Report Data (Demo is images)

Messing around with this today, found a way to stay in SQL Land with no VB. Add a dataset → Images Use a dataset embedded in my report, chose appropriate. [image] Query → =" DECLARE @json NVARCHAR(MAX) SELECT @json = [Character01] FROM dbo.[BAQReportParameter_" + Parameters!TableGuid.Value + "] SELECT [key] AS FileName, [value] AS Base64Data FROM OPENJSON(@json) " [image] Fields → FileName → FileName Base64Data → Base64Data [image] Add Image to report, chose mime …

[utaylor (Utah Taylor)]

Love it man, the code section on SSRS is always a wild place to do things- opens it up to a lot of other interesting possibilities… such as this.

Thanks for posting!

[MikeGross]

Awesome - this could change the way we’re doing customer specific shipping docs - thanks!

[dr_dan (Dan Ramirez)]

So as I was getting ready for work this morning, I was day-dreaming about SSRS of all things haha. This was a timely post to find when I opened up my email. Good stuff!

[Mark_Wonsil]

I like how you’re utilizing the Report Parameters to pass the data. Very clean.

[kve (Kevin Veldman)]

That was my first thought! All those SysTaskParams that are 80% empty. I’ve stared at those so many times… It never occurred to me to actually hijack the RunDirect and pass data directly! I’ve got functions where I set the required Params with code, but sticking whatever I want in the extra ones… brilliant.

[klincecum (Kevin Lincecum)]

Messing around with this today, found a way to stay in SQL Land with no VB.

Add a dataset → Images
Use a dataset embedded in my report, chose appropriate.

Query →

="
 DECLARE @json NVARCHAR(MAX)

 SELECT @json = [Character01]
 FROM dbo.[BAQReportParameter_" + Parameters!TableGuid.Value + "]

 SELECT
    [key] AS FileName,
    [value] AS Base64Data
 FROM OPENJSON(@json)
"

Fields →
FileName → FileName
Base64Data → Base64Data

---

Add Image to report, chose mime type.
Expression →

=Lookup("sample1", Fields!FileName.Value, Fields!Base64Data.Value, "Images")

---

Example with 4 Images →

[utaylor (Utah Taylor)]

That’s slick.

[klincecum (Kevin Lincecum)]

BTW, the top post, with the bpms is just a demo. You don’t need bpms to send this data down, that was just to prepopulate generic stuff.

You can always send down report specific stuff in that field, and use the same concepts.

[jbooker (Josh Booker)]

Couldn’t they make all SQL in RDLs be FROM OPENJSON(@json), and replace RDD to temp tables with BAQs to json output?

[klincecum (Kevin Lincecum)]

Probably, but that sounds highly innefficient. I am however extremely glad this exists for the situations where we can make use of it.

[jbooker (Josh Booker)]

hmmm… they are up to something…

[klincecum (Kevin Lincecum)]

I bet they intend to zip it.

[jbooker (Josh Booker)]

but how could it work for local preview of OOB RDL having SQL expressions?

[klincecum (Kevin Lincecum)]

Sometimes I make stuff up in my head.